MyBatis标签的使用

作为CRUD Boy，保存一些模板还是有用的，后续需要的话直接取过来改改就好，这里我做了个插入、更新、条件插入、条件更新

批量操作的MyBatis模板，均单测通过。首先我们定义表结构：

1 定义表结构

CREATE TABLE `user_info` (
`id` BIGINT ( 20 ) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`user_id` INT ( 11 ) NOT NULL,
`user_name` VARCHAR ( 255 ) DEFAULT NULL,
`db_update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '数据记录更新时间',
PRIMARY KEY ( `id` ),
UNIQUE KEY `uk_user_id` ( `user_id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;

2 逆向工程生成实体类、Mapper、Mapper.xml

（逆向工程不会用的参考另一篇博客：Mybatis逆向工程）

import java.io.Serializable;
import java.util.Date;

public class UserInfoPO implements Serializable {
    private Long id;
    private Integer userId;
    private String userName;
    private Date dbUpdateTime;

    // getter/setter
}

3 自定义Mapper扩展

MapperExt.java：

import dao.gen.po.UserInfoPO;
import java.util.List;

public interface UserInfoPOMapperExt extends UserInfoPOMapper {
    /**
     * 插入一条记录
     * 
     * @param userInfoPO
     * @return
     */
    int insertSelective(UserInfoPO userInfoPO);

    /**
     * 插入一条记录，如果唯一键冲突则更新
     * 
     * @param userInfoPO
     * @return
     */
    int insertOnDuplicateUpdate(UserInfoPO userInfoPO);

    /**
     * 更新一条记录
     * 
     * @param userInfoPO
     * @return
     */
    int updateSelective(UserInfoPO userInfoPO);

    /**
     * 批量插入
     * 
     * @param userInfoPOList
     * @return
     */
    int batchInsert(List<UserInfoPO> userInfoPOList);

    /**
     * 批量更新
     * 
     * @param userInfoPOList
     * @return
     */
    int batchUpdate(List<UserInfoPO> userInfoPOList);
}

对应的Mapper.xml（划重点）：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dao.gen.mapper.UserInfoPOMapperExt">
    <insert id="insertSelective" parameterType="dao.gen.po.UserInfoPO">
        <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Long">
            SELECT LAST_INSERT_ID()
        </selectKey>
        insert into user_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userId != null">
                user_id,
            </if>
            <if test="userName != null">
                user_name,
            </if>
            <if test="dbUpdateTime != null">
                db_update_time,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="userId != null">
                #{userId,jdbcType=INTEGER},
            </if>
            <if test="userName != null">
                #{userName,jdbcType=VARCHAR},
            </if>
            <if test="dbUpdateTime != null">
                #{dbUpdateTime,jdbcType=TIMESTAMP},
            </if>
        </trim>
    </insert>

    <insert id="insertOnDuplicateUpdate" parameterType="dao.gen.po.UserInfoPO">
        insert into user_info
        <trim prefix="(" suffix=")" suffixOverrides="," >
            <if test="id != null" >
                id,
            </if>
            <if test="userId != null" >
                user_id,
            </if>
            <if test="userName != null" >
                user_name,
            </if>
            <if test="dbUpdateTime != null" >
                db_update_time,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides="," >
            <if test="id != null" >
                #{id,jdbcType=BIGINT},
            </if>
            <if test="userId != null" >
                #{userId,jdbcType=INTEGER},
            </if>
            <if test="userName != null" >
                #{userName,jdbcType=VARCHAR},
            </if>
            <if test="dbUpdateTime != null" >
                #{dbUpdateTime,,jdbcType=TIMESTAMP},
            </if>
        </trim>
        ON DUPLICATE KEY UPDATE
        <trim suffixOverrides="," >
            <if test="userName != null" >
                user_name = #{userName,jdbcType=VARCHAR},
            </if>
            <if test="dbUpdateTime != null" >
                db_update_time = #{dbUpdateTime,,jdbcType=TIMESTAMP},
            </if>
        </trim>
    </insert>

    <update id="updateSelective" parameterType="dao.gen.po.UserInfoPO" >
        UPDATE user_info
        <set >
            <if test="id != null" >
                id = #{id,jdbcType=BIGINT},
            </if>
            <if test="userId != null" >
                user_id = #{userId,jdbcType=INTEGER},
            </if>
            <if test="userName != null" >
                user_name = #{userName,jdbcType=VARCHAR},
            </if>
            <if test="dbUpdateTime != null" >
                db_update_time = #{dbUpdateTime,,jdbcType=TIMESTAMP},
            </if>
        </set>
        WHERE
        <trim prefixOverrides="and | or">
            <if test="id != null">
                AND id = #{id,jdbcType=BIGINT}
            </if>
            <if test="userId != null" >
                AND user_id = #{userId,jdbcType=INTEGER}
            </if>
        </trim>
    </update>

    <insert id="batchInsert" parameterType="java.util.List">
        <selectKey resultType="java.lang.Long" keyProperty="id" order="AFTER" >
            SELECT LAST_INSERT_ID()
        </selectKey>
        INSERT INTO user_info(user_id, user_name)
        VALUES
        <foreach collection="list" item="userInfoPO" index="index" separator=",">
            (#{userInfoPO.userId,jdbcType=BIGINT}, #{userInfoPO.userName,jdbcType=VARCHAR})
        </foreach>
    </insert>

    <update id="batchUpdate" parameterType="java.util.List">
        <foreach collection="list" item="userInfoPO"  separator=";">
            UPDATE user_info
            <set >
                <if test="userInfoPO.id != null" >
                    id = #{userInfoPO.id,jdbcType=BIGINT},
                </if>
                <if test="userInfoPO.userId != null" >
                    user_id = #{userInfoPO.userId,jdbcType=INTEGER},
                </if>
                <if test="userInfoPO.userName != null" >
                    user_name = #{userInfoPO.userName,jdbcType=VARCHAR},
                </if>
                <if test="userInfoPO.dbUpdateTime != null" >
                    db_update_time = #{userInfoPO.dbUpdateTime,,jdbcType=TIMESTAMP},
                </if>
            </set>
            WHERE
            <trim prefixOverrides="and | or">
                <if test="userInfoPO.id != null">
                    AND id = #{userInfoPO.id,jdbcType=BIGINT}
                </if>
                <if test="userInfoPO.userId != null" >
                    AND user_id = #{userInfoPO.userId,jdbcType=INTEGER}
                </if>
            </trim>
        </foreach>
    </update>
</mapper>

4 Service层调用

import entity.dto.UserInfoDTO;
import java.util.List;

public interface UserInfoService {
    /**
     * 插入一条记录
     * @param userInfoDTO
     * @return
     */
    int insertSelective(UserInfoDTO userInfoDTO);

    /**
     * 插入一条记录，如果唯一键冲突则更新
     * @param userInfoDTO
     * @return
     */
    int insertOnDuplicateUpdate(UserInfoDTO userInfoDTO);

    /**
     * 批量插入
     * @param userInfoPOList
     */
    int batchInsert(List<UserInfoDTO> userInfoPOList);

    /**
     * 批量更新
     * @param userInfoPOList
     */
    int batchUpdate(List<UserInfoDTO> userInfoPOList);
}

import dao.gen.mapper.UserInfoPOMapperExt;
import dao.gen.po.UserInfoPO;
import entity.dto.UserInfoDTO;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import service.UserInfoService;

import java.util.ArrayList;
import java.util.List;

/**
 * @Description 用户信息
 * @Author lilong
 * @Date 2019-04-03 15:19
 */
@Service
public class UserInfoServiceImpl implements UserInfoService {
    @Autowired
    private UserInfoPOMapperExt userInfoPOMapper;

    @Override
    public int insertSelective(UserInfoDTO userInfoDTO) {
        return userInfoPOMapper.insertSelective(convertUserInfo2PO(userInfoDTO));
    }

    @Override
    public int insertOnDuplicateUpdate(UserInfoDTO userInfoDTO) {
        return userInfoPOMapper.insertOnDuplicateUpdate(convertUserInfo2PO(userInfoDTO));
    }

    @Override
    @Transactional
    public int batchInsert(List<UserInfoDTO> userInfoDTOList) {
        List<UserInfoPO> userInfoPOList = this.convertUserInfoList2PO(userInfoDTOList);
        return userInfoPOMapper.batchInsert(userInfoPOList);
    }

    @Override
    @Transactional
    public int batchUpdate(List<UserInfoDTO> userInfoDTOList) {
        List<UserInfoPO> userInfoPOList = this.convertUserInfoList2PO(userInfoDTOList);
        return userInfoPOMapper.batchUpdate(userInfoPOList);
    }

    private List<UserInfoPO> convertUserInfoList2PO(List<UserInfoDTO> userInfoDTOList) {
        List<UserInfoPO> userInfoPOList = new ArrayList<>();

        userInfoDTOList.forEach(userInfoDTO -> {
            userInfoPOList.add(convertUserInfo2PO(userInfoDTO));
        });

        return userInfoPOList;
    }

    private UserInfoPO convertUserInfo2PO(UserInfoDTO userInfoDTO) {
        UserInfoPO userInfoPO = new UserInfoPO();
        BeanUtils.copyProperties(userInfoDTO, userInfoPO);
        return userInfoPO;
    }
}

5 单测

import entity.dto.UserInfoDTO;
import mytest.base.BaseTest;
import org.springframework.beans.factory.annotation.Autowired;
import org.testng.annotations.Test;
import service.UserInfoService;

import java.util.ArrayList;
import java.util.List;

/**
 * @Description 用户信息单测
 * @Author lilong
 * @Date 2019-04-03 15:23
 */
public class UserInfoServiceTest extends BaseTest {
    @Autowired
    private UserInfoService userInfoService;

    @Test
    public void testBatchInsert() {
        List<UserInfoDTO> userInfoPOList = new ArrayList<>();
        userInfoPOList.add(new UserInfoDTO(4, "a1"));
        userInfoPOList.add(new UserInfoDTO(5, "b1"));
        userInfoPOList.add(new UserInfoDTO(6, "c1"));

        userInfoService.batchInsert(userInfoPOList);
    }

    @Test
    public void testInsertSelective() {
        userInfoService.insertSelective(new UserInfoDTO(8, "m"));
        userInfoService.insertSelective(new UserInfoDTO(9, null));
    }

    @Test
    public void testInsertOnDuplicateUpdate() {
        UserInfoDTO UserInfoDTO = new UserInfoDTO(7, "a3");
        userInfoService.insertOnDuplicateUpdate(UserInfoDTO);
    }

    @Test
    public void testBatchUpdate() {
        List<UserInfoDTO> userInfoPOList = new ArrayList<>();
        userInfoPOList.add(new UserInfoDTO(4, "a2"));
        userInfoPOList.add(new UserInfoDTO(5, "b2"));
        userInfoPOList.add(new UserInfoDTO(6, "c2"));
        userInfoService.batchUpdate(userInfoPOList);
    }
}