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

This article mainly explains "how to understand how to add, delete, query and modify mybatis in batches". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let the editor take you to learn "how to understand how to add, delete, query and modify mybatis in batches"!

Catalogue

Previous instructions:

There are three main situations:

(1) batch addition of mybatis

(2) batch deletion of mybatis

(3) mybatis batch query

(4) batch modification of mybatis

MySql Case function

Dynamic batch modification: DeviceMapper.xml

Dynamic batch modification: DeviceMapper.java

Control layer (xxxxController)

Every time I write in batch, I have to search on the Internet. Although I have done it many times, I still can't remember it (shame), so I just record it today.

Previous instructions:

Foreach is mainly used in building in conditions, which can iterate over a collection in a SQL statement. The main attribute of the foreach element is item,index,collection,open,separator,close.

Item represents the alias of each element in the collection when iterating. Index specifies a name to indicate the location of each iteration during the iteration. Open indicates where the statement begins, separator indicates what symbol is used as the delimiter between each iteration, and close indicates what ends with. The most important and error-prone attribute when using foreach is the collection attribute, which must be specified. However, the value of this property is different in different cases.

There are three main situations:

1. If a single parameter is passed and the parameter type is a List, the value of the collection attribute is list

two。 If a single parameter is passed and the parameter type is an array array, the property value of collection is array

3. If multiple parameters are passed, we need to encapsulate them into a Map. Of course, a single parameter can also be encapsulated as a map. In fact, if you pass a parameter, it will also be encapsulated as a Map in the breast. The key of map is the parameter name, so the value of the collection attribute is the key of the passed List or array object in its own encapsulated map.

(1) batch addition of mybatis

Mapper.xml

Insert into el_user_info (id,user_name,user_tel,pass_word,user_sex,del_mark,position_id,agency_id,create_date,update_date,role_id,employee_id,org_id) values (# {item.id}, # {item.userName}, # {item.userTel}, # {item.passWord}, # {item.userSex}, # {item.delMark} # {item.positionId}, # {item.agencyId}, # {item.createDate}, # {item.updateDate}, # {item.roleId}, # {item.employeeId}, # {item.orgId})

Mapper:

/ * bulk insert * * @ param list * @ return * / int createBatchUserInfoList (List list); serviceImpl implementation class: try {List userList = new ArrayList (); if (CollectionUtils.isNotEmpty (list)) {/ / Organization id Long orgId = elAppInfoService.getOrg (). GetOrgId (); for (int I = 0; I

< list.size(); i++) { Map map = list.get(i); String userSex = map.get("userSex").toString().trim(); String userName = map.get("userName").toString().trim(); String userTel = map.get("userTel").toString().trim(); String key = userName + userTel; String redisCacheByKey = redisCacheService.getRedisCacheByKey(key); log.info(redisCacheByKey); if (!StringUtils.isEmpty(redisCacheByKey)) { //redisCacheService.deleteRedisCacheByKey(key); continue; } if ("男".equals(userSex)) { userSex = "0"; } else if ("女".equals(userSex)){ userSex = "1"; } ElUserInfo user = new ElUserInfo(); user.setUserName(userName); user.setUserTel(userTel); user.setPassWord(MD5(map.get("passWord").toString().trim())); user.setUserSex(userSex); user.setPositionId(postionId); user.setAgencyId(agencyId); user.setCreateDate(new Date()); user.setUpdateDate(new Date()); user.setDelMark(0); user.setRoleId(1L); user.setEmployeeId(0L); user.setOrgId(orgId); userList.add(user); } if (CollectionUtils.isNotEmpty(userList)) { //先持久化本地 row = userInfoMapper.createBatchUserInfoList(userList); if (row >

0) {/ / synchronous organization platform String add = orgEmployeeService.addOrganRoleUserToPlatform (userList, "add") after successful persistence If (! StringUtils.isEmpty (add) & &! "- 1" .equals (add) & &! "null" .equals (add)) {/ / batch modify OrgId and EmployeeId userInfoMapper.updateBatchOrgId (userList) with the user's mobile phone number as the only label } log.info (this.getClass (). GetName () + "UserInfoThread" + add.toString ());} catch (Exception e) {log.error ("elPositionInfoServiceImpl's UserInfoThread method error" + e.getMessage (), e);} (2) mybatis batch deletion

Mapper.xml:

Delete from t_user where id in (# {id}) delete from t_user where id in # {id}

Mapper:

Int batchDeleteUser (int [] ids); (3) mybatis batch query

Mapper.xml:

Select from el_position_info where roleCode in # {item.roleCode}

Mapper:

List getPostionListIdsByRoleCode (List list); (4) batch modification of mybatis

Mybatis batch modification (the value of update is also dynamic)

Recently, the company has a business: count the online status of the device app, write a heartbeat, and get the status of app every minute, mainly divided into:

(1) the intranet is online

(2) the external network is online

(3) third-party network

(4) offline

Put it in the collection, and then I'm batch modifying the identity status of the onlineState for each device. This requires dynamic batch modification of values in onlineState, but mybatis does not support set onlineState =? (onlineState is dynamic). Then by consulting the relevant information, through the case when then of mysql to achieve. The specific implementation is as follows:

MySql Case function SELECT SUM (population) CASE country WHEN, China, THEN, Asia, WHEN, India, THEN, Asia, WHEN, Japan, THEN, Asia, WHEN, United States, THEN, North America, WHEN, Canada, THEN, North America, WHEN. Brother 'THEN' North America 'ELSE' other 'END FROM Table_A dynamic batch modification: DeviceMapper.xml update t_device set online_state = case device_no WHEN # {item.deviceNo} THEN # {item.onlineState} END where device_no in # {device.deviceNo} dynamic batch modification: DeviceMapper.javaint updateBatchOnlineState (List list) The control layer (xxxxController) / / the front end of the online device number is called once in 300 seconds, and the server considers it expired List deviceNos = DeviceCache.getDeviceNo (640 seconds); List list = new ArrayList (); if (CollectionUtils.isNotEmpty (deviceNos)) {for (String str: deviceNos) {Device device = new Device (); int indexOf = str.lastIndexOf ("-"); String deviceNo = str.substring (0, indexOf); String type = str.substring (indexOf+1, str.length ()) Device.setDeviceNo (deviceNo); device.setOnlineState (Integer.valueOf (type)); list.add (device);} int row = deviceService.updateBatchOnlineState (list); if (row < 1) {logger.debug ("batch modification failed!") ;} else {logger.debug ("device online status has been obtained in real time after batch modification!") ;}} else {logger.debug ("there is no device online at present!") ; deviceService.modifyAllNotOnline ();} at this point, I believe you have a better understanding of "how to understand how to add, delete, query and modify mybatis in batches". You might as well do it in practice! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.