CODEWITHSUNDEEP
javajdbcderby
LittleMahdy
LittleMahdy

Reputation: 59

getGeneratedKeys() returns an Empty ResultSet

Hello there and thanks for reading. I'm trying to retrieve the ID of the newly inserted data, but I always get an empty ResultSet.

 Connection con = main.getCon();
 String sqlCommand = "Insert Into Relations(name,explanation) values(?,?)";
 PreparedStatement state = 
 con.prepareStatement(sqlCommand,Statement.RETURN_GENERATED_KEYS);
 state.setString(1,name.getText());
 state.setString(2,explanation.getText());
 int affectedRows = state.executeUpdate();
 assert (affectedRows>0);
        
 ResultSet rs = state.getGeneratedKeys();
 assert rs.next();
 int instertedID= rs.getInt("ID");

Not sure what's wrong with it. Checked different samples online, but couldn't figure out what's my mistake. I also tried it with Statement, but no luck with that either.
Point 1: the code runs smoothly and my data in inserted into the database. Point 2: there are examples online for this very case, you can check it here: https://www.baeldung.com/jdbc-returning-generated-keys

I just realized that my ResultSet wasn't empty, I had problem with using my debugger and that's why I thought it was empty. As Mark Rotteveel mentioned in a comment, the problem was with "assert" statement.

Upvotes: 2

Views: 1405

Answers (4)

divya maddipudi
divya maddipudi

Reputation: 1

String sqlCommand = "Insert Into Relations (name, explanation) values(?, ?)";
PreparedStatement state = con.prepareStatement(sqlCommand, Statement.RETURN_GENERATED_KEYS);
 state.setString(1,name.getText());
 state.setString(2,explanation.getText());
 state.executeUpdate();
 

ResultSet resultSet = state.getGeneratedKeys();

if(resultSet.next()) {
    System.out.println(resultSet.getInt(1)); //Indicate the corresponding column index value.
}

Upvotes: 0

Mark Rotteveel
Mark Rotteveel

Reputation: 108971

The problem is your use of assert rs.next(). Assertions in Java are intended for checking invariants (eg during testing), but when you normally run Java, assert statements are not executed, they are only executed when explicitly enabling this with the -ea commandline option.

As a result, rs.next() is not called, so your result set is still positioned before the first row when you call rs.getInt(1). Instead use if (rs.next()) { ... }.

Upvotes: 5

Mustafa Poya
Mustafa Poya

Reputation: 3027

change the last line of code to this because the DBMS you are using may not support the getting value by column name so pass the index of that column:

int instertedID = rs.getInt(1);

Upvotes: 0

rzwitserloot
rzwitserloot

Reputation: 102842

This is DB engine dependent. Some tips:

JDBC is low-level and not appropriate to program with

It's a complicated API. Use something that makes it easier: JDBI, or JOOQ. They may have abstractions over insertion that takes care of this stuff for you.

Some DB engines require that you list the column name

Try:

con.prepareStatement(sqlCommand, new String[] {"UNID"});

Some DB engines will only return generated values as direct resultset

Don't call .executeUpdate(); instead, call .executeQuery() which returns a ResultSet; check that one.

Something else

Post the exact table structure and DB engine you're working with if the above doesn't help.

Your code is broken

You can't create resource objects (once that must be closed) unless you do so safely, and you're not doing so safely. Use try-with-resources:

String sql = "INSERT INTO relations(name, explanation) VALUES (?, ?)";
try (Connection con = main.getCon();
    PreparedStatement ps = con.prepareStatement(sql, new String[] {"unid"})) {

    state.setString(1, name.getText());
    state.setString(2, explanation.getText());
    try (ResultSet rs = state.executeQuery()) {
        if (!rs.next()) throw new SQLException("insert didn't return autogen?");
        System.out.println(rs.getInt(1));
    }
}

ResultSets, Statements, PreparedStatements, and Connections are all resources (must be closed!) - if you want to store one of those things in a field, you can do that, but only if the class that contains this field is itself a resource: It must have a close() method, it must implement AutoClosable, and you can then only make instances of this class with try-with-resources as above.

Failure to adhere to these rules means your app seems to work, but is leaking resources as it runs, thus, if you let it run long enough, it will start crashing. Also, your DB engine will grind to a halt as more and more connections are left open, stuck forever.

Upvotes: 1

Related Questions