JPA / Hibernate: CriteriaBuilder - How to create query using relationship object?
Using the canonical Metamodel and a couple of joins, it should work. Try if you get some hints from the following pseudo-code (not tested):
...
Predicate predicate = cb.disjunction();
if (usersList != null) {
ListJoin<ScheduleRequest, Application> applications = scheduleRequest.join(ScheduleRequest_.applications);
ListJoin<Application, UserApplication> userApplications = applications.join(Application_.userApplications);
Join<UserApplication, User> user = userApplications.join(UserApplication_.userId);
for (String userName : usersList) {
predicate = builder.or(predicate, builder.equal(user.get(User_.name), userName));
}
}
criteria.where(predicate);
...
In order to understand Criteria Queries, have a look at these tutorials: http://www.ibm.com/developerworks/java/library/j-typesafejpa/ http://docs.oracle.com/javaee/6/tutorial/doc/gjitv.html
The second link should also guide you on how to use Metamodel classes, that should be built automatically by the compiler / IDE.
Jemru
Updated on September 07, 2020Comments
-
Jemru over 3 years
I have the following four tables:
SCHEDULE_REQUEST TABLE: ID, APPLICATION_ID (FK)
APPLICATION TABLE: ID, CODE
USER_APPLICATION TABLE: APPLICATION_ID (FK), USER_ID (FK)
USER TABLE: ID, NAME
Now I wanted to create a
CriteriaBuilder
where condition is to selectScheduleRequests
for specified user Ids.I have the following codes:
List<User> usersList = getSelectedUsers(); // userList contains users I wanted to select CriteriaBuilder builder = getJpaTemplate().getEntityManagerFactory().getCriteriaBuilder(); CriteriaQuery<ScheduleRequest> criteria = builder.createQuery(ScheduleRequest.class); Root<ScheduleRequest> scheduleRequest = criteria.from(ScheduleRequest.class); criteria = criteria.select(scheduleRequest); ParameterExpression<User> usersIdsParam = null; if (usersList != null) { usersIdsParam = builder.parameter(User.class); params.add(builder.equal(scheduleRequest.get("application.userApplications.user"), usersIdsParam)); } criteria = criteria.where(params.toArray(new Predicate[0])); TypedQuery<ScheduleRequest> query = getJpaTemplate().getEntityManagerFactory().createEntityManager().createQuery(criteria); // Compile Time Error here: // The method setParameter(Parameter<T>, T) in the type TypedQuery<ScheduleRequest> is not // applicable for the arguments (ParameterExpression<User>, List<User>) query.setParameter(usersIdsParam, usersList); return query.getResultList();
Can you please help me how to pass query filter to a relationship object? I think what I did in "application.userApplications.user" is wrong? Please really need help.
Thank you in advance!
-
Jemru over 12 yearsHi perissf. Thank for the helping. But I don't have classes "ScheduleRequest_", "UserApplication_", "User_". How do i create them? And also in your pseudo-code, you didn't include "Application" table? The relationship table is USER_APPLICATION table, relating USER and APPLICATION tables. Please kindly advise. Really appreciate it. Thanks.
-
Jemru over 12 yearsI saw some tutorial here but I'm not sure how to do apply it in my case. altuure.com/2010/09/23/…
-
perissf over 12 yearsI have updated my answer by adding some references and by adding the third join as per your information