Mybatis update operation return value problem

The back-end data persistence uses mybatis. When it is high and the account balance is increased or decreased, it plans to use optimistic lock to solve this problem. There is a problem when obtaining the return value of the update operation. It seems that the int return value obtained by mybatis's update operation is not the number of affected rows. It's embarrassing.

Generally speaking, we know that when we use mybatis to define operations such as insert delete in the mapper interface, define a return value of type int, and judge the number of affected rows in the database by whether the value is 0, so as to judge whether the operation is successful.

What exactly does the update return value represent? Let's verify it. Suppose there is a table and two pieces of data as follows:

Let's write a simple unit test case to verify. First, use mybatis to simply write a mapper to update. The content in the XML is:

The database connection is configured as:

Next, let's write a simple unit test to verify whether the return value of update is the number of affected records. The corresponding unit test code is as follows:

According to the unit test code, we will change the value of the phone field of the two records in the database from 12345678 to 6666. Under normal circumstances, the resultcode will return 2. Because the update operation affects the two records in the database, it is consistent with our expectation. If everything is normal, the unit test will pass. Let's run and see the results:

After the unit test is passed, check the records in the database:

This shows that the return value of the update operation of mybatis does return the number of affected rows... Is this really the case?

We know that when the records in the database are modified, the repeated update operation will not affect the number of new rows. To verify what I said, let's try:

Then, according to the logic: when we execute the unit test again, the result code should return 0, which is inconsistent with our expected number 2, which will lead to the failure of the test. Run the unit test again:

Actually, it's still passed. You've seen the wisdom here. By default, the return value of mybatis's update operation is the number of matched records, not the number of affected records.

Strictly speaking, this is not the return value of mybatis. Mybatis is only the return value of the database connection driver (usually JDBC). In other words, if the driver tells that the update of two records is affected, we will get that the return value of mybatis will be 2, which has nothing to do with mybatis itself.

I understand the truth. If we have to explicitly return the number of affected records in the update operation of mybatis, is there any way?

Of course.

By explicitly specifying the useaffectedrows option on the JDBC URL, we can get the number of affected records:

We slightly modified our database connection configuration and added the useaffectedrows field:

At this point, the update operation of mybatis should return the number of affected items. Let's run the unit test again to try:

The update operation returns the number of affected records. We know that 0 is inconsistent with our expected 2. Naturally, the unit test fails.

summary

The above is the return value of mybatis update operation introduced by Xiaobian. I hope it will be helpful to you. If you have any questions, please leave me a message, and Xiaobian will reply to you in time. Thank you very much for your support for the programming tips website!