【技术碎片】【PostgreSQL】Mybatis-Plus多值模糊查询，分页查询，order by的使用，遇到的问题_mybatis orderby多个

前言

本文阐述PG或者mybatis-plus中的一些用法

注：MyBatis-Plus是一个对 MyBatis 的增强工具，在 MyBatis 的基础上提供额外的功能，旨在简化开发、提高效率。它是在 MyBatis 的基础上进行扩展的，提供了许多便捷的功能和改进，它在 MyBatis 的基础上 提供了一套通用的 Mapper 接口和 Service 类，它们包含了常见的数据库操作方法，如查询、插入、更新和删除，降低了代码量并减少了错误的可能性，并且它提供了一个强大的条件构造器，允许开发者使用 Lambda 表达式来构建复杂的查询条件，使查询语句更加简洁易懂，这些功能简化了开发工作，提高了代码的可读性和可维护性

Mybatis-Plus分页查询

PageResponse

我们先有分页请求体定义有：

PageResponse.java

import lombok.Data;

import java.io.Serializable;
import java.util.List;

@Data
public class PageResponse implements Serializable {

    /**
     * 总记录数
     */
    private int totalCount;

    /**
     * 每页记录数
     */
    private int pageSize;

    /**
     * 总页数
     */
    private int totalPage;

    /**
     * 当前页数
     */
    private int pageNo;

    /**
     * 列表数据
     */
    private List<?> list;
    
    /**
     * 分页
     *
     * @param list       列表数据
     * @param totalCountReq 总记录数
     * @param pageSizeReq   每页记录数
     * @param pageNoReq   当前页数
     */
    public PageResponse(List<?> list, int totalCountReq, int pageSizeReq, int pageNoReq) {
        // 参数设置
        int total = totalCountReq;
        int pageSize = pageSizeReq;
        int pageNo = pageNoReq;
        int startRow = pageNo > 0 ? (pageNo - 1) * pageSize : 0;
        int endRow = startRow + pageSize * (pageNo > 0 ? 1 : 0);
        startRow = Math.min(total, startRow);
        endRow = Math.min(total, endRow);
        int totalPage = (total % pageSize == 0) ? (total/pageSize) : (total/pageSize + 1);
        // 分页
        list = list.subList(startRow, endRow);

        this.totalCount = total;
        this.pageSize = pageSize;
        this.totalPage = totalPage;
        this.pageNo = pageNo;
        this.list = list;

    }
}


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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

int pageSizeReq, int pageNoReq 一般是请求体传过来的，比如:

import lombok.Data;

@Data
public class PageRequest implements Serializable {

   /**
    * 当前页数
    */
   private int pageNo = 1;

   /**
    * 每页记录数
    */
   private int pageSize = 10;

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

Response

同一请求体有：
Response.java

/**
 * 统一Response
 * @author 
 */
@Data
@EqualsAndHashCode
public class Response<T> implements Serializable {

    private static final long serialVersionUID = 2760683477101939502L;

    /**
     * 响应码
     * @see org.sample.common.Constants#RESULT_CODE_SUCCESS
     */
    @NotNull
    private int responseCode;

    /**
     * 消息内容
     */
    @NotNull
    private String message;  // 注释也可以写在这里

    /**
     * 响应数据
     */
    private T data;

}
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

ResponseGenerator

响应结果生成工具有：

ResponseGenerator.java

package org.sample.common.response;

import org.sample.common.Constants;
import org.springframework.util.StringUtils;

/**
 * 响应结果生成工具
 * @author
 */
public class ResponseGenerator {


    public static Response genSuccessResponse() {
        Response response = new Response();
        response.setResponseCode(Constants.RESULT_CODE_SUCCESS);
        response.setMessage(Constants.DEFAULT_SUCCESS_MESSAGE);
        return response;
    }

    public static Response genSuccessResponse(String message) {
        Response response = new Response();
        response.setResponseCode(Constants.RESULT_CODE_SUCCESS);
        response.setMessage(message);
        return response;
    }

