java 使用原生JDBC的方式进行批量插入数据

平时在处理批量数据插入的数据库的时候，会用到mybatis自带的批量处理saveBatch方法或者用sql语句的insert（....）values()方法，或者用到foreach，但是有时候效果并不明显，尤其是上万条数据并且需要插入的字段比较多的时候，这些方法比较耗时，效果并不明显。最近在做项目的时候，使用原生JDBC方式处理批量插入，效果比较明显，特意整理出来供大家参考。

@Resource
private DruidDataSource druidDataSource;  

private void batchInsertOriginal(List<FinancialMaterialMonthPurchaseSaleStock> lastList) {
        String sql = "INSERT INTO financial_material_month_purchase_sale_stock (" +
                "org_id, year, month, material_id, warehouse_id," +
                "start_all_qty, start_all_money, order_instock_qty, order_instock_money, order_instock_tax_free_money," +
                "order_return_qty, order_return_money, order_return_tax_free_money, audit_invoice_qty," +
                "audit_invoice_money, audit_invoice_tax_free_money, estimate_qty, estimate_money, estimate_tax_free_money," +
                "last_estimate_qty, last_estimate_tax_free_money, get_material_qty, get_material_money, return_material_qty," +
                "return_material_money, move_out_qty, move_out_money, move_in_qty, move_in_money," +
                "other_instock_qty, other_outstock_qty, other_outstock_money,inventory_adjust_qty, inventory_adjust_money," +
                "surplus_qty,surplus_money, other_instock_money,create_time" +
                ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)";

        Connection conn = DataSourceUtils.getConnection(druidDataSource);

        try (PreparedStatement ps = conn.prepareStatement(sql)) {

            int batchSize = 500;
            int count = 0;

            for (FinancialMaterialMonthPurchaseSaleStock item : lastList) {
                int i = 1;
                ps.setObject(i++, item.getOrgId());
                ps.setObject(i++, item.getYear());
                ps.setObject(i++, item.getMonth());
                ps.setObject(i++, item.getMaterialId());
                ps.setObject(i++, item.getWarehouseId());

                ps.setObject(i++, item.getStartAllQty());
                ps.setObject(i++, item.getStartAllMoney());
                ps.setObject(i++, item.getOrderInstockQty());
                ps.setObject(i++, item.getOrderInstockMoney());
                ps.setObject(i++, item.getOrderInstockTaxFreeMoney());

                ps.setObject(i++, item.getOrderReturnQty());
                ps.setObject(i++, item.getOrderReturnMoney());
                ps.setObject(i++, item.getOrderReturnTaxFreeMoney());
                ps.setObject(i++, item.getAuditInvoiceQty());

                ps.setObject(i++, item.getAuditInvoiceMoney());
                ps.setObject(i++, item.getAuditInvoiceTaxFreeMoney());
                ps.setObject(i++, item.getEstimateQty());
                ps.setObject(i++, item.getEstimateMoney());
                ps.setObject(i++, item.getEstimateTaxFreeMoney());

                ps.setObject(i++, item.getLastEstimateQty());
                ps.setObject(i++, item.getLastEstimateTaxFreeMoney());
                ps.setObject(i++, item.getGetMaterialQty());
                ps.setObject(i++, item.getGetMaterialMoney());
                ps.setObject(i++, item.getReturnMaterialQty());

                ps.setObject(i++, item.getReturnMaterialMoney());
                ps.setObject(i++, item.getMoveOutQty());
                ps.setObject(i++, item.getMoveOutMoney());
                ps.setObject(i++, item.getMoveInQty());
                ps.setObject(i++, item.getMoveInMoney());

                ps.setObject(i++, item.getOtherInstockQty());
                ps.setObject(i++, item.getOtherOutstockQty());
                ps.setObject(i++, item.getOtherOutstockMoney());
                ps.setObject(i++, item.getInventoryAdjustQty());
                ps.setObject(i++, item.getInventoryAdjustMoney());

                ps.setObject(i++, item.getSurplusQty());
                ps.setObject(i++, item.getSurplusMoney());
                ps.setObject(i++, item.getOtherInstockMoney());
                ps.setObject(i++, LocalDateTime.now());
                ps.addBatch();

                if (++count % batchSize == 0) {
                    ps.executeBatch();
                }
            }

            ps.executeBatch();
        } catch (SQLException e) {
            log.error("批量失败！", e);
            throw new BusinessException("批量失败！");
        }
    }

数据库连接获取的方式有多种，还可以通过使用数据库连接地址，用名，密码的打的方式

  Connection conn = DriverManager.getConnection(dataSourceUrl, user, passWord);

使用这种方式特别要注意的一点，在配置数据库连接的时候一定要开启如下配置，开启批量执行 SQL 语句功能，减少数据库交互次数

rewriteBatchedstatements=true

使用以上方法进行批量更新数据

private void updateClientMaterialInfoList(List<SubstrateParams> updateList) throws SQLException {
        Connection conn = DriverManager.getConnection(dataSourceUrl, user, passWord);
        conn.setAutoCommit(false);//关闭自动提交
        String time = CalendarUntil.ToDateString();
        String sql = "UPDATE t_substrate_params set plating_thickness=?,other_requirements=?,thickness_tolerance_requirements=?,purpose=?," +
                "surface_grade=?,surface_treatment=?,width_tolerance_requirements=?,coil_weight=? " +
                "  WHERE material_id=?";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (SubstrateParams clientMaterialInfo : updateList) {//更新数据并清空之前的放大系数字段
                ps.setObject(1, clientMaterialInfo.getPlatingThickness());
                ps.setObject(2, clientMaterialInfo.getOtherRequirements());
                ps.setObject(3, clientMaterialInfo.getThicknessToleranceRequirements());
                ps.setObject(4, clientMaterialInfo.getPurpose());
                ps.setObject(5, clientMaterialInfo.getSurfaceGrade());
                ps.setObject(6, clientMaterialInfo.getSurfaceTreatment());
                ps.setObject(7, clientMaterialInfo.getWidthToleranceRequirements());
                ps.setObject(8, clientMaterialInfo.getCoilWeight());
                ps.setObject(9, clientMaterialInfo.getMaterialId());
                ps.addBatch();//将sql语句打包
            }
            ps.executeBatch();// 将容器中的sql语句提交
            ps.clearBatch();//清空
            conn.commit();//所有语句都执行完毕后才手动提交sql语句
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ps.close();
            conn.close();
        }

    }

使用以上方法处理数据，提升性能比较明显，减少耗时，提高效率。