How to set a Collection/List to a named parameter of a JPA criteria query?

Question

A single named parameter can be set to a JPA criteria query something like the following. The parameter is of the type Long in this case.

public StateTable find(Long id)
{
    CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
    CriteriaQuery<StateTable> criteriaQuery = criteriaBuilder.createQuery(StateTable.class);
    Metamodel metamodel=entityManager.getMetamodel();
    EntityType<StateTable> entityType = metamodel.entity(StateTable.class);
    Root<StateTable> root = criteriaQuery.from(entityType);

    ParameterExpression<Long> parameterExpression=criteriaBuilder.parameter(Long.class);
    criteriaQuery.where(criteriaBuilder.equal(root.get(StateTable_.stateId), parameterExpression));

    TypedQuery<StateTable> typedQuery = entityManager.createQuery(criteriaQuery);
    return typedQuery.setParameter(parameterExpression, id).getSingleResult();
}

This query inside the method returns a single object of the StateTable (just say state) entity which I'm dealing with and corresponds to the following JPQL query.

entityManager.createQuery("select s from StateTable s where s.stateId=:id")
             .setParameter("id", id)
             .getSingleResult();

I need to find more than one row that corresponds to a list of ids supplied via java.util.List<Long>. The following is the incomplete version of the criteria query.

public List<StateTable> find(List<Long> ids)
{
    CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
    CriteriaQuery<StateTable> criteriaQuery=criteriaBuilder.createQuery(StateTable.class);
    Metamodel metamodel=entityManager.getMetamodel();
    EntityType<StateTable> entityType = metamodel.entity(StateTable.class);
    Root<StateTable> root = criteriaQuery.from(entityType);

    ParameterExpression<Long> parameterExpression = criteriaBuilder.parameter(Long.class);
    criteriaQuery.where(criteriaBuilder.in(root.get(StateTable_.stateId)).value(parameterExpression));

    TypedQuery<StateTable> typedQuery = entityManager.createQuery(criteriaQuery);
    return typedQuery.setParameter(parameterExpression, 1L).getResultList();
}

It uses an in() query but I made it return only a single row, since I don't know whether it is possible to set a list of ids to ParameterExpression or not.

In short, this criteria query should correspond to the following JPQL query.

entityManager.createQuery("from StateTable where stateId in(:id)")
             .setParameter("id", ids)
             .getResultList();

Is there a way to set a List<Long> to ParameterExpression as specified?

score 3 · Accepted Answer · answered Jun 14 '13 at 06:14

The following approach worked for me.

public List<StateTable> find(List<Long> ids)
{
    CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
    CriteriaQuery<StateTable> criteriaQuery=criteriaBuilder.createQuery(StateTable.class);
    Metamodel metamodel=entityManager.getMetamodel();
    EntityType<StateTable> entityType = metamodel.entity(StateTable.class);
    Root<StateTable> root = criteriaQuery.from(entityType);

    //ParameterExpression<Long> parameterExpression = criteriaBuilder.parameter(Long.class);
    //criteriaQuery.where(criteriaBuilder.in(root.get(StateTable_.stateId)).value(parameterExpression));
    criteriaQuery.where(root.get(StateTable_.stateId).in(ids));

    TypedQuery<StateTable> typedQuery = entityManager.createQuery(criteriaQuery);
    return typedQuery.getResultList();
}

I just added the following line.

criteriaQuery.where(root.get(StateTable_.stateId).in(ids));

removing the above commented lines from the incomplete version of the query in the question.

This does mean server-side query caching is negatively impacted. I wonder if there is a way to accomplish this while using a ParameterExpression? — Jannik Jochem, Feb 27 '15 at 10:47
Until now, I saw only this even with JPA 2.1 criteria bulk update and delete. — Tiny, Feb 27 '15 at 10:54
I don't think this is array bind. If you check the actual SQL, you will see the params are still set one by one, like `in (?, ?, ?)`, not as a collection(`in (?)`), so downvote. What I see possible is Hibernate's `setParameterList()`, but is not part of JPA. — WesternGun, Nov 11 '21 at 19:31

score 3 · Answer 2 · edited May 23 '17 at 12:01

3

I was recently investigating the same thing and found a solution that shouldn't impact server-side query caching. Using a ParameterExpression as part of the In clause

Please note that this should have been a response to a comment from Jannik Jochem under this page's answer; however, I am few rep short for that, so feel free to kill this post and add a comment if you have enough rep.

edited May 23 '17 at 12:01

Community

1
1

answered Jul 01 '15 at 11:55

Arides

111
7

How to set a Collection/List to a named parameter of a JPA criteria query?

2 Answers2