java - Postgres如何进行多笔交易

Question

我在向数据库中进行多次插入时遇到问题，我的问题最初是我无法插入表中，因为我需要插入的最后一条记录的最后一个 ID。

如果我尝试向表中插入更多记录，它不起作用，为什么？这是因为插入直到事务提交，所以它只会插入一条记录，并插入最后一个id。这是我的java代码:

@Service 
@Transactional
public class ServicioEvaluacionImpl implements ServicioEvaluacion{


    @Autowired
    @Qualifier("sfGas")
    SessionFactory sf;

    Session session;    

    @Autowired
    EncuestaDaoImpl encuestaDao;

    @Autowired
    ClienteDaoImpl clienteDao;
    @Autowired
    DelegacionDaoImpl delegacionDao;
    @Autowired
    ServicioGenericoCRUD  servicioCRUD;


    public void guardarEvaluacion(String parametros) {

        JSONObject obj = new JSONObject(parametros);

        JSONObject cliente = obj.getJSONObject("cliente");


        Integer lastIdCliente = new Integer(0);

        String nombre = cliente.getString("nombre");
        String apellidos = cliente.getString("apellidos");
        String sexo = cliente.getString("sexo");
        String email = cliente.getString("email");
        String area = cliente.getString("area");
        String puesto = cliente.getString("puesto");
        int delegacion = cliente.getInt("delegacion");

        Delegacion delegacionObj = delegacionDao.getDelegacion(delegacion);

        Cliente clienteObj = new Cliente();
        clienteObj.setNombre(nombre);
        clienteObj.setApellidos(apellidos);
        clienteObj.setEmail(email);
        clienteObj.setSexo(sexo);
        clienteObj.setPuesto(puesto);
        clienteObj.setArea(area);
        clienteObj.setDelegacion(delegacionObj);
        //clienteDao.setCliente(clienteObj);
        Transaction transaction = null;

        try{
            Session session = sf.getCurrentSession();

             //obtiene ultimo id insertado
            List<Object> qTemp = servicioCRUD.consultaSQL("Select max(id_cliente) from cliente;");

            for(Object registro : qTemp){

                lastIdCliente = Integer.parseInt(registro.toString());
            }

            int ultimoID = lastIdCliente.intValue()+1;

            clienteObj.setIdCliente(ultimoID);
            servicioCRUD.create(clienteObj);        


        JSONArray evaluaciones = obj.getJSONArray("evaluaciones");
        JSONArray idPregunta = obj.getJSONArray("idPregunta");
        JSONArray comentarios = obj.getJSONArray("comentarios");
        String sugerencias = obj.getString("sugerencias");

        Respuestas respuesta = new Respuestas();
        Comentarios comentario = new Comentarios();
        Sugerencias sugerencia = new Sugerencias();
    respuesta.setCliente(clienteObj);
    comentario.setCliente(clienteObj);
    sugerencia.setCliente(clienteObj);

    java.util.Date date = new java.util.Date();
    java.sql.Date sqlDate = new java.sql.Date(date.getTime()); 
    sugerencia.setFecha(sqlDate);
    sugerencia.setSugerencia(sugerencias);

        for (int i = 0; i < evaluaciones.length(); i++) {

              transaction = session.beginTransaction();

            Encuesta pregunta = encuestaDao.getEncuesta(idPregunta.getInt(i));

            respuesta.setEncuesta(pregunta);


            int respuestaID=0;

            //obtiene ultimo id de respuesta
             qTemp = servicioCRUD.consultaSQL("SELECT max(id_respuesta) FROM respuestas");

                for(Object registro : qTemp){

                Integer lastIdPregunta = Integer.parseInt(registro.toString());
                 respuestaID = lastIdPregunta.intValue()+1;
                 System.out.println("loop::"+i+"   -ult id resp: "+ respuestaID);
                }

                respuesta.setCalificacion((short) evaluaciones.getInt(i));
                respuesta.setIdRespuesta(respuestaID);


                //insercion respuestas
                respuesta.setFecha(sqlDate);
                servicioCRUD.create(respuesta);

                //si no hay comentarios no insertar
            if(!comentarios.getString(i).equals("")){

                  int commentID = 0;
                 qTemp = servicioCRUD.consultaSQL("SELECT max(id_comentario) FROM comentarios");

                    for(Object registro : qTemp){

                    Integer lastIdComent = Integer.parseInt(registro.toString());
                    commentID = lastIdComent.intValue()+1;
                    }

                    comentario.setEncuesta(pregunta);
                    comentario.setIdComentario(commentID);
                    comentario.setComentario(comentarios.getString(i));
                    comentario.setFecha(sqlDate);
                    servicioCRUD.create(comentario);

            } 

             transaction.commit();

            }
          transaction = session.beginTransaction();

        if(!sugerencias.equals("")){

              int sugerenciaID = 0;
                 qTemp = servicioCRUD.consultaSQL("SELECT max(id_comentario) FROM comentarios");

                    for(Object registro : qTemp){

                    Integer sugerenciaIdComent = Integer.parseInt(registro.toString());
                    sugerenciaID = sugerenciaIdComent.intValue()+1;
                    }
                    sugerencia.setIdSugerencia(sugerenciaID);
                    servicioCRUD.create(sugerencia);


        }

         transaction.commit();



        }catch(Exception e){
            e.printStackTrace();
            transaction.rollback();
        }finally{

            session.flush();

        }




    }

}

我试图在循环结束时提交，但它给出了一个错误。还有另一种方法可以修复表顺序而无需解决方法吗？如果没有，我能做什么？我需要按顺序插入这些记录。

