Using Hibernate Criteria Builder to query across several joined entities

Question

For a school group project, I am building a small social media site aimed at joining up musicians. Our site backend is built using Spring Boot with Hibernate JPA.

Our site consists of Users, Posts, Tags, and SkillLevels. Every User is in a bidirectional OneToMany relationship with Posts, mapped on the Post side. For every Post, any individual Tag can be associated with that Post once, and a SkillLevel is assigned to that application. Essentially, Posts and Tags are in a ManyToMany relationship, with a SkillLevel applied to each relation. In usage, an example would be a Post with the Tag "violin" applied and the SkillLevel "beginner" associated with that application.

This relationship between Posts, Tags, and SkillLevels is accomplished using a join table called AppliedSkillLevel and is set up following the example in this article with Post corresponding to Student, Tag corresponding to Course, and AppliedSkillLevel corresponding to Rating. The one major difference is that, while they store a rating score directly in the join table, I have SkillLevels as a separate entity, connected to the AppliedSkillLevel by an additional ManyToOne relationship, mapped on the AppliedSkillLevel side. This allows me to associate names with skill levels and to easily refactor relationships.

Now that I have all of that set up, I would like to build a robust search functionality for the site. I would like to be able to do searches such as "find Posts with the Tag 'violin' where the skill level 'intermediate' is applied to the relation between that Post and Tag" or "find Posts with contentType(a field of Post) == video and (the Tag 'violin' where the skill level 'intermediate' is applied to the relation between that Post and Tag or the Tag 'guitar' where the skill level 'master' is applied" or do queries about Users with some combination of local fields and Posts with the types of criteria already described.

For this I thought that using the Hibernate Criteria API and building a structure to do query building would be my best approach. However, I am having trouble figuring out how to do criteria building in a way that ensures that I am getting correct results. Having references to Join and Fetch being used to cross tables, my first thought was to join/fetch up the whole entity graph (or at least all of the parts that are needed for a given search) into one combined From object. However, I found this Stack Overflow Post which indicates that this is not possible for Fetch and you can only fetch either adjacent relations, or simply a maximum of two relations, it is unclear to me. I would assume that the same rule applies to Joins.

So the question becomes, how can I do these queries in a manner that obtains correct results? If I want to, for instance, get Posts with the tag 'violin' and the skill level 'advanced' applied to that post tag combination, how can I do that? I could start with a Root<Post> and join to AppliedSkillLevel, and from there join to Tag and SkillLevel separately, but when I query on them, how do I know that I have a Post with a (violin, advanced) pair, rather than a post with (violin, intermediate) and (guitar, advanced). Similarly, if I am after a User with a Post fitting a certain profile, how do I know that I have found one, rather than having found a User where one Post fits part of the profile and another Post fits the rest of the profile?

Thank you in advanced.

Answer 1

you could always cast a fetch a join like this:

Join <Table1, Table2>  joinTable = (Join <Table1, Table2>) root.fetch(Table1_.table2s);

What would allow you to do fecth, and treat it as a Join,

Although what I would recommend would be to use all Joins (establishing the relationships between entities such as Lazy) and return a Pojo instead of the entity with the necessary information in a way similar to the following:

// assuming these variables as input variables of the method
String tagName = "violin";
String skillName = "advanced"
Long idSkill = null;

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<PostPojo> cq = cb.createQuery(PostPojo.class);
Root<Post> rootPost = cq.from(Post.class);
Join<Post,AppliedSkillLevel> joinAppliedSkillLevel = rootPost.join(Post_.appliedSkillLevels,JoinType.LEFT);
Join<AppliedSkillLevel,Tag> joinTag = joinAppliedSkillLevel.join(AppliedSkillLevel_.tags,JoinType.LEFT);
Join<AppliedStillLevel,SkillLevel> joinSkillLevel =  = joinAppliedSkillLevel.join(AppliedSkillLevel_.skillLevels,JoinType.LEFT);

List<Predicate> listPredicates = new ArrayList<>();
listPredicates.add(cb.equal(joinTag.get(Tag_.name),tagName));
listPredicates.add(cb.equal(joinSkillLevel.get(SkillLevel_.name),skillName));
// the predicates you want, you could set filters programatically like this:
/*
 * if(idSkill != null){
 *     listPredicates.add(cb.equal(joinSkillLevel.get(SkillLevel_.name),idSkill));
 * }
 */
cq.where(listPredicates .toArray(new Predicate[listPredicates .size()]));

cq.multiselect(
    rootPost.get(Post_.id),
    rootPost.get(Post_.title),
    rootPost.get(Post_.content),
    [...] // other fields you want
    joinTag.get(Tag_.id),
    joinTag.get(Tag_.name),
    [...] // other fields you want
    joinSkillLevel.get(SkillLevel_.id),
    joinSkillLevel.get(SkillLevel_.name)
    [...] // other fields you want
);

List<PostPojo> results = entityManager.createQuery(cq).getResultList();

And in your Pojo class PostPojo

and in your PostPojo a constructor that matches in order and type with each and every one of the parameters of the multiselect clause (assuming the types because you didn't put the entities).

public PostPojo(Long idPost, String titlePost, String contentPost, Long idTag, 
            String nameTag, Long idSkill, String nameSkill){
     this.idPost = idPost
     [...]
}

In this way you will obtain a list of PostPojo objects with the information and filters thah you require, and without using fetch and establishing the relationships of the entities as Lazy, you will optimize memory use.