使用spring data jpa实现统一的多条件查询

由于篇幅限制，本文中，我们只给出了部分示例代码。
如果你需要完整的代码，请点击：https://github.com/mengyunzhi/springBootSampleCode/tree/master/multiQuery

本文开发环境：java:1.8 + maven:3.3

WHY TO DO

在系统开发中，我们避免不了要使用多条件查询，比如我们按学生的性氏来查找学生，按学生所在的班级查找学生，按学生入学的时间来查找学生等。在以往的开发中，进行综合查询时，我们需要为每个实体单独写综合查询的代码。具体思路为：先将查询条件加入map，然后在综合查询时，取出map中的值，并加入查询条件中，最后实现综合查询。

时间一长，我们发现在查询的过程中，大多有以下规律：

• 传入的值类型为number（short int long ...)时，大多进行的为精确查询。
• 传入的值的类型为String时，大多进行的为模糊查询。
• 传入的值为Collection，大多进行为in查询。
• 传入的值为Date时，大多进行的为范围查询。

鉴于大多数符合以上规律，本文将阐述了一种简单的方法，能够使得我们像使用findById(Long id)一样，来使用pageByEntity(Object entity)进行综合条件查询。

本文需求

有3个实体类：教师，班级，地址。一个实体基类,YunZhiAbstractEntity
三者的关系如下：

功能需求如下：
以klass对象做为查询参数，按klass对象中的属性值来进行综合查询：
比如：

1. 设置klass对象的name值为zhangsan，则模糊查出所有name为zhangsan班级信息。
2. 设置klass对象的totalStudentCount值为3，则查出所有totalStudentCount为3班级信息。
3. 设置klass对象中的teacher。则查询时加入klass->teacher的信息。如果teacher设置了ID，则加入teacher对应ID的精确查询，如果未设置ID，则查询teacher的其它属性，其属性不为null，加入查询条件。
4. 设置klass对象中的teacher中的address，则按第3点的标准进行klass->teacher->address的关联查询。
5. 可以在查询中实现排除某些字段（不为null，也不进行关联查询)。
6. 可以实现对某些字段的范围查询。

知识准备

本文大体将使用到以下知识点：

• spring-data-jpa的5.5章节对综合查询进行了说明，并且给出了示例代码。
• java的反射机制。
• java的注解。

基本思路：

1. 根据传入的查询实体，反射出其字段、字段中的注解、字段的值。然后在依次判断其类型，按类型的不同，加入查询条件。
2. 如果字段类型为实体，则说明进行关联查询(join),此时，进行递归调用，来获取关联查询实体的字段，并按字段类型，字段值进行综合查询
3. 特殊的查询（排除字段，范围查询的开始，结束字段，in查询），使用注解来协助完成。

代码实现

本文github中给出的示例代码，除in查询外，已经实现了其它几点在本文中所需要的功能。本节中，只直接给出关键代码，其它的关联代码，还需要到github中进行查看。

文件列表

├── java
│   └── com
│       └── mengyunzhi
│           └── springbootsamplecode
│               └── multiquery
│                   ├── MultiQueryApplication.java
│                   ├── annotation
│                   │   ├── BeginQueryParam.java
│                   │   ├── EndQueryParam.java
│                   │   └── IgnoreQueryParam.java
│                   ├── entity
│                   │   ├── Address.java
│                   │   ├── Klass.java
│                   │   ├── Teacher.java
│                   │   └── YunZhiAbstractEntity.java
│                   ├── repository
│                   │   ├── AddressRepository.java
│                   │   ├── KlassRepository.java
│                   │   └── TeacherRepository.java
│                   └── service
│                       ├── AddressService.java
│                       ├── AddressServiceImpl.java
│                       ├── CommonService.java
│                       ├── KlassService.java
│                       ├── KlassServiceImpl.java
│                       ├── TeacherService.java
│                       ├── TeacherServiceImpl.java
│                       ├── YunzhiService.java
│                       └── YunzhiServiceImpl.java
└── resources
    └── application.yml

实体类的继承我们在需求中已经给出。annotation注解，repository仓库（dao)层，service服务层中都很普通，它们是本文完成综合查询的基础。最下方的 YunzhiServiceImpl.java，便是我们今天的主角。

YunzhiService

package com.mengyunzhi.springbootsamplecode.multiquery.service;


import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

import java.util.List;

/**
 * @author panjie
 */
public interface YunzhiService {
    /**
     * 通过传入的实体进行多条件查询
     * @param entity
     * @param pageable
     * @return 分页数据
     * panjie
     */
    Page<?> page(JpaSpecificationExecutor jpaSpecificationExecutor, Object entity, Pageable pageable);

