How to call an auto-incremented MySQL primary key to a Java variable

Question

//DATABASE TABLE:

| Pid     | int(6) unsigned zerofill | NO   | PRI | NULL    | auto_increment |

| Ino     | varchar(20)              | NO   | PRI |         |                |

| pno     | int(11)                  | NO   | PRI | 0       |                |

| cno     | int(11)                  | NO   | PRI | 0       |                |

| Pamount | double                   | NO   |     |         |                |

| Pdate   | date                     | NO   |     |         |                |

| Ptype   | varchar(15)              | NO   |     |         |                |

INSERTING VALUES

String ch_sql =
    "INSERT INTO Payment(Ino , pno , cno, Pamount, Pdate, Ptype) VALUES('" +
    (paymentIno) + "','" +
    (cashPno) + "','" +
    (cashCno) + "','" +
    (cashAmount) + "','" +
    (cashDate) + "','" +
    (cashType) + "')";
ch.executeUpdate(ch_sql);

Now I have to get the auto-incremented MySQL value to a Java variable so I can insert that pid into the table below.

Another table where I should insert the pid:

pid | int(11) | NO | PRI | 0

Everything happens in one click event.

private void jButton25ActionPerformed(java.awt.event.ActionEvent evt) {

    try {
        Connection_db cd = new Connection_db();
        Connection con = cd.createconnection();
        Statement ch = con.createStatement();
        Statement cq = con.createStatement();
        Statement dd = con.createStatement();

        String paymentIno = Payment_Invoice_No.getText();
        String depositDate = DD_date.getText();
        String depositBrand = DD_brand.getText();
        String sql_get = "SELECT Pno, Cno FROM invoice WHERE Ino = '" + (paymentIno) + "'";
        ResultSet sql_get_rs = ch.executeQuery(sql_get);
        String cashPno = "";
        String cashCno = "";

        while (sql_get_rs.next()) {
            cashPno = sql_get_rs.getString("Pno");
            cashCno = sql_get_rs.getString("Cno");
        }

        int cashAmount = Integer.parseInt(Cash_Amount.getText());
        String cashDate = Cash_Date.getText();
        String cashType = "";

        if (rb_cash.isSelected()) {
            cashType = "Cash";
        } else if (rb_cheque.isSelected()) {
            cashType = "Cheque";
        } else if (rb_dd.isSelected()) {
            cashType = "Direct Deposit";
        }

        String ch_sql =
            "INSERT INTO Payment(Ino , pno , cno, Pamount, Pdate, Ptype) VALUES('" +
            (paymentIno) + "','" +
            (cashPno) + "','" +
            (cashCno) + "','" +
            (cashAmount) + "','" +
            (cashDate) + "','" +
            (cashType) + "')";
        ch.executeUpdate(ch_sql);
        JOptionPane.showMessageDialog(null, "Sucessfully paid");

        int paymentBalance = Integer.parseInt(Payment_Invoice_Balance.getText());
        paymentBalance = paymentBalance - cashAmount;
        String sql_balance =
            "UPDATE invoice set Ibalance = '" + (paymentBalance) +
            "' WHERE ino = '" + (paymentIno) + "'";
        ch.executeUpdate(sql_balance);
        Payment_Invoice_Balance.setText(String.valueOf(paymentBalance));

        if (rb_cheque.isSelected()) {
            //
        } else if (rb_dd.isSelected()) {
            // ????  String sql_pid = "SELECT pid FROM Payment WHERE pid"

            String sql_directD =
                "INSERT INTO Payment VALUES('" +
                (paymentIno) + "','" +
                (cashPno) + "','" +
                (cashCno) + "','" +
                (cashAmount) + "','" +
                (cashDate) + "','" +
                (cashType) + "')";
        }
    }
    catch (Exception e) {
        JOptionPane.showMessageDialog(this, e.getMessage());
    }
}

It's not clear what you're asking here, both because the formatting of your question makes it unreadable, and because you don't seem to be asking a question. Please read through [How do I ask a good question?](http://stackoverflow.com/help/how-to-ask), then come back and edit your question accordingly. — DaveyDaveDave, Apr 16 '17 at 07:11

Basil Battikhi · Answer 1 · 2017-04-16T10:52:28.267

0

 try (ResultSet generatedKeys = ch.getGeneratedKeys()) {
        if (generatedKeys.next()) {
           return generatedKeys.getLong(1);
        }
        else {
            throw new SQLException("Creating user failed, no ID obtained.");
        }
    }

edited Apr 16 '17 at 10:52

answered Apr 16 '17 at 07:10

Basil Battikhi

2,638
1
18
34

i'm new so i didn't quite understand what you did here. – Stefan Shiloh Apr 16 '17 at 07:33
can you show us the whole method please so that we can help you more ! – Basil Battikhi Apr 16 '17 at 07:34
i edited the post with that whole method – Stefan Shiloh Apr 16 '17 at 10:00
okay i have updated my answer – Basil Battikhi Apr 16 '17 at 10:52

How to call an auto-incremented MySQL primary key to a Java variable

1 Answers1