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.

score 2 · Answer 1 · answered Jul 04 '18 at 08:35

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();

score 1 · Answer 2 · answered Nov 05 '17 at 04:50

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 05 '17 at 04:50

Tapas

11
3

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 Nov 05 '17 at 17:17
When I use `Boolean ret = stmt.execute(sqlStatement);`, it returns `false`. What does this mean? – Gabriel Nov 05 '17 at 17:24

Java SQL Update Query Not Working

2 Answers2