JDBC3 中可以直接獲取當(dāng)前插入記錄的 ID 值，具體的調(diào)用方式如下：
Statement stmt = conn.createStatement();
stmt.executeUpdate("INSERT INTO authors (first_name, last_name) values
(′George′, ′Orwell′)", Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getGeneratedKeys();
if ( rs.next() ) ...{
int key = rs.getInt();
}
由于實(shí)際與數(shù)據(jù)庫(kù)交互采用的是 JdbcTemplate，因而需要找到它對(duì)這種方式的支持。經(jīng)過(guò)實(shí)際的查看 Spring 的 API 發(fā)現(xiàn)其本身提供相應(yīng)的方法支持，經(jīng)過(guò)多次的實(shí)驗(yàn)后得到如下的實(shí)現(xiàn)方法：
private void insert(final Profile profile)...{
final String _save = "insert into Newsletter_Profile (user_id, publication_id, last_update) values (?, ?, getdate())";
JdbcTemplate template = this.getJdbcTemplate();
KeyHolder keyHolder = new GeneratedKeyHolder();
template.update(new PreparedStatementCreator() ...{
public PreparedStatement createPreparedStatement(Connection con)
throws SQLException ...{
int i = 0;
PreparedStatement ps = con.prepareStatement(_save,
Statement.RETURN_GENERATED_KEYS);
ps.setInt(++i, profile.getCustomerId().intValue());
ps.setInt(++i, profile.getPublication().getId());
return ps;
}
}, keyHolder);
profile.setId(keyHolder.getKey().intValue());
}
特別需要注意的地方是
Statement.RETURN_GENERATED_KEYS，在使用MS SQL Server 2005 提供的 JDBC Driver 中上面的部分是必須的。之所以這么說(shuō)是因?yàn)?google 出來(lái)的所有資料都是沒(méi)有該部分的，甚至 Spring 自身的 document 中也是沒(méi)有該參數(shù)的。我現(xiàn)在不知道那些代碼是否能夠真正的獲取到 Key，但是現(xiàn)在我 suppose 它們是可以 run 的。
如果沒(méi)有加入
Statement.RETURN_GENERATED_KEYS ，在實(shí)際進(jìn)行數(shù)據(jù)庫(kù)操作時(shí)會(huì)出現(xiàn)如下的異常：
PreparedStatementCallback; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; The statement must be executed before any results can be obtained.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The statement must be executed before any results can be obtained.
caused by : com.microsoft.sqlserver.jdbc.SQLServerException: The statement must be executed before any results can be obtained.
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; The statement must be executed before any results can be obtained.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The statement must be executed before any results can be obtained. Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The statement must be executed before any results can be obtained.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getGeneratedKeys(Unknown Source)
at weblogic.jdbc.wrapper.PreparedStatement_com_microsoft_sqlserver_jdbc_SQLServerPreparedStatement.getGeneratedKeys(Unknown Source)
at org.springframework.jdbc.core.JdbcTemplate$3.doInPreparedStatement(JdbcTemplate.java:772)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:527)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:767)
at com.fdc.reports20.dao.NewsletterDAO.insert(NewsletterDAO.java:179)
at com.fdc.reports20.dao.NewsletterDAO.save(NewsletterDAO.java:153)
at com.fdc.reports20.dao.NewsletterDAO.update(NewsletterDAO.java:138)
at com.fdc.reports20.business.service.user.AlertServiceImpl.updateNewsletter(AlertServiceImpl.java:146)
at com.fdc.reports20.business.service.user.AlertServiceImpl$$FastClassByCGLIB$$52b80fbc.invoke()
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:674)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:154)
at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:52)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)
at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:53)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:615)
at com.fdc.reports20.business.service.user.AlertServiceImpl$$EnhancerByCGLIB$$a12ee5d8.updateNewsletter()
at com.fdc.reports20.web.delegate.AlertBD.updateNewsletter(AlertBD.java:78)
at com.fdc.reports20.web.jpf.um.workbench.WorkBenchController.editPublicationEmails(WorkBenchController.java:149)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)