MyBatisAPI

发表于 更新于 分类于 ， ，

MyBatis 与 MyBatis-Plus 高频 API 速查

适合单体项目 / SSM / Spring Boot 后端开发快速查阅。

1. 总览

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
MyBatis
├─ 注解 SQL：@Select / @Insert / @Update / @Delete
├─ 多参数传递：@Param
├─ 结果映射：resultType / resultMap
├─ 动态 SQL：<if> <where> <set> <foreach>
└─ 复杂查询：XML 自定义 SQL

MyBatis-Plus
├─ BaseMapper<T>
│  ├─ insert
│  ├─ deleteById / deleteBatchIds
│  ├─ updateById
│  ├─ selectById / selectBatchIds
│  ├─ selectList / selectOne / selectCount
│  └─ selectPage
├─ Wrapper
│  ├─ QueryWrapper
│  ├─ LambdaQueryWrapper
│  ├─ UpdateWrapper
│  └─ LambdaUpdateWrapper
├─ IService / ServiceImpl
│  ├─ save / saveBatch
│  ├─ getById / list / page
│  ├─ updateById / update
│  └─ removeById / remove
└─ 链式调用
   ├─ query()
   ├─ lambdaQuery()
   ├─ update()
   └─ lambdaUpdate()

2. MyBatis 高频 API

2.1 @Select

1
2
3
4
5
6
@Mapper
public interface UserMapper {

    @Select("select * from user where id = #{id}")
    User selectById(Long id);
}

用途:

  • 简单单表查询
  • SQL 很短、逻辑不复杂

2.2 @Insert

1
2
@Insert("insert into user(username, age) values(#{username}, #{age})")
int insert(User user);

返回值:

  • 一般是影响行数

2.3 @Update

1
2
@Update("update user set username = #{username}, age = #{age} where id = #{id}")
int updateById(User user);

2.4 @Delete

1
2
@Delete("delete from user where id = #{id}")
int deleteById(Long id);

2.5 @Param

多个参数时很常用。

1
2
3
@Select("select * from user where username = #{name} and age = #{age}")
User selectByNameAndAge(@Param("name") String username,
                        @Param("age") Integer age);

作用:

  • 给 SQL 中的参数显式命名
  • 避免多个参数时取值混乱

2.6 XML 自定义 SQL

Mapper 接口

1
2
3
4
public interface UserMapper {
    List<User> selectByCondition(@Param("name") String name,
                                 @Param("minAge") Integer minAge);
}

XML

