Java: MySQL query. Get generated ID.

Question

I have a table that contains a column called 'id' that is an INT that auto increments and is set to be the primary key.

I need to get the value of the generated ID after the query is run. I have the following:

String sql = "INSERT...";
Statement statement = sqlConnection.createStatement();
    int result = statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
    this.id = statement.getGeneratedKeys().getInt("id");

I get the following exception on the last line of code:

javax.servlet.ServletException: java.sql.SQLException: Column 'id' not found.
    com.joelj.music.rest.NewUser.doPost(NewUser.java:44)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:754)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
    com.joelj.music.rest.filters.Prefilter.doFilter(Prefilter.java:44)

If I change it to statement.getGeneratedKeys().getInt(0); I get the following:

javax.servlet.ServletException: java.sql.SQLException: Before start of result set
    com.joelj.music.rest.NewUser.doPost(NewUser.java:44)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:754)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
    com.joelj.music.rest.filters.Prefilter.doFilter(Prefilter.java:44)

I've gone through the other methods and have tried this and that. I have looked at example code found on the internet. I just can't see what I'm doing wrong.

Note: The query is run in both of the solutions I've included. So the connection works fine. It's getting back the value of the ID that isn't working.

Thanks for the help.

Answer 1

Have you tried using statement.getGeneratedKeys().getInt(1)? The Result set is one based.