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; }