Java JDBC批处理插入数据操作

最新推荐文章于 2023-01-20 15:27:45 发布

echome33

最新推荐文章于 2023-01-20 15:27:45 发布

阅读量1.4k

点赞数

分类专栏： Java

Java 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

PreparedStatement 批量更新,插入数据到Oracle

/**  
 * 更新数据库已有的customer信息  
 * @param List<CustomerBean>  
 * @return   
 */  
public int updateExistsInfo(List<CustomerBean> updateList){  
    //查询的SQL语句  
    String sql = "update t_customer set LICENSE_KEY=?,CORPORATE_NAME=?,INTEGRATED_CLASSIFICATION=?,BOSSHEAD=?," +  
            "CONTACT_PHONE=?,ORDER_FREQUENCY=?,CONTACT_ADDRESS=?,USER_ID=? where CUSTOMER_ID=?" ;      
    //插入需要的数据库对象  
    Connection conn = null;  
    PreparedStatement pstmt = null;  
  
    try  {            
        conn = new DBSource().getConnection();  
        //设置事务属性  
        conn.setAutoCommit(false);    
        pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);              
        for(CustomerBean cbean : updateList){  
            pstmt.setString(1, cbean.getLicense_key());  
            pstmt.setString(2, cbean.getCorporate_name());  
            pstmt.setString(3, cbean.getIntegrated_classification());  
            pstmt.setString(4, cbean.getBosshead());  
            pstmt.setString(5, cbean.getContact_phone());  
            pstmt.setString(6, cbean.getOrder_frequency());  
            pstmt.setString(7, cbean.getContact_address());  
            pstmt.setInt   (8, cbean.getUser_id());  
            pstmt.setInt   (9, cbean.getCustomer_id());  
              
            pstmt.addBatch();  
              
        }  
        int[] tt = pstmt.executeBatch();  
        System.out.println("update : " + tt.length);  
  
        //提交，设置事务初始值  
        conn.commit();  
        conn.setAutoCommit(true);  
  
        //插入成功，返回  
        return tt.length;  
  
    }catch(SQLException ex){  
        try{  
            //提交失败，执行回滚操作  
            conn.rollback();  
  
        }catch (SQLException e) {  
            e.printStackTrace();  
            System.err.println("updateExistsInfo回滚执行失败!!!");  
        }  
  
        ex.printStackTrace();  
        System.err.println("updateExistsInfo执行失败");  
  
        //插入失败返回标志0  
        return 0;  
  
    }finally {  
        try{  
            //关闭资源  
            if(pstmt != null)pstmt.close();  
            if(conn != null)conn.close();  
              
        }catch (SQLException e) {  
            e.printStackTrace();  
            System.err.println("资源关闭失败!!!");  
        }  
    }  
}   
  
/**  
 * 插入数据中没有的customer信息  
 * @param List<CustomerBean>  
 * @return   
 */  
public int insertNewInfo(List<CustomerBean> insertList){  
              
    //查询的SQL语句  
    String sql = "insert into t_customer(CUSTOMER_ID," +  
            "LICENSE_KEY,CORPORATE_NAME,INTEGRATED_CLASSIFICATION,BOSSHEAD,CONTACT_PHONE," +  
            "ORDER_FREQUENCY,CONTACT_ADDRESS,USER_ID,CUSTOMER_NUM,CUSTOMER_CODING," +  
            "INVESTIGATION_TIME,SMS_REC_FLAG,WAP_FLAG,PRICE_GATHERING_FLAG,SOCIETY_STOCK_FLAG," +  
            "REGION_TYPE)" +  
            "VALUES(CUSTOMER.NEXTVAL," +  
            "?,?,?,?,?," +  
            "?,?,?,?,?," +  
            "TO_DATE(?,'YYYY-MM-DD'),?,0,0,0," +  
            "?)" ;  
      
    //插入需要的数据库对象  
    Connection conn = null;  
    PreparedStatement pstmt = null;  
  
    try  {            
        conn = new DBSource().getConnection();  
  
        //设置事务属性  
        conn.setAutoCommit(false);  
          
        pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);              
  
        for(CustomerBean cbean : insertList){  
            pstmt.setString(1, cbean.getLicense_key());  
            pstmt.setString(2, cbean.getCorporate_name());  
            pstmt.setString(3, cbean.getIntegrated_classification());  
            pstmt.setString(4, cbean.getBosshead());  
            pstmt.setString(5, cbean.getContact_phone());  
            pstmt.setString(6, cbean.getOrder_frequency());  
            pstmt.setString(7, cbean.getContact_address());  
            pstmt.setInt(8, cbean.getUser_id());  
            pstmt.setString(9, "gyyc00000");//  
            pstmt.setString(10, "95000000");//  
            pstmt.setString(11, getToday());  
            pstmt.setInt(12, cbean.getSms_rec_flag());  
            pstmt.setInt(13, cbean.getRegion_type());  
              
  
            pstmt.addBatch();  
  
        }  
        int[] tt = pstmt.executeBatch();  
        System.out.println("insert : " + tt.length);  
  
        //提交，设置事务初始值  
        conn.commit();  
        conn.setAutoCommit(true);  
  
        //插入成功，返回  
        return tt.length;  
  
    }catch(SQLException ex){  
        try{  
            //提交失败，执行回滚操作  
            conn.rollback();  
  
        }catch (SQLException e) {  
            e.printStackTrace();  
            System.err.println("insertNewInfo回滚执行失败!!!");  
        }  
  
        ex.printStackTrace();  
        System.err.println("insertNewInfo执行失败");  
  
        //插入失败返回标志0  
        return 0;  
  
    }finally {  
        try{  
            //关闭资源  
            if(pstmt != null)pstmt.close();  
            if(conn != null)conn.close();  
              
        }catch (SQLException e) {  
            e.printStackTrace();  
            System.err.println("资源关闭失败!!!");  
        }  
    }  
}

