Criteria API with Unidirectional relationship

Question

I have three entities. I need construct Criteria API where i can select project if unique users will be more than userCount variable.

@Entity
@Table(name = "client")
public class Client {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Getter @Setter private Long id;
}

@Entity
@Table(name = "session")
public class Session {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Getter @Setter private Long id;

    @ManyToOne
    @JoinColumn(name = "client_id", referencedColumnName = "id")
    @Getter @Setter private Client client;

    @ManyToOne
    @JoinColumn(name = "project_id", referencedColumnName = "id")
    @Getter @Setter private Project project;
}

@Entity
@Table(name = "project")
public class Project {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Getter @Setter private Long id;
}

I want select all project, where unique users >= userCount. I construct query in jpql

@Query("select p from Project p where (select distinct count(cli.id) from Client as cli 
join Session sess on sess.client = cli 
join Project as proj on proj = sess.project 
where proj.id = p.id) >= :userCount")

I wrote criteria:

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Project> cq = cb.createQuery(Project.class);
        Root<Project> projectRoot = cq.from(Project.class);

        List<Predicate> predicates = new ArrayList<>();

        Subquery<Long> sub = cq.subquery(Long.class);
        Root<Client> subRoot = sub.from(Client.class);

        Join<Client, Session> sessionClientJoin = subRoot.join("sessions");
        Join<Session, Project> sessionProjectJoin = sessionClientJoin.join("project");

        sub.select(cb.count(subRoot.get("id"))).distinct(true);
        sub.where(cb.equal(projectRoot.get("id"), sessionProjectJoin.get("id")));

        predicates.add(cb.greaterThanOrEqualTo(sub, DefaultParamsHolder.NUMBER_OF_USERS));
        cq.select(projectRoot);
        cq.where(predicates.toArray(new Predicate[0]));
        List<Project> project = em.createQuery(cq).getResultList();

This work fine, but this criteria require sessions in Client class.

@Entity
@Table(name = "client")
public class Client {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Getter @Setter private Long id;

    @OneToMany(mappedBy = "client", fetch = FetchType.LAZY)
    private List<Session> sessions;
}

This is bad for me. I need construct Criteria API without sessions in Client class. I tried this:

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Project> cq = cb.createQuery(Project.class);
        Root<Project> projectRoot = cq.from(Project.class);

        List<Predicate> predicates = new ArrayList<>();

        Subquery<Long> sub = cq.subquery(Long.class);
        Root<Client> subRoot = sub.from(Client.class);

        Join<Session, Client> sessionClientJoin = subRoot.join("client");
        Join<Session, Project> sessionProjectJoin = sessionClientJoin.join("project");

        sub.select(cb.count(subRoot.get("id"))).distinct(true);
        sub.where(cb.equal(projectRoot.get("id"), sessionProjectJoin.get("id")));

        predicates.add(cb.greaterThanOrEqualTo(sub, DefaultParamsHolder.NUMBER_OF_USERS));
        cq.select(projectRoot);
        cq.where(predicates.toArray(new Predicate[0]));
        List<Project> project = em.createQuery(cq).getResultList();

This not work.

java.lang.IllegalArgumentException: Unable to locate Attribute  with the the given name [client] on this ManagedType [com.engage.domain.model.statisctic.Client]

How i can do this without sessions in Client class?

If you have working join - use it in both cases. – Antoniossss Oct 09 '19 at 08:48 — Antoniossss, Oct 09 '19 at 08:48

score 0 · Answer 1 · answered Oct 09 '19 at 08:43

You have

Root<Client> subRoot = sub.from(Client.class);
Join<Session, Client> sessionClientJoin = subRoot.join("client");

This join says "join to Client class entity that is bound to client property". Client does not have property client - and that is what error says.

maybe you wanted to join sessions to the client

   Join<Client, Session> sessionClientJoin = subRoot.join("sessions");

My suggestion is to start using static genearted metamodel. This will guarantee validity of properties and typesafety on compiletime.

https://www.baeldung.com/hibernate-criteria-queries-metamodel

Criteria API with Unidirectional relationship

1 Answers1