Java SQL Update Query Not Working

Question

I'm working on a simple java project that uses JavaDB and MySQL to introduce the use of databases. I'm trying to write a method for updating the scores of a game in a database.

public void setTeamsScore(int matchNumber, int hScore, int vScore) throws SQLException
{
   Statement stmt = connection.createStatement();
   String sqlStatement = "UPDATE Matches " +
                         "SET HomeTeamScore = " + hScore + 
                         " WHERE " +
                         "MatchNumber = " + matchNumber;
   stmt.executeUpdate(sqlStatement);

   sqlStatement = "UPDATE Matches " +
                  "SET VisitorTeamScore = " + vScore +
                  " WHERE " +
                  "MatchNumber = " + matchNumber;
   stmt.executeUpdate(sqlStatement);
}

I get no errors at runtime, and when I check the return value of the update statement, it returns 1 (which if I understand correctly, means that 1 row was updated in the database). However, the database doesn't get updated at all and keeps the same values from before.

At first, I thought that maybe auto-commit wasn't working, so I tried turning auto-commit off and using connection.comit() but that didn't solve the problem either.

Any guidance would be much appreciated.

SohanRoni · Accepted Answer · 2018-07-04 08:35:49Z

2

First of all you have to check if the Auto-commit is set to true or false . if false then you have to commit the connection after the SQL execution .

int rows = stmt.executeUpdate(sqlStatement);
System.out.println("Rows impacted : " + rows );
stmt.commit();
stmt.close();

answered Jul 4, 2018 at 8:35

SohanRoni

212 bronze badges

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

Comments

Tapas · Accepted Answer · 2017-11-05 04:50:32Z

1

You need to call both stmt.execute(sql) and stmt.executeUpdate(sql)

First check if your query returns a true result set or not.

Boolean ret = stmt.execute(sqlStatement);

Then update the records

int rows = stmt.executeUpdate(sqlStatement); System.out.println("Rows impacted : " + rows );

If the data is still not updated check your connection object.

answered Nov 5, 2017 at 4:50

Tapas

113 bronze badges

2 Comments

Gabriel Over a year ago

Hi, thanks for your reply. I tried what you said and the data is still not updated... The console still says "Rows impacted : 1", but nothing changes. I have tried checking the connection but I'm using the same connection for other methods in the same class with no problem...

Gabriel Over a year ago

When I use Boolean ret = stmt.execute(sqlStatement);, it returns false. What does this mean?

Collectives™ on Stack Overflow

Java SQL Update Query Not Working

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related