JDBC: Generating the value of primary key for prepared statements

Question

Suppose I want to add a new row to my table via JDBC. In my table, I have an auto incrementing primary key field (so I can update the table later), and another regular field.

userid BIGINT AUTO_INCREMENT PRIMARY KEY,
username TEXT,

Now, I am creating the new statement and executing it using prepared statements, like so:

//dummy execute to get the generated keys
stmt.execute("SELECT * FROM user;", Statement.RETURN_GENERATED_KEYS);

        ResultSet rs = stmt.getGeneratedKeys();
        int id=1;
        //this is never executed, the resultset is always empty...
        if(rs.next())
        {
            System.out.println("not empty");
            id = rs.getInt(1);
        }
        System.out.println(id); //therefore, id is always 1
//prepare a statement to execute in SQL
        stmt=con.prepareStatement("INSERT INTO user VALUES (?,?);", Statement.RETURN_GENERATED_KEYS);

        //fill in the ?'s with their respective values
        ((PreparedStatement) stmt).setString(1, String.valueOf(id));
        ((PreparedStatement) stmt).setString(2, user);


        //execute statement
        ((PreparedStatement) stmt).executeUpdate();

As you see, I want the value of the generated key so that I can use a prepared statement to update all the columns in the newly generated row (otherwise I get a No value specified for parameter 1 error).

But when I do the above code, I get an

Duplicate entry '1' for key 'PRIMARY'

This seems to me that the resultset is always empty. So I am not accessing the value correctly. Why is this so, and how can I fix this so that I can use my same structure of prepared statements to execute these queries?

You have to test `rs.next()` result with an `if` before using `rs.getXXX` ! (some reading: https://docs.oracle.com/javase/tutorial/jdbc/basics/) — , Feb 23 '16 at 06:26
I have edited my post to include what I think would be adequate checking of an empty resultset, and get a new error (i.e. the resultset is always empty, so something is not being accessed properly). Also @VikrantKashyap I have included the structure of my table- just two columns, one of which is the autogenerated value I want to gather so I can add a new row — Raj Raina, Feb 23 '16 at 06:32
have you debug the application and look what you found in your `ResultSet`.. — Vikrant Kashyap, Feb 23 '16 at 06:36
have you debug the application and look what you found in your `ResultSet`.. — Vikrant Kashyap, Feb 23 '16 at 06:37
Yes, I edited the question to indicate that the resultset is always empty. I am not accessing the latest generated key properly. — Raj Raina, Feb 23 '16 at 06:40

Drunix · Accepted Answer · 2016-02-23T07:16:49.170

1

You can call getGeneratedKeys only after you have executed you statement, not before. See https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#getGeneratedKeys-- Simply preparing the statement does not generate the new key. Just drop the id column from your insert and insert only user.

edited Feb 23 '16 at 07:16

answered Feb 23 '16 at 07:11

Drunix

3,313
8
28
50

Right, so I have edited my code to call an execute before I grab the generatedKey, and yet again the ResultSet is empty. Please take a look and let me know what I am still doing wrong. Thanks! – Raj Raina Feb 23 '16 at 07:18
No, don't execute another query. Execute 'INSERT INTO user(username) VALUES (?)' and retrieve the generated value afterwards. Also you should stop editing your code in place. Add an update section with the new code, otherwise it won't help other people who have the same problem. – Drunix Feb 23 '16 at 07:23
Thank you! It worked! I have accepted your answer. – Raj Raina Feb 23 '16 at 07:26

score 0 · Answer 2 · answered Feb 23 '16 at 07:10

1.Since your id is Auto Increment Column you should pass the value for that filed for first time (try to do it through MYSQL server directly. 2.Don't try to perform operations on empty result set. 3.In your case id always will be 1 since if statement doesn't execute.

Thank you.

JDBC: Generating the value of primary key for prepared statements

2 Answers2