mybatis-crud

前言

工作中程序员开发根本CURD，使用频繁，本文对多种增删改查写法做出汇总对比，明确什么场景哪种写法更适合。

从繁到简分为Mybatis-XML、Mybatis-注解和MybatisPlus写法。

正文

以employee员工表和department部门表举例，文末附demo

public class Employee extends BaseEntity {
    /**
     * 部门id
     */
    private Long departId;
    /**
     * 姓名
     */
    private String employName;
    ...
}

public class Department extends BaseEntity {
    /**
     * 部门名称
     */
    private String departName;
    ...
}

单个新增

//Mybatis-xml写法
int insertOne(Employee employee);

<insert id="insertOne" parameterType="com.springboot.base.entity.Employee" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO employee(depart_id, employ_name, age, sex, employ_desc)
    VALUES (#{departId}, #{employName}, #{age}, #{sex}, #{employDesc});
</insert>

//Mybatis-注解写法
@Insert("insert into department(depart_name,depart_desc) values(#{departName},#{departDesc})")
@Options(useGeneratedKeys=true, keyProperty="id")
int insertOne(Department department);

//MybatisPlus写法
boolean result = employeeService.save(employee);

批量新增

//Mybatis-xml写法
int batchInsert(@Param("employeeList") List<Employee> employees);

<insert id="batchInsert" parameterType="com.springboot.base.entity.Employee">
    INSERT INTO employee(depart_id, employ_name, age, sex, employ_desc)
    VALUES
    <foreach collection="employeeList" item="item" separator=",">
        (#{item.departId}, #{item.employName}, #{item.age}, #{item.sex}, #{item.employDesc})
    </foreach>
</insert>

//MybatisPlus写法
boolean result = employeeService.saveBatch(employeeList);

删除

//Mybatis-xml写法
int deleteById(Long id);

<update id="deleteById">
    update employee set is_deleted='1' where id=#{id}
</update>

//Mybatis-注解写法
@Delete("delete from department where id = #{id}")
int deleteById(Long id);

//MybatisPlus写法
boolean result = employeeService.removeById(2L);

修改-值可改为空

//Mybatis-xml写法
int updateAllById(Employee employee);

<update id="updateAllById" parameterType="com.springboot.base.entity.Employee">
    update employee set depart_id=#{departId},name=#{employName},age=#{age},sex=#{sex},desc=#{employDesc} where id=#{id}
</update>

//Mybatis-注解写法
@Update("update department set depart_name=#{departName},depart_desc=#{departDesc} where id=#{id}")
int updateAllById(Department department);

//MybatisPlus写法
QueryWrapper<Employee> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("id", employee.getId());
employeeService.update(employee, queryWrapper);

修改-值不为空

//Mybatis-注解写法
int updateByIdSelective(Employee employee);

<update id="updateByIdSelective" parameterType="com.springboot.base.entity.Employee">
    update employee
    <set>
        <if test="departId!=null">
            depart_id=#{departId},
        </if>
        <if test="employName!=null and employName!=''">
            employ_name=#{employName},
        </if>
        ...
    </set>
    where id=#{id}
</update>

//MybatisPlus写法
boolean b = employeeService.updateById(employee);

单表查询

//Mybatis-xml写法
Employee queryById(Long id);

<select id="queryById" resultType="com.springboot.base.entity.Employee">
    select * from employee where id=#{id}
</select>

//Mybatis-注解写法
@Select("select * from department where id=#{id}")
Department queryById(Long id);

//Mybatis-注解里foreach
@Select("<script>" +
        "select * from department where id in " +
        "<foreach collection='ids' item='id' open='(' close=')' separator=','>" +
        "#{id}" +
        "</foreach>" +
        "</script>")
List<Department> queryListByIds(@Param("ids")List<Long> ids);

//MybatisPlus写法
Employee employee = employeeService.getById(1L);

多表查询

//Mybatis-xml写法
List<EmployeeInfoResDTO> queryListByCondition(EmployeeInfoReqDTO reqDTO);

<select id="queryListByCondition" parameterType="com.springboot.base.dto.EmployeeInfoReqDTO" resultType="com.springboot.base.dto.EmployeeInfoResDTO">
    select e.*,d.depart_name from employee e left join department d on e.depart_id=d.id
    <where>
        <if test="departId!=null">
             and e.depart_id=#{departId}
        </if>
        ...
        <if test="employDesc!=null and employDesc!=''">
            and e.employ_desc like concat('%', #{employDesc}, '%')
        </if>
    </where>
</select>

//MybatisPlus写法
QueryWrapper<EmployeeInfoReqDTO> queryWrapper = new QueryWrapper<>();
if(reqDTO.getDepartId()!=null){
    queryWrapper.eq("e.depart_id", reqDTO.getDepartId());
}
...
if(StringUtils.hasLength(reqDTO.getEmployDesc())){
    queryWrapper.like("e.employ_desc", reqDTO.getEmployDesc());
}
employeeMapper.queryListByCondition2(queryWrapper);

//Mybatis-注解+MybatisPlus写法
@Select("select e.*,d.depart_name from employee e left join department d on e.depart_id=d.id ${ew.customSqlSegment}")
List<EmployeeInfoResDTO> queryListByCondition2(@Param(Constants.WRAPPER) Wrapper<EmployeeInfoReqDTO> wrapper);

分页查询

//mybatisPlus单表分页
IPage<Employee> page = new Page<>(1, 3);
IPage<Employee> result = employeeService.page(page);

//mybatisPlus结合mybatis-xml多表分页
IPage<EmployeeInfoResDTO> queryListByCondition(EmployeeInfoPageReqDTO reqDTO);

//使用
EmployeeInfoPageReqDTO reqDTO = EmployeeInfoPageReqDTO.builder().departId(1L).build();
reqDTO.setCurrent(1L);
reqDTO.setSize(3);
IPage<EmployeeInfoResDTO> result2 = employeeMapper.queryListByCondition(reqDTO);

<select id="queryListByCondition" parameterType="com.springboot.base.dto.EmployeeInfoReqDTO" resultType="com.springboot.base.dto.EmployeeInfoResDTO">
    select e.*,d.depart_name from employee e left join department d on e.depart_id=d.id
    <where>
        <if test="departId!=null">
             and e.depart_id=#{departId}
        </if>
    </where>
</select>

//mybatisPlus结合mybatis-注解多表分页
@Select("select e.*,d.depart_name from employee e left join department d on e.depart_id=d.id ${ew.customSqlSegment}")
IPage<EmployeeInfoResDTO> queryListByCondition2(IPage<EmployeeInfoResDTO> page,@Param(Constants.WRAPPER) Wrapper<EmployeeInfoReqDTO> wrapper);

//使用
QueryWrapper<EmployeeInfoReqDTO> queryWrapper = new QueryWrapper<>();
if(reqDTO.getDepartId()!=null){
    queryWrapper.eq("e.depart_id", reqDTO.getDepartId());
}
IPage<EmployeeInfoResDTO> page = new Page<>(reqDTO.getCurrent(), reqDTO.getSize());
IPage<EmployeeInfoResDTO> result3=employeeMapper.queryListByCondition2(page, queryWrapper);

总结

结合以上写法对比，个人总结如下：

Mybatis-XML如果sql比较复杂，有复杂条件判断，就放在在xml里独立开来，更易维护
Mybatis-注解如果sql相对简单，仅少量表关联，就用注解方式，但需注意用到标签/条件时要<script>标注头尾
MybatisPlus推荐使用，直接支持单表CRUD，无缝分页，java编码实现动态sql，但多表关联查询时需要结合mybatis实现

工作中公司如果没有相应规范要求，则可按照以上建议灵活使用。

Demo地址 https://gitee.com/coolnote/mybatis-demo.git
Mybatis官网 https://mybatis.p2hp.com
MybatisPlus官网 https://baomidou.com