CODEWITHSUNDEEP
javamysqljavadb
Gabriel
Gabriel

Reputation: 11

Java SQL Update Query Not Working

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.

Upvotes: 1

Views: 5443

Answers (2)

SohanRoni
SohanRoni

Reputation: 21

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

Upvotes: 2

Tapas
Tapas

Reputation: 11

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.

Upvotes: 1

Related Questions