[java][jdbc] 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 list){
    final List tempBpplist = list;
    String sql = "insert into buy_bean(id,bid,pid,s,datetime,mark,count)" + " values(null,?,?,?,?,?,?)";
    this.getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter()  {
        @Override
        publicint getBatchSize()
        {
            return tempBpplist.size();
        }
        @Override
        publicvoid 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 addProduct(List expList) throws SQLException {
    final List 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 list = new ArrayList();
    while(rs.next()) {
        list.add(rs.getInt(1)); //取得ID
    }
    con.close();
    pstmt.close();
    rs.close();
    return list;
}