简介

MyBatis-Plus（简称 MP）是一个 MyBatis 的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生。

快速入门

定义 user表，表结构如下：

id name age email manager_id create_time
1087982257332887553 大boss 40 boss@baomidou.com 2019-01-11 14:20:20
1088248166370832385 王天风 25 wtf@baomidou.com 1087982257332887553 2019-02-05 11:12:22
1088250446457389058 李艺伟 28 lyw@baomidou.com 1088248166370832385 2019-02-14 08:31:16

定义实体类 ：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
@Data
public class User {
    private Long id;

    private String name;

    private Integer age;

    private String email;

    private Long managerId;

    private Date createTime;
}

dao 接口，UserMapper，只需要继承 BaseMapper<User>

测试例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@Slf4j
@SpringBootTest
@RunWith(SpringRunner.class)
public class UserMapperTest {

    @Autowired
    UserMapper userMapper;

    @Test
    public void testSelectAll(){
        List<User> userList = userMapper.selectList(null);
        Assert.assertEquals(5, userList.size());
        userList.forEach(System.out::println);
    }
}

条件查询

  1. 名字包含雨并且年龄小于40 (name like ‘%雨%’ and age < 40)

    1
    2
    3
    4
    5
    6
    7
    8
    @Test
        public void selectByWrapper(){
            QueryWrapper<User> queryWrapper = new QueryWrapper<>();

            queryWrapper.like("name", "雨").lt("age", 40);
            List<User> users = userMapper.selectList(queryWrapper);
            users.forEach(System.out::println);
        }

  2. 名字包含雨并且年龄大于等于20且小于等于40并且email不能为空 (name like ‘%雨%’ and age between 20 and 40 and email is not null)

    1
    2
    3
    4
    5
    6
    7
    @Test
        public void selectByWrapper2(){
            QueryWrapper<User> queryWrapper = new QueryWrapper<>();
            queryWrapper.like("name", "雨").between("age", 20, 40).isNotNull("email");
            List<User> users = userMapper.selectList(queryWrapper);
            users.forEach(System.out::println);
        }

  3. 创建日期为2019/2/14并且直属上级名字为王姓 (date_formate(‘%Y-%M-%d’, date) and manager_id in (select manager_id from user where name like ‘王%’))

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    @Test
        public void selectByWrapper4(){
            Date date = new Date(2019, 2, 5);
            QueryWrapper<User> queryWrapper = new QueryWrapper<>();
            queryWrapper.
                    apply("date_format(create_time,'%Y-%m-%d') = {0}", "2019-02-05")
                    .inSql("manager_id", "select manager_id from user where name like '王%'");
            List<User> users = userMapper.selectList(queryWrapper);
            users.forEach(System.out::println);
        }

  4. 按照直属上级分组，查询每组的平均年龄、最大年龄、最小年龄，并且只取年龄总和小于500的组 (select avg(age), max(age), min(age) from user group by manager_id having sum(age) < 500)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    @Test
        public void selectByWrapper12(){
            QueryWrapper<User> queryWrapper = new QueryWrapper<>();
            queryWrapper.select("avg(age), max(age), min(age)")
                    .groupBy("manager_id")
                    .having("sum(age) < {0}", 500);
            List<Map<String, Object>> users = userMapper.selectMaps(queryWrapper);
            users.forEach(System.out::println);
        }

  5. lambda 条件查询器

    1
    2
    3
    4
    5
    6
    7
    @Test
        public void selectByWrapper13(){
            LambdaQueryWrapper<User> lambdaQuery = Wrappers.lambdaQuery();
            lambdaQuery.like(User::getName, "雨").lt(User::getAge, 40);
            List<Map<String, Object>> users = userMapper.selectMaps(lambdaQuery);
            users.forEach(System.out::println);
        }

  6. 根据实体查询

    1
    2
    3
    4
    5
    6
    7
    8
    9
    @Test
        public void selectByEntity(){
            User user = new User();
            user.setName("王天风");
            user.setAge(25);
            QueryWrapper<User> queryWrapper = new QueryWrapper<>(user);
            List<User> users = userMapper.selectList(queryWrapper);
            users.forEach(System.out::println);
        }