不同查询构建器的使用方式（Mybatis、Mybatis-Plus、Mybatis-Flex、Spring Data JPA、QueryDsl）

一、SQL查询需求

实现如下3个查询：

-- 查询1
SELECT 
* 
FROM SAMPLE 
WHERE
id = 1 
AND name LIKE '%ha%' 
AND create_time BETWEEN '2014-11-24' AND '2024-11-24'  -- 查询2
SELECT 
* 
FROM SAMPLE 
WHERE
id = 1 
OR name LIKE '%ha%' 
OR create_time BETWEEN '2014-11-24' AND '2024-11-24'  -- 查询3
SELECT 
* 
FROM SAMPLE 
WHERE
( id = 1 OR name LIKE '%ha%')
AND create_time BETWEEN '2014-11-24' AND '2024-11-24' 

查询参数：

package com.luo.query;class SampleQuery {private Long id;private String name;private LocalDateTime startDate;private LocalDateTime endDate;//省略getter/setter...
}

Sample数据对象：

package com.luo.po;class Sample {private Long id;private String name;private LocalDateTime createTime;//省略getter/setter...
}

二、【Mybaits】xml方式

定义dao接口：

package com.luo.dao;class SamplerMapper {List<Sample> select1(SampleQuery query);List<Sample> select2(SampleQuery query);List<Sample> select3(SampleQuery query);
}

定义mapper.xml，使用xml编写动态sql条件：

<?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="com.luo.dao.SampleMapper">   <select id="select1" resultType="com.luo.po.Sample">SELECT *FROM SAMPLE<where><if test="id != null">AND id = #{id}</if><if test="name != null">AND name LIKE CONCAT('%',#{name},'%')</if><if test="startDate != null and endDate != null">AND create_time BETWEEN #{startDate} AND #{endDate}</if></where></select><select id="select2" resultType="com.luo.po.Sample">SELECT *FROM SAMPLE<where><if test="id != null">OR id = #{id}</if><if test="name != null">OR name LIKE CONCAT('%',#{name},'%')</if><if test="startDate != null and endDate != null">OR create_time BETWEEN #{startDate} AND #{endDate}</if></where></select><select id="select3" resultType="com.luo.po.Sample">SELECT *FROM SAMPLE<where><if test="id != null or name != null">(<if test="id != null">id = #{id} OR </if><if test="name != null">name LIKE CONCAT('%',#{name},'%')</if>)</if>            <if test="startDate != null and endDate != null">AND create_time BETWEEN #{startDate} AND #{endDate}</if></where></select>
</mapper>

三、【Mybatis-Plus】QueryWrapper方式

定义dao接口，使用Mybatis-Plus QueryWrapper 动态构建查询条件：

import com.baomidou.mybatisplus.core.mapper.BaseMapper
import com.baomidou.mybatisplus.core.toolkit.Wrappers;public interface SampleMapper extends BaseMapper<Sample> {default List<Sample> select1(SampleQueryDto query) {return this.selectList(Wrappers.<Sample>lambdaQuery().eq(Objects.nonNull(query.getId()), Sample::getId, query.getId()).like(Objects.nonNull(query.getName()), Sample::getName, query.getName()).between(Objects.nonNull(query.getStartDate()) && Objects.nonNull(query.getEndDate()), Sample::getCreateTime, query.getStartDate(), query.getEndDate()));}default List<Sample> select2(SampleQueryDto query) {return this.selectList(Wrappers.<Sample>lambdaQuery().or().eq(Objects.nonNull(query.getId()), Sample::getId, query.getId()).or().like(Objects.nonNull(query.getName()), Sample::getName, query.getName()).or().between(Objects.nonNull(query.getStartDate()) && Objects.nonNull(query.getEndDate()), Sample::getCreateTime, query.getStartDate(), query.getEndDate()));}default List<Sample> select3(SampleQueryDto query) {return this.selectList(Wrappers.<Sample>lambdaQuery().between(Objects.nonNull(query.getStartDate()) && Objects.nonNull(query.getEndDate()), Sample::getCreateTime, query.getStartDate(), query.getEndDate()).and(Objects.nonNull(query.getId()) || Objects.nonNull(query.getName()),wrapper -> wrapper.eq(Objects.nonNull(query.getId()), Sample::getId, query.getId()).or().like(Objects.nonNull(query.getName()), Sample::getName, query.getName())));}
}

