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!

don't use ";" it can breaks the sql; are you getting any exception ? — Javier Toja, Oct 27 '16 at 16:40
Mybe you need to refresh your db, or commit `c.setAutoCommit(True);` — Ilyes, Oct 27 '16 at 16:42
@karelss From the Postgres [documentation](https://www.postgresql.org/docs/6.4/static/install12418.htm): `Get in the habit of including those SQL semicolons` — Tim Biegeleisen, Oct 27 '16 at 16:43
try(Connection con = getConnection(url, username, password, "org.postgresql.Driver"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql); ) { //statements }catch(....){} Read more here http://stackoverflow.com/questions/2225221/closing-database-connections-in-java — SedJ601, Oct 27 '16 at 16:58

Kushan · Accepted Answer · 2016-10-28T04:08:47.757

11

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.

edited Oct 28 '16 at 04:08

answered Oct 27 '16 at 16:47

Kushan

10,657
4
37
41

Thank you so much, my code now works as intended :). – Benoit Goderre Oct 27 '16 at 16:55
An explanation why this solves the problem would add just that little extra to your answer. – Mark Rotteveel Oct 27 '16 at 18:12
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. – Benoit Goderre Oct 27 '16 at 20:11

score 1 · Answer 2 · answered Jun 23 '20 at 15:39

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.

ExecuteUpdate sql statement in Java not working

2 Answers2

Linked