1 Reply Latest reply: Jul 13, 2012 2:37 AM by 949229 RSS

    Double Insert On Table

    949229
      I created a JPA project that also has a Swing project for a GUI that utilizes the JPA code to insert a record in an Oracle table via a stored procedure. The table has a trigger on it that generates a new ID via sequence.nextval. The stored procedure has OUT parameter for the new ID. The problem is, that when the code executes, two entries are made into the table with the same data.

      The call to getResultList() in the executing code below is to get the OUT parameter that contains the new ID value. The message box that pops up fires only once. When I execute this code from another project like in Main().... a "stand alone" project, ( in other words not using via a GUI) everything works as expected. Meaning no double insert.

      It's only when I fire it off via this GUI that I get the double insert.



      Stored Proc

      PROCEDURE uspCreate(
      p_PHONE IN VARCHAR2,
      p_STATE IN VARCHAR2,
      p_FIRST_NAME IN VARCHAR2,
      p_ADDRESS IN VARCHAR2,
      p_EMAIL IN VARCHAR2,
      p_CITY IN VARCHAR2,
      p_LAST_NAME IN VARCHAR2,
      p_PKID OUT NUMBER

      )
      IS
      BEGIN
      INSERT
      INTO
      CUSTOMERS
      (
      PHONE ,
      STATE ,
      FIRST_NAME ,
      ADDRESS ,
      EMAIL ,
      CITY ,
      LAST_NAME
      -- PKID
      )
      VALUES
      (
      p_PHONE ,
      p_STATE ,
      p_FIRST_NAME ,
      p_ADDRESS ,
      p_EMAIL ,
      p_CITY ,
      p_LAST_NAME
      --CUSTOMER_SEQ.nextval,
      );

      select CUSTOMER_SEQ.currval INTO p_PKID from DUAL;
      END;

      GUI Code


      JButton btnSubmit = new JButton("Submit");
      btnSubmit.addActionListener(new ActionListener() {

      public void actionPerformed(ActionEvent arg0) {
                          
           AddNewCustomer();
           }
                
      });
                
      btnSubmit.setBounds(24, 341, 91, 23);          
      frmNewCustomer.getContentPane().add(btnSubmit);


      public void AddNewCustomer()
           {
                Customer.AddCustomer(this.txtFirstName.getText(),
                this.txtLastName.getText(),
                this.txtAddress.getText(),
                this.txtCity.getText(),
                this.txtState.getText(),
                this.txtEmail.getText(),
                this.txtPhone.getText());
                
                JOptionPane.showMessageDialog(frmNewCustomer, "Added new customer", "Status", 1);
           }

      Class Code

      @Entity
      @Table(name="CUSTOMERS")
      @NamedStoredProcedureQuery(
           name="AddCustomer",
           procedureName="uspCreate",
           parameters = {
                     @StoredProcedureParameter(queryParameter="p_PHONE", type=String.class),
                     @StoredProcedureParameter(queryParameter="p_STATE", type=String.class),
                     @StoredProcedureParameter(queryParameter="p_FIRST_NAME", type=String.class),
                     @StoredProcedureParameter(queryParameter="p_ADDRESS", type=String.class),
                     @StoredProcedureParameter(queryParameter="p_EMAIL", type=String.class),
                     @StoredProcedureParameter(queryParameter="p_CITY", type=String.class),
                     @StoredProcedureParameter(queryParameter="p_LAST_NAME", type=String.class),
                     @StoredProcedureParameter(queryParameter="p_PKID", direction=Direction.OUT, type=Integer.class)
           }
           )




      protected static void AddCustomer(String sFirstName,String sLastName, String sAddress, String sCity, String sState,String sEmail,String sPhone )
           {
                
                try {
                     factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
                     EntityManager em = factory.createEntityManager();
                     em.getTransaction().begin();
                                    
                     Query q = em.createNamedQuery("AddCustomer");
                     q.setParameter("p_PHONE", sPhone);
                     q.setParameter("p_STATE", sState);
                     q.setParameter("p_FIRST_NAME", sFirstName);
                     q.setParameter("p_ADDRESS", sAddress);
                     q.setParameter("p_EMAIL", sEmail);
                     q.setParameter("p_CITY", sCity);
                     q.setParameter("p_LAST_NAME", sLastName);
                     int r = q.executeUpdate();                    
                     em.getTransaction().commit();
                     
                     List results = q.getResultList();
                     if(results != null)
                     {               
                          Object id = results.get(0);
                          if(id != null)
                          {
                               System.out.println(id.toString());
                          }
                     }
                     em.close();     
                } catch (Exception e) {
                     
                     e.printStackTrace();
                }
                
           }

      Edited by: user10985627 on Jul 12, 2012 11:55 PM
        • 1. Re: Double Insert On Table
          949229
          I solved this problem. Changed code to....

          protected static void AddCustomer(String sFirstName,String sLastName, String sAddress, String sCity, String sState,String sEmail,String sPhone )
               {
                    
                    try {
                         factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
                         EntityManager em = factory.createEntityManager();
                         em.getTransaction().begin();
                                        
                         Query q = em.createNamedQuery("AddCustomer");
                         q.setParameter("p_PHONE", sPhone);
                         q.setParameter("p_STATE", sState);
                         q.setParameter("p_FIRST_NAME", sFirstName);
                         q.setParameter("p_ADDRESS", sAddress);
                         q.setParameter("p_EMAIL", sEmail);
                         q.setParameter("p_CITY", sCity);
                         q.setParameter("p_LAST_NAME", sLastName);
                         //int r = q.executeUpdate();                    
                         //em.getTransaction().commit();
                         
                         List results = q.getResultList();
                         if(results != null)
                         {               
                              Object id = results.get(0);
                              if(id != null)
                              {
                                   System.out.println(id.toString());
                              }
                              em.getTransaction().commit();
                         }
                         em.close();     
                    } catch (Exception e) {
                         
                         e.printStackTrace();
                    }
                    
               }