四、【Mybatis-Flex】QueryWrapper方式

定义dao接口，使用Mybatis-Flex QueryWrapper 动态构建查询条件：

import com.mybatisflex.core.BaseMapper;
import com.mybatisflex.core.query.QueryWrapperpublic interface SampleMapper extends BaseMapper<Sample> {default List<Sample> select1(SampleQueryDto query) {return this.selectListByQuery(QueryWrapper.create().eq(Sample::getId, query.getId(), Objects.nonNull(query.getId())).like(Sample::getName, query.getName(), Objects.nonNull(query.getName())).between(Sample::getCreateTime, query.getStartDate(), query.getEndDate(), Objects.nonNull(query.getStartDate()) && Objects.nonNull(query.getEndDate())));}default List<Sample> select2(SampleQueryDto query) {return this.selectListByQuery(QueryWrapper.create().or(Sample::getId).eq(query.getId(), Objects.nonNull(query.getId())).or(Sample::getName).like(query.getName(), Objects.nonNull(query.getName())).or(Sample::getCreateTime).between(query.getStartDate(), query.getEndDate(), Objects.nonNull(query.getStartDate()) && Objects.nonNull(query.getEndDate())));}default List<Sample> select3(SampleQueryDto query) {return this.selectListByQuery(QueryWrapper.create().between(Sample::getCreateTime, query.getStartDate(), query.getEndDate(), Objects.nonNull(query.getStartDate()) && Objects.nonNull(query.getEndDate())).and(queryWrapper -> {queryWrapper.eq(Sample::getId, query.getId(), Objects.nonNull(query.getId())).or(Sample::getName).like(query.getName(), Objects.nonNull(query.getName()));}, Objects.nonNull(query.getId()) || Objects.nonNull(query.getName())));}
}

五、【Mybatis-Flex】QueryWrapper & APT方式

定义dao接口，使用Mybatis-Flex QueryWrapper 和 基于APT生成的查询辅助类 动态构建查询条件：

import com.mybatisflex.core.BaseMapper;
import com.mybatisflex.core.query.QueryWrapper;
import static com.luo.po.table.SampleTableDef.SAMPLE;public interface SampleMapper extends BaseMapper<Sample> {default List<Sample> select1(SampleQueryDto query) {return this.selectListByQuery(QueryWrapper.create().where(SAMPLE.ID.eq(query.getId(), Objects.nonNull(query.getId()))).and(SAMPLE.NAME.like(query.getName(), Objects.nonNull(query.getName()))).and(SAMPLE.CREATE_TIME.between(query.getStartDate(), query.getEndDate(), Objects.nonNull(query.getStartDate()) && Objects.nonNull(query.getEndDate()))));}default List<Sample> select2(SampleQueryDto query) {return this.selectListByQuery(QueryWrapper.create().where(SAMPLE.ID.eq(query.getId(), Objects.nonNull(query.getId()))).or(SAMPLE.NAME.like(query.getName(), Objects.nonNull(query.getName()))).or(SAMPLE.CREATE_TIME.between(query.getStartDate(), query.getEndDate(), Objects.nonNull(query.getStartDate()) && Objects.nonNull(query.getEndDate()))));}default List<Sample> select3(SampleQueryDto query) {return this.selectListByQuery(QueryWrapper.create().where(SAMPLE.CREATE_TIME.between(query.getStartDate(), query.getEndDate(), Objects.nonNull(query.getStartDate()) && Objects.nonNull(query.getEndDate()))).and(SAMPLE.ID.eq(query.getId(), Objects.nonNull(query.getId())).or(SAMPLE.NAME.like(query.getName(), Objects.nonNull(query.getName())))));}
}

