47

I have a list of IDs in a String, and want to use Hibernate to get the rows with these IDs. TrackedItem is a Hibernate/JPA entity (sorry if I'm getting the naming mixed up here).

My code is:

String idsText = "380, 382, 386";
ArrayList<Long> ids = new ArrayList<Long>();

for (String i : idsText.split(","))
{
    ids.add(Long.getLong(i));
}

List<TrackedItem> items = TrackedItem.find("id IN (?)", ids).fetch();

But that fails: JPAQueryException occured : Error while executing query from models.TrackedItem where id IN (?): java.util.ArrayList cannot be cast to java.lang.Long

How can I make the IN part work? Thanks.

Pascal Thivent
  • 562,542
  • 136
  • 1,062
  • 1,124
Amy B
  • 17,874
  • 12
  • 64
  • 83
  • `Long.getLong(i)` doesn't do what you think it does. Use `Long.parseLong(i)` instead. See http://stackoverflow.com/questions/7376857/long-getlong-failing-returning-null-to-valid-string – Natix Mar 12 '15 at 17:06

3 Answers3

80

The syntax of your JPQL query is incorrect. Either use (with a positional parameter):

List<Long> ids = Arrays.asList(380L, 382L, 386L);
Query query = em.createQuery("FROM TrackedItem item WHERE item.id IN (?1)");
query.setParameterList(1, ids)
List<TrackedItem> items = query.getResultList();

Or (with a named parameter):

List<Long> ids = Arrays.asList(380L, 382L, 386L);
Query query = em.createQuery("FROM TrackedItem item WHERE item.id IN :ids");
query.setParameterList("ids", ids)
List<TrackedItem> items = query.getResultList();

Below, the relevant sections of the JPA 1.0 specification about parameters:

4.6.4.1 Positional Parameters

The following rules apply to positional parameters.

  • Input parameters are designated by the question mark (?) prefix followed by an integer. For example: ?1.
  • Input parameters are numbered starting from 1.
    Note that the same parameter can be used more than once in the query string and that the ordering of the use of parameters within the query string need not conform to the order of the positional parameters.

4.6.4.2 Named Parameters

A named parameter is an identifier that is prefixed by the ":" symbol. It follows the rules for identifiers defined in Section 4.4.1. Named parameters are case sensitive.

Example:

SELECT c
FROM Customer c
WHERE c.status = :stat

Section 3.6.1 describes the API for the binding of named query parameters

themenace
  • 2,601
  • 2
  • 20
  • 33
Pascal Thivent
  • 562,542
  • 136
  • 1,062
  • 1,124
12

If you're unlucky enough to be using older non-JPA hibernate, this should work for you:

Query query = session.createQuery("FROM TrackedItem item WHERE item.id IN (:items)");
query.setParameterList("items", Arrays.asList(380L, 382L, 386L));

@SuppressWarnings("unchecked")
List<TrackedItem> results = query.list();
depsypher
  • 1,084
  • 11
  • 20
  • Unlucky? Older? o_O Bare Hibernate has more features compared to JPA (e.g. see answer by Aleksandar Radulović). Also it seems like your answer is a duplicate to the accepted answer.. – Stanislav Bashkyrtsev Sep 06 '20 at 08:27
  • At the time I wrote this bare hibernate required xml mappings versus annotations. Also, the accepted answer was edited to match what my answer says. A lot can change over the years. – depsypher Sep 07 '20 at 15:02
  • Bare Hibernate had annotations years before your answer :) But this doesn't contradicts to the answer (only to your last comment). I just don't see how XML mapping can be considered "unlucky way" as it's more flexible and cleaner. BTW, the accepted answer wasn't changed much since its first version - the name of the method was wrong, but that deserved a comment. – Stanislav Bashkyrtsev Sep 07 '20 at 16:10
  • Well the main reason I added this answer was to correct the bad method name, but even though that's fixed in the accepted answer still I think it's still useful to show how to get this list using the hibernate api. Maybe marginally so, but query.getResultList() versus query.list() is still helpful. If that's changed since 2015 I'll go ahead and delete my answer. – depsypher Sep 07 '20 at 23:16
1

Even when your query executes correctly, you may face an error if your query parameter contains too many values.

One possible solution to this problem, if you are using Hibernate 5.1 or newer, is to use Session.byMultipleIds().

session
    .byMultipleIds(TrackedItem.class)
    .multiLoad(1L, 2L, 3L);

For more information, see https://thoughts-on-java.org/fetch-multiple-entities-id-hibernate/

Aleksandar Radulović
  • 330
  • 3
  • 8