Mybatis-Flex之QueryWrapper

最新推荐文章于 2025-06-29 23:45:45 发布

我也有梦想呀

最新推荐文章于 2025-06-29 23:45:45 发布

阅读量2.3k

点赞数 2

CC 4.0 BY-SA版权

分类专栏： MybatisFlex 文章标签： mybatis java

本文链接：https://blog.youkuaiyun.com/weixin_45529338/article/details/132630225

MybatisFlex 专栏收录该内容

4 篇文章

订阅专栏

本文围绕MyBatis展开，介绍了完整DQL语句、动态条件、SQL函数、Lambda扩展、字符串扩展、CPI工具等内容。重点对QueryWrapper方法进行全解，涵盖查询、条件设置、连接查询、分页等多种操作，还提及使用时传入null值的注意点。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、完整DQL语句

/**
     * 使用QueryWrapper构建超复杂SQL语句
     */
    @Test
    public void testQueryWrapper1() {
        QueryWrapper wrapper = QueryWrapper.create()
                .select(SYS_USER_ENTITY.ALL_COLUMNS, SYS_ROLE_ENTITY.ALL_COLUMNS, SYS_MENU_ENTITY.ALL_COLUMNS)
                .from(SYS_USER_ENTITY)
                .leftJoin(SYS_USER_ROLE_ENTITY).on(SYS_USER_ENTITY.ID.eq(SYS_USER_ROLE_ENTITY.USER_ID))
                .leftJoin(SYS_ROLE_ENTITY).on(SYS_ROLE_ENTITY.ID.eq(SYS_USER_ROLE_ENTITY.ROLE_ID))
                .leftJoin(SYS_ROLE_MENU_ENTITY).on(SYS_ROLE_ENTITY.ID.eq(SYS_ROLE_MENU_ENTITY.ROLE_ID))
                .leftJoin(SYS_MENU_ENTITY).on(SYS_ROLE_MENU_ENTITY.MENU_ID.eq(SYS_MENU_ENTITY.ID))
                .where(SYS_USER_ENTITY.ID.gt(0))
                .groupBy(SYS_USER_ENTITY.USERNAME)
                .having(SYS_USER_ENTITY.USERNAME.like("a"))
                .orderBy(SYS_USER_ENTITY.PASSWORD.desc())
                .union(QueryWrapper.create().from(ACCOUNT))
                .limit(3);
        printSql(wrapper);
    }

结果

SELECT
    ` sys_user `.*,
    ` sys_role `.*,
    ` sys_menu `.*
  FROM
    ` sys_user `
    LEFT JOIN ` sys_user_role ` ON ` sys_user `.` id ` = ` sys_user_role `.` user_id `
    LEFT JOIN ` sys_role ` ON ` sys_role `.` id ` = ` sys_user_role `.` role_id `
    LEFT JOIN ` sys_role_menu ` ON ` sys_role `.` id ` = ` sys_role_menu `.` role_id `
    LEFT JOIN ` sys_menu ` ON ` sys_role_menu `.` menu_id ` = ` sys_menu `.` id `
  WHERE
    ` sys_user `.` id ` > 0
  GROUP BY
    ` sys_user `.` username `
  HAVING
    ` sys_user `.` username ` LIKE '%a%'
  ORDER BY
    ` sys_user `.` password ` DESC

2、动态条件

/**
     * 动态条件
     */
    @Test
    public void testDynamic() {
        // 例如；
        QueryWrapper wrapper = QueryWrapper.create().select(SYS_USER_ENTITY.ALL_COLUMNS).from(SYS_USER_ENTITY);

        String name = "";
        boolean flag = name != null && !name.trim().isEmpty();

        // 方式一：为null或""不拼接条件
        wrapper.where(flag ? SYS_USER_ENTITY.NAME.eq(name) : QueryMethods.noCondition());
        // 方式二
        wrapper.and(qw -> {
            qw.or(SYS_USER_ENTITY.NAME.eq(name).when(flag)).or(SYS_USER_ENTITY.USERNAME.eq(name).when(StringUtil.isNotBlank(name)));
        });
        // 方式三
        wrapper.where(SYS_USER_ENTITY.NAME.eq(name).when(If::hasText));
        wrapper.and(SYS_USER_ENTITY.NAME.eq(name).when(StringUtil.isNotBlank(name)));
        // 方式四
        wrapper.where(SYS_USER_ENTITY.NAME.eq(name, If::hasText));

        printSql(wrapper);
    }