1
2
3
4
5
6
7
8
9
10
11
<select id="selectByCondition" resultType="com.demo.entity.User">
    select * from user
    <where>
        <if test="name != null and name != ''">
            and username like concat('%', #{name}, '%')
        </if>
        <if test="minAge != null">
            and age &gt;= #{minAge}
        </if>
    </where>
</select>

适用场景:

  • 条件查询
  • 多条件动态拼接
  • SQL 逻辑稍复杂的单表查询

2.7 <if>

1
2
3
<if test="status != null">
    and status = #{status}
</if>

作用:

  • 条件满足才拼接 SQL

2.8 <where>

1
2
3
4
5
6
7
8
<where>
    <if test="name != null">
        and username = #{name}
    </if>
    <if test="age != null">
        and age = #{age}
    </if>
</where>

作用:

  • 自动处理多余的 and
  • 自动补 where

2.9 <set>

1
2
3
4
5
6
7
8
9
<update id="updateSelective">
    update user
    <set>
        <if test="username != null">username = #{username},</if>
        <if test="age != null">age = #{age},</if>
        <if test="email != null">email = #{email},</if>
    </set>
    where id = #{id}
</update>

作用:

  • 动态更新非空字段

2.10 <foreach>

批量查询

1
2
3
4
5
6
<select id="selectByIds" resultType="com.demo.entity.User">
    select * from user where id in
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

Mapper

1
List<User> selectByIds(@Param("ids") List<Long> ids);

高频场景:

  • in 查询
  • 批量删除
  • 批量插入拼值列表

2.11 resultType

1
2
3
<select id="selectById" resultType="com.demo.entity.User">
    select * from user where id = #{id}
</select>

作用:

  • 将查询结果直接映射为对象类型

2.12 resultMap

当数据库字段名和 Java 属性名不一致时很有用。

实体类

1
2
3
4
5
public class User {
    private Long id;
    private String userName;
    private LocalDateTime createTime;
}

XML

1
2
3
4
5
6
7
8
9
<resultMap id="userMap" type="com.demo.entity.User">
    <id property="id" column="id"/>
    <result property="userName" column="user_name"/>
    <result property="createTime" column="create_time"/>
</resultMap>

<select id="selectById" resultMap="userMap">
    select id, user_name, create_time from user where id = #{id}
</select>

2.13 MyBatis 常用模板

Mapper

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Mapper
public interface UserMapper {

    User selectById(Long id);

    List<User> selectByCondition(@Param("name") String name,
                                 @Param("minAge") Integer minAge);

    int insert(User user);

    int updateSelective(User user);

    int deleteById(Long id);

    List<User> selectByIds(@Param("ids") List<Long> ids);
}

XML

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
<mapper namespace="com.demo.mapper.UserMapper">

    <select id="selectById" resultType="com.demo.entity.User">
        select * from user where id = #{id}
    </select>

    <select id="selectByCondition" resultType="com.demo.entity.User">
        select * from user
        <where>
            <if test="name != null and name != ''">
                and username like concat('%', #{name}, '%')
            </if>
            <if test="minAge != null">
                and age &gt;= #{minAge}
            </if>
        </where>
    </select>

    <insert id="insert">
        insert into user(username, age)
        values(#{username}, #{age})
    </insert>

    <update id="updateSelective">
        update user
        <set>
            <if test="username != null">username = #{username},</if>
            <if test="age != null">age = #{age},</if>
        </set>
        where id = #{id}
    </update>

    <delete id="deleteById">
        delete from user where id = #{id}
    </delete>

</mapper>

3. MyBatis-Plus 高频 API

3.1 BaseMapper<T>

1
2
public interface UserMapper extends BaseMapper<User> {
}

继承后即可直接使用通用 CRUD。

3.2 insert

1
2
3
4
5
User user = new User();
user.setUsername("Gilbert");
user.setAge(20);

userMapper.insert(user);

3.3 deleteById

1
userMapper.deleteById(1L);

3.4 deleteBatchIds

1
userMapper.deleteBatchIds(Arrays.asList(1L, 2L, 3L));

3.5 updateById

1
2
3
4
5
6
User user = new User();
user.setId(1L);
user.setUsername("Arwen");
user.setAge(21);

userMapper.updateById(user);

特点:

  • 根据主键更新

3.6 selectById

1
User user = userMapper.selectById(1L);

3.7 selectBatchIds

1
List<User> users = userMapper.selectBatchIds(Arrays.asList(1L, 2L, 3L));

3.8 selectList

1
2
3
4
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("status", 1).orderByDesc("create_time");

List<User> list = userMapper.selectList(wrapper);

3.9 selectOne

1
2
3
4
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("username", "Gilbert");

User user = userMapper.selectOne(wrapper);

注意:

  • 结果必须只有一条,否则可能报错

3.10 selectCount

1
2
3
4
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("status", 1);

Long count = userMapper.selectCount(wrapper);

3.11 selectPage

1
2
3
4
5
6
7
8
9
Page<User> page = new Page<>(1, 10);

QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.like("username", "G").orderByDesc("create_time");

Page<User> result = userMapper.selectPage(page, wrapper);

System.out.println(result.getRecords()); // 当前页数据
System.out.println(result.getTotal());   // 总条数

4. Wrapper 条件构造器

4.1 QueryWrapper

1
2
3
4
5
6
7
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("status", 1)
       .like("username", "Gil")
       .ge("age", 18)
       .orderByDesc("create_time");

List<User> list = userMapper.selectList(wrapper);

4.2 LambdaQueryWrapper

更推荐,字段更安全。

1
2
3
4
5
6
7
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.eq(User::getStatus, 1)
       .like(User::getUsername, "Gil")
       .ge(User::getAge, 18)
       .orderByDesc(User::getCreateTime);

List<User> list = userMapper.selectList(wrapper);

优势:

  • 避免字符串字段名写错
  • 字段重构时更安全

4.3 常用条件 API 表

API 含义 示例
eq 等于 eq("status", 1)
ne 不等于 ne("status", 0)
gt 大于 gt("age", 18)
ge 大于等于 ge("age", 18)
lt 小于 lt("age", 60)
le 小于等于 le("age", 60)
like 模糊匹配 like("username", "Gil")
likeLeft 左模糊 likeLeft("username", "bert")
likeRight 右模糊 likeRight("username", "Gil")
in in 查询 in("id", ids)
between 区间 between("age", 18, 30)
isNull 判空 isNull("email")
isNotNull 非空 isNotNull("phone")
orderByAsc 升序 orderByAsc("sort")
orderByDesc 降序 orderByDesc("create_time")
last SQL 尾拼接 last("limit 1")

4.4 条件成立才拼接

1
2
3
4
5
6
7
8
String name = "Gil";
Integer minAge = null;

LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.like(name != null && !name.isEmpty(), User::getUsername, name)
       .ge(minAge != null, User::getAge, minAge);

List<User> users = userMapper.selectList(wrapper);

作用:

  • 适合搜索条件可选场景

5. 更新构造器

5.1 UpdateWrapper

1
2
3
4
UpdateWrapper<User> wrapper = new UpdateWrapper<>();
wrapper.eq("status", 0).set("status", 1);

userMapper.update(null, wrapper);

对应 SQL:

1
update user set status = 1 where status = 0

5.2 LambdaUpdateWrapper

1
2
3
4
5
LambdaUpdateWrapper<User> wrapper = new LambdaUpdateWrapper<>();
wrapper.eq(User::getStatus, 0)
       .set(User::getStatus, 1);

userMapper.update(null, wrapper);

5.3 实体 + 条件更新

1
2
3
4
5
6
7
User user = new User();
user.setUsername("NewName");

LambdaUpdateWrapper<User> wrapper = new LambdaUpdateWrapper<>();
wrapper.eq(User::getId, 1L);

userMapper.update(user, wrapper);

6. MyBatis-Plus Service 层高频 API

6.1 定义方式

Service 接口

1
2
public interface UserService extends IService<User> {
}

实现类

1
2
3
4
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User>
        implements UserService {
}

6.2 save

1
2
3
4
5
User user = new User();
user.setUsername("Gilbert");
user.setAge(20);

userService.save(user);

6.3 saveBatch

1
userService.saveBatch(Arrays.asList(user1, user2, user3));

6.4 getById

1
User user = userService.getById(1L);

6.5 list

查全部

1
List<User> users = userService.list();

带条件

1
2
3
4
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.eq(User::getStatus, 1);

List<User> users = userService.list(wrapper);

6.6 getOne

1
2
3
4
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.eq(User::getUsername, "Gilbert");

User user = userService.getOne(wrapper);

6.7 page

1
2
3
4
5
6
Page<User> page = new Page<>(1, 10);

LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.like(User::getUsername, "G");

Page<User> result = userService.page(page, wrapper);

6.8 updateById

1
2
3
4
5
User user = new User();
user.setId(1L);
user.setAge(22);

userService.updateById(user);

6.9 update

1
2
3
4
5
LambdaUpdateWrapper<User> wrapper = new LambdaUpdateWrapper<>();
wrapper.eq(User::getId, 1L)
       .set(User::getAge, 23);

userService.update(wrapper);

6.10 removeById

1
userService.removeById(1L);

6.11 remove

1
2
3
4
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.eq(User::getStatus, 0);

userService.remove(wrapper);

7. 链式调用 API

7.1 query()

1
2
3
4
List<User> users = userService.query()
        .eq("status", 1)
        .orderByDesc("create_time")
        .list();

7.2 lambdaQuery()

推荐。

1
2
3
4
5
List<User> users = userService.lambdaQuery()
        .eq(User::getStatus, 1)
        .like(User::getUsername, "Gil")
        .orderByDesc(User::getCreateTime)
        .list();

7.3 one()

1
2
3
User user = userService.lambdaQuery()
        .eq(User::getId, 1L)
        .one();

7.4 count()

1
2
3
long count = userService.lambdaQuery()
        .eq(User::getStatus, 1)
        .count();

7.5 lambdaUpdate()

1
2
3
4
userService.lambdaUpdate()
        .eq(User::getId, 1L)
        .set(User::getUsername, "Arwen")
        .update();

8. 实体类常见注解

8.1 @TableName

1
2
3
@TableName("tb_user")
public class User {
}

8.2 @TableId

1
2
@TableId(type = IdType.AUTO)
private Long id;

8.3 @TableField

1
2
@TableField("create_time")
private LocalDateTime createTime;

8.4 @TableField(exist = false)

1
2
@TableField(exist = false)
private String extraInfo;

作用:

  • 该属性不是数据库字段

9. 实战模板对照

9.1 按 id 查询

MyBatis

1
2
@Select("select * from user where id = #{id}")
User selectById(Long id);

MyBatis-Plus

1
User user = userMapper.selectById(id);

9.2 条件查询列表

MyBatis XML

1
2
3
4
5
6
7
8
<select id="selectByCondition" resultType="User">
    select * from user
    <where>
        <if test="name != null and name != ''">
            and username like concat('%', #{name}, '%')
        </if>
    </where>
</select>

MyBatis-Plus

1
2
3
List<User> users = userService.lambdaQuery()
        .like(name != null && !name.isEmpty(), User::getUsername, name)
        .list();

9.3 分页查询

MyBatis

1
2
3
4
5
<select id="selectPage" resultType="User">
    select * from user
    order by create_time desc
    limit #{offset}, #{size}
</select>

MyBatis-Plus

1
2
3
4
5
Page<User> page = new Page<>(current, size);
Page<User> result = userService.page(
        page,
        new LambdaQueryWrapper<User>().orderByDesc(User::getCreateTime)
);

9.4 按条件更新

MyBatis

1
2
3
<update id="updateStatusById">
    update user set status = #{status} where id = #{id}
</update>

MyBatis-Plus

1
2
3
4
userService.lambdaUpdate()
        .eq(User::getId, id)
        .set(User::getStatus, status)
        .update();

9.5 批量查询

MyBatis

1
2
3
4
5
6
<select id="selectByIds" resultType="User">
    select * from user where id in
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

MyBatis-Plus

1
List<User> users = userMapper.selectBatchIds(ids);

10. 如何选择 MyBatis 与 MyBatis-Plus

适合 MyBatis-Plus 的场景

  • 单表 CRUD
  • 常规分页
  • 后台管理接口
  • 快速开发
  • 业务条件查询不算太复杂

适合 MyBatis 的场景

  • 多表复杂关联
  • 报表 SQL
  • 复杂动态 SQL
  • 需要精细控制 SQL 性能

11. 高频核心清单

MyBatis 必会

  • @Select
  • @Param
  • <if>
  • <where>
  • <set>
  • <foreach>
  • resultMap

MyBatis-Plus 必会

  • selectById
  • selectList
  • selectPage
  • insert
  • updateById
  • deleteById
  • LambdaQueryWrapper
  • LambdaUpdateWrapper
  • lambdaQuery()
  • lambdaUpdate()
  • save
  • getById
  • list
  • page

12. 面试 / 笔记版超短总结

1
2
3
4
5
6
7
8
9
10
11
12
MyBatis：
- 核心是自己写 SQL
- 简单场景可用注解
- 复杂场景常用 XML + 动态 SQL
- 高频标签：if / where / set / foreach

MyBatis-Plus：
- 在 MyBatis 基础上增强 CRUD
- BaseMapper 提供通用增删改查
- Wrapper 负责拼条件
- IService / ServiceImpl 再封装常用业务操作
- 推荐优先使用 LambdaQueryWrapper / lambdaQuery()，更安全

13. 一句话记忆

1
2
简单单表 CRUD 优先 MyBatis-Plus；
复杂多表 SQL 回到 MyBatis XML。