java - 尝试插入方法时出现多键错误

Question

这是我正在尝试的:

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();

我使用的是 Postgres，所以这个方法应该返回 serial column id 的值。但都得到这是

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 causeorg.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)

这是什么，我该如何解决？

Answer 1

这个问题我自己遇到过并解决过。我建议重构您对准备好的陈述的方法。这是您需要做的:创建一个实现 org.springframework.jdbc.core.PreparedStatementCreator 的类。此类将处理如何将数据提供给准备好的语句。这是一个例子

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;
    }

}

注意对 connection.prepareStatement 的调用。第二个参数指定您希望 GeneratedKeyHolder 使用的列。

使用您的 PreparedStatementCreator。例如:

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