JPQL collection parameter - mismatch the expected type

Question

A bit of context: I have a Spring app with Hibernate.

I want to get all Location entities filtered by ID so I pass a set of IDs as parameter to the query. The problem is that on the query.setParameter("ids", locationIds); row I get the following error:

:Parameter value element [728331] did not match expected type [java.lang.Long (n/a)]

I am confused since the set I am giving is set of Long values. So I assume no explicit casting should be done when passing it as parameter, right? Does anyone has suggestion what is causing the error?

I checked other similar questions but I didn't find one that solve my issue.

@Repository
@Transactional(propagation = Propagation.MANDATORY)
public class LocationDao {

    @PersistenceContext
    private EntityManager em;

    public List<Location> getLocationsByIds(Set<Long> locationIds) {
        if (locationIds == null || locationIds.isEmpty()) {
            return null;
        }
        final TypedQuery<Location> query =
                em.createQuery("FROM Location l WHERE l.id IN :ids", Location.class);
        query.setParameter("ids", locationIds);
        return query.getResultList();
    }
}

@Entity
@Table(name = "location")
public class Location {

    @Id
    private Long id;

    // other fields
}

EDIT: Hibernate entity manager version: 4.3.8.Final

It does not help. I updated the question to add the Hibernate version. Here is why I use it without the parenthesis http://stackoverflow.com/a/21341663/776546 — nyxz, Jul 01 '15 at 10:53
Please try : query.setParameter("ids", locationIds.toArray()); — ooozguuur, Jul 01 '15 at 11:30
Where from `locationIds` is coming? Iterate over it and check that all elements are `Long`-s. — Aleksandr M, Jul 01 '15 at 11:39

score 2 · Answer 1 · answered Jul 01 '15 at 12:31

Found the problem. The locationIds are not exactly Set<Long> locationIds but Set<BigInteger>.

I retrieve the IDs through a native query since I need to perform recursive search in locations. Although I cast it to List<Long> it is actually returns a List<BigInteger>. Here is the code:

private static final String SQL_FIND_LOCATION_AND_CHILDREN_IDS =
        " WITH RECURSIVE result_table(id) AS ( "
                + "  SELECT  pl.id "
                + "  FROM location AS pl "
                + "  WHERE pl.id = :parentId "
                + "UNION ALL "
                + "  SELECT c.id "
                + "  FROM result_table AS p, location AS c "
                + "  WHERE c.parent = p.id "
                + ") "
                + "SELECT n.id FROM result_table AS n";

@SuppressWarnings("unchecked")
public List<Long> getLocationAndAllChildren(Long parentId) {
    final Query query = em.createNativeQuery(SQL_FIND_LOCATION_AND_CHILDREN_IDS);
    query.setParameter("parentId", parentId);

    return query.getResultList();
}

Then I can just take the long value of the BigInteger since I am sure the values fit in Long's size.

@SuppressWarnings("unchecked")
public List<Long> getLocationAndAllChildren(Long parentId) {
    final Query query = em.createNativeQuery(SQL_FIND_LOCATION_AND_CHILDREN_IDS);
    query.setParameter("parentId", parentId);

    final List<BigInteger> resultList = query.getResultList();
    final List<Long> result = new ArrayList<Long>();
    for (BigInteger bigIntId : resultList) {
        result.add(bigIntId.longValue());
    }
    return result;
}

Thanks to all for replying and sorry for wasting your time.

JPQL collection parameter - mismatch the expected type

1 Answers1