mybatis动态数据更新 + 批量动态数据插入
动态更新
<update id="updateElevator" parameterType="com.diantijiang.saas.data.elevator.Elevator" > update dc_elevator <set> <if test="inspectionCode != null">inspection_code = #{inspectionCode,jdbcType=VARCHAR},</if> <if test="brandCode!=null">brand_code = #{brandCode},</if> <if test="modelSpecs!=null">model_specs = #{modelSpecs},</if> <if test="elevatorType!=null">elevator_type=#{elevatorType},</if> <if test="idDistrict!=null">id_district=#{idDistrict},</if> <if test="idPropertyCompany!=null">id_property_company=#{idPropertyCompany},</if> <if test="idServiceCompany!=null">id_service_company=#{idServiceCompany},</if> <if test="idServiceManager!=null">id_service_manager=#{idServiceManager},</if> <if test="contractCode!=null">contract_code=#{contractCode},</if> <if test="runStatus!=null">run_status=#{runStatus},</if> <if test="qrCodeCheck!=null">qr_code_check=#{qrCodeCheck},</if> <if test="qrCodeProduct!=null">qr_code_product=#{qrCodeProduct},</if> <if test="lastAnnualDate!=null">last_annual_date=#{lastAnnualDate},</if> <if test="nextAnnualDate!=null">next_annual_date=#{nextAnnualDate},</if> <if test="lastMaintainDate!=null">last_maintain_date=#{lastMaintainDate},</if> <if test="nextMaintainDate!=null">next_maintain_date=#{nextMaintainDate},</if> <if test="repairTimes!=null">repair_times=#{repairTimes},</if> <if test="maintainTimes!=null">maintain_times=#{maintainTimes},</if> <if test="accessoriesExchanges!=null">accessories_exchanges=#{accessoriesExchanges},</if> <if test="installDate!=null">Install_date=#{installDate},</if> <if test="status!=null">status=#{status},</if> <if test="createdBy!=null">created_by=#{createdBy},</if> <if test="createdDate!=null">created_date=#{createdDate},</if> <if test="updatedBy!=null">updated_by=#{updatedBy},</if> <if test="updatedDate!=null">updated_date=#{updatedDate}</if> </set> where id_elevator = #{idElevator} </update>
批量动态插入
说明：当前方式在插入一条时正常，插入多条时会出现拼接错误！如果需要多条插入请参照修订方法！
<insert id="batchInsert" parameterType="java.util.List" > insert into dc_elevator ( <foreach collection="list" separator="," item="item" index="index"> <if test="item.inspectionCode != null">inspection_code,</if> <if test="item.brandCode!=null">brand_code,</if> <if test="item.modelSpecs!=null">model_specs,</if> <if test="item.elevatorType!=null">elevator_type,</if> <if test="item.idDistrict!=null">id_district,</if> <if test="item.idPropertyCompany!=null">id_property_company,</if> <if test="item.idServiceCompany!=null">id_service_company,</if> <if test="item.idServiceManager!=null">id_service_manager,</if> <if test="item.contractCode!=null">contract_code,</if> <if test="item.runStatus!=null">run_status,</if> <if test="item.qrCodeCheck!=null">qr_code_check,</if> <if test="item.qrCodeProduct!=null">qr_code_product,</if> <if test="item.lastAnnualDate!=null">last_annual_date,</if> <if test="item.nextAnnualDate!=null">next_annual_date,</if> <if test="item.lastMaintainDate!=null">last_maintain_date,</if> <if test="item.nextMaintainDate!=null">next_maintain_date,</if> <if test="item.repairTimes!=null">repair_times,</if> <if test="item.maintainTimes!=null">maintain_times,</if> <if test="item.accessoriesExchanges!=null">accessories_exchanges,</if> <if test="item.installDate!=null">Install_date,</if> <if test="item.status!=null">status,</if> <if test="item.createdBy!=null">created_by,</if> <if test="item.createdDate!=null">created_date,</if> <if test="item.updatedBy!=null">updated_by,</if> <if test="item.updatedDate!=null">updated_date,</if> id_elevator </foreach> ) values <foreach collection="list" index="index" item="item" separator=","> ( <if test="item.inspectionCode != null">#{item.inspectionCode},</if> <if test="item.brandCode!=null">#{item.brandCode},</if> <if test="item.modelSpecs!=null">#{item.modelSpecs},</if> <if test="item.elevatorType!=null">#{item.elevatorType},</if> <if test="item.idDistrict!=null">#{item.idDistrict},</if> <if test="item.idPropertyCompany!=null">#{item.idPropertyCompany},</if> <if test="item.idServiceCompany!=null">#{item.idServiceCompany},</if> <if test="item.idServiceManager!=null">#{item.idServiceManager},</if> <if test="item.contractCode!=null">#{item.contractCode},</if> <if test="item.runStatus!=null">#{item.runStatus},</if> <if test="item.qrCodeCheck!=null">#{item.qrCodeCheck},</if> <if test="item.qrCodeProduct!=null">#{item.qrCodeProduct},</if> <if test="item.lastAnnualDate!=null">#{item.lastAnnualDate},</if> <if test="item.nextAnnualDate!=null">#{item.nextAnnualDate},</if> <if test="item.lastMaintainDate!=null">#{item.lastMaintainDate},</if> <if test="item.nextMaintainDate!=null">#{item.nextMaintainDate},</if> <if test="item.repairTimes!=null">#{item.repairTimes},</if> <if test="item.maintainTimes!=null">#{item.maintainTimes},</if> <if test="item.accessoriesExchanges!=null"> #{item.accessoriesExchanges},</if> <if test="item.installDate!=null">#{item.installDate},</if> <if test="item.status!=null">#{item.status},</if> <if test="item.createdBy!=null">#{item.createdBy},</if> <if test="item.createdDate!=null">#{item.createdDate},</if> <if test="item.updatedBy!=null">#{item.updatedBy},</if> <if test="item.updatedDate!=null">#{item.updatedDate},</if> #{item.idElevator} ) </foreach> </insert>
修订方法
实体类
@Repository(value = "log") public class Log { private String id; //日志编号 private String userid; //用户名 private String time; //时间 private String type; //类型 private String detail; //详情 private String ip; //ip地址
......
dao层
@Service(value = "logDao") public interface ILogDao { //测试动态批量插入数据 boolean batchInsert( @Param("key") Log log, @Param("value") List<Log> list ); }
mapper.xml
<!--测试批量插入（传入的参数不止一个，就不要指定参数类型）--> <insert id="batchInsert" > -- parameterType="java.util.HashMap" INSERT INTO log( <if test="null != key.userid"> userid, </if> <if test="null != key.detail"> detail, </if> <if test="null != key.ip"> ip, </if> id ) VALUES <foreach collection="value" index="index" item="item" separator=","> ( <if test="null != item.userid">#{item.userid},</if> <if test="null != item.detail"> #{item.detail}, </if> <if test="null != item.ip"> #{item.ip}, </if> #{item.id} ) </foreach> </insert>
工具类：计算对象非空属性的数量
package com.amayadream.webchat.utils; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.util.Map; /** * @Description: 对象属性工具 * @Author: WangSong * @CreateDate: 2020/8/2 4:59 */ public class ObjectFieldUtil { //计算对象非空属性个数 public static int calculateNotEmptyFieldsNum(Object object) { int num = 0; if (object != null) { Class<?> entity = object.getClass(); Field[] fields = entity.getDeclaredFields();//获取该类的所有成员变量（私有的） for (Field field : fields) { try { field.setAccessible(true); if (field.get(object) != null && !"".equals(field.get(object))) { num ++; break; } } catch (IllegalAccessException e) { e.printStackTrace(); } } } return num; } }
test类
package com.amayadream.webchat.test; import com.amayadream.webchat.dao.ILogDao; import com.amayadream.webchat.pojo.Log; import com.amayadream.webchat.utils.ObjectFieldUtil; import org.junit.After; import org.junit.Before; import org.junit.Test; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import java.util.*; /** * @Description: 测试 * @Author: WangSong * @CreateDate: 2020/8/2 2:33 */ public class TestPro { private ApplicationContext ac; @Before public void init() { ac = new ClassPathXmlApplicationContext("classpath:spring/spring-mybatis.xml"); } @After public void closesql() { } @Test public void testBatchInsert() { ILogDao logDao = (ILogDao) ac.getBean("logDao"); //测试批量插入 List<Log> list = new ArrayList<>(); Log keyLog = new Log(); for (int i = 0; i < 4; i++) { //value Log log = new Log(); log.setId("114"+i); log.setIp("192.168.1.1"); log.setUserid("admin"); log.setDetail("测试数据"+i); list.add(log); //key --- 保证在执行sql时不出错：不为空的每个字段都能被插入 if (ObjectFieldUtil.calculateNotEmptyFieldsNum(keyLog) < ObjectFieldUtil.calculateNotEmptyFieldsNum(log)) { keyLog = log; //只要当前对象的非空属性个数 > 上一个对象非空属性个数 ----> 当前对象作为key } } System.out.println(logDao.batchInsert(keyLog,list)); //测试查询 List<Log> logs = logDao.selectAll(0, 10); logs.forEach(e -> { System.out.println(e); }); //单个数据插入 // Log log = new Log(); // log.setId("114"); // log.setIp("192.168.1.1"); // log.setUserid("admin"); // log.setDetail("测试数据"); // System.out.println(logDao.insert(log)); } }
结果
动态插入 - 返回主键
<insert id="insertCardWithId" parameterType="com.diantijiang.saas.account.entity.BankCard"> /*先插入，再将主键返回实体*/ <selectKey resultType="java.lang.Long" order="AFTER" keyProperty="cardId"> SELECT LAST_INSERT_ID() </selectKey> /*插入*/ INSERT INTO ac_bank_card <trim prefix="(" suffix=")" suffixOverrides=","> bank_name, card_number, <if test="null != expiryDate and ''!= expiryDate">expiry_date,</if> ower, account_id </trim> VALUES <trim prefix="(" suffix=")" suffixOverrides=","> #{bankName}, #{cardNumber}, <if test="null != expiryDate and ''!=expiryDate"> #{expiryDate}, </if> #{ower}, #{accountId} </trim> </insert>