结果

SELECT
  *
FROM
  ` sys_user `
WHERE
  ` name ` = 'zs'
  AND ` name ` = 'zs'
  AND ` name ` = 'zs'
  AND (
    ` name ` = 'zs'
    OR ` username ` = 'zs'
  )
  AND ` name ` = 'zs'

3、SQL函数

官方说明select function

/**
     * sql函数
     */
    @Test
    public void testSqlFunction1() {
        QueryWrapper wrapper = QueryWrapper.create()
                .select(ACCOUNT.ID, ACCOUNT.USER_NAME,
                        QueryMethods.max(ACCOUNT.BIRTHDAY),
                        QueryMethods.avg(ACCOUNT.AGE).as("avgAge"))
                .from(ACCOUNT);
        printSql(wrapper);
        /**
         * SELECT
         *   ` id `,
         *   ` user_name `,
         *   MAX(` birthday `),
         *   AVG(` age `) AS ` avgAge `
         * FROM
         *   ` tb_account `
         */
    }

    @Test
    public void testSqlFunction2() {
        QueryWrapper wrapper = QueryWrapper.create()
                .select(concat(ACCOUNT.USER_NAME, string("ly")))
                .from(ACCOUNT)
                .where(not(ACCOUNT.AGE.eq(3)));
        printSql(wrapper);
        /**
         * SELECT
         *   CONCAT(` user_name `, 'ly')
         * FROM
         *   ` tb_account `
         * WHERE
         *   NOT (` age ` = 3)
         */
    }

4、Lambda 扩展

@Test
    public void testLambda() {
        QueryWrapper wrapper = QueryWrapper.create()
                .from(Account.class)
                .select(Account::getId, Account::getUserName)
                .where(Account::getAge).gt(20)
                .or(Account::getUserName).like("ly")
                .or(Account::getBirthday).between(DateUtil.parseDate("2023-08-10"), DateUtil.parseDate("2023-08-16"));
        printSql(wrapper);
    }

结果

SELECT
  ` id `,
  ` user_name `
FROM
  ` tb_account `
WHERE
  ` age ` > 20
  OR ` user_name ` LIKE '%ly%'
  OR ` birthday ` BETWEEN '2023-08-10 00:00:00' AND '2023-08-16 00:00:00'

5、字符串扩展

前面的所有操作都可以使用字符串的形式进行拼接,当然混着用也是可以的

/**
     * 字符串扩展
     */
    @Test
    public void testString() {
        long id = 1L;
        QueryWrapper wrapper = QueryWrapper.create()
                .select("id", "user_name", "age")
                .from("tb_account")
                .where("id = ?", id);
        printSql(wrapper);
    }

结果

SELECT
  id,
  user_name,
  age
FROM
  ` tb_account `
WHERE
  id = 1

6、CPI工具

用于设置修改QueryWrapper相关属性信息

@Test
    public void testCPI() {
        QueryWrapper wrapper = QueryWrapper.create()
                .select(ACCOUNT.ALL_COLUMNS)
                .from(ACCOUNT)
                .where(ACCOUNT.AGE.gt(10))
                .limit(10);
        CPI.setLimitRows(wrapper, 1L);

        printSql(wrapper);
    }

7、QueryWrapper方法全解

(1) select *

查询所有列

SELECT * FROM ` tb_account `

/**
     * select *
     */
    @Test
    public void testSelectAll() {
        QueryWrapper wrapper = QueryWrapper.create()
                .select(ACCOUNT.ALL_COLUMNS)
                .from(ACCOUNT);
        printSql(wrapper);
    }

(2) select as

给某些列取别名

SELECT ` user_name ` AS ` user_name ` FROM ` tb_account `

/**
     * select as
     */
    @Test
    public void testSelectAs() {
        QueryWrapper wrapper = QueryWrapper.create()
                .select(ACCOUNT.USER_NAME.as(Account::getUserName))
                .from(ACCOUNT);
        printSql(wrapper);
    }

(3) select multi table

查询多张表

