ExecuteUpdate sql statement in Java not working

Question

I am learning how to use SQL with Java. I have installed the JDBC driver successfully and I am able to read the records from a database and print it on the screen.

My problem occurs when trying to do either an update or insert statement, where nothing happens. Here is my code:

Method where the problem resides

public static void updateSchools(ArrayList<String> newSchool)
{
    try
    {
        openDatabase();
        stmt = c.createStatement();
        int numberOfRows = stmt.executeUpdate("UPDATE schools SET address='abc' WHERE abbreviation='2';");
        System.out.println(numberOfRows);
        closeDatabase();
    }
    catch (Exception e)
    {
        System.err.println(e.getClass().getName() + ": " + e.getMessage());
        System.exit(0);
    }
}

Support functions

public static void openDatabase()
{
    c = null;
    stmt = null;
    try
    {
        Class.forName("org.postgresql.Driver");
        c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/Badminton", "postgres", "postgrespass");
        c.setAutoCommit(false);
    }
    catch (Exception e)
    {
        System.err.println(e.getClass().getName() + ": " + e.getMessage());
        System.exit(0);
    }
    System.out.println("Database opened successfully");
}

public static void closeDatabase()
{
    try
    {
        stmt.close();
        c.close();
    }
    catch (Exception e)
    {
        System.err.println(e.getClass().getName() + ": " + e.getMessage());
        System.exit(0);
    }
    System.out.println("Database closed successfully");
}

Here is an image of my very simple database:

The result in the console is the following, although no databases changes were done:

Database opened successfully

1

Database closed successfully

Thanks in advance!

Solution 1

Remove c.setAutoCommit(false) line from the openDatabase method.

Or

Add c.commit() at the end of the updateSchool method.

After the auto-commit mode is disabled, no SQL statements are committed until you call the method commit explicitly. All statements executed after the previous call to the method commit are included in the current transaction and committed together as a unit.

Solution 2

The OP's query has been solved, but this might help someone.

It turns out that you cannot execute two different Statements(or PreparedStatements) in a single batch.

I was having the same problem, no errors, no exceptions, the database record simply wouldn't update as it should.

Resusing the previously used PreparedStatement solved the problem.

Comments

[Benoit Goderre]

Thank you so much, my code now works as intended :).

[Mark Rotteveel]

An explanation why this solves the problem would add just that little extra to your answer.

[Benoit Goderre]

My insert and update queries were not committed to the database, which is why I would not seen any changes. Once I added the commit line after my operations, the changes were done successfully.