作为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);
}
}