    /**
     * 通过传入的实体查询所有数据
     * @param entity
     * @return
     * panjie
     */
    List<?> findAll(JpaSpecificationExecutor jpaSpecificationExecutor, Object entity);
}

YunzhiServiceImpl

代码中，已经给出详细的注释。请参阅。

package com.mengyunzhi.springbootsamplecode.multiquery.service;

import com.mengyunzhi.springbootsamplecode.multiquery.annotation.BeginQueryParam;
import com.mengyunzhi.springbootsamplecode.multiquery.annotation.EndQueryParam;
import com.mengyunzhi.springbootsamplecode.multiquery.annotation.IgnoreQueryParam;
import com.mengyunzhi.springbootsamplecode.multiquery.entity.YunZhiAbstractEntity;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Service;

import javax.persistence.criteria.*;
import java.lang.reflect.Field;
import java.sql.Date;
import java.util.Calendar;
import java.util.Collection;
import java.util.List;

/**
 * 多条件综合查询
 *
 * @author panjie
 */
@Service
public class YunzhiServiceImpl implements YunzhiService {
    private static final Logger logger = LoggerFactory.getLogger(YunzhiServiceImpl.class);

    @Override
    public Page<Object> page(JpaSpecificationExecutor jpaSpecificationExecutor, Object entity, Pageable pageable) {
        Specification specification = this.getSpecificationByEntity(entity);
        Page<Object> objectPage = jpaSpecificationExecutor.findAll(specification, pageable);
        return objectPage;
    }

    @Override
    public List<Object> findAll(JpaSpecificationExecutor jpaSpecificationExecutor, Object entity) {
        Specification specification = this.getSpecificationByEntity(entity);
        List<Object> objectList = jpaSpecificationExecutor.findAll(specification);
        return objectList;
    }

