CODEWITHSUNDEEP
javamysql
Diego Donoso
Diego Donoso

Reputation: 69

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

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

Upvotes: 0

Views: 161

Answers (1)

rzwitserloot
rzwitserloot

Reputation: 103773

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.

Upvotes: 1

Related Questions