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