    private Specification getSpecificationByEntity(Object entity) {
        Specification<Object> specification = new Specification<Object>() {
            private Predicate predicate = null;
            private CriteriaBuilder criteriaBuilder;

            // 设置and谓语.注意，这里只能设置and关系的谓语，如果谓语为OR，则需要手动设置
            private void andPredicate(Predicate predicate) {
                if (null != predicate) {
                    if (null == this.predicate) {
                        this.predicate = predicate;
                    } else {
                        this.predicate = this.criteriaBuilder.and(this.predicate, predicate);
                    }
                }
            }

            private void generatePredicate(Object entity, From<Object, ?> root) {
                logger.debug("反射字段，按字段类型，进行综合查询");
                Field[] fields = entity.getClass().getDeclaredFields();
                try {
                    for (Field field : fields) {
                        logger.info("反射字段名，及字段值。并设置为字段可见");
                        String name = field.getName();
                        field.setAccessible(true);
                        Object value = field.get(entity);

                        if (value != null) {
                            if (field.getAnnotation(IgnoreQueryParam.class) != null) {
                                logger.debug("存在@IgnoreQueryParam注解, 跳出");
                                continue;
                            }

                            // 初始化两个界限的变量
                            Boolean isBegin = false;
                            Boolean isEnd = false;

                            // 查找开始与结束的注解
                            BeginQueryParam beginQueryParam = field.getAnnotation(BeginQueryParam.class);
                            if (beginQueryParam != null) {
                                logger.debug("存在@BeginQueryParam注解");
                                isBegin = true;
                                name = beginQueryParam.name();
                            } else if (field.getAnnotation(EndQueryParam.class) != null) {
                                logger.debug("存在@EndQueryParam注解");
                                isEnd = true;
                                name = field.getAnnotation(EndQueryParam.class).name();
                            }

                            // 按字段类型进行查询
                            if (value instanceof String) {
                                logger.debug("字符串则进行模糊查询");
                                String stringValue = ((String) value);
                                if (!stringValue.isEmpty()) {
                                    this.andPredicate(criteriaBuilder.like(root.get(name).as(String.class), "%" + stringValue + "%"));
                                }
                            } else if (value instanceof Number) {
                                logger.debug("如果为number，则进行精确或范围查询");
                                if (value instanceof Short) {
                                    Short shortValue = (Short) value;
                                    if (isBegin) {
                                        this.andPredicate(criteriaBuilder.greaterThanOrEqualTo(root.get(name).as(Short.class), shortValue));
                                    } else if (isEnd) {
                                        this.andPredicate(criteriaBuilder.lessThanOrEqualTo(root.get(name).as(Short.class), shortValue));
                                    } else {
                                        this.andPredicate(criteriaBuilder.equal(root.get(name).as(Short.class), shortValue));
                                    }
                                } else if (value instanceof Integer) {
                                    Integer integerValue = (Integer) value;
                                    if (isBegin) {
                                        this.andPredicate(criteriaBuilder.greaterThanOrEqualTo(root.get(name).as(Integer.class), integerValue));
                                    } else if (isEnd) {
                                        this.andPredicate(criteriaBuilder.lessThanOrEqualTo(root.get(name).as(Integer.class), integerValue));
                                    } else {
                                        this.andPredicate(criteriaBuilder.equal(root.get(name).as(Integer.class), integerValue));
                                    }
                                } else if (value instanceof Long) {
                                    Long longValue = (Long) value;
                                    if (isBegin) {
                                        this.andPredicate(criteriaBuilder.greaterThanOrEqualTo(root.get(name).as(Long.class), longValue));
                                    } else if (isEnd) {
                                        this.andPredicate(criteriaBuilder.lessThanOrEqualTo(root.get(name).as(Long.class), longValue));
                                    } else {
                                        this.andPredicate(criteriaBuilder.equal(root.get(name).as(Long.class), longValue));
                                    }
                                } else {
                                    logger.error("综合查询Number类型，暂时只支持到Short,Integer,Long");
                                }
                            } else if (value instanceof Calendar) {
                                logger.debug("Calendar类型");
                                Calendar calendarValue = (Calendar) value;
                                if (isBegin) {
                                    this.andPredicate(criteriaBuilder.greaterThanOrEqualTo(root.get(name).as(Calendar.class), calendarValue));
                                } else if (isEnd) {
                                    this.andPredicate(criteriaBuilder.lessThanOrEqualTo(root.get(name).as(Calendar.class), calendarValue));
                                } else {
                                    this.andPredicate(criteriaBuilder.equal(root.get(name).as(Calendar.class), calendarValue));
                                }
                            } else if (value instanceof Date) {
                                logger.debug("Sql.Date类型");
                                Date dateValue = (Date) value;
                                if (isBegin) {
                                    this.andPredicate(criteriaBuilder.greaterThanOrEqualTo(root.get(name).as(Date.class), dateValue));
                                } else if (isEnd) {
                                    this.andPredicate(criteriaBuilder.lessThanOrEqualTo(root.get(name).as(Date.class), dateValue));
                                } else {
                                    this.andPredicate(criteriaBuilder.equal(root.get(name).as(Date.class), dateValue));
                                }
                            } else if (value instanceof YunZhiAbstractEntity) {
                                logger.debug("是实体类");
                                YunZhiAbstractEntity yunZhiAbstractEntity = (YunZhiAbstractEntity) value;
                                if (yunZhiAbstractEntity.getId() != null) {
                                    logger.debug("对应的ManyToOne，加入了id, 则按ID查询");
                                    this.andPredicate(criteriaBuilder.equal(root.join(name).get("id").as(Long.class), yunZhiAbstractEntity.getId()));
                                } else {
                                    logger.debug("未加入id, 则进行Join查询");
                                    this.generatePredicate(value, root.join(name));
                                }

                            } else if (value instanceof Collection) {
                                Collection<?> collectionValue = (Collection<?>)value;
                                if (collectionValue.size() > 0) {
                                    logger.warn("暂不支持一对多，多对多查询");
                                    // todo: 一对多，多对多查询
                                }
                            } else {
                                logger.error("综合查询暂不支持传入的数据类型", name, field);
                            }
                        }
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }

            @Override
            public Predicate toPredicate(Root<Object> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                // 设置CriteriaBuilder，用于合并谓语
                this.criteriaBuilder = criteriaBuilder;
                this.generatePredicate(entity, root);
                return this.predicate;
            }
        };

        return specification;
    }
}

测试

代码中，已经给出详细的注释。请参阅。

package com.mengyunzhi.springbootsamplecode.multiquery.service;

import com.mengyunzhi.springbootsamplecode.multiquery.entity.Address;
import com.mengyunzhi.springbootsamplecode.multiquery.entity.Klass;
import com.mengyunzhi.springbootsamplecode.multiquery.entity.Teacher;
import com.mengyunzhi.springbootsamplecode.multiquery.repository.KlassRepository;
import org.assertj.core.api.Assertions;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.Calendar;
import java.util.List;

/**
 * @author panjie
 */
@SpringBootTest
@RunWith(SpringRunner.class)
public class YunzhiServiceImplTest {
    private final static Logger logger = LoggerFactory.getLogger(YunzhiServiceImplTest.class);
    @Autowired KlassService klassService;
    @Autowired YunzhiService yunzhiService;
    @Autowired TeacherService teacherService;
    @Autowired AddressService addressService;
    @Autowired
    KlassRepository klassRepository;

    @Test
    public void pageByEntity() {
        Klass originKlass = klassService.getOneSavedKlass();
        PageRequest pageRequest = PageRequest.of(0, 2);

        Klass klass = this.initQueryKlass();
        Page<Klass> klassPage = (Page<Klass>) yunzhiService.page(klassRepository, klass, pageRequest);
        Assertions.assertThat(klassPage.getTotalElements()).isEqualTo(1);

        logger.info("更改short值 ，断言返回为0");
        klass.setTotalStudentCount((short) 11);
        klassPage = (Page<Klass>) yunzhiService.page(klassRepository, klass, pageRequest);
        Assertions.assertThat(klassPage.getTotalElements()).isEqualTo(0);

        logger.info("设置short值为null ，断言返回为1");
        klass.setTotalStudentCount(null);
        klassPage = (Page<Klass>) yunzhiService.page(klassRepository, klass, pageRequest);
        Assertions.assertThat(klassPage.getTotalElements()).isEqualTo(1);

        logger.info("更改int值 ，断言返回为0");
        klass.setIntegerTest(101);
        klassPage = (Page<Klass>) yunzhiService.page(klassRepository, klass, pageRequest);
        Assertions.assertThat(klassPage.getTotalElements()).isEqualTo(0);

        logger.info("设置int值为null ，断言返回为1");
        klass.setIntegerTest(null);
        klassPage = (Page<Klass>) yunzhiService.page(klassRepository, klass, pageRequest);
        Assertions.assertThat(klassPage.getTotalElements()).isEqualTo(1);

        logger.info("更改long值 ，断言返回为0");
        klass.setLongTest((long) 1001);
        klassPage = (Page<Klass>) yunzhiService.page(klassRepository, klass, pageRequest);
        Assertions.assertThat(klassPage.getTotalElements()).isEqualTo(0);

        logger.info("设置long值为null ，断言返回为1");
        klass.setLongTest(null);
        klassPage = (Page<Klass>) yunzhiService.page(klassRepository, klass, pageRequest);
        Assertions.assertThat(klassPage.getTotalElements()).isEqualTo(1);

        logger.info("测试关联实体");
        klass = this.initQueryKlass();
        klass.setTeacher(originKlass.getTeacher());
        klassPage = (Page<Klass>) yunzhiService.page(klassRepository, klass, pageRequest);
        Assertions.assertThat(klassPage.getTotalElements()).isEqualTo(1);

        logger.info("更改关联实体");
        Teacher teacher = teacherService.getOneSavedTeacher();
        klass.setTeacher(teacher);
        klassPage = (Page<Klass>) yunzhiService.page(klassRepository, klass, pageRequest);
        Assertions.assertThat(klassPage.getTotalElements()).isEqualTo(0);

        logger.info("测试二级关联实体Address");
        teacher = new Teacher();
        teacher.setAddress(originKlass.getTeacher().getAddress());
        klass.setTeacher(teacher);
        klassPage = (Page<Klass>) yunzhiService.page(klassRepository, klass, pageRequest);
        Assertions.assertThat(klassPage.getTotalElements()).isEqualTo(1);

        logger.info("更改测试二级关联实体Address");
        teacher.setAddress(addressService.getOneSavedAddress());
        klassPage = (Page<Klass>) yunzhiService.page(klassRepository, klass, pageRequest);
        Assertions.assertThat(klassPage.getTotalElements()).isEqualTo(0);

        logger.info("测试二级关联实体 属性");
        Address address = new Address();
        address.setCity("测试城市");
        teacher.setAddress(address);
        klassPage = (Page<Klass>) yunzhiService.page(klassRepository, klass, pageRequest);
        Assertions.assertThat(klassPage.getTotalElements()).isEqualTo(1);

        logger.info("测试二级关联实体 属性");
        address.setCity("测试城市不存在");
        teacher.setAddress(address);
        klassPage = (Page<Klass>) yunzhiService.page(klassRepository, klass, pageRequest);
        Assertions.assertThat(klassPage.getTotalElements()).isEqualTo(0);

        logger.info("测试@IgnoreQueryParam注解");
        klass.setTeacher(null);
        klass.setIgnoreTeacher(teacher);
        klassPage = (Page<Klass>) yunzhiService.page(klassRepository, klass, pageRequest);
        Assertions.assertThat(klassPage.getTotalElements()).isEqualTo(1);

        logger.info("测试@BeginQueryParam, @EndQueryParam注解在Calendar上在作用");
        klass = this.initQueryKlass();
        teacher = new Teacher();
        klass.setTeacher(teacher);
        Calendar beginCalendar = Calendar.getInstance();
        beginCalendar.setTimeInMillis(originKlass.getTeacher().getCreateTime().getTimeInMillis());
        Calendar endCalendar = Calendar.getInstance();
        endCalendar.setTimeInMillis(originKlass.getTeacher().getCreateTime().getTimeInMillis());
        teacher.setBeginCreateTime(beginCalendar);
        teacher.setEndCreateTime(endCalendar);
        klassPage = (Page<Klass>) yunzhiService.page(klassRepository, klass, pageRequest);
        Assertions.assertThat(klassPage.getTotalElements()).isEqualTo(1);

        logger.info("将范围扩大");
        beginCalendar.add(Calendar.MINUTE, -1);
        endCalendar.add(Calendar.MINUTE, 1);
        klassPage = (Page<Klass>) yunzhiService.page(klassRepository, klass, pageRequest);
        Assertions.assertThat(klassPage.getTotalElements()).isEqualTo(1);

        logger.info("区间后移");
        beginCalendar.add(Calendar.MINUTE, 2);
        endCalendar.add(Calendar.MINUTE, 2);
        klassPage = (Page<Klass>) yunzhiService.page(klassRepository, klass, pageRequest);
        Assertions.assertThat(klassPage.getTotalElements()).isEqualTo(0);

        logger.info("区间前移");
        beginCalendar.add(Calendar.MINUTE, -4);
        endCalendar.add(Calendar.MINUTE, -4);
        klassPage = (Page<Klass>) yunzhiService.page(klassRepository, klass, pageRequest);
        Assertions.assertThat(klassPage.getTotalElements()).isEqualTo(0);

        logger.info("区间调换");
        beginCalendar.add(Calendar.MINUTE, 4);
        klassPage = (Page<Klass>) yunzhiService.page(klassRepository, klass, pageRequest);
        Assertions.assertThat(klassPage.getTotalElements()).isEqualTo(0);

        logger.info("date的区间测试累了，不测了");
    }

    @Test
    public void findAllByEntity() {
        logger.info("上面的分页，已经将该测试的测试完了，这里只是做做样子，防止语法错语");
        Klass originKlass = klassService.getOneSavedKlass();

        Klass klass = this.initQueryKlass();
        List<Klass> klassPage = (List<Klass>) yunzhiService.findAll(klassRepository, klass);
        Assertions.assertThat(klassPage.size()).isEqualTo(1);

        logger.info("测试二级关联实体Address");
        Teacher teacher = new Teacher();
        teacher.setAddress(originKlass.getTeacher().getAddress());
        klass.setTeacher(teacher);
        klassPage = (List<Klass>) yunzhiService.findAll(klassRepository, klass);
        Assertions.assertThat(klassPage.size()).isEqualTo(1);
    }

    /**
     * 获取初始化用于查询的班级
     * @return
     * panjie
     */
    private Klass initQueryKlass() {
        logger.info("加入所有的测试信息，断言返回1条记录");
        Klass klass = new Klass();
        klass.setName("测试班级名称");
        klass.setTotalStudentCount((short) 10);
        klass.setIntegerTest(100);
        klass.setLongTest(1000L);
        return klass;
    }
}

总结

从第一次接触spring-data-jpa的综合查询，再到自己熟练使用，再到学生熟练使用，再到进行其它的项目维护时，见到了单表综合查询，再到今天的多实体综合查询。一步步的证明在学习--实践--学习的路上，我们是对的。该方式也是当前，我们能找到最有效的学习方法。

简单的事情重复做，你就是专家；重复的事情认真做，你就是赢家。

学习的道路如此简单：重复简单的，思索重复的。你就是自己人生的赢家！

最后，感谢我聪明好学的学生们给了我在项目敲定完工日期后，仍然可以静心学习的自信；感谢spring-boot，让我在整个的学习过程中，一次次的提升对面向接口开发思想的认识；感谢来自廊坊的张应亮先生的单表查询代码给了我实现多表关联查询的灵感与动力；感觉廊坊世通科技的李广川总经理对团队的信任及支持。谢谢你们！