Spring利用JDBCTemplate實現批量插入和返回id - langtianya－IT工程師數位筆記本

文章出處

1、先介紹一下java.sql.Connection接口提供的三個在執行插入語句后可取的自動生成的主鍵的方法：

//第一個是 
PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException;   
其中autoGenerateKeys 有兩個可選值：Statement.RETURN_GENERATED_KEYS、Statement.NO_GENERATED_KEYS        
//第二個是
PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException;
//第三個是
PreparedStatement prepareStatement(String sql, String[] columnNames)throws SQLEception;


//批量插入Person實例，返回每條插入記錄的主鍵值
public int[] insert(List<Person> persons) throws SQLException{
    String sql = "insert into test_table(name) values(?)" ;
    int i = 0 ;
    int rowCount = persons.size() ;
    int[] keys = new int[rowCount] ;
    DataSource ds = SimpleDBSource.getDB() ;
    Connection conn = ds.getConnection() ;
    //根據主鍵列名取得自動生成主鍵值
    String[] columnNames= {"id"} ;
    PreparedStatement pstmt = conn.prepareStatement(sql, columnNames) ;
    Person p = null ;
    for (i = 0 ; i < rowCount ; i++){
        p = persons.get(i) ;
        pstmt.setString(1, p.getName()) ;
        pstmt.addBatch();
    }
    pstmt.executeBatch() ;
        //取得自動生成的主鍵值的結果集
    ResultSet rs = pstmt.getGeneratedKeys() ;
    while(rs.next() && i < rowCount){
        keys[i] = rs.getInt(1) ;
        i++ ;
    }
    return keys ;
}

2、下面是Spring的JDBCTemplate實例

插入一條記錄返回剛插入記錄的id

Java代碼


public int addBean(final Bean b){   
           
        final String strSql = "insert into buy(id,c,s,remark,line,cdatetime," +   
                "c_id,a_id,count,type) values(null,?,?,?,?,?,?,?,?,?)";   
        KeyHolder keyHolder = new GeneratedKeyHolder();   
           
        this.getJdbcTemplate().update(   
                new PreparedStatementCreator(){   
                    public java.sql.PreparedStatement createPreparedStatement(Connection conn) throws SQLException{   
                        int i = 0;   
                        java.sql.PreparedStatement ps = conn.prepareStatement(strSql);    
                        ps = conn.prepareStatement(strSql, Statement.RETURN_GENERATED_KEYS);   
                        ps.setString(++i, b.getC());   
                        ps.setInt(++i,b.getS() );   
                        ps.setString(++i,b.getR() );   
                        ps.setString(++i,b.getline() );   
                        ps.setString(++i,b.getCDatetime() );   
                        ps.setInt(++i,b.getCId() );   
                        ps.setInt(++i,b.getAId());   
                        ps.setInt(++i,b.getCount());   
                        ps.setInt(++i,b.getType());   
                        return ps;   
                    }   
                },   
                keyHolder);   
       
        return keyHolder.getKey().intValue();   
    }  

public int addBean(final Bean b){
        
        final String strSql = "insert into buy(id,c,s,remark,line,cdatetime," +
                "c_id,a_id,count,type) values(null,?,?,?,?,?,?,?,?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        
        this.getJdbcTemplate().update(
                new PreparedStatementCreator(){
                    public java.sql.PreparedStatement createPreparedStatement(Connection conn) throws SQLException{
                        int i = 0;
                        java.sql.PreparedStatement ps = conn.prepareStatement(strSql); 
                        ps = conn.prepareStatement(strSql, Statement.RETURN_GENERATED_KEYS);
                        ps.setString(++i, b.getC());
                        ps.setInt(++i,b.getS() );
                        ps.setString(++i,b.getR() );
                        ps.setString(++i,b.getline() );
                        ps.setString(++i,b.getCDatetime() );
                        ps.setInt(++i,b.getCId() );
                        ps.setInt(++i,b.getAId());
                        ps.setInt(++i,b.getCount());
                        ps.setInt(++i,b.getType());
                        return ps;
                    }
                },
                keyHolder);
    
        return keyHolder.getKey().intValue();
    }

 2.批量插入數據

Java代碼
public void addBuyBean(List<BuyBean> list)    
    {    
       final List<BuyBean> tempBpplist = list;    
       String sql="insert into buy_bean(id,bid,pid,s,datetime,mark,count)" +   
            " values(null,?,?,?,?,?,?)";    
       this.getJdbcTemplate().batchUpdate(sql,new BatchPreparedStatementSetter() {   
  
            @Override  
            public int getBatchSize() {   
                 return tempBpplist.size();    
            }   
            @Override  
            public void setValues(PreparedStatement ps, int i)   
                    throws SQLException {   
                  ps.setInt(1, tempBpplist.get(i).getBId());    
                  ps.setInt(2, tempBpplist.get(i).getPId());    
                  ps.setInt(3, tempBpplist.get(i).getS());    
                  ps.setString(4, tempBpplist.get(i).getDatetime());    
                  ps.setString(5, tempBpplist.get(i).getMark());                    
                  ps.setInt(6, tempBpplist.get(i).getCount());   
            }    
      });    
    }  

public void addBuyBean(List<BuyBean> list) 
    { 
       final List<BuyBean> tempBpplist = list; 
       String sql="insert into buy_bean(id,bid,pid,s,datetime,mark,count)" +
               " values(null,?,?,?,?,?,?)"; 
       this.getJdbcTemplate().batchUpdate(sql,new BatchPreparedStatementSetter() {

            @Override
            public int getBatchSize() {
                 return tempBpplist.size(); 
            }
            @Override
            public void setValues(PreparedStatement ps, int i)
                    throws SQLException {
                  ps.setInt(1, tempBpplist.get(i).getBId()); 
                  ps.setInt(2, tempBpplist.get(i).getPId()); 
                  ps.setInt(3, tempBpplist.get(i).getS()); 
                  ps.setString(4, tempBpplist.get(i).getDatetime()); 
                  ps.setString(5, tempBpplist.get(i).getMark());                  
                  ps.setInt(6, tempBpplist.get(i).getCount());
            } 
      }); 
    }

 3.批量插入并返回批量id

注：由于JDBCTemplate不支持批量插入后返回批量id，所以此處使用jdbc原生的方法實現此功能

Java代碼
public List<Integer> addProduct(List<ProductBean> expList) throws SQLException {   
       final List<ProductBean> tempexpList = expList;   
         
       String sql="insert into product(id,s_id,status,datetime,"  
            + " count,o_id,reasons"  
            + " values(null,?,?,?,?,?,?)";   
          
       DbOperation dbOp = new DbOperation();   
       dbOp.init();   
       Connection con = dbOp.getConn();   
       con.setAutoCommit(false);   
       PreparedStatement pstmt = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);   
       for (ProductBean n : tempexpList) {   
           pstmt.setInt(1,n.getSId());      
           pstmt.setInt(2,n.getStatus());    
           pstmt.setString(3,n.getDatetime());    
           pstmt.setInt(4,n.getCount());   
           pstmt.setInt(5,n.getOId());   
           pstmt.setInt(6,n.getReasons());   
           pstmt.addBatch();   
       }   
       pstmt.executeBatch();    
       con.commit();      
       ResultSet rs = pstmt.getGeneratedKeys(); //獲取結果   
       List<Integer> list = new ArrayList<Integer>();    
       while(rs.next()) {   
           list.add(rs.getInt(1));//取得ID   
       }   
       con.close();   
       pstmt.close();   
       rs.close();   
          
       return list;   
          
}