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

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

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.批量插入数据

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原生的方法实现此功能

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

 

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