Spring MVC using GeneratedKeyHolder() – java.io.NotSerializableException-Collection of common programming errors

11 years ago
admin
7 minutes

I am trying to preform an insert into an mysql database using jdbcTemplate and GeneratedKeyHolder() for retrieving autogenerated PK colum value. However i am getting an exception. If i remove keyHolder parameter the record inserts perfectly however i am not able to get the autogenerated PK sine keyHolder was removed. The class Crime implements Serializable. Under is the code:

How do i structure the statement so i can receive the keyHolder value ?

DAO

KeyHolder keyHolder = new GeneratedKeyHolder();
logger.debug("In saveCrime");
logger.debug("In saveCrime");
sql = "INSERT INTO crimeTrack.tblcrimes (crimeDetails, " +
    "crimeLatLocation,crimeLongLocation, crimeStreetLn1, crimeStreetLn2, crimeDate, "+
    "recordedById,statusId, offenceCatId, offenceTypeId, crimeLevelId, investigatingOfficerId,crimeTime) " +
    "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";

int save = getJdbcTemplate().update(new PreparedStatementCreator(){

    public java.sql.PreparedStatement createPreparedStatement(
        java.sql.Connection connection) throws SQLException {
            PreparedStatement ps =(PreparedStatement) connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, crime.getCrimeDetails());
            ps.setFloat(2, (float)crime.getCrimeLatLocation());
            ps.setFloat(3, (float)crime.getCrimeLongLocation());
            ps.setString(4, crime.getCrimeStreetLn1());
            ps.setString(5, crime.getCrimeStreetLn2());
            ps.setString(6,  crime.getCrimeDate());
            ps.setString(7, "sim");
            ps.setInt(8, crime.getStatusId());
            ps.setInt(9, crime.getOffenceCatId());
            ps.setInt(10, crime.getOffenceTypeId());
            ps.setInt(11, crime.getCrimeLevelId());
            ps.setString(12, crime.getInvestigatingOfficerId());
            ps.setString(13, crime.getCrimeTime()); 

            return ps;
        }
    },keyHolder);

Error Log