SELECT
  ` T1 `.*,
  ` T2 `.*
FROM
  ` tb_user ` AS ` T1 `,
  ` tb_card ` AS ` T2 `
WHERE ` T1 `.` id ` = ` T2 `.` user_id `

/**
     * select multi-table
     */
    @Test
    public void testSelectMultiTableColumn() {
        QueryWrapper wrapper = QueryWrapper.create()
                .select(USER.ALL_COLUMNS, CARD.ALL_COLUMNS)
                .from(USER.as("T1"), CARD.as("T2"))
                .where(USER.ID.eq(CARD.USER_ID));
        printSql(wrapper);
    }

(4) select function

查询时使用函数

SELECT
   MAX(` age `),
   AVG(` age `),
   MIN(` age `) AS ` age_avg `
 FROM
   ` tb_user `

/**
     * select function
     */
    @Test
    public void testSelectFunction() {
        QueryWrapper wrapper = QueryWrapper.create()
                .select(max(USER.AGE), avg(USER.AGE), min(USER.AGE).as("age_avg"))
                .from(USER);
        printSql(wrapper);
    }

所有函数参考官网AllFunction

(5) 列计算

在列上做一些计算操作

SELECT
  (` age ` + 10) AS ` age `,
  SUM(` id ` * ` age `) AS ` total `
FROM
  ` tb_account `

/**
     * select 列计算
     */
    @Test
    public void testColumnCalculation() {
        QueryWrapper wrapper = QueryWrapper.create()
                .select(ACCOUNT.AGE.add(10).as("age"),
                        sum(ACCOUNT.ID.multiply(ACCOUNT.AGE)).as("total"))
                .from(ACCOUNT);
        printSql(wrapper);
    }

(6) select case…when

case … when条件语句

 SELECT
   ` user_name `,
   (
     CASE
       WHEN ` age ` >= 18 THEN '青年'
       WHEN ` age ` >= 30 THEN '中年'
       WHEN ` age ` >= 60 THEN '老年'
       ELSE '其他'
     END
   ) AS ` desc `
 FROM
   ` tb_account `

/**
     * select case...when
     */
    @Test
    public void selectCaseWhen() {
        QueryWrapper wrapper = QueryWrapper.create()
                .from(ACCOUNT)
                .select(ACCOUNT.USER_NAME,
                        case_().when(ACCOUNT.AGE.ge(18))
                                .then("青年")
                                .when(ACCOUNT.AGE.ge(30))
                                .then("中年")
                                .when(ACCOUNT.AGE.ge(60))
                                .then("老年")
                                .else_("其他")
                                .end().as("desc")
                );
        printSql(wrapper);
    }

(7) where 条件

查询条件

SELECT
  ` id `,
  ` age `,
  ` birthday `,
  ` user_name `
FROM
  ` tb_account `
WHERE
  (
    ` id ` <= 3
    OR ` age ` >= 10
  )
  AND ` user_name ` LIKE '张%'

/**
     * where 条件
     */
    @Test
    public void testWhere() {
        QueryWrapper wrapper = QueryWrapper.create()
                .select(ACCOUNT.DEFAULT_COLUMNS)
                .from(ACCOUNT)
                .where(ACCOUNT.ID.le(3).or(ACCOUNT.AGE.ge(10)))
                .and(ACCOUNT.USER_NAME.likeLeft("张"));
        printSql(wrapper);
    }

(8) dynamic where 条件

动态查询条件

SELECT
  ` id `,
  ` age `,
  ` birthday `,
  ` user_name `
FROM
  ` tb_account `
WHERE
  ` user_name ` LIKE 'zhang%'

/**
     * dynamic where 条件
     */
    @Test
    public void testDynamicWhere1() {
        boolean flag = false;
        QueryWrapper wrapper = QueryWrapper.create()
                .select(ACCOUNT.DEFAULT_COLUMNS)
                .from(ACCOUNT)
                .where(flag ? ACCOUNT.AGE.gt(10) : noCondition())
                .and(ACCOUNT.USER_NAME.likeLeft("zhang"));
        printSql(wrapper);
    }

 @Test
    public void testDynamicWhere2() {
        String name = "";
        QueryWrapper wrapper = QueryWrapper.create()
                .select(ACCOUNT.ALL_COLUMNS)
                .from(ACCOUNT)
                .where(ACCOUNT.USER_NAME.like(name).when(StringUtil.isNotBlank(name)));
        printSql(wrapper);
    }

