How to get auto-incremented ID in Java using MySQL?

Question

I am following a tutorial (Client System) trying to make a CRUD system using Java and for the database MySQL. The thing is that the tutorial was made in Java/ORACLE so in the part to add a client to the database i am getting some errors (i have been searching a solution before posting this but i can't understand how to make it works)

This is my code

public String agregarCliente(Connection con , Cliente cli)
{
    PreparedStatement pst = null;
    String sql = "INSERT INTO clientes (IDCLIENTE, NOMBRES, APELLIDOS, GENERO) "
            + "VALUES(CLIENTE_SEQ.NEXTVAL, ?, ?, ?)";
    try 
    {
        pst = con.prepareStatement(sql);
        pst.setString(1, cli.getNombres());
        pst.setString(2, cli.getApellidos());
        pst.setString(3, cli.getGenero()+"");
        mensaje = "CLIENTE GUARDADO CORRECTAMENTE";
        pst.execute();
        pst.close();
    } 
    catch (SQLException e) 
    {
        // REST OF THE CODE
    }
    return mensaje;
}

What do i have to replace CLIENTE_SEQ.NEXTVAL ? I can't create a sequence in MySQL, and don't know how to get the latest Client ID. Consider IDCLIENTE is declared as auto-incremented.

Client Table

If i leave only 3 question marks " + "VALUES(?, ?, ?)"; " i get

 Column count doesn't match value count at row 1

If i leave 3 question marks and 3 values in the table

String sql = "INSERT INTO clientes (NOMBRES, APELLIDOS, GENERO) "
        + "VALUES(?, ?, ?)";

it says that the client was added succefully but when i do a select * from clientes; it says it's empty :S

Just remove the ID column from your insert statement. It should be set automatically. Also, you may consider switching over to the JPA. — maio290
– maio290, Commented May 3, 2021 at 22:25
If i remove or leave it with just "VALUES(?, ?, ?, ?);" i get the message "Client added succesfully " but it aint get added i don't know why :/ — Diego Donoso
– Diego Donoso, Commented May 3, 2021 at 22:29
Well, are you just silently catching the exception? Since this statement shouldn't pass with four question marks... — maio290
– maio290, Commented May 3, 2021 at 22:30
You should only have 3 question marks since you removed the ID from the insert .. You only need 3 values inserted .. — Zak
– Zak, Commented May 3, 2021 at 22:31
if i keep 3 question marks and try to insert some stuff to test i get " Column count doesn't match value count at row 1 " — Diego Donoso
– Diego Donoso, Commented May 3, 2021 at 22:39

Basil Bourque · Accepted Answer · 2021-05-04 01:30:15Z

The simplest way is to simply stop doing this:

String sql = "INSERT INTO clientes (NOMBRES, APELLIDOS, GENERO) "
            + "VALUES(?, ?, ?)";

Columns in SQL can have a 'default'. When you insert a new row into a table, then any of the columns you simply did not include get this default value. This is why the INSERT statement even lets you write the column names in the first place - technically you don't have to do that, you can just write INSERT INTO clientes VALUES(?, ?, ?); - but this then requires that you provide all the values, and most SQL tables have an auto-incrementing primary key column which you don't WANT to include here, so this is rarely used and (correctly) rarely shows up in SQL tutorials.

automatic auto-incrementing primary keys are always set up to have as default value the local equivalent of 'get me the next value of this sequence and increment it'. So that's all you need.

The one downside, of course, is that you now have no idea what the ID is of the row you just inserted!

There are solutions for that. In general, what you're doing here, which is raw JDBC, is not a good idea: JDBC is a horrible API for 'end users'. That's more or less by design (JDBC is old, which never helps, but mostly, it is designed to expose every feature that any DB engine has, in a simple way for JDBC driver builders, because the aim is to be the glue, and not so much to be a particularly nice API for you). Use JDBI or JOOQ which are libraries that are nice to use and built on top of JDBC; they are still SQL based unlike e.g. Hibernate/JPA which is best thought of as an object oriented non-SQL database persistence system which, as implementation detail you should never mess with, happens to store things into an SQL-based database.

However, if you must:

after running the execute command:

try (ResultSet gen = pst.getGeneratedKeys()) {
    // voila
}

You can read that resultset out for the generated key.

Note that your code is lightly broken and leaks resources. Use ARM (the try(stuff) {} construct I'm using above is called Automatic Resource Management: ARM) as I'm doing above for every resource. For DBs, it's all resources: Connection is, PreparedStatement is, and ResultSet is as well! (though, at least, if you close a connection it closes all the things you made from it, so theoretically you can just ARM the connection, but it's more idiomatic to ARM all 3.

JDBI and JOOQ, again, make all this stuff simpler.

Collectives™ on Stack Overflow

How to get auto-incremented ID in Java using MySQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related