spring JdbcTemplate批量插入以及单个插入时获取id

时间:2025-02-20 20:37:32

1. 批量更新插入

jdbcTemplate.batchUpdate(String sql, List<Object[]> batchArgs)

Object[]数组的长度为每条记录的参数个数(也就是sql语句中"?"的个数)

List: 总共插入多少条记录。

/**
* 批量插入订单详情
*
*/
public void addBatch(List<OrderDetail> orderDetails) {
List<Object[]> params = new ArrayList<>();
for(OrderDetail orderDetail : orderDetails) {
params.add(new Object[]{orderDetail.getProductName(), orderDetail.getProductPrice(),
orderDetail.getProductCount(), orderDetail.getParentid()});
}
final String sql = "insert into order_detail(pro_name,pro_price,pro_count,parent_id) values(?,?,?,?)";
jdbcTemplate.batchUpdate(sql, params); }

2. 单条记录插入获取主键id

/**
* 插入并获取主键
*/
public int addAndGetId(Order order) {
final String sql = "insert into orders(name, address,createtime,totalprice,status) values(?,?,?,?,?)";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setObject(1, order.getName());
ps.setObject(2, order.getAddress());
ps.setObject(3, order.getCreateTime());
ps.setObject(4, order.getTotalPrice());
ps.setObject(5, order.getStatus());
return ps;
}
}, keyHolder);
return keyHolder.getKey().intValue();
}