在Spring中用select last_insert_id()时遇到问题

2014-01-25  来源:本站原创  分类:oracle  人气:33 

一直使用的Oracle数据库,通过序列来实现自增字段,插入之前就已经获得了自增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();②获取对应的表自增主键值 }

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脚本:


create table t_post_id(sequence_id int) type = MYISAM;
insert into t_post_id values(0);

由于主键维护表的并发访问量很大,所以最好将其声明为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);

相关文章
  • 在Spring中用select last_insert_id()时遇到问题 2014-01-25

    一直使用的Oracle数据库,通过序列来实现自增字段,插入之前就已经获得了自增id,保存下来即可在后来的操作中使用 今天在使用MySQL时却不知如何处理,插入记录后不知怎样获得刚刚插入的id,查过文档后发现了select last_insert_id(),在插入之后执行此查询,即可获得自增id,喜出望外. 可用到自己的程序中之后却得不到想要的结果,于是就怀疑到了Spring头上,因为通过基本JDBC测试是没有任何问题的,所以就去跟踪Spring JDBC, 看过源码之后才豁然开朗,原来Sprin

  • 解决Hibernate JPA中insert插入数据后自动执行select last_insert_id() 2014-08-28

    这篇文章主要介绍了Hibernate JPA中insert插入数据后自动执行select last_insert_id()的解决方法.需要的朋友可以过来参考下,希望对大家有所帮助 今天做项目遇到了一个问题,是以前没注意的.我用的是Spring MVC+ Hibernate JPA + MySQL数据库.在插入数据后SQL执行日志中会多出一条select语句: Hibernate: insert into click_statstic (logDate, memoId, src, typeId)

  • Spring采用velicity视图时解决Cannot expose session attr... 2013-05-06

    Spring采用velicity视图时,使用@SessionAttributes("")注解出现Cannot expose session attribute 'user' because of an existing model object of the same name异常问题 错误原因: 因为request session默认是不会加入velicity context中的,所以要配置上 exposeRequestAttributes和exposeSessionAttribut

  • Spring的Bean生命周期内的callback方法和Spring容器启动关闭时的callback方法 2014-11-05

    通过Spring管理的bean,在其生命周期内可以配置被Spring调用的callback方法一般有两种:通过实现Spring提供的特点接口和通过配置callback方法.(如果bean是prototype的话,由于其销毁不被Spring管理,其销毁方法不会被调用) 1,通过实现Spring提供的特点接口:通过实现InitializingBean接口,Spring容器会在初始化bean的时候调用该bean的afterPropertiesSet方法,通过实现DisposableBean接口,Spr

  • Spring加载resource时classpath*:与classpath:的区别 2011-12-23

    Spring可以通过指定classpath*:与classpath:前缀加路径的方式从classpath加载文件,如bean的定义文件.classpath*:的出现是为了从多个jar文件中加载相同的文件.classpath:只能加载找到的第一个文件. 比如 resource1.jar中的package 'com.test.rs' 有一个 'jarAppcontext.xml' 文件,内容如下: <bean name="ProcessorImplA" class="com

  • 关于Spring Batch 执行系统命令时进程阻塞的问题 2013-05-21

    Spring Batch提供了SystemCommandTasklet用来执行系统命令.底层使用JDK Runtime.exec()方法,Process.waitFor()来获取运行结果. SystemCommandTasklet部分源码: public RepeatStatus execute(StepContribution contribution, ChunkContext chunkContext) throws Exception { FutureTask<Integer> sys

  • 加速Java应用开发速度1--加速spring/hibernate应用调试时启动速度 2014-02-10

    环境 配置: thinkpad t410 内存:4G内存 CPU:Intel P8700 双核2.53GHZ 系统:WIN XP 开发工具:Intellij IDEA 12.0.4 Maven + spring3.2.3 + hibernate4.2.2+Spring data jpa 1.3.1 未优化前spring容器启动速度: 16890毫秒 =(14609毫秒(ContextLoaderListener加载的)+2281毫秒(Springmvc加载的) 优化后spring容器启动速度:

  • 关于Spring 声明式事务处理时,throws exception不回滚的问题 2013-11-28

    文章转自: 前一段时间,项目代码评审,发现有TX不使用Spring的事务处理,而直接封装方法,手动进行数据的回滚,得悉原因是:抛出异常以后事务不起作 用,没有回滚.这理由顿时让人很无语,不过还算个聪明的TX,知晓另辟蹊径,但是在insert的时候,手动回滚直接delete就可以了,如果是 update,不晓得还会有什么更犀利的办法. 仔细评审代码细节,发现该TX压根没有按照框架原先设计在service层throws BusinessException,而是直接throws Exception.S

  • Spring中用java config简化xml配置 2014-11-02

    个人感觉还是这种配置方式最灵活了. package com.baobaotao.conf; public class LogDao { public void print(){ System.out.println("helloworld"); } } package com.baobaotao.conf; public class UserDao { public void print(){ System.out.println("Helloworld"); }

  • 使用DataTable.Select 方法时,特殊字符的转义方法分享 2014-12-24

    常见的特殊字符 基本都能转义 ,不知道还有没有漏掉的 ,目前使用正常 ,有需要的朋友可以参考一下 public static string Replace(string oldStr) { if (string.IsNullOrEmpty(oldStr)) { return ""; } string str2 = Regex.Replace(oldStr, @"[\[\+\\\|\(\)\^\*\""\]'%~#-&]", delegat

  • MYSQL中获取得最后一条记录的语句 2014-07-14

    MYSQL中获取得最后一条记录的语句,需要的朋友可以参考下. 在MySQL中,使用auto_increment类型的id字段作为表的主键,并用它作为其他表的外键,形成"主从表结构",这是数据库设计中常见的用法.但是在具体生成id的时候,我们的操作顺序一般是:先在主表中插入记录,然后获得自动生成的id,以它为基础插入从表的记录.这里面有个困难,就是插入主表记录后,如何获得它对应的id.通常的做法,是通过"select max(id) from tablename"的做

  • MYSQL中取得最后一条记录 2012-11-17

    在MySQL中,使用auto_increment类型的id字段作为表的主键,并用它作为其他表的外键,形成"主从表结构",这是数据库设计中常见的用法.但是在具体生成id的时候,我们的操作顺序一般是:先在主表中插入记录,然后获得自动生成的id,以它为基础插入从表的记录.这里面有个困难,就是插入主表记录后,如何获得它对应的id.通常的做法,是通过"select max(id) from tablename"的做法,但是显然这种做法需要考虑并发的情况,需要在事务中对主表加以

  • Spring&JDBC杂谈 2012-07-13

    下面的问题都是从以下代码引申而来: this.simpleJdbcOperation.getJdbcOperations().update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection conn) throws SQLException { sql = "DELETE FROM A a WHERE a.name=:name AND a.t

  • 使用MySQL的LAST_INSERT_ID来确定各分表的唯一ID值 2013-10-11

    MySQL数据表结构中,一般情况下,都会定义一个具有'AUTO_INCREMENT'扩展属性的'ID'字段,以确保数据表的每一条记录都可以用这个ID唯一确定 分表除了表名的索引不同之外,表结构都是一样的,如果各表的'ID'字段仍采用'AUTO_INCREMENT'的方式的话,ID就不能唯确定一条记录了. 这时就需要一种处于各个分表之外的机制来生成ID,我们一般采用一张单独的数据表(不妨假设表名为'ticket_mutex')来保存这个ID,无论哪个分表有数据增加时,都是先到ticket_mute

  • Mysql中LAST_INSERT_ID()的函数使用详解 2014-12-07

    从名字可以看出,LAST_INSERT_ID即为最后插入的ID值,有了这个实用的函数,我们可以实现很多问题,下面我们就来深入探讨下. 最近和Sobin在做一个精品课程的项目,因为用到一个固定的id作为表间关联,所以在前一个表插入数据后要把插入数据生成的自增id传递给下一个表.研究了一番决定使用Mysql提供了一个LAST_INSERT_ID()的函数. LAST_INSERT_ID() (with no argument) returns the first automatically gene

  • Spring 事务机制详解 2012-10-21

    Spring事务机制主要包括声明式事务和编程式事务,此处侧重讲解声明式事务,编程式事务在实际开发中得不到广泛使用,仅供学习参考. Spring声明式事务让我们从复杂的事务处理中得到解脱.使得我们再也无需要去处理获得连接.关闭连接.事务提交和回滚等这些操作.再也无需要我们在与事务相关的方法中处理大量的try-catch-finally代码.我们在使用Spring声明式事务时,有一个非常重要的概念就是事务属性.事务属性通常由事务的传播行为,事务的隔离级别,事务的超时值和事务只读标志组成.我们在进行事

  • MyBatis创建实体对象时获取自动生成的主键值 2013-07-03

    例如表结构: CREATE TABLE person ( id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) ); 实体类: public class Person { private long id; private String name; ... } Mapper接口: public interface PersonMapper { void createPerson(Person newPerson); } 则通

  • Spring 的优秀工具类盘点 (转)(存档) 2013-10-23

    文件资源操作 文件资源的操作是应用程序中常见的功能,如当上传一个文件后将其保存在特定目录下,从指定地址加载一个配置文件等等.我们一般使用 JDK 的 I/O 处理类完成这些操作,但对于一般的应用程序来说,JDK 的这些操作类所提供的方法过于底层,直接使用它们进行文件操作不但程序编写复杂而且容易产生错误.相比于 JDK 的 File,Spring 的 Resource 接口(资源概念的描述接口)抽象层面更高且涵盖面更广,Spring 提供了许多方便易用的资源操作工具类,它们大大降低资源操作的复杂度

  • Spring的JNDI数据源连接池配置 2014-03-25

    在使用 Tomcat服务器 + SpringFramework 进行JavaEE项目的开发部署的时候可以在Tomcat的配置文件中进行JDBC数据源的配置,具体步骤如下(这里省略了工程的建立步骤): 1) 添加如下代码到tomcat的conf目录下的server.xml中: Xml代码 <Context> <Resource name="jdbc/demoDB" auth="Container" type="javax.sql.DataS

  • Spring 事务管理高级应用难点剖析: 第 2 部分 2014-07-10

    联合军种作战的混乱 Spring 抽象的 DAO 体系兼容多种数据访问技术,它们各有特色,各有千秋.像 Hibernate 是非常优秀的 ORM 实现方案,但对底层 SQL 的控制不太方便:而 iBatis 则通过模板化技术让您方便地控制 SQL,但没有 Hibernate 那样高的开发效率:自由度最高的当然是直接使用 Spring JDBC 莫属了,但是它也是最底层的,灵活的代价是代码的繁复.很难说哪种数据访问技术是最优秀的,只有在某种特定的场景下,才能给出答案.所以在一个应用中,往往采用多个