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

public static void batchInsert() throws ClassNotFoundException, SQLException{
        long start = System.currentTimeMillis();
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection(
                        "jdbc:mysql://127.0.0.1:3306/kxh?useServerPrepStmts=false&rewriteBatchedStatements=true",
                        "root", "root");

        connection.setAutoCommit(false);
        PreparedStatement cmd = connection
                .prepareStatement("insert into test1 values(?,?)");

        for (int i = 0; i < 1000000; i++) {//100万条数据
            cmd.setInt(1, i);
            cmd.setString(2, "test");
            cmd.addBatch();
            if(i%1000==0){
                cmd.executeBatch();
            }
        }
        cmd.executeBatch();
        connection.commit();

        cmd.close();
        connection.close();

        long end = System.currentTimeMillis();
        System.out.println("批量插入需要时间:"+(end - start)); //批量插入需要时间:24675
    }

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;  

}