CODEWITHSUNDEEP
javajdbc
user1830797
user1830797

Reputation: 75

Using getGeneratedKeys() Java

Can anyone see how I'm missuing this ResultSet? I'm getting the below errors from customerID = rs.getInt(1):

public boolean addCustomer(Customer customer) {
    String customerSQL = "INSERT INTO Customer (first_name, last_name)"
            + "VALUES (?, ?)";
    String emailSQL = "INSERT INTO Email (customer_ID, email_address, primary_email)"
            + "VALUES (?,?,?)";
    int customerID = 0;
   
    try (Connection connection = getConnection();
            PreparedStatement customerPs = connection.prepareStatement(
                    customerSQL, new String[] {"CUSTOMER_ID"}); )  {
       
        customerPs.setString(1, customer.getFirstName());
        customerPs.setString(2, customer.getLastName());
        customerPs.executeUpdate();
        ResultSet rs = customerPs.getGeneratedKeys();
       
        System.out.println("Result Set: " + rs.toString());
        customerID = rs.getInt(1);
        System.out.print("Customer ID: " + customerID);
       
    }
    catch(SQLException e)
    {
        System.err.println("Error: " + e);
        e.printStackTrace(System.out);
        return false;
    }
   
    return false;
}

OUTPUT

run:
Bill Joel
[email protected]
[email protected]
[email protected]

Result Set: org.apache.derby.impl.jdbc.EmbedResultSet40@61c70928
Error: java.sql.SQLException: Invalid cursor state - no current row.
java.sql.SQLException: Invalid cursor state - no current row.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.ConnectionChild.newSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedResultSet.checkOnRow(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedResultSet.getColumn(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedResultSet.getInt(Unknown Source)
        at customeremailmanager.CustomerDB.addCustomer(CustomerDB.java:78)
        at customeremailmanager.CustomerEmailManager.main(CustomerEmailManager.java:24)
Caused by: java.sql.SQLException: Invalid cursor state - no current row.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
        ... 11 more
BUILD SUCCESSFUL (total time: 1 second)

Upvotes: 2

Views: 5949

Answers (2)

kosa
kosa

Reputation: 66637

You need to first make sure rows are available. See this tutorial on how to use ResultsSet to retrieve data.

As per doc:

You access the data in a ResultSet object through a cursor. Note that this cursor is not a database cursor. This cursor is a pointer that points to one row of data in the ResultSet. Initially, the cursor is positioned before the first row. The method ResultSet.next moves the cursor to the next row. This method returns false if the cursor is positioned after the last row. This method repeatedly calls the ResultSet.next method with a while loop to iterate through all the data in the ResultSet

Example:

while (rs.next()) {
    customerID = rs.getInt(1);
}

Upvotes: 0

PermGenError
PermGenError

Reputation: 46408

you have to invoke ResultSet.next() before retriving the rows from your ResultSet Object:

ResultSet rs = customerPs.getGeneratedKeys();
while (rs.next()) {
    System.out.println("Result Set: " + rs.toString());
    customerID = rs.getInt(1);
}

Upvotes: 6

Related Questions