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?

Answer 1

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.

Answer 2

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.