Spring利用JDBCTemplate实现批量插入和返回id

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;   

}

转载于:https://blog.51cto.com/ciyorecord/2095774