I have some problem with Specification construction.
I have two entities:
Candidate:
@Entity
@Table(name = "candidates")
public class Candidate extends BaseEntity {
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "category_id", insertable = false, updatable = false)
@JsonIgnore
private Category category;
@Column(name = "category_id")
private Long categoryId;
…
}
and Category:
@Entity
@Table(name = "categories")
public class Category extends BaseEntity {
@OneToMany(mappedBy = "category")
@JoinColumn(name = "category_id")
@JsonBackReference(value = "categories-candidates")
private List<Candidate> candidates = new ArrayList<>();
…
}
Some Candidate
has null in category
field. And I need to sort by Category's name
field, not by some Candidate's fields. At the same time, I have where-condition by Candidate's field uploadId
.
I need to create Specification to equivalense of SQL (I checked this request - this is exactly what I need):
SELECT * FROM candidates
LEFT JOIN categories ON candidates.category_id = categories.id
WHERE candidates.upload_id = 1
ORDER BY categories."name"
I tried to do that:
public static Specification<Candidate> candidatesByUploadId(final long uploadId) {
return ((root, criteriaQuery, criteriaBuilder) -> {
Join<Candidate, Category> join = root.join("category", JoinType.LEFT);
criteriaQuery.orderBy(criteriaBuilder.desc(join.get("name")));
return criteriaBuilder.equal(join.get("uploadId"), uploadId);
});
}
But I can't:
- There is no "uploadId" field in join, 'cause this is join left join for Category-to-Candidate, not vica versa
- I need all Candidate records, even with null Category, so I can't use inner join
- I can't use
JoinType.RIGHT
- it doesn't supported and I got the Exception - I tryed to change relation owner in entities, but it didn't help me
- I shouldn't use
@Query
things, I need to do it intoSpecification<>
- If I write
return criteriaBuilder.equal(root.get("uploadId"), uploadId);
withroot
insteadjoin
, I haven't join results
How I can get this?
CodePudding user response:
You can use Join.getOn()
that transforms Join
to Predicate
.
public static Specification<Candidate> candidatesByUploadId(final long uploadId) {
return ((root, criteriaQuery, criteriaBuilder) -> {
Join<Candidate, Category> join = root.join("category", JoinType.LEFT);
criteriaQuery.orderBy(criteriaBuilder.desc(join.get("name")));
return criteriaBuilder.equal(root.get("uploadId"), uploadId)
// here it is
.and(join.getOn());
});
}
CodePudding user response:
First thing is first
You need to specify one column as updatable = false
& insertable = false
, as you are specifying two properties in Candidate
entity (category
& categoryId
). So your Candidate
entity will look like following
@Entity
@Table(name = "candidates")
public class Candidate extends BaseEntity {
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "category_id", insertable = false, updatable = false)
@JsonIgnore
private Category category;
@Column(name = "category_id", updatable = false, insertable = false)
private Long categoryId;
…
}
Then your specification will look like following:
public class CandidateSpecification {
// Specification for upload id clause
public static Specification<Candidate> candidatesByUploadId(final long uploadId) {
if(uploadId == null) return null;
return ((root, query, builder) -> {
return builder.equal(root.get(Candidate_.uploadId), uploadId);
});
}
// Specification for left join category.
public static Specification<Candidate> joinCategory() {
return (root, query, builder) -> {
root.join(Candidate_.category, JoinType.LEFT);
return builder.conjuction();
}
}
...
}
Now the query part
public List<Candidate> getCandidatesByUploadIdSortedByCategoryName(Long uploadId) {
Specification<Candidate> specification = Specification.where(CandidateSpecification.candidatesByUploadId(uploadId))
.and(CandidateSpecification.joinCategory);
return candidateRepository.findAll(specification, Sort.by("category.name"));
}