JPA-Specification 实现复杂查询

  • 1、条件查询:
  1. condition: 如果为true(默认),应用此条件查询。

  2. property: 字段名称。

  3. values: 具体查询的值,eq/ne/like 支持多个值。

例子:

注意:基类需继承JpaRepository、JpaSpecificationExecutor !

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));

}

 

  • 2、Equal/NotEqual例子

 

查询任何昵称等于 "地主",名字等于 "王保长"、"卢队长"或为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例子

       查询任何年龄在1622,生日在某个时间段的人。

 

 

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、多对多查询,查询任何年龄在2235之间并且其地址在 "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、多对多查询,查询任何年龄在2235之间并且其地址在 "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 来对象关系映射来解决可能有点过于复杂了。或者,使用虚拟视图并给一个易读的、有意义的类名来解决特定的问题也许是一个不错的选择。

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页