首页 文章

Spring Data JPA:创建规范查询获取联接

提问于
浏览
23

TL;DR :如何使用Spring Data JPA中的规范复制JPQL Join-Fetch操作?

我正在尝试构建一个类,它将使用Spring Data JPA处理JPA实体的动态查询构建 . 为此,我定义了许多创建 Predicate 对象的方法(例如Spring Data JPA docs和其他地方建议的),然后在提交适当的查询参数时将它们链接起来 . 我的一些实体与帮助描述它们的其他实体具有一对多的关系,这些实体在查询时被急切地获取并且合并到用于DTO创建的集合或 Map 中 . 一个简化的例子:

@Entity
public class Gene {

    @Id 
    @Column(name="entrez_gene_id")
    privateLong id;

    @Column(name="gene_symbol")
    private String symbol;

    @Column(name="species")
    private String species;

    @OneToMany(mappedBy="gene", fetch=FetchType.EAGER) 
    private Set<GeneSymbolAlias> aliases;

    @OneToMany(mappedBy="gene", fetch=FetchType.EAGER) 
    private Set<GeneAttributes> attributes;

    // etc...

}

@Entity
public class GeneSymbolAlias {

    @Id 
    @Column(name = "alias_id")
    private Long id;

    @Column(name="gene_symbol")
    private String symbol;

    @ManyToOne(fetch=FetchType.LAZY) 
    @JoinColumn(name="entrez_gene_id")
    private Gene gene;

    // etc...

}

查询字符串参数作为键值对从 Controller 类传递到 Service 类,在那里它们被处理并组装成 Predicates

@Service
public class GeneService {

    @Autowired private GeneRepository repository;
    @Autowired private GeneSpecificationBuilder builder;

    public List<Gene> findGenes(Map<String,Object> params){
        return repository.findAll(builder.getSpecifications(params));
    }

    //etc...

}

@Component
public class GeneSpecificationBuilder {

    public Specifications<Gene> getSpecifications(Map<String,Object> params){
        Specifications<Gene> = null;
        for (Map.Entry param: params.entrySet()){
            Specification<Gene> specification = null;
            if (param.getKey().equals("symbol")){
                specification = symbolEquals((String) param.getValue());
            } else if (param.getKey().equals("species")){
                specification = speciesEquals((String) param.getValue());
            } //etc
            if (specification != null){
               if (specifications == null){
                   specifications = Specifications.where(specification);
               } else {
                   specifications.and(specification);
               }
            }
        } 
        return specifications;
    }

    private Specification<Gene> symbolEquals(String symbol){
        return new Specification<Gene>(){
            @Override public Predicate toPredicate(Root<Gene> root, CriteriaQuery<?> query, CriteriaBuilder builder){
                return builder.equal(root.get("symbol"), symbol);
            }
        };
    }

    // etc...

}

在此示例中,每次我想要检索 Gene 记录时,我还想要其关联的 GeneAttributeGeneSymbolAlias 记录 . 这一切都按预期工作,并且单个 Gene 的请求将触发3个查询:每个查询一个 GeneGeneAttributeGeneSymbolAlias .

问题是没有理由需要运行3个查询来获得具有嵌入属性和别名的单个 Gene 实体 . 这可以在纯SQL中完成,可以在我的Spring Data JPA存储库中使用JPQL查询来完成:

@Query(value = "select g from Gene g left join fetch g.attributes join fetch g.aliases where g.symbol = ?1 order by g.entrezGeneId")
List<Gene> findBySymbol(String symbol);

如何使用规范复制此提取策略?我发现this question here,但它似乎只是让懒惰的提取成为渴望的提取 .

3 回答

  • 19

    规格等级:

    public class MatchAllWithSymbol extends Specification<Gene> {
        private String symbol;
    
        public CustomSpec (String symbol) {
        this.symbol = symbol;
        }
    
        @Override
        public Predicate toPredicate(Root<Gene> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
    
            //This part allow to use this specification in pageable queries
            //but you must be aware that the results will be paged in   
            //application memory!
            Class clazz = query.getResultType();
            if (clazz.equals(Long.class) || clazz.equals(long.class))
                return null;
    
            //building the desired query
            root.fetch("aliases", JoinType.LEFT);
            root.fetch("attributes", JoinType.LEFT);
            query.distinct(true);        
            query.orderBy(cb.asc(root.get("entrezGeneId")));
            return cb.equal(root.get("symbol"), symbol);
        }
    }
    

    用法:

    List<Gene> list = GeneRepository.findAll(new MatchAllWithSymbol("Symbol"));
    
  • 2

    您可以在创建规范时指定连接提取,但由于同一规范将由可分页方法使用,例如findAll(规范var1,可分页var2),并且计数查询将因连接提取而抱怨 . 因此,为了处理我们可以检查CriteriaQuery的resultType并仅在它不是Long时应用连接(计数查询的结果类型) . 见下面的代码:

    public static Specification<Item> findByCustomer(Customer customer) {
        return (root, criteriaQuery, criteriaBuilder) -> {
            /*
                Join fetch should be applied only for query to fetch the "data", not for "count" query to do pagination.
                Handled this by checking the criteriaQuery.getResultType(), if it's long that means query is
                for count so not appending join fetch else append it.
             */
            if (Long.class != criteriaQuery.getResultType()) {
                root.fetch(Person_.itemInfo.getName(), JoinType.LEFT);
            }
            return criteriaBuilder.equal(root.get(Person_.customer), customer);
        };
    }
    
  • 4

    我建议这个库用于规范 . https://github.com/tkaczmarzyk/specification-arg-resolver

    从这个图书馆:https://github.com/tkaczmarzyk/specification-arg-resolver#join-fetch

    您可以使用@JoinFetch批注指定执行获取连接的路径 . 例如:

    @RequestMapping("/customers")
    public Object findByOrderedOrFavouriteItem(
            @Joins({
                @Join(path = "orders", alias = "o")
                @Join(path = "favourites", alias = "f")
            })
            @Or({
                @Spec(path="o.itemName", params="item", spec=Like.class),
                @Spec(path="f.itemName", params="item", spec=Like.class)}) Specification<Customer> customersByItem) {
    
        return customerRepo.findAll(customersByItem);
    }
    

相关问题