Java Spring Data JPA：创建规范查询获取连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29348742/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Spring Data JPA: Creating Specification Query Fetch Joins
提问by woemler
TL;DR: How do you replicate JPQL Join-Fetch operations using specifications in Spring Data JPA?
TL;DR：您如何使用 Spring Data JPA 中的规范复制 JPQL Join-Fetch 操作？
I am trying to build a class that will handle dynamic query building for JPA entities using Spring Data JPA. To do this, I am defining a number of methods that create Predicateobjects (such as is suggested in the Spring Data JPA docsand elsewhere), and then chaining them when the appropriate query parameter is submitted. Some of my entities have one-to-many relationships with other entities that help describe them, which are eagerly fetched when queried and coalesced into collections or maps for DTO creation. A simplified example:
我正在尝试构建一个类，该类将使用 Spring Data JPA 处理 JPA 实体的动态查询构建。为此，我定义了许多创建Predicate对象的方法（例如Spring Data JPA 文档和其他地方的建议），然后在提交适当的查询参数时将它们链接起来。我的一些实体与有助于描述它们的其他实体具有一对多关系，当查询并合并到集合或映射中以创建 DTO 时，这些实体会被急切地获取。一个简化的例子：
@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...
}
Query string parameters are passed from the Controllerclass to the Serviceclass as key-value pairs, where they are processed and assembled into Predicates:
查询字符串参数作为键值对从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...
}
In this example, every time I want to retrieve a Generecord, I also want its associated GeneAttributeand GeneSymbolAliasrecords. This all works as expected, and a request for a single Genewill fire off 3 queries: one each to the Gene, GeneAttribute, and GeneSymbolAliastables.
在这个例子中，每次我想要检索一条Gene记录时，我也想要它的关联GeneAttribute和GeneSymbolAlias记录。这一切都按预期工作，并为一个单一的请求Gene将触发关闭3个查询：每一个到Gene，GeneAttribute和GeneSymbolAlias表。
The problem is that there is no reason that 3 queries need to run to get a single Geneentity with embedded attributes and aliases. This can be done in plain SQL, and it can be done with a JPQL query in my Spring Data JPA repository:
问题是没有理由需要运行 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);
How can I replicate this fetching strategy using Specifications? I found this question here, but it only seems to make lazy fetches into eager fetches.
回答by DonCziken
Specification class:
规格等级：
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);
}
}
Usage:
用法：
List<Gene> list = GeneRepository.findAll(new MatchAllWithSymbol("Symbol"));
回答by suraj bahl
You can specify the join fetch while creating Specification but since the same specification will be used by pageable methods also like findAll(Specification var1, Pageable var2) and count query will complain because of join fetch. Therefore, to handle that we can check the resultType of CriteriaQuery and apply join only if it is not Long (result type for count query). see below code:
您可以在创建规范时指定连接提取，但由于可分页方法也将使用相同的规范，例如 findAll(Specification var1, Pageable 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);
};
}
回答by kafkas
I suggest this library for specification. https://github.com/tkaczmarzyk/specification-arg-resolver
我建议使用这个库进行规范。 https://github.com/tkaczmarzyk/specification-arg-resolver
From this library : https://github.com/tkaczmarzyk/specification-arg-resolver#join-fetch
You can use @JoinFetch annotation to specify paths to perform fetch join on. For example:
来自这个库：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);
}
