Kohei Nozaki's blog 

Using JPQL IN clause with composite key


Posted on Sunday Oct 25, 2015 at 08:16PM in JPA


Assume we have an entity named Employee:

@Entity
public class Employee implements Serializable {
    @Id
    @GeneratedValue
    private Long id;
    @Embedded
    private EmployeeName employeeName;

    // accessor omitted

And EmployeeName:

@Embeddable
public class EmployeeName implements Serializable {
    private String firstName;
    private String lastName;

    // accessor omitted

Using preceding entity, We want to execute following JPQL:

SELECT e FROM Employee e WHERE e.employeeName IN :employeeNames

Will it work? It works for Hibernate 4.3.11.Final but unfortunately not for EclipseLink 2.6.1.

Hibernate generates following SQL for the JPQL and the parameter of a List contains two elements:

Hibernate: select employee0_.id as id1_0_, employee0_.firstName as firstNam2_0_, employee0_.lastName as lastName3_0_ from Employee employee0_ where employee0_.firstName=? and employee0_.lastName=? or employee0_.firstName=? and employee0_.lastName=?
[Employee{id=1, employeeName=EmployeeName{firstName='Scott', lastName='Vogel'}}, Employee{id=2, employeeName=EmployeeName{firstName='Nick', lastName='Jett'}}]

EclipseLink failed to generate correct SQL for the JPQL. In such case, you need to create a JPQL by hand or Criteria API that uses each column separately (lastName and firstName). EclipseLink produces following Exception:

Exception in thread "main" javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.1.v20150916-55dc7c3): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLDataException: An attempt was made to get a data value of type 'BIGINT' from a data value of type 'entity.EmployeeName'.
Error Code: 20000
Call: SELECT ID, FIRSTNAME, LASTNAME FROM EMPLOYEE WHERE (ID IN (?,?))
	bind => [EmployeeName{firstName='Scott', lastName='Vogel'}, EmployeeName{firstName='Nick', lastName='Jett'}]
Query: ReadAllQuery(referenceClass=Employee sql="SELECT ID, FIRSTNAME, LASTNAME FROM EMPLOYEE WHERE (ID IN ?)")
	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)
	at main.Main.main(Main.java:45)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at com.intellij.rt.execution.application.AppMain.main(AppMain.java:140)
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.1.v20150916-55dc7c3): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLDataException: An attempt was made to get a data value of type 'BIGINT' from a data value of type 'entity.EmployeeName'.
Error Code: 20000
Call: SELECT ID, FIRSTNAME, LASTNAME FROM EMPLOYEE WHERE (ID IN (?,?))
	bind => [EmployeeName{firstName='Scott', lastName='Vogel'}, EmployeeName{firstName='Nick', lastName='Jett'}]
Query: ReadAllQuery(referenceClass=Employee sql="SELECT ID, FIRSTNAME, LASTNAME FROM EMPLOYEE WHERE (ID IN ?)")
	at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:684)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:560)
	at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2055)
	at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:570)
	at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:258)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:299)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:694)
	at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2740)
	at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2693)
	at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:559)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1175)
	at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:904)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1134)
	at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:460)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1222)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1857)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1839)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1804)
	at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258)
	... 7 more

Complete source code that has been used in the test can be obtained from https://github.com/lbtc-xxx/jpa-composite-in