编辑:我修复了表顺序的问题，但仍然无法将多于一条的记录保存到数据库中，这次没有堆栈跟踪错误。

堆栈跟踪:

11:03:21,812 INFO  [stdout] (http-localhost-127.0.0.1-8080-3) loop::0   -ult id resp: 5

11:03:22,118 INFO  [stdout] (http-localhost-127.0.0.1-8080-3) loop::1   -ult id resp: 5

11:03:22,195 ERROR [stderr] (http-localhost-127.0.0.1-8080-3) org.hibernate.TransactionException: Transaction not successfully started

11:03:22,195 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:127)

11:03:22,196 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at com.gas.servicioImpl.ServicioEvaluacionImpl.guardarEvaluacion(ServicioEvaluacionImpl.java:163)

11:03:22,196 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at com.gas.rest.EvaluacionController.setEvaluaciones(EvaluacionController.java:24)

11:03:22,196 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

11:03:22,197 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

11:03:22,197 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

11:03:22,197 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at java.lang.reflect.Method.invoke(Method.java:606)

11:03:22,198 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215)

11:03:22,198 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)

11:03:22,198 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)

11:03:22,199 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:743)

11:03:22,199 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:672)

11:03:22,200 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:82)

11:03:22,200 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:919)

11:03:22,200 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:851)

11:03:22,201 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:953)

11:03:22,201 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:855)

11:03:22,202 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at javax.servlet.http.HttpServlet.service(HttpServlet.java:754)

11:03:22,202 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:829)

11:03:22,202 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)

11:03:22,203 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:329)

11:03:22,203 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248)

11:03:22,203 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:230)

11:03:22,204 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:106)

11:03:22,204 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:280)

11:03:22,204 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248)

11:03:22,205 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:275)

11:03:22,205 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:161)

11:03:22,205 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:153)

11:03:22,206 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:155)

11:03:22,206 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)

11:03:22,207 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)

11:03:22,207 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:368)

11:03:22,207 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:877)

11:03:22,208 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:671)

11:03:22,208 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:930)

11:03:22,208 ERROR [stderr] (http-localhost-127.0.0.1-8080-3)   at java.lang.Thread.run(Thread.java:745)

11:03:22,209 ERROR [org.apache.catalina.core.ContainerBase.[jboss.web].[default-host].[/gasnaturalfenosa].[appServlet]] (http-localhost-127.0.0.1-8080-3) Servlet.service() para servlet appServlet lanzó excepción: java.lang.NullPointerException
    at com.gas.servicioImpl.ServicioEvaluacionImpl.guardarEvaluacion(ServicioEvaluacionImpl.java:192) [classes:]
    at com.gas.rest.EvaluacionController.setEvaluaciones(EvaluacionController.java:24) [classes:]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_79]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_79]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_79]
    at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_79]
    at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215) [spring-web-3.2.13.RELEASE.jar:3.2.13.RELEASE]
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132) [spring-web-3.2.13.RELEASE.jar:3.2.13.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104) [spring-webmvc-3.2.13.RELEASE.jar:3.2.13.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:743) [spring-webmvc-3.2.13.RELEASE.jar:3.2.13.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:672) [spring-webmvc-3.2.13.RELEASE.jar:3.2.13.RELEASE]
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:82) [spring-webmvc-3.2.13.RELEASE.jar:3.2.13.RELEASE]
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:919) [spring-webmvc-3.2.13.RELEASE.jar:3.2.13.RELEASE]
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:851) [spring-webmvc-3.2.13.RELEASE.jar:3.2.13.RELEASE]
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:953) [spring-webmvc-3.2.13.RELEASE.jar:3.2.13.RELEASE]
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:855) [spring-webmvc-3.2.13.RELEASE.jar:3.2.13.RELEASE]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:754) [jboss-servlet-api_3.0_spec-1.0.0.Final.jar:1.0.0.Final]
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:829) [spring-webmvc-3.2.13.RELEASE.jar:3.2.13.RELEASE]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:847) [jboss-servlet-api_3.0_spec-1.0.0.Final.jar:1.0.0.Final]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:329) [jbossweb-7.0.13.Final.jar:]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248) [jbossweb-7.0.13.Final.jar:]
    at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:230) [spring-orm-3.2.13.RELEASE.jar:3.2.13.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:106) [spring-web-3.2.13.RELEASE.jar:3.2.13.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:280) [jbossweb-7.0.13.Final.jar:]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248) [jbossweb-7.0.13.Final.jar:]
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:275) [jbossweb-7.0.13.Final.jar:]
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:161) [jbossweb-7.0.13.Final.jar:]
    at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:153) [jboss-as-web-7.1.1.Final.jar:7.1.1.Final]
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:155) [jbossweb-7.0.13.Final.jar:]
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) [jbossweb-7.0.13.Final.jar:]
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) [jbossweb-7.0.13.Final.jar:]
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:368) [jbossweb-7.0.13.Final.jar:]
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:877) [jbossweb-7.0.13.Final.jar:]
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:671) [jbossweb-7.0.13.Final.jar:]
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:930) [jbossweb-7.0.13.Final.jar:]
    at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_79]

Answer 1

问题不在于你的提交。这是一个 NullPointerException 第 192 行。

我不知道第 192 行是哪一行，但我敢打赌它是你的交易 block 之外的一行。当您尝试回滚事务时，您会从 Catch - Rollback 中收到另一个异常。

寻找Null变量，你的问题就可以解决。当您尝试回滚时，您还可以检查事务是否已打开。或者添加更多 try {} catch {} block ，因为您的代码很容易出错。