在Spring中用select last_insert_id()时遇到问题
程序员文章站
2023-12-06 08:03:34
今天在使用MySQL时却不知如何处理，插入记录后不知怎样获得刚刚插入的id，查过文档后发现了select last_insert_id()，在插入之后执行此查询，即可获得自增id，喜出望外。可用到...
今天在使用MySQL时却不知如何处理，插入记录后不知怎样获得刚刚插入的id，查过文档后发现了select last_insert_id()，在插入之后执行此查询，即可获得自增id，喜出望外。
可用到自己的程序中之后却得不到想要的结果，于是就怀疑到了Spring头上，因为通过基本JDBC测试是没有任何问题的，所以就去跟踪Spring JDBC, 看过源码之后才豁然开朗，原来Spring中如此获得数据库Connection的：Connection con = DataSourceUtils.getConnection(getDataSource());, 哎，只能怪自己Spring掌握不够好，所以就不能在执行insert之后去执行select last_insert_id()了，因为select last_insert_id()是真对当前Connection插入和更新操作的，那在Spring中如何获得新增记录的键值呢，没有办法只能去Google了。
发现了如下文章，来自IT168，标题为《Spring应用数据主键的生成策略盘点》，摘录如下：
在一般情况下，在新增领域对象后，都需要获取对应的主键值。使用应用层来维护主键，在一定程度上有利于程序性能的优化和应用移植性的提高。在采用数据库自增主键的方案里，如果JDBC驱动不能绑定新增记录对应的主键，就需要手工执行查询语句以获取对应的主键值，对于高并发的系统，这很容易返回错误的主键。通过带缓存的DataFieldMaxValueIncrementer，可以一次获取批量的主键值，供多次插入领域对象时使用，它的执行性能是很高的。
使用数据库的自增主键
我们经常使用数据的自增字段作为表主键，也即主键值不在应用层产生，而是在新增记录时，由数据库产生。这样，应用层在保存对象前并不知道对象主键值，而必须在保存数据后才能从数据库中返回主键值。在很多情况下，我们需要获取新对象持久化后的主键值。在Hibernate等ORM框架，新对象持久化后，Hibernate会自动将主键值绑定到对象上，给程序的开发带来了很多方便。
在JDBC 3.0规范中，当新增记录时，允许将数据库自动产生的主键值绑定到Statement或PreparedStatement中。使用Statement时，可以通过以下方法绑定主键值：
int executeUpdate(String sql,int autoGeneratedKeys)
也可以通过Connection创建绑定自增值的PreparedStatement：
PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)
当autoGeneratedKeys参数设置为Statement.RETURN_GENERATED_KEYS值时即可绑定数据库产生的主键值，设置为Statement.NO_GENERATED_KEYS时，不绑定主键值。下面的代码演示了Statement绑定并获取数据库产生的主键值的过程：
Statement stmt = conn.createStatement(); String sql = "INSERT INTO t_topic(topic_title,user_id) VALUES(‘测试主题','123') "; stmt.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS); ①指定绑定表自增主键值 ResultSet rs = stmt.getGeneratedKeys(); if ( rs.next() ) { int key = rs.getInt();②获取对应的表自增主键值 }
如果数据库的并发率很高，比如在插入记录后执行查询主键之前，数据库又执行了若干条插入记录的SQL语句，这时，通过表 1 返回的主键值就是最后一条插入语句的主键值，而非我们希望的主键值了。所以使用查询语句获取表自增键值是不安全的，这也是为什么有些数据库（如 Oracle、Firebird）故意不提供自增键，而只提供序列的原因，序列强制要求你在新增记录前，先获取主键值。Oracle通过SELECT <SEQUENCE_NAME>.nextval FROM DUAL获取序列的下一个值，而FireBird通过SELECT GEN_ID(<SEQUENCE_NAME> 1) FROM RDB$DATABASE获取序列的下一个值。在10.4.1小节中，我们还将讲解应用层自增键的相关知识。
应用层产生主键
Spring JDBC提供了自增键以及行集的支持，自增键对象让我们可以不依赖数据库的自增键，在应用层为新记录提供主键值。在JDK 1.4中引入了RowSet，它允许在连接断开的情况下操作数据，在这节里，我们将介绍如何在Spring JDBC中使用RowSet。
自增键的使用
一般数据库都提供了自增键的功能，如MySql的auto_increment、SqlServerr的identity字段等。Spring允许你在应用层产生主键值，为此定义了 org.springframework.jdbc.support.incrementer.DataFieldMaxValueIncrementer 接口，提供两种产生主键的方案：第一，通过序列产生主键；第二，通过表产生主键。根据主键产生方式和数据库的不同，Spring提供了若干实现类，如图 1所示：
图 1 DateFieldValueIncrementer继承类图
根据不同的主键产生方式，可能需要配置表名、主键字段名或序列名等信息。下面，我们以Oracle和MySql为例分别讲解使用序列及表字段产生主键值的方式。
DataFieldMaxValueIncrementer接口定义了3个获取下一个主键值的方法：
int nextIntValue()：获取下一个主键值，主键数据类型为int；
long nextLongValue()：获取下一个主键值，主键数据类型为long；
String nextStringValue()：获取下一个主键值，主键数据类型为String；
在其抽象实现类AbstractDataFieldMaxValueIncrementer中，提供了几个重要的属性，其中 incrementerName定义序列或主键表的名称；如果返回的主键是String类型，则paddingLength属性可能会派上用场，它允许你指定返回主键的长度，不足的部分前面补0。
HsqlMaxValueIncrementer和MySQLMaxValueIncrementer两个主键值产生器基于表进行工作。通过 columnName属性定义主键列的名字，通过cacheSize属性定义缓存的主键个数，当内存中的主键值用完后，产生器将一次性获取 cacheSize个主键，这样可以减少数据访问的次数，提高应用的性能。
我们通过DateFieldValueIncrementer从数据库中获取主键值来弥补这个缺陷。首先，调整PostJdbcDao的代码，添加DateFieldValueIncrementer属性，并通过它从序列中得到下一个主键值：
代码清单 13 使用DateFieldValueIncrementer产生主键
在②处，我们通过incre.nextIntValue()获取下一个主键值。
以序列方式产生主键值
在Oracle数据库中创建一个seq_post_id序列，使用这个序列为t_post提供主键值，以下是创建seq_post_id的脚本：
create table t_post_id(sequence_id int) type = MYISAM;
insert into t_post_id values(0);
小结
主键的生产方式从产生地点上可以分为应用层产生和数据库产生两种方式。应用层借助数据库的序列或表产生主键，这种方式可以保证程序的可移植性和安全性，同时可以通过缓存机制提高运行效率。有些数据库支持数据表自增键的主键产生机制，在JDBC 3.0以前的版本中，无法通过Statement自动获取新增记录的对应主键。这时需要在插入数据后，马上执行一条数据库相关的主键获取SQL语句以得到对应的主键值，在数据库高并发的情况下，有可能获取到不正确的主键值。在这种情况下，在插入数据前事先在应用层准备好主键值是很好的备选方案。
另外补充一点在SqlUpdate执行update之前需设置setReturnGeneratedKeys(true);
可用到自己的程序中之后却得不到想要的结果，于是就怀疑到了Spring头上，因为通过基本JDBC测试是没有任何问题的，所以就去跟踪Spring JDBC, 看过源码之后才豁然开朗，原来Spring中如此获得数据库Connection的：Connection con = DataSourceUtils.getConnection(getDataSource());, 哎，只能怪自己Spring掌握不够好，所以就不能在执行insert之后去执行select last_insert_id()了，因为select last_insert_id()是真对当前Connection插入和更新操作的，那在Spring中如何获得新增记录的键值呢，没有办法只能去Google了。
发现了如下文章，来自IT168，标题为《Spring应用数据主键的生成策略盘点》，摘录如下：
在一般情况下，在新增领域对象后，都需要获取对应的主键值。使用应用层来维护主键，在一定程度上有利于程序性能的优化和应用移植性的提高。在采用数据库自增主键的方案里，如果JDBC驱动不能绑定新增记录对应的主键，就需要手工执行查询语句以获取对应的主键值，对于高并发的系统，这很容易返回错误的主键。通过带缓存的DataFieldMaxValueIncrementer，可以一次获取批量的主键值，供多次插入领域对象时使用，它的执行性能是很高的。
使用数据库的自增主键
我们经常使用数据的自增字段作为表主键，也即主键值不在应用层产生，而是在新增记录时，由数据库产生。这样，应用层在保存对象前并不知道对象主键值，而必须在保存数据后才能从数据库中返回主键值。在很多情况下，我们需要获取新对象持久化后的主键值。在Hibernate等ORM框架，新对象持久化后，Hibernate会自动将主键值绑定到对象上，给程序的开发带来了很多方便。
在JDBC 3.0规范中，当新增记录时，允许将数据库自动产生的主键值绑定到Statement或PreparedStatement中。使用Statement时，可以通过以下方法绑定主键值：
int executeUpdate(String sql,int autoGeneratedKeys)
也可以通过Connection创建绑定自增值的PreparedStatement：
PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)
当autoGeneratedKeys参数设置为Statement.RETURN_GENERATED_KEYS值时即可绑定数据库产生的主键值，设置为Statement.NO_GENERATED_KEYS时，不绑定主键值。下面的代码演示了Statement绑定并获取数据库产生的主键值的过程：
Statement stmt = conn.createStatement(); String sql = "INSERT INTO t_topic(topic_title,user_id) VALUES(‘测试主题','123') "; stmt.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS); ①指定绑定表自增主键值 ResultSet rs = stmt.getGeneratedKeys(); if ( rs.next() ) { int key = rs.getInt();②获取对应的表自增主键值 }
Spring利用这一技术，提供了一个可以返回新增记录对应主键值的方法：
int update(PreparedStatementCreator psc, KeyHolder generatedKeyHolder)
org.springframework.jdbc.support.KeyHolder是一个回调接口，Spring使用它保存新增记录对应的主键，该接口的接口方法描述如下：
Number getKey() throws InvalidDataAccessApiUsageException
当 仅插入一行数据，主键不是复合键且是数字类型时，通过该方法可以直接返回新的主键值。如果是复合主键，或者有多个主键返回时，该方法抛出 InvalidDataAccessApiUsageException。该方法是最常用的方法，因为一般情况下，我们一次仅插入一条数据并且主键字段类型为数字类型；
Map getKeys() throws InvalidDataAccessApiUsageException
如果是复合主键，则列名和列值构成Map中的一个Entry。如果返回的是多个主键，则该方法抛出InvalidDataAccessApiUsageException异常；
List getKeyList()：
如果返回多个主键，即PreparedStatement新增了多条记录，则每一个主键对应一个Map，多个Map构成一个List。
Spring为KeyHolder接口指代了一个通用的实现类GeneratedKeyHolder，该类返回新增记录时的自增长主键值。假设我们希望在新增论坛板块对象后，希望将主键值加载到对象中，则可以按以下代码进行调整：
public void addForum(final Forum forum) { final String sql = "INSERT INTO t_forum(forum_name,forum_desc) VALUES(?,?)"; KeyHolder keyHolder = new GeneratedKeyHolder();①创建一个主键执有者 getJdbcTemplate().update(new PreparedStatementCreator(){ public PreparedStatement createPreparedStatement(Connection conn) throws SQLException { PreparedStatement ps = conn.prepareStatement(sql); ps.setString(1, forum.getForumName()); ps.setString(2, forum.getForumDesc()); return ps; } },keyHolder); forum.setForumId(keyHolder.getKey().intValue());②从主键执有者中获取主键 }
这样，在调用addForum(final Forum forum)新增forum领域对象后，forum将拥有对应的主键值，方便后继的使用。
在JDBC 3.0之前的版本中，PreparedStatement不能绑定主键，如果采用表自增键（如MySql的auto increment或SqlServer的identity）将给获取正确的主键值带来挑战——因为你必须在插入数据后，马上执行另一条获取新增主键的查询语句。表 1给出了不同数据库获取最新自增主键值的查询语句：
表 1 不同数据库获取新增加的主键值
数据库 获取新增主键的查询语句 DB2 IDENTITY_VAL_LOCAL() Informix SELECT dbinfo('sqlca.sqlerrd1') FROM <TABLE> Sybase SELECT @@IDENTITY SqlServer SELECT SCOPE_IDENTITY()或SELECT @@IDENTITY MySql SELECT LAST_INSERT_ID() HsqlDB CALL IDENTITY() Cloudscape IDENTITY_VAL_LOCAL() Derby IDENTITY_VAL_LOCAL() PostgreSQL SELECT nextval('<TABLE>_SEQ')
如果数据库的并发率很高，比如在插入记录后执行查询主键之前，数据库又执行了若干条插入记录的SQL语句，这时，通过表 1 返回的主键值就是最后一条插入语句的主键值，而非我们希望的主键值了。所以使用查询语句获取表自增键值是不安全的，这也是为什么有些数据库（如 Oracle、Firebird）故意不提供自增键，而只提供序列的原因，序列强制要求你在新增记录前，先获取主键值。Oracle通过SELECT <SEQUENCE_NAME>.nextval FROM DUAL获取序列的下一个值，而FireBird通过SELECT GEN_ID(<SEQUENCE_NAME> 1) FROM RDB$DATABASE获取序列的下一个值。在10.4.1小节中，我们还将讲解应用层自增键的相关知识。
应用层产生主键
Spring JDBC提供了自增键以及行集的支持，自增键对象让我们可以不依赖数据库的自增键，在应用层为新记录提供主键值。在JDK 1.4中引入了RowSet，它允许在连接断开的情况下操作数据，在这节里，我们将介绍如何在Spring JDBC中使用RowSet。
自增键的使用
一般数据库都提供了自增键的功能，如MySql的auto_increment、SqlServerr的identity字段等。Spring允许你在应用层产生主键值，为此定义了 org.springframework.jdbc.support.incrementer.DataFieldMaxValueIncrementer 接口，提供两种产生主键的方案：第一，通过序列产生主键；第二，通过表产生主键。根据主键产生方式和数据库的不同，Spring提供了若干实现类，如图 1所示：
图 1 DateFieldValueIncrementer继承类图
根据不同的主键产生方式，可能需要配置表名、主键字段名或序列名等信息。下面，我们以Oracle和MySql为例分别讲解使用序列及表字段产生主键值的方式。
DataFieldMaxValueIncrementer接口定义了3个获取下一个主键值的方法：
int nextIntValue()：获取下一个主键值，主键数据类型为int；
long nextLongValue()：获取下一个主键值，主键数据类型为long；
String nextStringValue()：获取下一个主键值，主键数据类型为String；
在其抽象实现类AbstractDataFieldMaxValueIncrementer中，提供了几个重要的属性，其中 incrementerName定义序列或主键表的名称；如果返回的主键是String类型，则paddingLength属性可能会派上用场，它允许你指定返回主键的长度，不足的部分前面补0。
HsqlMaxValueIncrementer和MySQLMaxValueIncrementer两个主键值产生器基于表进行工作。通过 columnName属性定义主键列的名字，通过cacheSize属性定义缓存的主键个数，当内存中的主键值用完后，产生器将一次性获取 cacheSize个主键，这样可以减少数据访问的次数，提高应用的性能。
我们通过DateFieldValueIncrementer从数据库中获取主键值来弥补这个缺陷。首先，调整PostJdbcDao的代码，添加DateFieldValueIncrementer属性，并通过它从序列中得到下一个主键值：
代码清单 13 使用DateFieldValueIncrementer产生主键
public class PostJdbcDao extends JdbcDaoSupport implements PostDao { private DataFieldMaxValueIncrementer incre; ①主键值产生器 public void addPost(final Post post) { … getJdbcTemplate().execute( sql,new AbstractLobCreatingPreparedStatementCallback( this.lobHandler) { protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException { ps.setInt(1, incre.nextIntValue());②获取下一个主键值 … } }); } …//省略get/setter方法 }
在②处，我们通过incre.nextIntValue()获取下一个主键值。
以序列方式产生主键值
在Oracle数据库中创建一个seq_post_id序列，使用这个序列为t_post提供主键值，以下是创建seq_post_id的脚本：
create sequence seq_post_id increment by 1start with 1;
接着，调整Spring的配置，使用OracleSequenceMaxValueIncrementer作为主键产生器：
<bean id="incre" class="org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer"><property name="incrementerName" value="seq_post_id"/> ①指定序列名 <property name="dataSource" ref="dataSource"/> ②设置数据源 </bean><bean id="postDao" parent="dao" class="com.baobaotao.dao.jdbc.PostJdbcDao"><property name="lobHandler" ref="oracleLobHandler"/><property name="incre" ref="incre"/> ③添加主键主键产生器 </bean>
以表方式产生主键值
在Mysql中创建一张用于维护t_post主键的t_post_id表，以下是创建该表及插入初始化的SQL脚本：
由于主键维护表的并发访问量很大，所以最好将其声明为MYISAM类型，此外需要为该表提供初始值，以便后续主键值在此之上进行递增。
调整为MySql数据库后，我们仅需要对Spring配置进行小小的调整就可以了：
<bean id="incre"class="org.springframework.jdbc.support.incrementer.MySQLMaxValueIncrementer"><property name="incrementerName" value="t_post_id"/> ①设置维护主键的表名 <property name="columnName" value="sequence_id"/>②用于生成主键值的列名 <property name="cacheSize" value="10"/> ③缓存大小 <property name="dataSource" ref="dataSource"/></bean><bean id="postDao" parent="dao" class="com.baobaotao.dao.jdbc.PostJdbcDao"><property name="lobHandler" ref="defaultLobHandler"/><property name="incre" ref="incre"/></bean> incrementerName和columnName都很容易理解，cacheSize决定一次返回的主键个数，这里我们设置为10。当第一次通过 MySQLMaxValueIncrementer# nextIntValue()获取主键值时，MySQLMaxValueIncrementer将使t_post_id. sequence_id递增10，而后续9次调用nextIntValue()方法时，都从缓存中获取主键值。直到第10次再次调用 nextIntValue()方法时，才会再次将t_post_id. sequence_id字段值递增10，如此循环反复。
小结
主键的生产方式从产生地点上可以分为应用层产生和数据库产生两种方式。应用层借助数据库的序列或表产生主键，这种方式可以保证程序的可移植性和安全性，同时可以通过缓存机制提高运行效率。有些数据库支持数据表自增键的主键产生机制，在JDBC 3.0以前的版本中，无法通过Statement自动获取新增记录的对应主键。这时需要在插入数据后，马上执行一条数据库相关的主键获取SQL语句以得到对应的主键值，在数据库高并发的情况下，有可能获取到不正确的主键值。在这种情况下，在插入数据前事先在应用层准备好主键值是很好的备选方案。
另外补充一点在SqlUpdate执行update之前需设置setReturnGeneratedKeys(true);
上一篇： Oracle 的入门心得 强烈推荐