GeeCON Prague 2014*
The moose likes JDBC and the fly likes Question about executeUpdate and MySQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Question about executeUpdate and MySQL" Watch "Question about executeUpdate and MySQL" New topic
Author

Question about executeUpdate and MySQL

Darien Cheung
Ranch Hand

Joined: Aug 13, 2004
Posts: 36
I just started using MySQL and connected it to a Java front-end using MyConnector/J & JDBC. In my front-end, I save all the data in the table by calling executeUpdate with (UPDATE <table> SET <COL1>=<VAL>, <COL2>=<VAL> etc. WHERE <PRI-KEY> = <VAL>

My question is: does executeUpdate() return the number of rows that matched or the number of rows that were actually changed?

When I run the query from the MySQL console, I can see that 1 rows matched and if the values of the columns are exactly the same as in my query, the number of rows affected is 0.

However, when I look at the return value of executeUpdate() in my Java program, it is always 1 even if the values are not updating anything. The JDBC API says it should return the number of rows affected, so I don't expect to see 1 every time.

The whole purpose of this exercise is to show how many rows actually have new data should the user change the data in the front-end GUI and did a save. To say that every row has changed even if no data has been updated isn't correct.

Any comments are appreciated. Thanks!
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Hi Darien,

Using your terminology executeUpdate() returns the number of rows that matched. When performing an UPDATE via SQL the RDBMS does not check the existing data before determining whether or not to update it (as it makes no difference to the outcome). Imagine the processing overhead required to do that, and for no benefit at all in the vast majority of cases. The database log will show that a change was made to the record although the before and after images are identical and locks will be held against the record during the transaction. Some databases can have automatically updated fields, such as SQL Server's TIMESTAMP, that are modified implicitly by any UPDATE.

Typically, in my experience, the kind of processing that you're describing happens before you get to the database. For example in a web page you may choose not to submit at all if the user has not modified any values. This could be achieved by holding original values in hidden fields and comparing on submit using JavaScript. You could implement the same thing server-side before initiating the DB query. Swing, for example, has a built-in mechanism to deal with this.

When you think about it, it's really the most efficient course of action.

Hope this helps.

Jules
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Question about executeUpdate and MySQL