I am using JDBC to update a row in my MySQL database:
pConnection.setAutoCommit(true);
PreparedStatement pstmt = pConnection.prepareStatement("update mg_build_queue " + //
"set buildsetid=?,locale=?,areacode=?,distversionid=?,platformid=?,version=?," + //
"priority=?,buildstatus=?,computername=?,buildoutput=?,started=?,finished=?, packageid=?, lockcounter=0 where buildid=?" //
);
pstmt.setInt(1, mBuildSet.getId());
pstmt.setString(2, Locale.localesToString(mLocales, ","));
pstmt.setString(3, mAreaCode.toString());
pstmt.setInt(4, mDistVersionId);
pstmt.setInt(5, mPlatform);
pstmt.setInt(6, mVersion);
pstmt.setLong(7, mPriority);
pstmt.setInt(8, mBuildStatus);
pstmt.setString(9, mComputerName);
pstmt.setString(10, mBuildOutput);
pstmt.setTimestamp(11, timeToTimestamp(mStarted));
pstmt.setTimestamp(12, timeToTimestamp(mFinished));
pstmt.setInt(13, mResultPackageId);
pstmt.setInt(14, mBuildId);
LOGGER.debug("Updating data for mg_build_queue: " + pstmt);
pstmt.execute();
LOGGER.debug("Updated " + pstmt.getUpdateCount() + " rows.");
This is generating the following output:
2012-05-24 09:54:33,211 [Thread-1] DEBUG com.buildmaster.BuildQueueEntryImpl - Updating data for mg_build_queue: com.mysql.jdbc.JDBC4PreparedStatement@35e09eab: update mg_build_queue set buildsetid=201,locale='FR',areacode='XX',distversionid=95,platformid=4604,version=65807,priority=33652480,buildstatus=1,computername='MY_COMPUTER-C:\\BUILDS',buildoutput='',started='2012-05-24 09:54:33',finished='2012-05-24 19:45:27', packageid=0, lockcounter=0 where buildid=122418
2012-05-24 09:54:33,214 [Thread-1] DEBUG com.buildmaster.BuildQueueEntryImpl - Updated 1 rows.
I see no exception. If I query for the entry in DBVisualizer, I see only the old value. If I run the command by hand in DBVisualizer (copied and pasted from above), I can see the updated value.
Why is this happening?
calling pConnection.commit() is must to reflect changes in database if you explicitly set
pConnection.setAutoCommit(false)
The issue appears to be that DBVisualizer is doing some result caching. I disconnected and reconnected using DBVisualizer and can see the update. Thanks to all for their suggestions.
Thanks to a Hans Bergsten on the DBVisualizer forums, here is how to prevent my issue:
Tools -> Tool Properties -> Database -> MySQL -> Physical Connection
Change Transaction Isolation to TRANSACTION_READ_COMMITTED.
NOTE I also restarted my MySQL database, which I don't think affected things, but is worth mentioning.
This is probably caused by MySQL's default isolation level which is "REPEATABLE READ".
Provided your JDBC statement was committed properly you also need to end the open transaction in DBVisualizer. Any select statement starts a transaction and unless you terminate that you won't see any changes done by other transactions.
Another option is to change the default isolation level to READ COMMITTED and you should be fine
Weird but this problem stopped when I restarted Eclipse
if(checkInputs() && txt_id.getText() == null)
{
String UpdateQuery = null;
PreparedStatement ps=null;
Connection con=getConnection();
if(ImgPath == null)
{
try {
UpdateQuery="UPDATE products SET name = ? , price = ? , add_date=? WHERE id=?";
ps=con.prepareStatement(UpdateQuery);
ps.setString(1, txt_name.getText());
ps.setString(2, txt_price.getText());
SimpleDateFormat dateformat=new SimpleDateFormat("dd-MM-yyyy");
String addDate=dateformat.format(btn_date.getDate());
ps.setString(3, addDate);
ps.setInt(4, Integer.parseInt(txt_id.getText()));
ps.executeUpdate();
} catch (SQLException ex) {
Logger.getLogger(Main_window.class.getName()).log(Level.SEVERE, null, ex);
}
}
else
{
try {
//update with image
InputStream img=new FileInputStream(new File(ImgPath));
UpdateQuery="UPDATE products SET name = ? , price = ? ,add_date=? , image = ? WHERE id=?";
ps=con.prepareStatement(UpdateQuery);
ps.setString(1, txt_name.getText());
ps.setString(2, txt_price.getText());
SimpleDateFormat dateformat=new SimpleDateFormat("dd-MM-yyyy");
String addDate=dateformat.format(btn_date.getDate());
ps.setString(3, addDate);
ps.setBlob(4, img);
ps.setInt(5, Integer.parseInt(txt_id.getText()));
ps.executeUpdate();
} catch (FileNotFoundException | SQLException ex)
{
Logger.getLogger(Main_window.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
else
{
JOptionPane.showMessageDialog(null,"One or more fields are empty or wrong");
}