@Test
    public void testDynamicWhere3() {
        String name = "aaa";
        QueryWrapper wrapper = QueryWrapper.create()
                .from(ACCOUNT)
                .where(ACCOUNT.USER_NAME.like(name).when(If.hasText(name)))
                .and(ACCOUNT.AGE.ge(10));
        /**
         *SELECT
         *   *
         * FROM
         *   ` tb_account `
         * WHERE
         *   ` user_name ` LIKE '%aaa%'
         *   AND ` age ` >= 10
         */
        printSql(wrapper);
    }

@Test
    public void testDynamicWhere4() {
        String name = "aaa";
        QueryWrapper wrapper = QueryWrapper.create()
                .select()
                .from(ACCOUNT)
                .where(ACCOUNT.ID.ge(2))
                .or(ACCOUNT.USER_NAME.like(name, StringUtil.isNotBlank(name)));
        /**
         *SELECT
         *   *
         * FROM
         *   ` tb_account `
         * WHERE
         *   ` id ` >= 2
         *   OR ` user_name ` LIKE '%aaa%'
         */
        printSql(wrapper);
    }

(9) where select 子查询

子查询

SELECT
  *
FROM
  ` tb_account `
WHERE
  ` id ` >= (
    SELECT
      ` id `
    FROM
      ` tb_account `
    WHERE
      ` birthday ` >= '2023-08-29 15:29:15'
  )

/**
     * where select 类似子查询
     */
    @Test
    public void testWhereSelect() {
        QueryWrapper wrapper = QueryWrapper.create()
                .select(ACCOUNT.ALL_COLUMNS)
                .from(ACCOUNT)
                .where(ACCOUNT.ID.ge(
                        select(ACCOUNT.ID).from(ACCOUNT).where(ACCOUNT.BIRTHDAY.ge(new Date()))
                ));
        printSql(wrapper);
    }

(10) where exists, not exists 条件

存在|不存在

SELECT
  *
FROM
  ` tb_account `
WHERE
  ` id ` >= 100
  AND EXISTS (
    SELECT
      1
    FROM
      ` tb_account `
    WHERE
      ` id ` = 1
  )

/**
     * where exists, not exists
     */
    @Test
    public void testWhereExistsAndNotExists() {
        QueryWrapper wrapper = QueryWrapper.create()
                .from(ACCOUNT)
                .where(ACCOUNT.ID.ge(100))
                .and(exists( // notExists
                        selectOne().from(ACCOUNT).where(ACCOUNT.ID.eq(1))
                ));
        printSql(wrapper);
    }

(11) and (…) or (…)

条件连接and|or

SELECT
   *
 FROM
   ` tb_account `
 WHERE
   ` id ` >= 1
   AND (
     ` age ` >= 18
     OR ` user_name ` LIKE '%zs%'
   )
   OR (
     ` age ` IN (20, 21, 22)
     AND ` user_name ` LIKE '%a'
   )

/**
     * and (...) or (...)
     */
    @Test
    public void testAndOr() {
        QueryWrapper wrapper = QueryWrapper.create()
                .select(ACCOUNT.ALL_COLUMNS)
                .from(ACCOUNT)
                .where(ACCOUNT.ID.ge(1))
                .and(ACCOUNT.AGE.ge(18).or(ACCOUNT.USER_NAME.like("zs")))
                .or(ACCOUNT.AGE.in(20, 21, 22).and(ACCOUNT.USER_NAME.likeRight("a")));
        printSql(wrapper);
    }

(12) group by

分组

SELECT
   *
 FROM
   ` tb_account `
 GROUP BY
   ` user_name `

/**
     * group by
     */
    @Test
    public void testGroupBy() {
        QueryWrapper wrapper = QueryWrapper.create()
                .select(ACCOUNT.ALL_COLUMNS)
                .from(ACCOUNT)
                .groupBy(ACCOUNT.USER_NAME);
        printSql(wrapper);
    }

(13) having

分组后筛选

SELECT
   *
 FROM
   ` tb_account `
 GROUP BY
   ` age `
 HAVING
   ` age ` > 18

