使用Spring获取我刚插入的主键

Question

我正在MySQL中的表中插入一行。这是代码：

public final String SQLADDPROJECT="insert into table(value1,value2,value3) values(?,?,?)";
getJdbcTemplate().update(SQLADDPROJECT, new Object[]{model.getValue1, 
            model.getValue2(),
            model.getValue3()});

这个插入语句运行正常，模型不为空。该表有一个自增的主键，我想获取那个主键，以便稍后在代码中使用它。我做了一些搜索，并使用了一个keyHolder，方式如下：

KeyHolder keyHolder = new GeneratedKeyHolder();
public final String SQLADDPROJECT="insert into table(value1,value2,value3) values(?,?,?)";
getJdbcTemplate().update(SQLADDPROJECT, new Object[]{model.getValue1, 
            model.getValue2(),
            model.getValue3()}, keyHolder);
Long pk=keyHolder.getKey().longValue();

但是我收到了一个异常：

java.sql.SQLException: Invalid argument value: java.io.NotSerializableException
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
com.mysql.jdbc.PreparedStatement.setSerializableObject(PreparedStatement.java:3920)
com.mysql.jdbc.PreparedStatement.setObject(PreparedStatement.java:3564)
org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:351)
org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:216)
org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:144)
org.springframework.jdbc.core.ArgPreparedStatementSetter.doSetValue(ArgPreparedStatementSetter.java:65)
org.springframework.jdbc.core.ArgPreparedStatementSetter.setValues(ArgPreparedStatementSetter.java:46)
org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:816)
org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:1)
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)
org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:812)
org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:868)
org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:876)
my.package.Dao.ProyectoDaoImp.saveProject(ProyectoDaoImp.java:54)
my.package.Services.ProyectoServiceImp.saveProject(ProyectoServiceImp.java:25)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:606)
org.springframework.expression.spel.support.ReflectiveMethodExecutor.execute(ReflectiveMethodExecutor.java:69)
org.springframework.expression.spel.ast.MethodReference.getValueInternal(MethodReference.java:109)
org.springframework.expression.spel.ast.CompoundExpression.getValueInternal(CompoundExpression.java:57)
org.springframework.expression.spel.ast.SpelNodeImpl.getTypedValue(SpelNodeImpl.java:102)
org.springframework.expression.spel.standard.SpelExpression.getValue(SpelExpression.java:97)
org.springframework.binding.expression.spel.SpringELExpression.getValue(SpringELExpression.java:84)
org.springframework.webflow.action.EvaluateAction.doExecute(EvaluateAction.java:75)
org.springframework.webflow.action.AbstractAction.execute(AbstractAction.java:188)
org.springframework.webflow.execution.AnnotatedAction.execute(AnnotatedAction.java:145)
org.springframework.webflow.execution.ActionExecutor.execute(ActionExecutor.java:51)
org.springframework.webflow.engine.ActionList.execute(ActionList.java:155)
org.springframework.webflow.engine.State.enter(State.java:193)
org.springframework.webflow.engine.Transition.execute(Transition.java:227)
org.springframework.webflow.engine.impl.FlowExecutionImpl.execute(FlowExecutionImpl.java:393)
org.springframework.webflow.engine.impl.RequestControlContextImpl.execute(RequestControlContextImpl.java:214)
org.springframework.webflow.engine.TransitionableState.handleEvent(TransitionableState.java:119)
org.springframework.webflow.engine.Flow.handleEvent(Flow.java:555)
org.springframework.webflow.engine.impl.FlowExecutionImpl.handleEvent(FlowExecutionImpl.java:388)
org.springframework.webflow.engine.impl.RequestControlContextImpl.handleEvent(RequestControlContextImpl.java:210)
org.springframework.webflow.engine.ViewState.handleEvent(ViewState.java:232)
org.springframework.webflow.engine.ViewState.resume(ViewState.java:196)
org.springframework.webflow.engine.Flow.resume(Flow.java:545)
org.springframework.webflow.engine.impl.FlowExecutionImpl.resume(FlowExecutionImpl.java:258)
org.springframework.webflow.executor.FlowExecutorImpl.resumeExecution(FlowExecutorImpl.java:169)
org.springframework.webflow.mvc.servlet.FlowHandlerAdapter.handle(FlowHandlerAdapter.java:183)
org.springframework.webflow.mvc.servlet.FlowController.handleRequest(FlowController.java:174)
org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
javax.servlet.http.HttpServlet.service(HttpServlet.java:728)

causa raíz

java.io.NotSerializableException: org.springframework.jdbc.support.GeneratedKeyHolder
java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1183)
java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:347)
com.mysql.jdbc.PreparedStatement.setSerializableObject(PreparedStatement.java:3909)
com.mysql.jdbc.PreparedStatement.setObject(PreparedStatement.java:3564)
   org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:351)
