spring JDBCTemplate 批量插入 返回id 批量插入返回批量id

1.插入一条记录返回刚插入记录的id

Java代码  

1. public int addBean(final Bean b){  
2.           
3.         final String strSql = "insert into buy(id,c,s,remark,line,cdatetime," +  
4.                 "c_id,a_id,count,type) values(null,?,?,?,?,?,?,?,?,?)";  
5.         KeyHolder keyHolder = new GeneratedKeyHolder();  
6.           
7.         this.getJdbcTemplate().update(  
8.                 new PreparedStatementCreator(){  
9.                     public java.sql.PreparedStatement createPreparedStatement(Connection conn) throws SQLException{  
10.                         int i = 0;  
11.                         java.sql.PreparedStatement ps = conn.prepareStatement(strSql);   
12.                         ps = conn.prepareStatement(strSql, Statement.RETURN_GENERATED_KEYS);  
13.                         ps.setString(++i, b.getC());  
14.                         ps.setInt(++i,b.getS() );  
15.                         ps.setString(++i,b.getR() );  
16.                         ps.setString(++i,b.getline() );  
17.                         ps.setString(++i,b.getCDatetime() );  
18.                         ps.setInt(++i,b.getCId() );  
19.                         ps.setInt(++i,b.getAId());  
20.                         ps.setInt(++i,b.getCount());  
21.                         ps.setInt(++i,b.getType());  
22.                         return ps;  
23.                     }  
24.                 },  
25.                 keyHolder);  
26.       
27.         return keyHolder.getKey().intValue();  
28.     }  

 2.批量插入数据

Java代码  

1. public void addBuyBean(List<BuyBean> list)   
2.     {   
3.        final List<BuyBean> tempBpplist = list;   
4.        String sql="insert into buy_bean(id,bid,pid,s,datetime,mark,count)" +  
5.             " values(null,?,?,?,?,?,?)";   
6.        this.getJdbcTemplate().batchUpdate(sql,new BatchPreparedStatementSetter() {  
7.   
8.             @Override  
9.             public int getBatchSize() {  
10.                  return tempBpplist.size();   
11.             }  
12.             @Override  
13.             public void setValues(PreparedStatement ps, int i)  
14.                     throws SQLException {  
15.                   ps.setInt(1, tempBpplist.get(i).getBId());   
16.                   ps.setInt(2, tempBpplist.get(i).getPId());   
17.                   ps.setInt(3, tempBpplist.get(i).getS());   
18.                   ps.setString(4, tempBpplist.get(i).getDatetime());   
19.                   ps.setString(5, tempBpplist.get(i).getMark());                   
20.                   ps.setInt(6, tempBpplist.get(i).getCount());  
21.             }   
22.       });   
23.     }  

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

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

Java代码  

1. public List<Integer> addProduct(List<ProductBean> expList) throws SQLException {  
2.        final List<ProductBean> tempexpList = expList;  
3.         
4.        String sql="insert into product(id,s_id,status,datetime,"  
5.             + " count,o_id,reasons"  
6.             + " values(null,?,?,?,?,?,?)";  
7.          
8.        DbOperation dbOp = new DbOperation();  
9.        dbOp.init();  
10.        Connection con = dbOp.getConn();  
11.        con.setAutoCommit(false);  
12.        PreparedStatement pstmt = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);  
13.        for (ProductBean n : tempexpList) {  
14.            pstmt.setInt(1,n.getSId());     
15.            pstmt.setInt(2,n.getStatus());   
16.            pstmt.setString(3,n.getDatetime());   
17.            pstmt.setInt(4,n.getCount());  
18.            pstmt.setInt(5,n.getOId());  
19.            pstmt.setInt(6,n.getReasons());  
20.            pstmt.addBatch();  
21.        }  
22.        pstmt.executeBatch();   
23.        con.commit();     
24.        ResultSet rs = pstmt.getGeneratedKeys(); //获取结果  
25.        List<Integer> list = new ArrayList<Integer>();   
26.        while(rs.next()) {  
27.            list.add(rs.getInt(1));//取得ID  
28.        }  
29.        con.close();  
30.        pstmt.close();  
31.        rs.close();  
32.          
33.        return list;  
34.          
35. }  

以上三组代码直接复制把对应的实体类名一改就可以直接使用在项目中，希望对大家有帮助

Java代码  

1. public SysTask createNewTask(final SysTask sysTask) {  
2.         KeyHolder keyHolder = new GeneratedKeyHolder();  
3.         jdbcTemplate.update(new PreparedStatementCreator() {  
4.             public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {  
5.                 String[] str = new String[] {"task_type","domain_id","machine_id","status"};  
6.                 Object[] obj = new Object[]{sysTask.getTaskType(),sysTask.getDomainId(),sysTask.getMachineId(),sysTask.getStatus()};  
7.                 PreparedStatement ps = conn.prepareStatement("insert into sys_task (task_type,domain_id,machine_id,status,publish_time) values(?,?,?,?,now())",  
8.                     str  
9.                 );  
10.                 for (int i = 0; i < obj.length; i++) {  
11.                     ps.setObject(i+1, obj[i]);  
12.                 }  
13.                 return ps;  
14.             }  
15.         }, keyHolder);  
16.         int taskId = keyHolder.getKey().intValue();//返回taskId  
17.         return getTaskById(taskId);  
18.     }