    public static Response genSuccessResponse(Object data) {
        Response response = new Response();
        response.setResponseCode(Constants.RESULT_CODE_SUCCESS);
        response.setMessage(Constants.DEFAULT_SUCCESS_MESSAGE);
        response.setData(data);
        return response;
    }

    public static Response genFailResponse(String message) {
        Response response = new Response();
        response.setResponseCode(Constants.RESULT_CODE_SERVER_ERROR);
        if (StringUtils.isEmpty(message)) {
            response.setMessage(Constants.DEFAULT_FAIL_MESSAGE);
        } else {
            response.setMessage(message);
        }
        return response;
    }

    public static Response genErrorResponse(int code, String message) {
        Response response = new Response();
        response.setResponseCode(code);
        response.setMessage(message);
        return response;
    }
}

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

HelloController

实例接口如下：

HelloController.java

/**
 * 测试接口
 * @author aaa
 */
@RestController
public class HelloController {

    /**
     * @param id 用户id
     * @param pageRequest 分页请求
     * @return Response<PageRequest> 返回体
     * @throws IOException
     */
    @RequestMapping("/say-hello")
    public Response<PageRequest> sayHello(@RequestParam("id") @NotNull String id,
                                          @RequestBody PageRequest pageRequest) throws IOException {
                                          
        Response<PageRequest> response = ResponseGenerator.genSuccessResponse(pageRequest);
        return response;
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

MybatisPlusConfig 分页查的配置

使用分页查询时需要配置mybatis-plus分页拦截器，不配置会导致分页查询时，返回的分页体中total为空，因为mybatis-plus为了性能考虑分页查询时默认不执行select count(*)。

MybatisPlusConfig.java

/**
 * mybatis-plus 配置类
 * @author zengweiyu
 * @since 2022-11-7
 */
@Configuration
public class MybatisPlusConfig {

    /**
     * mybatis-plus分页拦截器
     * 不配置会导致分页查询无法范湖total总数
     * @return MybatisPlusInterceptor
     */
    @Bean
    public MybatisPlusInterceptor paginationInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 添加分页插件
        PaginationInnerInterceptor pageInterceptor = new PaginationInnerInterceptor();
        // 设置数据库类型
        pageInterceptor.setDbType(DbType.POSTGRE_SQL);
        interceptor.addInnerInterceptor(pageInterceptor);
        return interceptor;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

userEntityServiceImpl

分页查询使用mybatis-plus 的 selectPage方法，除了传入Wrappers.lambdaQuery之外还需要传入mybatis-plus内置的分页体Page，并自定义pageNo和pageSize。

我们有服务实现类

userEntityServiceImpl.java

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;

		......

		// 初始化分页数
        Integer pageNo = userQueryRequest.getPageNo();
        Integer pageSize = userQueryRequest.getPageSize();
        // 初始化SQL分页查询结果
        Page<UserEntity> userEntityPage = null;
        
        try {
            userEntityPage = userEntityMapper.selectPage(
                new Page<>(pageNo, pageSize),
                Wrappers.lambdaQuery(UserEntity.class)
                    .like(UserEntity::getAccountName, accountName)
                    .like(UserEntity::getContentTitle, contentTitle)
                    .eq(UserEntity::getStatus, userQueryRequest.getStatus())
            ));
            logger.info("message tasks query");
        } catch (Exception e) {
        	logger.error("unknown exception when using database", e);
			throw new RuntimeException(e);
        }
        
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

order by多个字段排序与优先级

ORDER BY 是 SQL 中的一个关键子句，可以对查询结果集进行排序。如果需要按照特定的顺序显示数据时可以使用，如按字母顺序排列人员姓名，按数字值升序或降序排列记录。ORDER BY 子句对查询结果进行排序当使用多个字段进行排序时，这些字段会按照它们在 ORDER BY 子句中出现的顺序来决定排序优先级。

我们先有数据表如下：

假设有业务需求需要对查询结果进行多字段排序，比如对id和update_time需要降序排序，并且优先保证id的降序，其次再保证update_time的降序。

我们执行：

SELECT id, username, passwd, enabled, account_non_expired, account_non_locked, credentials_non_expired, is_deleted, create_time, create_user, update_time, update_user
FROM test.image_management_user
order by id, update_time desc
;

1
2
3
4
5

有结果如下：

可以看到id并没有降序排序，并且update_time也没有降序排序。

那是不是字段顺序问题，调转id 和 update_time的顺序查询：

SELECT id, username, passwd, enabled, account_non_expired, account_non_locked, credentials_non_expired, is_deleted, create_time, create_user, update_time, update_user
FROM test.image_management_user
order by update_time, id desc
;
1
2
3
4

可以发现结果也不对

可以发现，如果使用语句

SELECT id, username, passwd, enabled, account_non_expired, account_non_locked, credentials_non_expired, is_deleted, create_time, create_user, update_time, update_user
FROM test.image_management_user
order by update_time, id desc
;
1
2
3
4

则实际上是将查询结果按照update_time做升序，再对id做降序，并且以update_time升序为优先，也就是说desc只对它修饰的id字段生效，所以如果对多个字段排序，需要多次使用desc/asc来修饰多个字段，正确解决需求的语句如下：

SELECT id, username, passwd, enabled, account_non_expired, account_non_locked, credentials_non_expired, is_deleted, create_time, create_user, update_time, update_user
FROM test.image_management_user
order by id desc, update_time desc
;
1
2
3
4

结果为：

可以看到查询结果以id降序为有限，其次保证update_time的降序。

Mybatis-Plus中lambda表达式使用

MyBatis-Plus 的 Lambda 表达式可以用于构建复杂的查询条件，包括嵌套的 Lambda 表达式，使用通常涉及QueryWrapper或LambdaQueryWrapper对象，这些Wrapper允许构建复杂的查询条件，和各种方法一起使用，如 .and() （逻辑与） .eq()（等于）、.ne()（不等于）、.in()（在列表中）、.like()（模糊匹配）等等。

比如这个逻辑或条件查询：.or(i -> i.eq("business_type", 2).in("state", 2, 3))

表示查询 business_type 字段值为 2 或者 state 字段值为 2 或 3 的记录。

.or(b -> b.eq("state", 8).eq("bidding_sign", 1)) ：表示查询 state 字段值为 8 并且 bidding_sign 字段值为 1 的记录。

Mybatis-Plus多值模糊查询sql语句

有时候需要多个值在单个字段上进行模糊查询，比如对用户名user_name 字段中搜索包含张，或李，或赵的用户名，mysql中有

SELECT * FROM public.table WHERE user_name REGEXP '张|李|赵';
1

假设张，李，赵通过List<String>传到后端，请求体对象为input，则利用mybatis-plus我们可以实现多值模糊查询sql语句：

        // 多值模糊查询sql语句
        List<String> keyWords = input.getKeyWords();
        String sql = "user_name REGEXP ";
        if (!CollectionUtil.isEmpty(keyWords)) {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.append("'");
            for (String keyWord : keyWords) {
                sqlBuilder.append(keyWord).append("|");
            }
            sqlBuilder.deleteCharAt(sqlBuilder.length() - 1).append("'");;
            sql += sqlBuilder.toString();
        }

        // 数据库分页查询 - apply将sql语句应用
        Page<User> selectOutputPage = userInfoMapper.selectPage(
                new Page<>(input.getPageNo(), input.getPageSize()),
                Wrappers.lambdaQuery(User.class)
                .eq(User::getIsDeleted, Boolean.FALSE)
                .in(!input.getUserTypes().isEmpty(), User::getUserType, input.getUserTypes())
                .apply(sql)
        );
        
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

这里多值查询是或逻辑。

也可以通过 .and() 加lambda表达式实现，这里多值查询是与逻辑。

        // 数据库分页查询
        List<String> finalNameKeyWords = nameKeyWords;
        Page<User> UserPage = userMapper.selectPage(
                new Page<>(parameter.getPageNo(), parameter.getPageSize()),
                Wrappers.lambdaQuery(User.class)
                .eq(User::getIsDeleted, Boolean.FALSE)
                .in(!input.getUserTypes().isEmpty(), User::getUserType, input.getUserTypes())
                .and(!ObjectUtil.isEmpty(finalNameKeyWords), wq -> {
                    for (String finalNameKeyWord : finalNameKeyWords) {
                        wq.like(User::getLabelName, finalNameKeyWord);
                    }
                })
        );

1
2
3
4
5
6
7
8
9
10
11
12
13
14

改成.or()就是或逻辑

        // 数据库分页查询
        List<String> finalNameKeyWords = nameKeyWords;
        Page<User> UserPage = userMapper.selectPage(
                new Page<>(parameter.getPageNo(), parameter.getPageSize()),
                Wrappers.lambdaQuery(User.class)
                .eq(User::getIsDeleted, Boolean.FALSE)
                .in(!input.getUserTypes().isEmpty(), User::getUserType, input.getUserTypes())
                .or(!ObjectUtil.isEmpty(finalNameKeyWords), wq -> {
                    for (String finalNameKeyWord : finalNameKeyWords) {
                        wq.like(User::getLabelName, finalNameKeyWord);
                    }
                })
        );

1
2
3
4
5
6
7
8
9
10
11
12
13
14

Mapper.xml放在src/main/java下解决 Invalid bound statement (not found)

前言

在Mybatis项目中引入Mybatis-Plus框架，发现项目的xml直接放在src/main/java中，而不是src/main/resources。

引入Mybatis-Plus为了一致性也将xml直接放在src/main/java下，发现报错：

org.apache.ibatis.binding.BindingException: Invalid bound statement (not found)
1

文件结构

我的项目文件结构概览如下：

src/main/java

org.example.dao
|____ config
|____ controller
|____ ......
|____ dao
|____ |____ mapper
|____ |____|____ AAAMapper.xml
|____ |____|____ BBBMapper.xml
|____ |____|____ CCCMapper.xml
|____ |____|____ ......
|____ |____ AAAMapper.java
|____ |____ BBBMapper.java
|____ |____ CCCMapper.java
|____ |____ ......
|____ ......
|____ MainApplication.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

src/main/resources

application.yml
1

问题解决

配置文件application.yml中添加：

mybatis-plus:
  mapper-locations: classpath*:com/ys7/saas/algorithmtraining/dao/mapper/*Mapper.xml
1
2

如果是application.properties也是一样的，只是要转换一下格式。

pom.xml文件除了依赖以外，添加<resources>配置，指定目录为src/main/java，包含所有文件后缀为Mapper.xml的xml文件，当然这里<include>**/*Mapper.xml</include>也可以写成<include>**/*.xml</include>，这样就包含所有xml文件。

	......
	
    <dependencies>
    
		......
		
        <!-- mybatis-plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.4.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.velocity</groupId>
            <artifactId>velocity-engine-core</artifactId>
            <version>2.3</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter-test</artifactId>
            <version>3.4.2</version>
        </dependency>

    </dependencies>


    <build>

		......
		
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*Mapper.xml</include>
                </includes>
            </resource>
        </resources>
    </build>

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
38
39
40
41
42
43
44
45

MainApplication.java

/**
 * 服务启动类
 * MapperScan指定mapper扫描路径
 */
@MapperScan("org.example.dao")
@SpringBootApplication
public class MainApplication {

    public static void main(String[] args) {
        SpringApplication.run(MainApplication.class, args);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12

参考

https://blog.csdn.net/hanpenghu/article/details/83897618

https://baomidou.com/pages/f84a74/#%E8%87%AA%E5%AE%9A%E4%B9%89-sql-%E6%97%A0%E6%B3%95%E6%89%A7%E8%A1%8C