使用Java JDBC基本的API批量插入数据到数据库中

import java.sql.Connection;
import java.sql.Statement;
//...
Connection connection = new getConnection();
Statement statemenet = connection.createStatement();
for (Employee employee: employees) {
    String query = "insert into employee (name, city) values('"
            + employee.getName() + "','" + employee.getCity + "')";
    statemenet.addBatch(query);
}
statemenet.executeBatch();
statemenet.close();
connection.close();

请注意我们是如何从Employee对象中的数据动态创建查询并在批处理中添加，插入一气呵成。完美！是不是？
等等......你必须思考什么关于SQL注入？这样动态创建的查询SQL注入是很容易的。并且每个插入查询每次都被编译。
为什么不使用PreparedStatement而不是简单的声明。是的，这是个解决方案。下面是SQL注入安全批处理。
SQL Injection Safe Batch - SQL注入安全批处理
思考一下下面代码:

import java.sql.Connection;
import java.sql.PreparedStatement;
 //...
String sql = "insert into employee (name, city, phone) values (?, ?, ?)";
Connection connection = new getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
 for (Employee employee: employees) {
    ps.setString(1, employee.getName());
    ps.setString(2, employee.getCity());
    ps.setString(3, employee.getPhone());
    ps.addBatch();
}
ps.executeBatch();
ps.close();
connection.close();

看看上面的代码。漂亮。我们使用的java.sql.PreparedStatement和在批处理中添加INSERT查询。这是你必须实现批量插入逻辑的解决方案，而不是上述Statement那个。
这一解决方案仍然存在一个问题。考虑这样一个场景，在您想要插入到数据库使用批处理上万条记录。嗯，可能产生的OutOfMemoryError：

java.lang.OutOfMemoryError: Java heap space
com.mysql.jdbc.ServerPreparedStatement$BatchedBindValues.<init>(ServerPreparedStatement.java:72)
com.mysql.jdbc.ServerPreparedStatement.addBatch(ServerPreparedStatement.java:330)
org.apache.commons.dbcp.DelegatingPreparedStatement.addBatch(DelegatingPreparedStatement.java:171)

这是因为你试图在一个批次添加所有语句，并一次插入。最好的办法是将执行分批次。看看下面的解决方案
Smart Insert: Batch within Batch - 智能插入：将整批分批
这是一个简单的解决方案。考虑批量大小为1000，每1000个查询语句为一批插入提交。

String sql = "insert into employee (name, city, phone) values (?, ?, ?)";
Connection connection = new getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
final int batchSize = 1000;
int count = 0;
for (Employee employee: employees) {
    ps.setString(1, employee.getName());
    ps.setString(2, employee.getCity());
    ps.setString(3, employee.getPhone());
    ps.addBatch();
    if(++count % batchSize == 0) {
        ps.executeBatch();
    }
}
ps.executeBatch(); // insert remaining records
ps.close();
connection.close();

这才是理想的解决方案，它避免了SQL注入和内存不足的问题。看看我们如何递增计数器计数，一旦BATCHSIZE 达到 1000，我们调用executeBatch（）提交。