Fustigador
Reputation: 6459
Get primary key I just inserted with Spring
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
Upvotes: 4
Views: 2970
Answers (2)
JamesENL
Reputation: 6540
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.
Upvotes: 4
Kalyan
Reputation: 1909
Because there is no method in JdbcTemplate with update(String sql, Object[] args, KeyHolder generatedKeyHolder) arguments.
You only have update(PreparedStatementCreator psc, KeyHolder generatedKeyHolder)
Your method call is invoking one of the overloaded method update(String sql, Object[] args, int[] argTypes) which is causing java.sql.SQLException: Invalid argument value:
Upvotes: 2
Related Questions
- Get the last inserted record's primary key
- get auto-genearated key for the inserted record in mybatis
- insert a data and get back ID in spring boot JPA
- Java JDBC Get string primary key value after inserting data in table
- Getting the generated key after SQL Insert with Spring JDBC
- MyBatis 3 + PostgreSQL - get primary key value on insertion
- How to get the last inserted primary key' s value
- Spring MVC Retrieving Database Autogenerated PrimaryKey For Record After INSERT Record
- get primary key of last inserted record with JPA
- How to retrive a generated primary key when a new row is inserted in the oracle database using Spring JDBC?