JPA-Specification 实现复杂查询

Be_insighted

已于 2022-11-22 21:18:25 修改

阅读量4.7k

点赞数 3

CC 4.0 BY-SA版权

分类专栏： Java 文章标签： JPA Specification 条件查询复杂查询

于 2019-08-20 16:48:21 首次发布

本文链接：https://blog.youkuaiyun.com/Be_insighted/article/details/99854148

Java 专栏收录该内容

102 篇文章

订阅专栏

本文深入探讨了使用Java Persistence API (JPA)进行复杂查询的方法，包括条件查询、排序、分页、虚拟视图创建及多对一和多对多关联查询等。通过具体实例，展示了如何利用JPA的Specification接口构建动态查询条件，实现如模糊查询、范围查询、关联查询等功能。

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

1、条件查询：

condition: 如果为true(默认)，应用此条件查询。
property: 字段名称。
values: 具体查询的值，eq/ne/like 支持多个值。

JPA Specification in lessOrEqualTo 小于等于、in、相等(Oracle)

搜索某天之后数据数据 dayOfMonth

 public Specification<Booking> getBookingSpecification() {
        return (root, query, builder) -> {
            List<Predicate> predicates = new ArrayList<>();
            predicates.add(builder.equal(root.get("status"), ConstantEnum.BookingStatus.WORKING.getCode()));
            predicates.add(builder.equal(root.get("bookingType"), ConstantEnum.BookingType.CYCLE.getCode()));
            Date start = new Date();
            int dayOfMonth = DateUtil.getDay(start);
            int lastDayOfMonth = cipsDateUtil.getLastDayOfDate(start);
            List<Integer> days = new ArrayList<>();
            boolean oneDay = false;
            if (lastDayOfMonth == dayOfMonth && lastDayOfMonth == 28) {
                days = Arrays.asList(28, 29, 30, 31);
            } else if (lastDayOfMonth == dayOfMonth && lastDayOfMonth == 29) {
                days = Arrays.asList(29, 30, 31);
            } else if (lastDayOfMonth == dayOfMonth && lastDayOfMonth == 30) {
                days = Arrays.asList(30, 31);
            } else {
                oneDay = true;
                days = Arrays.asList(dayOfMonth);
            }

            if (oneDay) {
                predicates.add(builder.equal(root.get("dayOfMonth"), dayOfMonth));
            } else {
                predicates.add(root.get("dayOfMonth").in(days));
            }
            String startDate = DateUtil.formatTime(start).substring(0, 10);
            cn.hutool.core.date.DateUtil.parse(startDate, "yyyy-MM-dd");
            predicates.add(builder.lessThanOrEqualTo(root.get("firstRemitDate"), cn.hutool.core.date.DateUtil.parse(startDate, "yyyy-MM-dd")));
            Predicate[] p = new Predicate[predicates.size()];
            return builder.and(predicates.toArray(p));
        };
    }

例子：

注意：基类需继承JpaRepository、JpaSpecificationExecutor ！

2、Equal/NotEqual例子

public interface UserEntity extends JpaRepository<UserEntity, Long>, JpaSpecificationExecutor<UserEntity> {

}   

public Page<UserEntity> findAll(SearchRequest request) {

    Specification<UserEntity> specification = Specifications.<UserEntity>and()

            .eq(StringUtils.isNotBlank(request.getName()), "name", request.getName())

            .gt(Objects.nonNull(request.getAge()), "age", 18)

            .between("birthday", new Date(), new Date())

            .like("nickName", "%og%", "%me")

            .build();

    return userDao.findAll(specification, new PageRequest(0, 15));

}

查询任何昵称等于 "地主"，名字等于 "王保长"、"卢队长"或为null并且公司也为null的人。

public List<UserEntity> findAll(SearchRequest request) {

    Specification<UserEntity> specification = Specifications.<UserEntity>and()
            .eq("nickName", "地主")
            .eq(StringUtils.isNotBlank(request.getName()), "name", "王保长", "卢队长", null)
            .eq("company", null) //or eq("company", (Object) null)
            .build();

    return userDao.findAll(specification);

}

3、In/NotIn例子

