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
– Javier Toja, Commented Oct 27, 2016 at 16:40
Mybe you need to refresh your db, or commit c.setAutoCommit(True); — Ilyes
– Ilyes, Commented Oct 27, 2016 at 16:42
@karelss From the Postgres documentation: Get in the habit of including those SQL semicolons — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 27, 2016 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 stackoverflow.com/questions/2225221/… — SedJ601
– SedJ601, Commented Oct 27, 2016 at 16:58

Kushan · Accepted Answer · 2016-10-28 04:08:47Z

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, 2016 at 4:08

answered Oct 27, 2016 at 16:47

Kushan

10.7k4 gold badges39 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Benoit Goderre Over a year ago

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

Mark Rotteveel Over a year ago

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

Benoit Goderre Over a year ago

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.

drac_o · Accepted Answer · 2020-06-23 15:39:57Z

1

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.

answered Jun 23, 2020 at 15:39

drac_o

4475 silver badges12 bronze badges

Collectives™ on Stack Overflow

ExecuteUpdate sql statement in Java not working

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related