41734 [http-bio-8084-exec-4] DEBUG org.springframework.jdbc.support.SQLErrorCodesFactory  - SQL error codes for 'MySQL' found
41734 [http-bio-8084-exec-4] DEBUG org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator  - Unable to translate SQLException with Error code '0', will now try the fallback translator
41734 [http-bio-8084-exec-4] DEBUG org.springframework.jdbc.support.SQLStateSQLExceptionTranslator  - Extracted SQL state class 'S1' from value 'S1009'
41735 [http-bio-8084-exec-4] DEBUG org.springframework.web.servlet.mvc.method.annotation.ExceptionHandlerExceptionResolver  - Resolving exception from handler [public org.springframework.web.servlet.ModelAndView com.crimetrack.web.CrimeRegistrationController.handleSave(com.crimetrack.business.Crime,org.springframework.validation.BindingResult,org.springframework.ui.ModelMap,org.springframework.ui.Model) throws java.lang.Exception]: org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [INSERT INTO crimeTrack.tblcrimes (crimeRecNo,crimeDetails, crimeLatLocation,crimeLongLocation, crimeStreetLn1, crimeStreetLn2, crimeDate, recordedById,statusId, offenceCatId, offenceTypeId, crimeLevelId, investigatingOfficerId,crimeTime) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; Invalid argument value: java.io.NotSerializableException; nested exception is java.sql.SQLException: Invalid argument value: java.io.NotSerializableException
41737 [http-bio-8084-exec-4] DEBUG org.springframework.web.servlet.mvc.annotation.ResponseStatusExceptionResolver  - Resolving exception from handler [public org.springframework.web.servlet.ModelAndView com.crimetrack.web.CrimeRegistrationController.handleSave(com.crimetrack.business.Crime,org.springframework.validation.BindingResult,org.springframework.ui.ModelMap,org.springframework.ui.Model) throws java.lang.Exception]: org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [INSERT INTO crimeTrack.tblcrimes (crimeRecNo,crimeDetails, crimeLatLocation,crimeLongLocation, crimeStreetLn1, crimeStreetLn2, crimeDate, recordedById,statusId, offenceCatId, offenceTypeId, crimeLevelId, investigatingOfficerId,crimeTime) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; Invalid argument value: java.io.NotSerializableException; nested exception is java.sql.SQLException: Invalid argument value: java.io.NotSerializableException
41737 [http-bio-8084-exec-4] DEBUG org.springframework.web.servlet.mvc.support.DefaultHandlerExceptionResolver  - Resolving exception from handler [public org.springframework.web.servlet.ModelAndView com.crimetrack.web.CrimeRegistrationController.handleSave(com.crimetrack.business.Crime,org.springframework.validation.BindingResult,org.springframework.ui.ModelMap,org.springframework.ui.Model) throws java.lang.Exception]: org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [INSERT INTO crimeTrack.tblcrimes (crimeRecNo,crimeDetails, crimeLatLocation,crimeLongLocation, crimeStreetLn1, crimeStreetLn2, crimeDate, recordedById,statusId, offenceCatId, offenceTypeId, crimeLevelId, investigatingOfficerId,crimeTime) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; Invalid argument value: java.io.NotSerializableException; nested exception is java.sql.SQLException: Invalid argument value: java.io.NotSerializableException
41737 [http-bio-8084-exec-4] DEBUG org.springframework.web.servlet.DispatcherServlet  - Cleared thread-bound request context: org.apache.catalina.connector.RequestFacade@26f6379c
41737 [http-bio-8084-exec-4] DEBUG org.springframework.web.servlet.DispatcherServlet  - Could not complete request
org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [INSERT INTO crimeTrack.tblcrimes (crimeRecNo,crimeDetails, crimeLatLocation,crimeLongLocation, crimeStreetLn1, crimeStreetLn2, crimeDate, recordedById,statusId, offenceCatId, offenceTypeId, crimeLevelId, investigatingOfficerId,crimeTime) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; Invalid argument value: java.io.NotSerializableException; nested exception is java.sql.SQLException: Invalid argument value: java.io.NotSerializableException
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:107)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:603)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:812)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:868)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:876)
    at com.crimetrack.jdbc.JdbcCrimeDAO.saveCrime(JdbcCrimeDAO.java:54)
    at com.crimetrack.service.CrimeManager.saveCrime(CrimeManager.java:18)
    at com.crimetrack.web.CrimeRegistrationController.handleSave(CrimeRegistrationController.java:261)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:213)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:936)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1004)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Invalid argument value: java.io.NotSerializableException
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
    at com.mysql.jdbc.PreparedStatement.setSerializableObject(PreparedStatement.java:3359)
    at com.mysql.jdbc.PreparedStatement.setObject(PreparedStatement.java:3010)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:229)
    at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:351)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:216)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:144)
    at org.springframework.jdbc.core.ArgPreparedStatementSetter.doSetValue(ArgPreparedStatementSetter.java:65)
    at org.springframework.jdbc.core.ArgPreparedStatementSetter.setValues(ArgPreparedStatementSetter.java:46)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:816)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:1)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)
    ... 38 more
41739 [http-bio-8084-exec-4] DEBUG org.springframework.web.context.support.XmlWebApplicationContext  - Publishing event in WebApplicationContext for namespace 'crimetrack-servlet': ServletRequestHandledEvent: url=[/crimeTrack/crime_registration_save.htm]; client=[0:0:0:0:0:0:0:1]; method=[POST]; servlet=[crimetrack]; session=[null]; user=[null]; time=[149ms]; status=[failed: org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [INSERT INTO crimeTrack.tblcrimes (crimeRecNo,crimeDetails, crimeLatLocation,crimeLongLocation, crimeStreetLn1, crimeStreetLn2, crimeDate, recordedById,statusId, offenceCatId, offenceTypeId, crimeLevelId, investigatingOfficerId,crimeTime) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; Invalid argument value: java.io.NotSerializableException; nested exception is java.sql.SQLException: Invalid argument value: java.io.NotSerializableException]
41739 [http-bio-8084-exec-4] DEBUG org.springframework.web.context.support.XmlWebApplicationContext  - Publishing event in Root WebApplicationContext: ServletRequestHandledEvent: url=[/crimeTrack/crime_registration_save.htm]; client=[0:0:0:0:0:0:0:1]; method=[POST]; servlet=[crimetrack]; session=[null]; user=[null]; time=[149ms]; status=[failed: org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [INSERT INTO crimeTrack.tblcrimes (crimeRecNo,crimeDetails, crimeLatLocation,crimeLongLocation, crimeStreetLn1, crimeStreetLn2, crimeDate, recordedById,statusId, offenceCatId, offenceTypeId, crimeLevelId, investigatingOfficerId,crimeTime) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; Invalid argument value: java.io.NotSerializableException; nested exception is java.sql.SQLException: Invalid argument value: java.io.NotSerializableException]

  1. Buddy, I see what the problem. though you mentioned the reference of that example, I believe that you use the wrong method of jdbctemplate. check the api. You use a different method with that example.