JPA EclipseLink - Get multiple objects by primary key maintaining order

Question

I'm using EclipseLink as JPA implementation and I need to get multiple objects using the primary key (numeric id). But I also need to maintain the given id order.

Using native mySQL this kind of behaviour can be obtained using ORDER BY FIELD

SELECT id FROM table WHERE id IN(9,5,2,6) ORDER BY FIELD(id,9,5,2,6);

I'm now trying to replicate this query using JPA implementation. As already established from this thread, the ORDER BY FIELD is not supported, so I went to a more low-level approach using a JPA native query.

I'm try to reach this goal using a parameter query, instead of using a raw statement. The first implementation was like this

Class<?> clazz = ...;
List<Long> ids = ...;

EntityManagerFactory emf = ...;
EntityManager em = emf.createEntityManager();
String statement = "SELECT * FROM table WHERE id IN (?)";

Query createNativeQuery = em.createNativeQuery(statement, clazz);
createNativeQuery.setParameter(1, ids);
List resultList = createNativeQuery.getResultList();

As you can see the ORDER clause is not there yet, for the first step I just trying to make the parameter query work using the ids list with the IN operator. In the setParameter method I tried to provide the List object, a comma separated list (as string) but none of them works. At the end they all finish with a sql syntax error. I also tried to play with the parenthesis, with or without, but nothing works.

Here some test I made

String statement = "SELECT * FROM " + tableName + " WHERE id IN (?)";
Query createNativeQuery = emJpa.createNativeQuery(statement, this.em.getClassObject());
createNativeQuery.setParameter(1, ids);

The query does not give any error, but no results given.

String statement = "SELECT * FROM " + tableName + " WHERE id IN (?)";
Query createNativeQuery = emJpa.createNativeQuery(statement, this.em.getClassObject());
createNativeQuery.setParameter(1, Joiner.on(",").join(ids));

Only one result is given, but 7 ids was provided to the query

From this topic I also tried using ?1 instead of ?, but no changes. Is there a way to make the nativeQuery working with a list of ids?

For the moment I'm using the full raw SQL statement

String joinedId = Joiner.on(",").join(ids);
String statement = "SELECT * FROM " + tableName + " WHERE id IN (" + joinedId + ") ORDER BY FIELD(id," + joinedId + ")";
Query createNativeQuery = emJpa.createNativeQuery(statement, this.em.getClassObject());
createNativeQuery.getResultList();

But at first I started with the parameter query for optimization and performance related of parsing each time the statement.

EDIT

With the suggestion of Chris I tried a TypedQuery using the FUNCTION operator (which is available because I'm using the latest EclipseLink). Here is the resulting code

List<Long> ids = ...;
Class<?> clazz = ...;
String statement = "SELECT e FROM " + clazz.getSimpleName() + " e WHERE e.id IN (:idList) ORDER BY FUNCTION('FIELD', e.id, :idList)";

EntityManagerFactory emf = ...;
EntityManager em = emf.createEntityManager();

TypedQuery<?> query = em.createQuery(statement, clazz);
query.setParameter("idList", ids);

List resultList = query.getResultList();

And here is the error while executing this code

Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.3.v20160428-59c81c5): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Operand should contain 1 column(s)
Error Code: 1241
Call: SELECT ... all the fields ... FROM webcontent_type WHERE (ID IN ((?,?,?,?,?,?,?))) ORDER BY FIELD(ID, (?,?,?,?,?,?,?))
    bind => [14 parameters bound]
Query: ReadAllQuery(referenceClass=WebContentType sql="SELECT ... all the fields ... FROM webcontent_type WHERE (ID IN (?)) ORDER BY FIELD(ID, ?)")

EDIT 2

Tried without the parenthesis but there is still an error

SELECT e FROM FrameWorkUser e WHERE e.id IN :idList ORDER BY FUNCTION('FIELD', e.id, :idList)