六、【Spring Data JPA】Specification方式

定义仓库接口，使用Spring Data JPA Specification构建查询条件：

import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.util.CollectionUtils;import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;/*** 示例仓库（Specification）** @author luohq* @date 2024-11-25 13:01* @link https://docs.spring.io/spring-data/jpa/reference/jpa/specifications.html*/
public interface SampleRepo1 extends JpaRepository<Sample, String>, JpaSpecificationExecutor<Sample>, QuerydslPredicateExecutor<Sample> {default List<Sample> select1(SampleQuery query) {//构建查询条件Specification<Sample> specification = new Specification<>() {@Overridepublic Predicate toPredicate(Root<Sample> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {List<Predicate> predicates = new ArrayList<>(3);if (Objects.nonNull(query.getId())) {predicates.add(cb.equal(root.get("id"), query.getId()));}if (Objects.nonNull(query.getName())) {predicates.add(cb.like(root.get("name"), "%" + query.getName() + "%"));}if (Objects.nonNull(query.getStartDate()) && Objects.nonNull(query.getEndDate())) {predicates.add(cb.between(root.get("createTime"), query.getStartDate(), query.getEndDate()));}return cb.and(predicates.toArray(Predicate[]::new));}};//执行查询return this.findAll(specification);}default List<Sample> select2(SampleQuery query) {//构建查询条件Specification<Sample> specification = new Specification<>() {@Overridepublic Predicate toPredicate(Root<Sample> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {List<Predicate> predicates = new ArrayList<>(3);if (Objects.nonNull(query.getId())) {predicates.add(cb.equal(root.get("id"), query.getId()));}if (Objects.nonNull(query.getName())) {predicates.add(cb.like(root.get("name"), "%" + query.getName() + "%"));}if (Objects.nonNull(query.getStartDate()) && Objects.nonNull(query.getEndDate())) {predicates.add(cb.between(root.get("createTime"), query.getStartDate(), query.getEndDate()));}if (CollectionUtils.isEmpty(predicates)) {//默认添加一个成立条件，避免多个or条件为空时拼接0=1predicates.add(cb.equal(cb.literal(1), 1));}return cb.or(predicates.toArray(Predicate[]::new));}};//执行查询return this.findAll(specification);}default List<Sample> select3(SampleQuery query) {//构建查询条件Specification<Sample> specification = new Specification<>() {@Overridepublic Predicate toPredicate(Root<Sample> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {List<Predicate> predicates = new ArrayList<>(2);if (Objects.nonNull(query.getId())) {predicates.add(cb.equal(root.get("id"), query.getId()));}if (Objects.nonNull(query.getName())) {predicates.add(cb.like(root.get("name"), "%" + query.getName() + "%"));}if (CollectionUtils.isEmpty(predicates)) {//默认添加一个成立条件，避免多个or条件为空时拼接0=1predicates.add(cb.equal(cb.literal(1), 1));}Predicate group1 = cb.or(predicates.toArray(Predicate[]::new));predicates = new ArrayList<>(1);if (Objects.nonNull(query.getStartDate()) && Objects.nonNull(query.getEndDate())) {predicates.add(cb.between(root.get("createTime"), query.getStartDate(), query.getEndDate()));}Predicate group2 = cb.and(predicates.toArray(Predicate[]::new));return cb.and(group1, group2);}};//执行查询return this.findAll(specification);}
}

七、【Spring Data JPA】Specification & MetaModel方式

定义仓库接口，使用Spring Data JPA Specification 和 动态生成的JPA元模型 构建查询条件：

import com.luo.po.Sample;
import com.luo.po.Sample_;
import com.luo.query.SampleQuery;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.util.CollectionUtils;import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;/*** 示例仓库（Specification、MetaModel）** @author luohq* @date 2024-11-25 13:01* @link https://docs.spring.io/spring-data/jpa/reference/jpa/specifications.html* @link https://docs.jboss.org/hibernate/jpamodelgen/1.3/reference/en-US/html_single/*/
public interface SampleRepo2 extends JpaRepository<Sample, String>, JpaSpecificationExecutor<Sample>, QuerydslPredicateExecutor<Sample> {default List<Sample> select1(SampleQuery query) {//构建查询条件Specification<Sample> specification = new Specification<>() {@Overridepublic Predicate toPredicate(Root<Sample> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {List<Predicate> predicates = new ArrayList<>(3);if (Objects.nonNull(query.getId())) {predicates.add(cb.equal(root.get(Sample_.ID), query.getId()));}if (Objects.nonNull(query.getName())) {predicates.add(cb.like(root.get(Sample_.NAME), "%" + query.getName() + "%"));}if (Objects.nonNull(query.getStartDate()) && Objects.nonNull(query.getEndDate())) {predicates.add(cb.between(root.get(Sample_.CREATE_TIME), query.getStartDate(), query.getEndDate()));}return cb.and(predicates.toArray(Predicate[]::new));}};//执行查询return this.findAll(specification);}default List<Sample> select2(SampleQuery query) {//构建查询条件Specification<Sample> specification = new Specification<>() {@Overridepublic Predicate toPredicate(Root<Sample> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {List<Predicate> predicates = new ArrayList<>(3);if (Objects.nonNull(query.getId())) {predicates.add(cb.equal(root.get(Sample_.ID), query.getId()));}if (Objects.nonNull(query.getName())) {predicates.add(cb.like(root.get(Sample_.NAME), "%" + query.getName() + "%"));}if (Objects.nonNull(query.getStartDate()) && Objects.nonNull(query.getEndDate())) {predicates.add(cb.between(root.get(Sample_.CREATE_TIME), query.getStartDate(), query.getEndDate()));}if (CollectionUtils.isEmpty(predicates)) {//默认添加一个成立条件，避免多个or条件为空时拼接0=1predicates.add(cb.equal(cb.literal(1), 1));}return cb.or(predicates.toArray(Predicate[]::new));}};//执行查询return this.findAll(specification);}default List<Sample> select3(SampleQuery query) {//构建查询条件Specification<Sample> specification = new Specification<>() {@Overridepublic Predicate toPredicate(Root<Sample> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {List<Predicate> predicates = new ArrayList<>(2);if (Objects.nonNull(query.getId())) {predicates.add(cb.equal(root.get(Sample_.ID), query.getId()));}if (Objects.nonNull(query.getName())) {predicates.add(cb.like(root.get(Sample_.NAME), "%" + query.getName() + "%"));}if (CollectionUtils.isEmpty(predicates)) {//默认添加一个成立条件，避免多个or条件为空时拼接0=1predicates.add(cb.equal(cb.literal(1), 1));}Predicate group1 = cb.or(predicates.toArray(Predicate[]::new));predicates = new ArrayList<>(1);if (Objects.nonNull(query.getStartDate()) && Objects.nonNull(query.getEndDate())) {predicates.add(cb.between(root.get(Sample_.CREATE_TIME), query.getStartDate(), query.getEndDate()));}Predicate group2 = cb.and(predicates.toArray(Predicate[]::new));return cb.and(group1, group2);}};//执行查询return this.findAll(specification);}
}

八、【Spring Data JPA】QueryDsl方式

定义仓库接口，使用 Spring Data JPA 和 QueryDsl 构建查询条件：

import com.luo.po.Sample;
import static com.luo.po.QSample.sample;
import com.luo.query.SampleQuery;
import com.querydsl.core.types.Predicate;
import com.querydsl.core.types.dsl.BooleanExpression;
import com.querydsl.core.types.dsl.Expressions;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.StreamSupport;/*** 示例仓库（Specification、QueryDsl）** @author luohq* @date 2024-11-25 13:01* @link https://docs.spring.io/spring-data/jpa/reference/jpa/specifications.html* @link https://docs.spring.io/spring-data/jpa/reference/repositories/core-extensions.html#core.extensions.querydsl*/
public interface SampleRepo3 extends JpaRepository<Sample, String>, JpaSpecificationExecutor<Sample>, QuerydslPredicateExecutor<Sample> {default List<Sample> select1(SampleQuery query) {//Predicate predicate = sample.id.eq(query.getId())//        .and(sample.name.contains(query.getName()))//        .and(sample.createTime.between(query.getStartDate(), query.getEndDate()));//构建查询条件List<BooleanExpression> predicates = new ArrayList<>();if (query.getId() != null) {predicates.add(sample.id.eq(query.getId()));}if (query.getName() != null) {predicates.add(sample.name.contains(query.getName()));}if (query.getStartDate() != null && query.getEndDate() != null) {predicates.add(sample.createTime.between(query.getStartDate(), query.getEndDate()));}//避免空指针异常if (predicates.isEmpty()) {predicates.add(Expressions.asBoolean(true).isTrue());}//合并查询条件BooleanExpression predicate = Expressions.allOf(predicates.toArray(new BooleanExpression[predicates.size()]));//执行查询Iterable<Sample> all = this.findAll(predicate);return StreamSupport.stream(all.spliterator(), false).collect(Collectors.toList());}default List<Sample> select2(SampleQuery query) {//Predicate predicate = sample.id.eq(query.getId())//        .or(sample.name.contains(query.getName()))//        .or(sample.createTime.between(query.getStartDate(), query.getEndDate()));//构建查询条件List<BooleanExpression> predicates = new ArrayList<>();if (query.getId() != null) {predicates.add(sample.id.eq(query.getId()));}if (query.getName() != null) {predicates.add(sample.name.contains(query.getName()));}if (query.getStartDate() != null && query.getEndDate() != null) {predicates.add(sample.createTime.between(query.getStartDate(), query.getEndDate()));}//避免空指针异常if (predicates.isEmpty()) {predicates.add(Expressions.asBoolean(true).isTrue());}//合并查询条件BooleanExpression predicate = Expressions.anyOf(predicates.toArray(new BooleanExpression[predicates.size()]));//执行查询Iterable<Sample> all = this.findAll(predicate);return StreamSupport.stream(all.spliterator(), false).collect(Collectors.toList());}default List<Sample> select3(SampleQuery query) {//Predicate predicate = sample.createTime.between(query.getStartDate(), query.getEndDate())//        .and(sample.id.eq(query.getId())//                .or(sample.name.contains(query.getName()))//        );//构建查询条件 - 分组1List<BooleanExpression> predicates = new ArrayList<>();if (query.getStartDate() != null && query.getEndDate() != null) {predicates.add(sample.createTime.between(query.getStartDate(), query.getEndDate()));}//避免空指针异常if (predicates.isEmpty()) {predicates.add(Expressions.asBoolean(true).isTrue());}//合并查询条件BooleanExpression group1 = Expressions.allOf(predicates.toArray(new BooleanExpression[predicates.size()]));//构建查询条件 - 分组2predicates = new ArrayList<>();if (query.getId() != null) {predicates.add(sample.id.eq(query.getId()));}if (query.getName() != null) {predicates.add(sample.name.contains(query.getName()));}//避免空指针异常if (predicates.isEmpty()) {predicates.add(Expressions.asBoolean(true).isTrue());}//合并查询条件BooleanExpression group2 = Expressions.anyOf(predicates.toArray(new BooleanExpression[predicates.size()]));//合并分组条件Predicate predicate = Expressions.allOf(group1, group2);//执行查询Iterable<Sample> all = this.findAll(predicate);return StreamSupport.stream(all.spliterator(), false).collect(Collectors.toList());}
}