Shulou(Shulou.com)06/02 Report--

In this issue, the editor will bring you about the way Mybatis updates entity objects in batches. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

Mybatis batch updates entity objects (1) Dao layer interface / * according to update purchase plan (batch) * @ param plans * / void batchUpdatePlan (List plans) (2) Mapper.xml file comId = # {item.comId} AND id = # {item.id} UPDATE pub_purchase_plan WHEN comId = # {item.comId} AND id = # {item.id} THEN # {item.warehouseId} WHEN comId = # {item.comId} AND id = # {item.id} THEN # {item.productId} WHEN comId = # {item.comId} AND id = # {item.id} THEN # {item.amount} WHEN comId = # {item.comId} AND id = # {item.id} THEN # {item.deleted} WHEN comId = # {item.comId} AND id = # {item.id} THEN # {item.price} WHEN comId = # {item.comId} AND id = # {item.id} THEN # {item.type} Mybatis comparison of the efficiency of three methods of batch updating data

There are three ways to realize it.

1. Use the for loop to loop out N sql through the parameter set passed by the loop.

2. Batch update based on the case when condition of mysql.

3. Batch update with ON DUPLICATE KEY UPDATE

Let's implement it.

Note that for the first method to succeed, you need to follow the db link url with a parameter & allowMultiQueries=true.

Namely: jdbc:mysql://localhost:3306/mysqlTest?characterEncoding=utf-8&allowMultiQueries=true

In fact, this kind of thing written in the past is almost the same code, without repetition, directly on the code.

Update standard_relation standard_from_uuid = # {item.standardFromUuid,jdbcType=VARCHAR}, standard_to_uuid = # {item.standardToUuid,jdbcType=VARCHAR}, gmt_modified = # {item.gmtModified,jdbcType=TIMESTAMP} Where id = # {item.id JdbcType=BIGINT} update standard_relation when id=# {i.id} then # {i.standardFromUuid} When id=# {i.id} then # {i.standardToUuid} when id=# {i.id} then # {i.gmtModified} The third method of batch update for where id=# {i.id} Use ON DUPLICATE KEY UPDATE insert into standard_relation (id,relation_type, standard_from_uuid, standard_to_uuid, relation_score, stat, last_process_id, is_deleted, gmt_created, gmt_modified,relation_desc) VALUES (# {item.id,jdbcType=BIGINT}, # {item.relationType,jdbcType=VARCHAR}, # {item.standardFromUuid,jdbcType=VARCHAR}, # {item.standardToUuid,jdbcType=VARCHAR}) # {item.relationScore,jdbcType=DECIMAL}, # {item.stat,jdbcType=TINYINT}, # {item.lastProcessId,jdbcType=BIGINT}, # {item.isDeleted,jdbcType=TINYINT}, # {item.gmtCreated,jdbcType=TIMESTAMP}, # {item.gmtModified,jdbcType=TIMESTAMP}, # {item.relationDesc,jdbcType=VARCHAR}) ON DUPLICATE KEY UPDATE id=VALUES (id), relation_type = VALUES (relation_type), standard_from_uuid = VALUES (standard_from_uuid) Standard_to_uuid = VALUES (standard_to_uuid), relation_score = VALUES (relation_score), stat = VALUES (stat), last_process_id = VALUES (last_process_id), is_deleted = VALUES (is_deleted), gmt_created = VALUES (gmt_created), gmt_modified = VALUES (gmt_modified), relation_desc = VALUES (relation_desc) @ Override public void updateStandardRelations () {List list=standardRelationMapper.selectByStandardUuid ("xiemingjieupdate") For (StandardRelation tmp:list) {tmp.setStandardFromUuid (tmp.getStandardFromUuid () + "update"); tmp.setStandardToUuid (tmp.getStandardToUuid () + "update");} long begin=System.currentTimeMillis (); standardRelationManager.updateBatch (list); long end=System.currentTimeMillis (); System.out.print ("current batch update method usage time" + (end-begin) + "ms");}

Sql statement for loop efficiency is actually quite high, because it only has a loop body, but in the end there are more update statements, a large number of sql statements may cause sql blocking.

Although case when will only have one update statement in the end, there are a lot of loop bodies in xml, and each case when has to loop through the list collection, so it will be slow to spell sql in large quantities, so the efficiency problem is serious. It is recommended to insert it in batches when using it.

Duplicate key update can be seen to be the fastest, but it is generally banned in large companies, and companies generally prohibit the use of replace into and INSERT INTO. ON DUPLICATE KEY UPDATE, this kind of sql may cause data loss to be inconsistent with the self-increasing id value of the master-slave table. And when using this update, be sure to add id, and values () parentheses put the database field, not the property field of the java object.

According to the comprehensive consideration of efficiency and safety, it is very important to choose the right one.

This is how the editor shares the Mybatis batch update of entity objects. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.