Geek asks and answers

Why did my JDBC prepare update instructions without updating the database?

I use 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 generates 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 do not see an exception. If I request a record in DBVisualizer, I see only the old value. If I run the command manually in DBVisualizer (copied and pasted at the top), I can see the updated value.

Why is this happening?

+3
source share
5 answers

The problem is that DBVisualizer does some caching of the results. I disconnected and connected using DBVisualizer and was able to see the update. Thanks to everyone for their suggestions.

Hans Bergsten DBVisualizer, :

→ → → MySQL →

Transaction Isolation TRANSACTION_READ_COMMITTED.

MySQL, , , .

0

pConnection.commit() ,

pConnection.setAutoCommit(false)

0

, MySQL, " ".

If your JDBC instruction has been corrected properly, you also need to complete the open transaction in DBVisualizer. Any select statement starts a transaction, and if you do not stop the action, you will not see any changes made by other transactions.

Another option is to change the default isolation level to READ COMMITTED, and you should be fine

0
source

Strange, but this problem stopped when I restarted Eclipse

0
source 
    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");

    }
-1
source

More articles:


All Articles