MybatisPlus的lambda与xml组合查询

爱编程的Loren

于 2025-05-09 20:45:13 发布

阅读量331

点赞数 6

分类专栏： Java 框架部分文章标签： java spring boot mybatis

本文链接：https://blog.csdn.net/qq_35255384/article/details/147837072

版权

Java 同时被 2 个专栏收录

50 篇文章

订阅专栏

框架部分

32 篇文章

订阅专栏

一，需求描述

有时候当我们的业务很复杂或者由于多人开发原因，需要兼顾到多个不同风格导致的差异性组合查询时，会存在使用LambdaQueryWrapper与xml组合的方式，

本篇文章主要讨论该实现方式及步骤。

二，环境描述

JDK21 + SpringBoot3.3.3 + MybatisPlus + Maven + mysql5.8

引入的pom.xml 参照如下：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.3.3</version>
        <relativePath/>
    </parent>
    <groupId>org.example</groupId>
    <artifactId>test-springboot-mybatis-starrocks</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>21</maven.compiler.source>
        <maven.compiler.target>21</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <mybatis.plus.version>3.5.5</mybatis.plus.version>
        <dynamic.datasource.version>4.3.0</dynamic.datasource.version>
        <mysql.connector.version>8.0.32</mysql.connector.version>
        <druid.version>1.2.20</druid.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-spring-boot3-starter</artifactId>
            <version>${mybatis.plus.version}</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot3-starter</artifactId>
            <version>${dynamic.datasource.version}</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.connector.version}</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-3-starter</artifactId>
            <version>${druid.version}</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.26</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

三，代码实现步骤

1，表结构sql 及初始化测试脚本：

CREATE TABLE `t_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_username` (`username`) COMMENT '用户名唯一索引'
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

INSERT INTO `t_user` (`username`, `age`, `email`, `create_time`) VALUES
('张三', 25, 'zhangsan@example.com', '2023-01-01 10:00:00'),
('李四', 30, 'lisi@example.com', '2023-01-02 11:00:00'),
('王五', 28, 'wangwu@example.com', '2023-01-03 12:00:00'),
('赵六', 35, 'zhaoliu@example.com', '2023-01-04 13:00:00'),
('钱七', 22, 'qianqi@example.com', '2023-01-05 14:00:00'),
('孙八', 40, 'sunba@example.com', '2023-01-06 15:00:00'),
('周九', 27, 'zhoujiu@example.com', '2023-01-07 16:00:00'),
('吴十', 33, 'wushi@example.com', '2023-01-08 17:00:00');

2，配置文件 application.xml：

server:
  port: 8080
spring:
  datasource:
    dynamic:
      primary: db1 # 配置默认数据库
      datasource:
        db1: # 数据源1配置
          url: jdbc:mysql://localhost:3307/db1?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8
          username: loren
          password: 123456
          driver-class-name: com.mysql.cj.jdbc.Driver
        db2: # 数据源2配置
          url: jdbc:mysql://localhost:3307/db2?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8
          username: loren
          password: 123456
          driver-class-name: com.mysql.cj.jdbc.Driver
        db3: # 数据源3配置
          url: jdbc:mysql://localhost:3307/db3?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8
          username: loren
          password: 123456
          driver-class-name: com.mysql.cj.jdbc.Driver
      druid:
        initial-size: 1
        max-active: 20
        min-idle: 1
        max-wait: 60000
  autoconfigure:
    exclude:  com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure # 去除druid配置
#mybatis-plus:
#  configuration:
#    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
logging:
  level:
    #项目mapper目录
    org.example.mapper: DEBUG
#  mybatis-plus:
#    configuration:
#      log-impl: org.apache.ibatis.logging.slf4j.Slf4jImpl

3，入参及实体类相关对象

package org.example.model;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import java.time.LocalDateTime;
@Data
@TableName("t_user")
public class TUser {
    @TableId(type = IdType.AUTO)
    private Long id;
    private String username;
    private Integer age;
    private String email;
    private LocalDateTime createTime;
}

package org.example.model.params;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Accessors(chain = true)
public class QueryUserDTO {
    private String username;
    private Integer age;
    private String email;

}

package org.example.model.params;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Accessors(chain = true)
public class TUserParameter {
    private String username;
}

4，编写mapper接口及xml

package org.example.mapper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import org.apache.ibatis.annotations.Param;
import org.example.model.TUser;
import org.example.model.params.TUserParameter;
import java.util.List;
public interface TUserMapper extends BaseMapper<TUser> {
    List<TUser> selectByComposeConditions(
            @Param(Constants.WRAPPER) LambdaQueryWrapper<TUser> wrapper,
            @Param("p") TUserParameter parameter);
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.example.mapper.TUserMapper">
    <!-- 基础结果映射 -->
    <resultMap id="BaseResultMap" type="org.example.model.TUser">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="age" property="age"/>
        <result column="email" property="email"/>
        <result column="create_time" property="createTime"/>
    </resultMap>
    <!-- 自定义查询 -->
    <select id="selectByComposeConditions" resultType="org.example.model.TUser">
        SELECT * FROM t_user
        ${ew.customSqlSegment}
        <if test="p.username != null and p.username != ''">
            AND username LIKE CONCAT('%', #{p.username}, '%')
        </if>
    </select>
</mapper>

5，编写service层

package org.example.service;
import org.example.model.TUser;
import org.example.model.params.QueryUserDTO;
import java.util.List;
public interface TUserService {
    List<TUser> getUserList(QueryUserDTO queryUserDTO);
}

package org.example.service.impl;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import org.example.mapper.TUserMapper;
import org.example.model.TUser;
import org.example.model.params.QueryUserDTO;
import org.example.model.params.TUserParameter;
import org.example.service.TUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class TUserServiceImpl implements TUserService {
    @Autowired
    private TUserMapper tUserMapper;
    @Override
    public List<TUser> getUserList(QueryUserDTO queryUserDTO) {
        LambdaQueryWrapper<TUser> wrapper = new LambdaQueryWrapper<>();
        if (queryUserDTO != null && queryUserDTO.getEmail() != null) {
            wrapper.eq(TUser::getEmail, queryUserDTO.getEmail());
        }
        assert queryUserDTO != null;
        TUserParameter parameter = TUserParameter.builder()
                .username(queryUserDTO.getUsername())
                .build();
        return tUserMapper.selectByComposeConditions(wrapper, parameter);
    }
}

6，编写单元测试

package org.example.service;
import lombok.extern.slf4j.Slf4j;
import org.example.Main;
import org.example.model.params.QueryUserDTO;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest(classes = Main.class)
@Slf4j
public class TUserServiceTest {
    @Autowired
    private TUserService tUserService;
    @Test
    public void testGetUserList() {
        QueryUserDTO queryUserDTO = new QueryUserDTO("赵六", 35, "zhaoliu@example.com");
        log.info("查询结果：{}", tUserService.getUserList(queryUserDTO));
    }
}