查询任何名字等于 "王保长", "卢队长" 并且公司不等于 "Huawei" 或 "XiaoMi" 的人。

public List<UserEntity> findAll(SearchRequest request) {
    Specification<UserEntity> specification = Specifications.<UserEntity>and()
            .in("name", request.getNames().toArray()) //or in("name", "王保长", "卢队长")
            .notIn("company", "Huawei" , "XiaoMi")
            .build();
    return userDao.findAll(specification);
}

4、比较例子（Comparable）

支持任何实现Comparable接口的类的比较，查询任何年纪大于等于16的人。

public List<UserEntity> findAll(SearchRequest request) {

    Specification<UserEntity> specification = Specifications.<UserEntity>and()
            .gt(Objects.nonNull(request.getAge()), "age", 16)
            .lt("birthday", new Date())
            .build();

    return userDao.findAll(specification);
}

5、Between例子

查询任何年龄在16到22，生日在某个时间段的人。

public List<UserEntity> findAll(SearchRequest request) {
    Specification<UserEntity> specification = Specifications.<UserEntity>and()
            .between(Objects.nonNull(request.getAge(), "age", 16, 22)
            .between("birthday", new Date(), new Date())
            .build();

    return userDao.findAll(specification);
}

6、模糊查询：Like/NotLike例子

查询任何名字包含 %保% 或 %队%，公司不包含 %子% 的人。

public Page<UserEntity> findAll(SearchRequest request) {

    Specification<UserEntity> specification = Specifications.<UserEntity>and()
            .like("name", "%保%", "%队%")
            .notLike("company", "%子%")
            .build();

    return userDao.findAll(specification);
}

7、Or例子（支持或查询）

public List< UserEntity > findAll(SearchRequest request) {
    Specification<UserEntity> specification = Specifications.<UserEntity>or()
                    .like("name", "%保%")
                    .gt("age", 19)
                    .build();

    return userDao.findAll(specification);
}

8、关联查询

左连接查询

1、多对一查询，查询任何名字等于 "雷军" 并且此人的电话品牌是 "XiaoMi"的人。

public List<UserEntity> findAll(SearchRequest request) {
    Specification< UserEntity > specification = Specifications.< UserEntity >and()
        .eq(StringUtils.isNotBlank(request.getBrand()), "brand", "XiaoMi")
        .eq(StringUtils.isNotBlank(request.getUserEntityName()), "UserEntity.name", "雷军")
        .build();

    return userDao.findAll(specification);
}

2、多对多查询，查询任何年龄在22到35之间并且其地址在 "BeiJing" 的人。

public List<UserEntity> findAll(SearchRequest request) {
    Specification<UserEntity> specification = Specifications.<UserEntity>and()
        .between("age", 22, 35)
        .eq(StringUtils.isNotBlank(jack.getName()), "addresses.street", "BeiJing")
        .build();

    return userDao.findAll(specification);
}

9、自定义条件查询
自定义条件查询来实现多对一和多对多查询。

1、多对一查询，查询任何名字等于 "雷军" 并且此人的电话品牌是 "XiaoMi"的人。

public List<UserEntity> findAll(SearchRequest request) {

    Specification<UserEntity> specification = Specifications.<UserEntity>and()
        .eq(StringUtils.isNotBlank(request.getBrand()), "brand", "XiaoMi")
        .predicate(StringUtils.isNotBlank(request.getUserEntityName()), (root, query, cb) -> {

            Path<UserEntity> UserEntity = root.get("UserEntity");
            return cb.equal(UserEntity.get("name"), "雷军");
        }).build();

    return userDao.findAll(specification);
}

2、多对多查询，查询任何年龄在22到35之间并且其地址在 "BeiJing" 的人。

public List<UserEntity> findAll(SearchRequest request) {

    Specification<UserEntity> specification = Specifications.<UserEntity>and()
        .between("age", 22, 35)
        .predicate(StringUtils.isNotBlank(jack.getName()), ((root, query, cb) -> {

            Join address = root.join("addresses", JoinType.LEFT);
            return cb.equal(address.get("street"), "BeiJing");
        })).build();

    return userDao.findAll(specification);
}

10、排序Sort

public List<UserEntity> findAll(SearchRequest request) {

    Specification<UserEntity> specification = Specifications.<UserEntity>and()
            .eq(StringUtils.isNotBlank(request.getName()), "name", request.getName())
            .gt("age", 18)
            .between("birthday", new Date(), new Date())
            .like("nickName", "%队%")
            .build();

    Sort sort = Sorts.builder()
        .desc(StringUtils.isNotBlank(request.getName()), "name")
        .asc("birthday")
        .build();

    return UserDao.findAll(specification, sort);
}

11、分页

分页并按照名字倒序生日升序查询。

public Page<UserEntity> findAll(SearchRequest request) {

    Specification<UserEntity> specification = Specifications.<UserEntity>and()
            .eq(StringUtils.isNotBlank(request.getName()), "name", request.getName())
            .gt("age", 18)
            .between("birthday", new Date(), new Date())
            .like("nickName", "%队%")
            .build();

    Sort sort = Sorts.builder()
        .desc(StringUtils.isNotBlank(request.getName()), "name")
        .asc("birthday")
        .build();

    return userDao.findAll(specification, new PageRequest(0, 15, sort));
}

12、虚拟视图

如果你不想使用数据库视图(数据库依赖)，可以 @org.hibernate.annotations.Subselect 虚拟视图代替(灵活修改/提升可读性)。对于 Hibernate 映射来说虚拟视图和数据库视图没任何区别。

@Entity

@Immutable
@Data
@Subselect("SELECT u.id, u.name, u.age, ic.number  FROM UserEntity u LEFT JOIN id_card ic ON u.id_card_id=ic.id")
public class UserEntityIdCard {

    @Id
    private Long id;

    private String name;

    private Integer age;

    private String number;

}   

public List<UserEntityIdCard> findAll(SearchRequest request) {

    Specification<UserEntityIdCard> specification = Specifications.<UserEntityIdCard>and()
            .gt(Objects.nonNull(request.getAge()), "age", 18)
            .build();

    return userEntityIdCardDao.findAll(specification);
}

13、过滤掉空值

取出所有昵称不为空的数据

public static Specification getSpecification(UserEntity user) {

    return (root, query, builder) -> {
        List<Predicate> predicates = new ArrayList<>();
        
        predicates.add(builder.isNotNull(root.get("nickName").as(String.class)));
        Predicate[] p = new Predicate[predicates.size()];
        return builder.and(predicates.toArray(p));
    };
}

@Entity
@Table(name = "user")
@Data
public class UserEntity {
    @Id
    @GenericGenerator(name = "idGenerator", strategy = "uuid")
    @GeneratedValue(generator = "idGenerator")
    @Column(length = 64)
    String id;

    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @CreatedDate
    Date createTime;

    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @LastModifiedDate
    Date updateTime;

    boolean del;
    
    private Integer age;
    private String name;
    @Column(name = "nick_name")
    private String nickName;
    private String company;
    private Date birthday;
    @OneToOne(cascade = ALL)
    @JoinColumn(name = "id_card_id")
    private IdCard idCard;
    @OneToMany(cascade = ALL)
    private Set<Phone> phones = new HashSet<Phone>();
    @ManyToMany(cascade = ALL, fetch = FetchType.LAZY)
    private Set<Address> addresses = new HashSet<Address>();

}

@Entity
@Table(name = "address")
@Data
public class AddressEntity {   
    @Id
    @GenericGenerator(name = "idGenerator", strategy = "uuid")
    @GeneratedValue(generator = "idGenerator")
    @Column(length = 64)
    String id;

    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @CreatedDate
    Date createTime;

    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @LastModifiedDate
    Date updateTime;

    boolean del;
    
    @Column(name = "street")
    private String street;
    private Integer number;

}

说明：关于SearchRequest类，字段属性为搜索条件对应的字段

投射、分组和聚合

Spring Data JPA对投射、分组和聚合支持不是很好，此外，投射、分组和聚合大多数用在比较复杂的统计报表或性能要求比较高的查询，如果使用 Hibernate/JPA 来对象关系映射来解决可能有点过于复杂了。或者，使用虚拟视图并给一个易读的、有意义的类名来解决特定的问题也许是一个不错的选择。