前言

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

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

正文

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

java
 代码解读
复制代码
public class Employee extends BaseEntity {
    /**
     * 部门id
     */
    private Long departId;
    /**
     * 姓名
     */
    private String employName;
    ...
}

java
 代码解读
复制代码
public class Department extends BaseEntity {
    /**
     * 部门名称
     */
    private String departName;
    ...
}

单个新增

java
 代码解读
复制代码
//Mybatis-xml写法
int insertOne(Employee employee);

xml
 代码解读
复制代码
<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>

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

java
 代码解读
复制代码
//MybatisPlus写法
boolean result = employeeService.save(employee);

批量新增

java
 代码解读
复制代码
//Mybatis-xml写法
int batchInsert(@Param("employeeList") List employees);

xml
 代码解读
复制代码
<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>

java
 代码解读
复制代码
//MybatisPlus写法
boolean result = employeeService.saveBatch(employeeList);

删除

java
 代码解读
复制代码
//Mybatis-xml写法
int deleteById(Long id);

xml
 代码解读
复制代码
<update id="deleteById">
    update employee set is_deleted='1' where id=#{id}
update>

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

java
 代码解读
复制代码
//MybatisPlus写法
boolean result = employeeService.removeById(2L);

修改-可改为空

java
 代码解读
复制代码
//Mybatis-xml写法
int updateAllById(Employee employee);

xml
 代码解读
复制代码
<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>

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

java
 代码解读
复制代码
//MybatisPlus写法
QueryWrapper queryWrapper = new QueryWrapper<>();
queryWrapper.eq("id", employee.getId());
employeeService.update(employee, queryWrapper);

修改-不为空

java
 代码解读
复制代码
//Mybatis-注解写法
int updateByIdSelective(Employee employee);

xml
 代码解读
复制代码
<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>

java
 代码解读
复制代码
//MybatisPlus写法
boolean b = employeeService.updateById(employee);

单表查询

java
 代码解读
复制代码
//Mybatis-xml写法
Employee queryById(Long id);

xml
 代码解读
复制代码
<select id="queryById" resultType="com.springboot.base.entity.Employee">
    select * from employee where id=#{id}
select>

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

java
 代码解读
复制代码
//Mybatis-注解里foreach
@Select("")
List queryListByIds(@Param("ids")List ids);

java
 代码解读
复制代码
//MybatisPlus写法
Employee employee = employeeService.getById(1L);

多表查询

java
 代码解读
复制代码
//Mybatis-xml写法
List queryListByCondition(EmployeeInfoReqDTO reqDTO);

xml
 代码解读
复制代码
<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>

java
 代码解读
复制代码
//MybatisPlus写法
QueryWrapper 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);

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

分页查询

java
 代码解读
复制代码
//mybatisPlus单表分页
IPage page = new Page<>(1, 3);
IPage result = employeeService.page(page);

java
 代码解读
复制代码
//mybatisPlus结合mybatis-xml多表分页
IPage queryListByCondition(EmployeeInfoPageReqDTO reqDTO);

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

xml
 代码解读
复制代码
<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>

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

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

总结

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

• Mybatis-XML如果sql比较复杂，有复杂条件判断，就放在在xml里独立开来，更易维护
• Mybatis-注解如果sql相对简单，仅少量表关联，就用注解方式，但需注意用到标签/条件时要