/**
     * having
     */
    @Test
    public void testHaving() {
        QueryWrapper wrapper = QueryWrapper.create()
                .select(ACCOUNT.ALL_COLUMNS)
                .from(ACCOUNT)
                .groupBy(ACCOUNT.AGE)
                .having(ACCOUNT.AGE.gt(18));
        printSql(wrapper);
    }

(14) join (inner join、left join、right join、full join)

连接查询

SELECT
   ` T1 `.` id `,
   ` T1 `.` age `,
   ` T1 `.` email `,
   ` T1 `.` dept_id `,
   ` T1 `.` user_name `,
   ` T1 `.` gmt_create `,
   ` T1 `.` gmt_modified `,
   ` T2 `.` id ` AS ` tb_card $ id `,
   ` T2 `.` card_no `,
   ` T2 `.` user_id `,
   ` T2 `.` content `
 FROM
   ` tb_user ` AS ` T1 `
 INNER JOIN ` tb_card ` AS ` T2 ` ON ` T1 `.` id ` = ` T2 `.` user_id `
 LEFT JOIN ` tb_card ` ON ` T1 `.` id ` = ` T2 `.` user_id `
 RIGHT JOIN ` tb_card ` ON ` T2 `.` user_id ` = ` T1 `.` id `
 FULL JOIN ` tb_card ` ON ` T1 `.` id ` = ` T2 `.` user_id `
WHERE
  ` T1 `.` age ` > 18

/**
     * test join (inner join、left join、right join、full join)
     */
    @Test
    public void testJoin() {
        QueryWrapper wrapper = QueryWrapper.create()
                .select(USER.DEFAULT_COLUMNS, CARD.DEFAULT_COLUMNS)
                .from(USER).as("T1")
                // inner join
                .innerJoin(CARD).as("T2")
                .on(USER.ID.eq(CARD.USER_ID))
                // left join
                .leftJoin(CARD)
                .on(USER.ID.eq(CARD.USER_ID))
                // right join
                .rightJoin(CARD)
                .on(CARD.USER_ID.eq(USER.ID))
                // full join
                .fullJoin(CARD)
                .on(USER.ID.eq(CARD.USER_ID))
                .where(USER.AGE.gt(18));
        printSql(wrapper);
    }

(15) join on 多个条件

链表查询on多个条件

SELECT
   ` tb_user `.` id `,
   ` tb_user `.` age `,
   ` tb_user `.` email `,
   ` tb_user `.` dept_id `,
   ` tb_user `.` user_name `,
   ` tb_user `.` gmt_create `,
   ` tb_user `.` gmt_modified `
 FROM
   ` tb_user `
   LEFT JOIN ` tb_card ` ON ` tb_user `.` id ` = ` tb_card `.` user_id `
   AND ` tb_user `.` id ` >= 3
 WHERE
   ` tb_card `.` id ` > 5

/**
     * join on 多个条件
     */
    @Test
    public void testJoinOn() {
        QueryWrapper wrapper = QueryWrapper.create()
                .select(USER.DEFAULT_COLUMNS)
                .from(USER)
                .leftJoin(CARD)
                .on(
                        USER.ID.eq(CARD.USER_ID)
                                .and(USER.ID.ge(3))
                ).where(CARD.ID.gt(5));
        printSql(wrapper);
    }

(16) join select

join中嵌套子查询，类似产生一张中间表


SELECT
  ` tb_user `.` id `,
  ` tb_user `.` age `,
  ` tb_user `.` email `,
  ` tb_user `.` dept_id `,
  ` tb_user `.` user_name `,
  ` tb_user `.` gmt_create `,
  ` tb_user `.` gmt_modified `
FROM
  ` tb_user `
  LEFT JOIN (
    SELECT
      ` user_id `
    FROM
      ` tb_card ` AS ` temp `
    WHERE
      ` id ` > 3
  ) ON ` tb_user `.` id ` = 'temp.user_id'
 WHERE
   ` tb_user `.` age ` > 18
 GROUP BY
   ` tb_user `.` user_name `
 HAVING
   ` tb_user `.` user_name ` LIKE '%Z'

