Oracle primary key generator in Java

Question

My app allows users to create an account (stored in database) and place orders.
When a client registers himself, I want to generate a primary key named CLIENT_CODE to identify him, starting from x value and increment it with y value. (I'm using oracle 11g atm)
I've tried this so far:

private void jButton6ActionPerformed(java.awt.event.ActionEvent evt) {                                         
        String fname = jTextField9.getText();
        String lname = jTextField10.getText();
        String city = jTextField11.getText();
        String street = jTextField13.getText();
        String number = jTextField14.getText();
        String userClient = jTextField15.getText();
        String pass1 = String.valueOf(jPasswordField5.getPassword());
        String pass2 = String.valueOf(jPasswordField6.getPassword());

        if(verifyFields()){
            if(!checkUsername(userClient)){
                OraclePreparedStatement ps;
                OracleResultSet rs;
                String registerClient = "insert into CLIENT (FNAME_CL, LNAME, CITY, STREET, NUMBER, MONEY, CLIENT_CODE, USER_CLIENT, PASS) values (?, ?, ?, ?, ?, ?, ?, ?, ?)";

                try {
                    ps = (OraclePreparedStatement) JavaConnectDb.ConnectDb().prepareStatement(registerClient);
                    ps.setString(1, fname);
                    ps.setString(2, lname);
                    ps.setString(3, city);
                    ps.setString(4, street);
                    ps.setString(5, number);
                    ps.setDouble(6, 0.0);
                    ps.setInt(7, ???); <--- here should be the generated primary key
                    ps.setString(8, userClient);
                    ps.setString(9, pass1);

                    if(ps.executeUpdate() != 0){
                        JOptionPane.showMessageDialog(null, "Account created!");
                    } else{
                        JOptionPane.showMessageDialog(null, "Error: Check your info");
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(App.class.getName()).log(Level.SEVERE, null, ex);
                }


            }
        }
    }

Start your Client database entry with a CLIENT_CODE of x then on any future Client entries, merely retrieve the CLIENT_CODE used in the last (previous) database entry and increment it by y. — DevilsHnd - 退した
– DevilsHnd - 退した, Commented Nov 28, 2019 at 12:16
@DevilsHnd Don't do that; if two entries are made (nearly) simultaneously then they will both request the latest CLIENT_CODE and will both try to set the same value. You need a way to atomically generate unique values. — MT0
– MT0, Commented Nov 28, 2019 at 12:45

MT0 · Accepted Answer · 2019-11-28 12:37:29Z

1

Don't do it in Java; handle the primary key value creation in the database using a sequence:

CREATE SEQUENCE CLIENT__CLIENT_CODE__SEQ
START WITH 1
INCREMENT BY 1

Then just use your sequence in the INSERT statement and use the RETURNING clause to get the generated value as an OUT parameter of your prepared statement.

insert into CLIENT (
  FNAME_CL,
  LNAME,
  CITY,
  STREET,
  NUMBER,
  MONEY,
  CLIENT_CODE,
  USER_CLIENT,
  PASS
) values (
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  CLIENT__CLIENT_CODE__SEQ.NEXTVAL,
  ?,
  ?
) RETURNING CLIENT_CODE INTO ?

If you were using Oracle 12c then you could use GENERATED AS IDENTITY in the table's CREATE DDL statement to generate the values without creating a separate sequence.

answered Nov 28, 2019 at 12:37

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Măria Over a year ago

That was it. Thank you very much!

Collectives™ on Stack Overflow

Oracle primary key generator in Java

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related