Java mysql AWS execute succeed and then select does not return the new value

Question

I am working with AWS RDS (specifically mysql) and I am using SQL Workbench/J as a GUI tool. My server side code written in Java and here is my code:

Insert code:

try {
    Statement myStatement = insertConnectionObject.createStatement();
    myStatement.executeUpdate("INSERT INTO friends VALUES('buddy', '15', '123');");
    myStatement.close();
} catch(Exception ex) {
    // code for handling exceptions
} finally {
    myStatement.close();
    insertConnectionObject.close();
}

After that, I call the select code from the same table:

try {
    Statement myStatement = selectConnectionObject.createStatement();
    ResultSet returnedFriends = myStatement.executeQuery("SELECT * FROM friends;");
    //unfortunately, the returnedFriends will not return the new inserted value 'buddy'
} catch(Exception ex) {
    // code for handling exceptions
} finally {
    myStatement.close();
    insertConnectionObject.

unfortunately, the returnedFriends will not return the new inserted value 'buddy'.

If I will click the 'commit any pending database changes' button in the SQL Workbench/J GUI tool, and then run the select statement, the new value 'buddy' will return.

What have I tried until now?

1. Use the same connection object for both insert and select.
2. Open and close the connection after the insert command, and after every select command.
3. disable the auto commit and try to commit manually.
4. Inserting via code, and then selecting directly from the DB.

Answer 1

Have you tried setAutoCommit(true) on the connection, just in case it isn't?

Also, if your select is just to get a new key don't forget you can call myStatement.getGeneratedKeys() in with the update.

Answer 2

You should use executeQuery() to select . executeUpdate() returns nothing but int. It should give a compile time error, are you sure that the code is compiling rather than running last working version?

executeUpdate(String sql) Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.

So change your select code as below:

ResultSet returnedFriends = myStatement.executeQuery("SELECT * FROM friends;");

Answer 3

My problem was as simple and annoying as can be - apparently, I had to close the Workbench GUI when working from the code, which is kind of wired and requires probably deeper investigation from the Workbench / AWS teams.

Anyways, after closing this interface, everything just worked.

Thanks for the help!