/**
     * join select
     */
    @Test
    public void testJoinSelect() {
        QueryWrapper wrapper = QueryWrapper.create()
                .select(USER.DEFAULT_COLUMNS)
                .from(USER)
                .leftJoin(
                        select(CARD.USER_ID).from(CARD).where(CARD.ID.gt(3)).as("temp")
                ).on(USER.ID.eq("temp.user_id"))
                .where(USER.AGE.gt(18))
                .groupBy(USER.USER_NAME)
                .having(USER.USER_NAME.likeRight("Z"));
        printSql(wrapper);
    }

(17) limit… offset

分页

limit… offset MyBatis-Flex 能够自动识别当前数据库👍，并根据数据库的类型生成不同的 SQL，用户也可以很轻易的通过 DialectFactory 注册（新增或改写）自己的实现方言。

SELECT
   ` id `,
   ` age `,
   ` birthday `,
   ` user_name `
 FROM
   ` tb_account `
 WHERE
   ` id ` > 1
 ORDER BY
   ` id ` DESC,
   ` age ` ASC
 LIMIT
   0, 10

/**
     * limit... offset MyBatis-Flex 能够自动识别当前数据库👍，
     * 并根据数据库的类型生成不同的 SQL，用户也可以很轻易的通过 DialectFactory 注册（新增或改写）自己的实现方言。
     */
    @Test
    public void testLimitOffset() {
        QueryWrapper wrapper = QueryWrapper.create()
                .select(ACCOUNT.DEFAULT_COLUMNS)
                .from(ACCOUNT)
                .where(ACCOUNT.ID.gt(1))
                .orderBy(ACCOUNT.ID.desc(), ACCOUNT.AGE.asc())
                .limit(10)
                .offset(0);
        printSql(wrapper);
    }

(18) Entity 转化为 QueryWrapper

让实体属性值直接作为查询条件

 SELECT
   ` id `,
   ` user_name `,
   ` age `,
   ` birthday `
 FROM
   ` tb_account `
 WHERE
   ` user_name ` = 'ly'
   AND ` age ` = 18

/**
     * Entity 转化为 QueryWrapper
     * 这种方式，默认通过"and"进行连接
     */
    @Test
    public void testEntityToQueryWrapper() {
        Account account = new Account();
        account.setAge(18);
        account.setUserName("ly");

        QueryWrapper wrapper = QueryWrapper.create(account)
                .from(ACCOUNT);
        printSql(wrapper);
    }

(19) 自定义操作符

自定义操作符，让实体属性值通过and|or…进行连接

SELECT
  ` id `,
  ` user_name `,
  ` age `,
  ` birthday `
FROM
  ` tb_account `
WHERE
  ` user_name ` LIKE '%ly%'
  AND ` age ` >= 18

/**
     * 自定义操作符
     */
    @Test
    public void testEntityToQueryWrapper2() {
        Account account = new Account();
        account.setAge(18);
        account.setUserName("ly");
        SqlOperators operators = SqlOperators.of()
                .set(Account::getUserName, SqlOperator.LIKE)
                .set(Account::getAge, SqlOperator.GE);
        QueryWrapper wrapper = QueryWrapper.create(account,operators)
                .from(ACCOUNT);
        printSql(wrapper);
    }

(20) 注意点

在 QueryWrapper 的条件构建中，如果传入 null 值，则自动忽略该条件，
这有许多的好处，不需要额外的通过 when() 方法判断。
但是也带来一些额外的知识记忆点，因此，正对这一点需要特别注意一下

SELECT
  *
FROM
  ` tb_account `
WHERE
  ` id ` > 1

/**
     * 在 QueryWrapper 的条件构建中，如果传入 null 值，则自动忽略该条件，
     * 这有许多的好处，不需要额外的通过 when() 方法判断。
     * 但是也带来一些额外的知识记忆点， 因此，正对这一点需要特别注意一下
     */
    @Test
    public void testQeuryWrapperIgnoreNull() {
        String name = null;
        Integer age = null;
        QueryWrapper wrapper = QueryWrapper.create()
                .from(ACCOUNT)
                .where(ACCOUNT.AGE.eq(age))
                .and(ACCOUNT.USER_NAME.like(name))
                .and(ACCOUNT.ID.gt(1));
        printSql(wrapper);
    }