Get primary key I just inserted with Spring
mysql, spring
Solution
I've seen example around where they use a `KeyHolder` but I found that too much bother when there is a much simpler method available. I've literally done it this morning. You use a `SimpleJdbcInsert` object and it will return out the generated key.
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;
This method is obviously assuming that you have a `JdbcTemplate` available, and that your PK is a database generated value.
If any of this is unclear tell me and I can expand my answer.
Problem
Im inserting a row in a table in MySQL. this is the code: ``` public final String SQLADDPROJECT="insert into table(value1,value2,value3) values(?,?,?)"; getJdbcTemplate().update(SQLADDPROJECT, new Object[]{model.getValue1, model.getValue2(), model.getValue3()}); ``` This insert statement works ok, the model is not null. The table has a primary key that is autoincremental, and I would like to get that key, so I can use it later in code. I've done some search, and used a keyHolder, this way: ``` 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(); ``` But Im receiving an exception: ``` 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) ``` Can I use KeyHolder this way? if not, how can I get the pk I just inserted? thank you. Note: My class extends JdbcDaoSupport