Sql的Insert操作,因为表的主键一般都设置成自增的原因,相较于update操作,多了对自增id的获取。spring jdbc对insert操作的支持也很完善,不仅在JdbcTemplate的update方法中支持返回主键,更是单独提供SimpleJdbcInsert类帮助使用insert操作。
JdbcTemplate的Insert
spring使用KeyHolder作为自增列返回的持有器,update方法支持PreparedStatementCreator和KeyHolder两个参数,来完成insert操作并返回自增列的值。
@Test
public void testInsertAutoGeneratedKey(){
final String insertSql = "insert into t_wms_goods_stock (warehouse_id, amount) values(?,?)";
// 创建自增key的持有器
KeyHolder keyHolder = new GeneratedKeyHolder();
int insertRow = jdbcTemplate.update(new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
// 获取PreparedStatement,并指定返回自增key
PreparedStatement ps = con.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
ps.setLong(1, 1L);
ps.setDouble(2, 100.25);
return ps;
}
}, keyHolder);
if(insertRow > 0){
// getKey返回单一自增值
System.out.println("auto-generated key:" + keyHolder.getKey());
}
}
KeyHolder的getKey方法返回的是Number类型。
如果是批量insert,也可以支持
@Test
public void testMultiInsertAutoGeneratedKey(){
final String insertSql = "insert into t_wms_goods_stock (warehouse_id, amount) values(?,?),(?,?)";
KeyHolder keyHolder = new GeneratedKeyHolder();
int insertRow = jdbcTemplate.update(new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
PreparedStatement ps = con.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
ps.setLong(1, 1L);
ps.setDouble(2, 100.25);
ps.setLong(3, 2L);
ps.setDouble(4, 122.9);
return ps;
}
}, keyHolder);
if(insertRow > 0){
// getKeyList返回批量自增值
System.out.println("auto-generated key:" + keyHolder.getKeyList());
}
}
但是返回的是List<Map<String, Object>>,map的key是ColumnKey,value就是自增列的值。笔者使用的是mysql,返回的数据如下:
[{GENERATED_KEY=17}, {GENERATED_KEY=18}]
由于mysql一张表里不允许设置两个自增列,是否返回的自增column key名称就是GENERATED_KEY,笔者不能确认,如果有明确知晓的朋友,还请告知。
SimpleJdbcInsert
为了简化insert操作,spring提供了一个简便的,基于流式api的支持表插入的类,就是SimpleJdbcInsert。SimpleJdbcInsert提供三个execute方法执行insert操作
// 普通insert,返回影响行数
public int execute(Map<String, ?> args) {
return doExecute(args);
}
// 有自增id的单条insert
public Number executeAndReturnKey(Map<String, ?> args) {
return doExecuteAndReturnKey(args);
}
// 有自增id的insert,返回KeyHolder
public KeyHolder executeAndReturnKeyHolder(Map<String, ?> args) {
return doExecuteAndReturnKeyHolder(args);
}
先来看普通的insert
@Test
public void testInsertWithMap(){
SimpleJdbcInsert insert = new SimpleJdbcInsert(jdbcTemplate)
.withTableName("t_wms_goods_stock");
Map<String, Object> params = new HashMap<>();
params.put("id", 3);
params.put("warehouse_id", 1);
params.put("amount", 15d);
int insertCount = insert.execute(params);
System.out.println("insertCount:" + insertCount);
}
传入JdbcTemplate构造SimpleJdbcInsert,然后赋予操作的表名,使用map构造insert的column name和对应的column value,调用execute执行,返回影响的行数。简单深入下源码,可以看到底层执行的还是JdbcTemplate的方法。
getJdbcTemplate().update(getInsertString(), values.toArray(), getInsertTypes());
再来看有主键递增的insert
@Test
public void testInsertWithMapAndGeneratedKey(){
SimpleJdbcInsert insert = new SimpleJdbcInsert(jdbcTemplate)
.withTableName("t_wms_goods_stock")
// 设置自增column
.usingGeneratedKeyColumns("id");
Map<String, Object> params = new HashMap<>();
params.put("warehouse_id", 1);
params.put("amount", 32d);
Number newId = insert.executeAndReturnKey(params);
System.out.println("insert new id:" + newId);
}
构造SimpleJdbcInsert时,设置了自增column,然后调用executeAndReturnKey方法,返回Number类型的自增列的值。
对于executeAndReturnKeyHolder方法,由于参数是map方式,并不能支持批量insert。
至于execute的参数,spring还提供除map外的其他方式:SqlParameterSource,并提供两种常用的实现。
MapSqlParameterSource
类似于Map的一种参数
@Test
public void testMapSqlParameterSource(){
SimpleJdbcInsert insert = new SimpleJdbcInsert(jdbcTemplate)
.withTableName("t_wms_goods_stock")
.usingGeneratedKeyColumns("id");
SqlParameterSource parameterSource = new MapSqlParameterSource()
.addValue("warehouse_id", 1l)
.addValue("amount", 23d);
Number newId = insert.executeAndReturnKey(parameterSource);
System.out.println("insert new id:" + newId);
}
BeanPropertySqlParameterSource
传入对于的java对象,映射对象属性名称到数据库表的列名称(将java的驼峰式名称转换成sql的下划线分隔的名称)
public void testBeanPropertySqlParameterSource(){
SimpleJdbcInsert insert = new SimpleJdbcInsert(jdbcTemplate)
.withTableName("t_wms_goods_stock")
.usingGeneratedKeyColumns("id");
SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(new GoodsStock(1l, 23d));
Number newId = insert.executeAndReturnKey(parameterSource);
System.out.println("insert new id:" + newId);
}
此种方式,对于对象属性和表字段名称一致的是非常方便的。