Return int with auto incremented ID after insert statement

Question

i'm trying to return the just created table id as a int with the GetGeneratedKey method. But this does not work.

My code

    public int saveWorkout(String titel, String beschrijving, int categorie_id, int persoon_id) throws SQLException {
    int result = 0;
    try (Connection con = super.getConnection()) {
        String query = "INSERT INTO \"Workout\" VALUES(?,?,?,?)";
        String query2 = "INSERT INTO \"Workout_Oefening\" VALUES(?,?)";
        PreparedStatement pstmt = con.prepareStatement(query);
        pstmt.setString(1, titel);
        pstmt.setString(2, beschrijving);
        pstmt.setInt(3, persoon_id);
        pstmt.setInt(4, categorie_id);
        pstmt.executeUpdate();
        ResultSet keys = pstmt.getGeneratedKeys();
        if(keys.next()) {
            result = keys.getInt(1);
            System.out.println(keys.getInt(1));
        }

    } catch (SQLException sqle) {
        sqle.printStackTrace();
    }
    return result;
}

It always returns 0! if i print the keys.getint(1) thing it returns the following

org.apache.tomcat.dbcp.dbcp2.DelegatingResultSet@8a14f70

You need to use `con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS)` — Mark Rotteveel, Jun 23 '18 at 14:10

score 0 · Accepted Answer · answered Jun 23 '18 at 13:54

0

if(keys.next()) {
            keys.next(); //This is being called 2nd time.
            result = keys.getInt(1);
            System.out.println(keys.getInt(1));
        }

The issue is in your 2nd line keys.next();

You are calling it two times, even in the if condition, it traverses to the next in line.

Replace your code with this and I assume it would work correctly

if(keys.next()) {
                result = keys.getInt(1);
                System.out.println(keys.getInt(1));
            }

Reference : https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#next()

answered Jun 23 '18 at 13:54

Farhan Qasim

990
5
18

I have also tried this but it still returns the exact same unfortunately – Wesley van S Jun 23 '18 at 13:56
@WesleyvanS https://docs.oracle.com/cd/E17952_01/connector-j-en/connector-j-usagenotes-last-insert-id.html – Farhan Qasim Jun 23 '18 at 14:01

score 0 · Answer 2 · answered Jun 23 '18 at 14:03

0

I forgot to add

Statement.RETURN_GENERATED_KEYS after my preparedstatement.

answered Jun 23 '18 at 14:03

Wesley van S

69
1
12

Return int with auto incremented ID after insert statement

2 Answers2