Multiple keys error when trying insert method

Question

Here is what I'm trying:

private  int insertMaterial(AddMaterial add) {
    final String INSERT =
                "insert into material (name, keywords, started, finished, subject, description, c_method, c_time, financer, f_time, g_where, g_when, projectname, project_duration, projectleader, projectresearcher, projectfinancer, publication_details, financed, granted, project, publication) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";


    Object[] values = new Object[]{

        add.getMaterial().getName(),
        add.getMaterial().getKeywords(),
        add.getMaterial().getStarted(),
        add.getMaterial().getFinished(),
        add.getMaterial().getSubject(),
        add.getMaterial().getDescription(),
        add.getMaterial().getcMethod(),
        add.getMaterial().getcTime(),
        add.getMaterial().getFinancer(),
        add.getMaterial().getfTime(),
        add.getMaterial().getgWhere(),
        add.getMaterial().getgWhen(),
        add.getMaterial().getProjectName(),
        add.getMaterial().getProjectDuration(),
        add.getMaterial().getProjectLeader(),
        add.getMaterial().getProjectResearcher(),
        add.getMaterial().getProjectFinancer(),
        add.getMaterial().getPublicationDetails(),
        add.getMaterial().isFinanced(),
        add.getMaterial().isGranted(),
        add.getMaterial().isProject(),
        add.getMaterial().isPublication()

      };

    PreparedStatementCreatorFactory psc = new PreparedStatementCreatorFactory(INSERT);
         psc.addParameter(new SqlParameter("name", Types.VARCHAR));
         psc.addParameter(new SqlParameter("keywords", Types.VARCHAR));
         psc.addParameter(new SqlParameter("started", Types.VARCHAR));
         psc.addParameter(new SqlParameter("finished", Types.VARCHAR));
         psc.addParameter(new SqlParameter("subject", Types.VARCHAR));
         psc.addParameter(new SqlParameter("description", Types.VARCHAR));
         psc.addParameter(new SqlParameter("c_method", Types.VARCHAR));
         psc.addParameter(new SqlParameter("c_time", Types.VARCHAR));
         psc.addParameter(new SqlParameter("financer", Types.VARCHAR));
         psc.addParameter(new SqlParameter("f_time", Types.VARCHAR));
         psc.addParameter(new SqlParameter("g_where", Types.VARCHAR));
         psc.addParameter(new SqlParameter("g_when", Types.VARCHAR));
         psc.addParameter(new SqlParameter("projectname", Types.VARCHAR));
         psc.addParameter(new SqlParameter("project_duration", Types.VARCHAR));
         psc.addParameter(new SqlParameter("projectleader", Types.VARCHAR));
         psc.addParameter(new SqlParameter("projectresearcher", Types.VARCHAR));
         psc.addParameter(new SqlParameter("projectfinancer", Types.VARCHAR));
         psc.addParameter(new SqlParameter("publication_details", Types.VARCHAR));
         psc.addParameter(new SqlParameter("financed", Types.BOOLEAN));
         psc.addParameter(new SqlParameter("granted", Types.BOOLEAN));
         psc.addParameter(new SqlParameter("project", Types.BOOLEAN));
         psc.addParameter(new SqlParameter("publication", Types.BOOLEAN));





         psc.setReturnGeneratedKeys(true);
         KeyHolder generatedKeyHolder = new GeneratedKeyHolder();
         getJdbcTemplate().update(psc.newPreparedStatementCreator(values), generatedKeyHolder);



         return generatedKeyHolder.getKey().intValue();

I'm using Postgres, so this method should return the value of serial column id. But all get is this

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: The getKey method should only be used when a single key is returned.  The current key entry contains multiple keys: [{name=uu, keywords=, id=null, started=, finished=, subject=, description=, c_method=, c_time=, financer=, f_time=, g_where=, g_when=, projectname=, project_duration=, projectleader=, projectresearcher=, projectfinancer=, publication_details=, financed=false, project=false, publication=true, granted=false}]
 org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:659)
 org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:563)
 javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
 javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

root cause

org.springframework.dao.InvalidDataAccessApiUsageException: The getKey method should only be used when a single key is returned.  The current key entry contains multiple keys: [{name=uu, keywords=, id=null, started=, finished=, subject=, description=, c_method=, c_time=, financer=, f_time=, g_where=, g_when=, projectname=, project_duration=, projectleader=, projectresearcher=, projectfinancer=, publication_details=, financed=false, project=false, publication=true, granted=false}]
 org.springframework.jdbc.support.GeneratedKeyHolder.getKey(GeneratedKeyHolder.java:65)
 fi.utu.aineistopankki.database.JdbcDatabaseManager.insertMaterial(JdbcDatabaseManager.java:89)
 fi.utu.aineistopankki.database.JdbcDatabaseManager.insertAddMaterialValues(JdbcDatabaseManager.java:174)
 fi.utu.aineistopankki.database.JdbcDatabaseManager$$FastClassByCGLIB$$802c3a1f.invoke()
 net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191)
 org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:692)
 org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
 org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)
 org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
 org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:625)
 fi.utu.aineistopankki.database.JdbcDatabaseManager$$EnhancerByCGLIB$$cdfed3e.insertAddMaterialValues()
 net.viralpatel.spring3.controller.MaterialController.onSubmit(MaterialController.java:46)
 sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
 sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
 java.lang.reflect.Method.invoke(Method.java:597)
 org.springframework.web.bind.annotation.support.HandlerMethodInvoker.doInvokeMethod(HandlerMethodInvoker.java:710)
 org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:167)
 org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:414)
 org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:402)
 org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:771)
 org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:716)
 org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:647)
 org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:563)
 javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
 javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

What is this and how do I fix it?

Chris J · Accepted Answer · 2010-10-12 00:16:48Z

I have encountered and solved this problem myself. I would suggest refactoring your approach to prepared statements. Here is what you need to do: Create a class that implements org.springframework.jdbc.core.PreparedStatementCreator. This class will handle how data is given to the prepared statement. Here is an example

private class MaterialPreparedStatementCreator implements PreparedStatementCreator {
    private MaterialBean material;

    public MaterialPreparedStatementCreator (MaterialBean material){
        this.material = material;
    }

    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
        PreparedStatement ps = connection.prepareStatement(storeQuery, new String [] {"id"});
        ps.setString(1, material.getName()); 
                    /* add in the rest of the fields */
        return ps;
    }

}

Notice the call to connection.prepareStatement. The second parameter specifies which column you want to be used by the GeneratedKeyHolder.

Use your PreparedStatementCreator. For example:

public void createMaterial(MaterialBean material) {
    KeyHolder generatedKeyHolder = new GeneratedKeyHolder();
    this.getJdbcTemplate().update(new MaterialPreparedStatementCreator(material), generatedKeyHolder);
    material(generatedKeyHolder.getKey().longValue());
}

Mark Elliot · Accepted Answer · 2010-10-12 00:21:40Z

2

Use the getKeys() method instead of getKey(), apparently you have more than one key specified.

In particular, as the error tells you: getKey() will Retrieve the first item from the first map, assuming that there is just one item and just one map, and that the item is a number..

answered Oct 12, 2010 at 0:21

Mark Elliot

77.4k23 gold badges147 silver badges162 bronze badges

Comments

hhbarriuso · Accepted Answer · 2010-09-30 11:19:04Z

0

You should use setGeneratedKeysColumnNames method in PreparedStatementCreatorFactory to assign names of the columns used as keys in your table.

answered Sep 30, 2010 at 11:19

hhbarriuso

9765 silver badges7 bronze badges

Collectives™ on Stack Overflow

Multiple keys error when trying insert method

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related