org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:216)
org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:144)
org.springframework.jdbc.core.ArgPreparedStatementSetter.doSetValue(ArgPreparedStatementSetter.java:65)
org.springframework.jdbc.core.ArgPreparedStatementSetter.setValues(ArgPreparedStatementSetter.java:46)
org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:816)
org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:1)
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)
org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:812)
org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:868)
org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:876)
my.package.Dao.ProyectoDaoImp.saveProject(ProyectoDaoImp.java:54)
my.package.Services.ProyectoServiceImp.saveProject(ProyectoServiceImp.java:25)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:606)
org.springframework.expression.spel.support.ReflectiveMethodExecutor.execute(ReflectiveMethodExecutor.java:69)
org.springframework.expression.spel.ast.MethodReference.getValueInternal(MethodReference.java:109)
org.springframework.expression.spel.ast.CompoundExpression.getValueInternal(CompoundExpression.java:57)
org.springframework.expression.spel.ast.SpelNodeImpl.getTypedValue(SpelNodeImpl.java:102)
org.springframework.expression.spel.standard.SpelExpression.getValue(SpelExpression.java:97)
org.springframework.binding.expression.spel.SpringELExpression.getValue(SpringELExpression.java:84)
org.springframework.webflow.action.EvaluateAction.doExecute(EvaluateAction.java:75)
org.springframework.webflow.action.AbstractAction.execute(AbstractAction.java:188)
org.springframework.webflow.execution.AnnotatedAction.execute(AnnotatedAction.java:145)
org.springframework.webflow.execution.ActionExecutor.execute(ActionExecutor.java:51)
org.springframework.webflow.engine.ActionList.execute(ActionList.java:155)
org.springframework.webflow.engine.State.enter(State.java:193)
org.springframework.webflow.engine.Transition.execute(Transition.java:227)
org.springframework.webflow.engine.impl.FlowExecutionImpl.execute(FlowExecutionImpl.java:393)
org.springframework.webflow.engine.impl.RequestControlContextImpl.execute(RequestControlContextImpl.java:214)
org.springframework.webflow.engine.TransitionableState.handleEvent(TransitionableState.java:119)
org.springframework.webflow.engine.Flow.handleEvent(Flow.java:555)
org.springframework.webflow.engine.impl.FlowExecutionImpl.handleEvent(FlowExecutionImpl.java:388)
org.springframework.webflow.engine.impl.RequestControlContextImpl.handleEvent(RequestControlContextImpl.java:210)
org.springframework.webflow.engine.ViewState.handleEvent(ViewState.java:232)
org.springframework.webflow.engine.ViewState.resume(ViewState.java:196)
org.springframework.webflow.engine.Flow.resume(Flow.java:545)
org.springframework.webflow.engine.impl.FlowExecutionImpl.resume(FlowExecutionImpl.java:258)
org.springframework.webflow.executor.FlowExecutorImpl.resumeExecution(FlowExecutorImpl.java:169)
org.springframework.webflow.mvc.servlet.FlowHandlerAdapter.handle(FlowHandlerAdapter.java:183)
org.springframework.webflow.mvc.servlet.FlowController.handleRequest(FlowController.java:174)
org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
javax.servlet.http.HttpServlet.service(HttpServlet.java:728)

我能使用KeyHolder这种方式吗？如果不能，我该怎样才能获取我刚插入的主键呢？谢谢。

注：我的类是继承自JdbcDaoSupport的。

- Fustigador

Answer 1

我看到过一些示例使用KeyHolder，但我认为当有一个更简单的方法可用时，那就太麻烦了。今天早上我就是这么做的。您可以使用SimpleJdbcInsert对象，它将返回生成的键。

SimpleJdbcInsert insertContactList = new SimpleJdbcInsert(jdbcTemplate)
            .withTableName("FOO_TABLE").usingColumns("Other", "Columns")
            .usingGeneratedKeyColumns("FooID");

Map<String,Object> insertParameters = new HashMap<String, Object>();
/* 
 * These key pair values must match the columns that you 
 * specified in the .usingColumns() call above. If you don't
 * want to insert any other data, just don't call the .usingColumns method,
 * and insert a blank map.
 */ 
insertParameters.put("Other", foo.getOther());
insertParameters.put("Columns", foo.getColumns());

Number generatedFooId = insertContactList.executeAndReturnKey(insertParameters);
return generatedFooId;

这种方法显然假定你有可用的 JdbcTemplate，并且你的主键是由数据库生成的值。

如果有任何不清楚的地方，请告诉我，我可以扩展我的答案。

- JamesENL

---

我正在尝试它，如果它有效，我会给你反馈。谢谢！ - Fustigador

工作得很好，谢谢！如果有人在他的数据库中使用了任何“BINARY”字段，并将其用作“true”或“false”，如果您执行“insertparameters.put（“Other”，true）;”，它将启动DataTruncationException。您必须执行“insertParameters.put（“Other”，1）”。 - Fustigador

Answer 2

因为在JdbcTemplate中没有使用update(String sql, Object[] args, KeyHolder generatedKeyHolder)参数的方法。

你只有update(PreparedStatementCreator psc, KeyHolder generatedKeyHolder)方法可用。

你的方法调用正在调用重载的update(String sql, Object[] args, int[] argTypes)方法，这会导致java.sql.SQLException: Invalid argument value:错误。

- Kalyan

---

我知道，但是有一个签名为update(String sql, Object... args)的方法，这意味着您可以向该方法添加任意数量的任何类型的参数。我认为将KeyHolder添加到其中可能有效，但实际上并不行。 - Fustigador