I must say that with a list of one element the code works, but with another list of 10 elements there is an error

javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.3.v20160428-59c81c5): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Operand should contain 1 column(s)
Error Code: 1241
Call: SELECT .... FROM webcontent_type WHERE (ID IN (?,?,?,?,?,?,?)) ORDER BY FIELD(ID, (?,?,?,?,?,?,?))
    bind => [14 parameters bound]
Query: ReadAllQuery(referenceClass=WebContentType sql="SELECT .... FROM webcontent_type WHERE (ID IN ?) ORDER BY FIELD(ID, ?)")
    at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:382)
    at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:260)
    at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:473)

It seems that even w/o the parenthesis, the resulting statement has them

JPA native queries don't support collections, see here http://stackoverflow.com/questions/21484176/how-to-use-a-dynamic-parameter-in-a-in-clause-of-a-jpa-named-query — Guenther, Sep 15 '16 at 11:22
Is there a way to bind it joining the list as string before passing it as parameter? — Deviling Master, Sep 15 '16 at 11:25

Chris · Answer 1 · 2016-09-16T15:38:21.620

1

If you are going to use a native query, you must do it exactly like you would form the SQL for your database - this means you must break the list into its component parameters as JPA providers are not expected to change the SQL for you. Most providers handle lists in JPQL though, so "select e from Entity e where e.id in (:idList)" will work in EclipseLink.

Your missing bit is that 'FIELD' is not a JPQL construct. For this, you would have to use the JPQL 2.1 FUNCTION operator. Something like:

"Select e from Entity e where e.id in :idList order by FUNCTION('FIELD', e.id, :idList)"

edited Sep 16 '16 at 15:38

answered Sep 15 '16 at 18:00

Chris

20,138
2
29
43

I tried the code you suggested (I did not know about the `FUNCTION` clause). You can read the result in the main question, I added at the bottom the new section – Deviling Master Sep 16 '16 at 06:30
My mistake and I've corrected the answer slightly: don't use the () in your JPQL. It should just be "in :idList". You may still have to break up the list for the FUNCTION call though, or look into EclipseLink's calls a bit to fix the issue it has - I don't work with the source as much anymore, so I'd suggest looking through the bugs to see if there is one that might have a workaround. – Chris Sep 16 '16 at 15:38
I removed the parenthesis but there is the same error (see EDIT2 on the main question). As you suggested, the list in the FUNCTION call does not work. Did you find any bug related to this matter? I tried to find something but I got no results – Deviling Master Oct 03 '16 at 14:05
Your second edit shows it is working for the 'in' clause, and the problem with the FUNCTION clause is that it is missing a closing bracket. You can likely fix this in EclipseLink source, but I can't help you there, sorry. – Chris Oct 03 '16 at 16:50
Sorry, the missing closing bracket was an copy-paste error. The unbinded query shows the proper brackets, but the error is still the same. I now updated the EDIT2 section with the proper log – Deviling Master Oct 04 '16 at 07:19
The issue still seems to be with the FIELD list parameter, as these parameters are enclosed in brackets when you want it inline with the id parameter. This will require provider specific changes to make work - you are likely better breaking up the list into separate parameters. Using "SQL('FIELD(?, ?)', e.id, :idList)" instead of the FUNCTION in JPQL might be one other thing to try, but I believe the brackets are printed by the parameter handler. – Chris Oct 04 '16 at 16:27
As expected even using `ORDER BY SQL('FIELD(?,?)', e.id, :idList)` the error is still the same `Operand should contain 1 column(s)` and you can see the brackets as well `ORDER BY FIELD(ID,(?,?,?,?,?,?,?,?,?,?))`. I filled an [EclipseLink issue](https://bugs.eclipse.org/bugs/show_bug.cgi?id=505639). For the moment I'm manually creating the statement – Deviling Master Oct 10 '16 at 12:15

JPA EclipseLink - Get multiple objects by primary key maintaining order

1 Answers1