CODEWITHSUNDEEP
javahibernatejpacriteria
Guilherme Mussi
Guilherme Mussi

Reputation: 1057

Hibernate + JPA Criteria API not setting parameters in Many-To-Many relationship query?

I have a Many-To-Many relationship between Schedule and Agent (via the property assignedAgents).

I want to find Schedules that contain ANY of the agents I supply. I am trying to do this:

List<Agent> agentsToMatch = // ... I want schedules with any of these agents

CriteriaBuilder cb = session.getCriteriaBuilder();
CriteriaQuery<Schedule> query = cb.createQuery(Schedule.class);
Root<Schedule> schedule = query.from(Schedule.class);
query.where(schedule.get(Schedule_.assignedAgents).in( agentsToMatch ));

Using Hibernate 5.2.7, I get the following query when supplying 2 agents in the array:

select generatedAlias0 from Schedule as generatedAlias0 where generatedAlias0.assignedAgents in (:param0, :param1)

However, the parameters seem not be set, as I get the following exception:

WARN [qtp1782580546-44] o.h.engine.jdbc.spi.SqlExceptionHelper:129 - SQL Error: 0, SQLState: 22023
ERROR [qtp1782580546-44] o.h.engine.jdbc.spi.SqlExceptionHelper:131 - No value specified for parameter 1.
javax.persistence.PersistenceException: org.hibernate.exception.DataException: could not extract ResultSet
  at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:147)
  at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:155)
  at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1419)
  at org.hibernate.Query.getResultList(Query.java:427)
  at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:72)

Any ideas where I might be going wrong?

Upvotes: 0

Views: 1044

Answers (2)

Guilherme Mussi
Guilherme Mussi

Reputation: 1057

I fixed the problem by replacing .get with .join: Before:

query.where(schedule.get(Schedule_.assignedAgents).in( agentsToMatch ));

After:

query.where(schedule.join(Schedule_.assignedAgents).in( agentsToMatch ));

Upvotes: 1

fg78nc
fg78nc

Reputation: 5232

Correct way should be

query.where(schedule.get(Schedule_.assignedAgents)
.in(cb.parameter(List.class, "agentsToMatch" );

Then you supply parameter to your query

query.setParameter("agentsToMatch", agentsToMatch);

Upvotes: 0

Related Questions