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 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 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 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>