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


How to bind / lookup DataSource via JNDI without container


Posted on Sunday Oct 25, 2015 at 10:58AM in Technology


While I prefer deploying JPA based apps to a Java EE container and test it via Arquillian as integration testing, some occasions won’t allow it and need arises that using a Servlet container or Java SE environment.

To supply information that required to connect the database (e.g. JDBC URL or credentials), it’s preferable to use JNDI rather than using DriverManager or javax.persistence.jdbc.* properties in persistence.xml because using JNDI eliminates the need of managing such information in the application codebase, also it enables to use the container managed connection pool which is more flexible and scalable over another.

In such case, hard-coded JNDI name of a DataSource may be a problem in the time of testing because JNDI lookup doesn’t work without container as is. So we may need some considering of involve pluggable mechanism of acquiring java.sql.Connection instance or creating persistence.xml for unit testing.

These solutions are not much difficult to implement, but it’s preferable if JNDI lookup does work without container as well because it will decrease amount of testing specific code. In this posting, I’ll give you a complete example of looking up a DataSource without container using bare InitialContext and the non-jta-datasource persistence descriptor definition.

Environment

  • tomcat-catalina artifact of Apache Tomcat 8.0.28: Enables binding a resource to JNDI context in Java SE environment

  • Apache Commons DBCP 1.4: Supplies BasicDataSource class so make the example in database independent manner

  • Apache Derby 10.12.1.1

  • EclipseLink 2.6.1

  • Oracle JDK8u60

Dependencies

<dependencies>
    <dependency>
        <groupId>org.eclipse.persistence</groupId>
        <artifactId>eclipselink</artifactId>
        <version>2.6.1</version>
    </dependency>
    <dependency>
        <groupId>commons-dbcp</groupId>
        <artifactId>commons-dbcp</artifactId>
        <version>1.4</version>
    </dependency>
    <dependency>
        <groupId>org.apache.derby</groupId>
        <artifactId>derby</artifactId>
        <version>10.12.1.1</version>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.apache.tomcat</groupId>
        <artifactId>tomcat-catalina</artifactId>
        <version>8.0.28</version>
        <scope>runtime</scope>
    </dependency>
</dependencies>

persistence.xml

Note that the non-jta-data-source is used with JNDI name of DataSource.

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
    <persistence-unit name="myPU" transaction-type="RESOURCE_LOCAL">
        <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
        <non-jta-data-source>java:comp/env/jdbc/database</non-jta-data-source>
        <class>entity.Employee</class>
        <shared-cache-mode>NONE</shared-cache-mode>
        <properties>
            <property name="javax.persistence.schema-generation.database.action" value="create"/>
            <property name="eclipselink.logging.level" value="FINE"/>
            <property name="eclipselink.logging.parameters" value="true"/>
        </properties>
    </persistence-unit>
</persistence>

Employee.java

This is a simple JPA entity class that will be used in testing.

@Entity
public class Employee implements Serializable {
    @Id
    private Long id;
    private String firstName;
    private String lastName;

    // accessors omitted

Main.java

This binds a DataSource of Embedded in-memory Apache Derby database to java:comp/env/jdbc/database, then lookup it via InitialContext and EntityManagerFactory.

public class Main {
    private static final String JNDI = "java:comp/env/jdbc/database";

    public static void main(String[] args) throws Exception {
        bind();
        lookup();
        final EntityManagerFactory emf = Persistence.createEntityManagerFactory("myPU");
        populate(emf);
        query(emf);
    }

    private static void bind() throws NamingException {
        System.setProperty(Context.INITIAL_CONTEXT_FACTORY, "org.apache.naming.java.javaURLContextFactory");
        System.setProperty(Context.URL_PKG_PREFIXES, "org.apache.naming");

        final BasicDataSource ds = new BasicDataSource();
        ds.setUrl("jdbc:derby:memory:myDB;create=true");

        final Context context = new InitialContext();
        try {
            context.createSubcontext("java:");
            context.createSubcontext("java:comp");
            context.createSubcontext("java:comp/env");
            context.createSubcontext("java:comp/env/jdbc");
            context.bind(JNDI, ds);
        } finally {
            context.close();
        }
    }

    private static void lookup() throws NamingException, SQLException {
        final Context context = new InitialContext();
        try {
            final DataSource ds = (DataSource) context.lookup(JNDI);
            try (final Connection cn = ds.getConnection();
                 final Statement st = cn.createStatement();
                 final ResultSet rs = st.executeQuery("SELECT CURRENT_TIMESTAMP FROM SYSIBM.SYSDUMMY1")) {
                while (rs.next()) {
                    System.out.println(rs.getTimestamp(1));
                }
            }
        } finally {
            context.close();
        }
    }

    private static void populate(final EntityManagerFactory emf) {
        final EntityManager em = emf.createEntityManager();
        try {
            final EntityTransaction tx = em.getTransaction();
            tx.begin();
            final Employee emp = new Employee();
            emp.setId(1l);
            emp.setFirstName("Jane");
            emp.setLastName("Doe");
            em.persist(emp);
            tx.commit();
        } finally {
            em.close();
        }
    }

    private static void query(final EntityManagerFactory emf) {
        final EntityManager em = emf.createEntityManager();
        try {
            System.out.println(em.find(Employee.class, 1l));
        } finally {
            em.close();
        }
    }
}

Log

You can see the lookup() method dumped CURRENT_TIMESTAMP and EclipseLink successfully acquired a DataSource as follows.

2015-10-25 10:33:24.235
[EL Fine]: server: 2015-10-25 10:33:24.478--Thread(Thread[main,5,main])--Configured server platform: org.eclipse.persistence.platform.server.NoServerPlatform
[EL Config]: metadata: 2015-10-25 10:33:24.633--ServerSession(1323434987)--Thread(Thread[main,5,main])--The access type for the persistent class [class entity.Employee] is set to [FIELD].
[EL Config]: metadata: 2015-10-25 10:33:24.654--ServerSession(1323434987)--Thread(Thread[main,5,main])--The alias name for the entity class [class entity.Employee] is being defaulted to: Employee.
[EL Config]: metadata: 2015-10-25 10:33:24.656--ServerSession(1323434987)--Thread(Thread[main,5,main])--The table name for entity [class entity.Employee] is being defaulted to: EMPLOYEE.
[EL Config]: metadata: 2015-10-25 10:33:24.666--ServerSession(1323434987)--Thread(Thread[main,5,main])--The column name for element [firstName] is being defaulted to: FIRSTNAME.
[EL Config]: metadata: 2015-10-25 10:33:24.668--ServerSession(1323434987)--Thread(Thread[main,5,main])--The column name for element [lastName] is being defaulted to: LASTNAME.
[EL Config]: metadata: 2015-10-25 10:33:24.668--ServerSession(1323434987)--Thread(Thread[main,5,main])--The column name for element [id] is being defaulted to: ID.
[EL Info]: 2015-10-25 10:33:24.7--ServerSession(1323434987)--Thread(Thread[main,5,main])--EclipseLink, version: Eclipse Persistence Services - 2.6.1.v20150916-55dc7c3
[EL Fine]: connection: 2015-10-25 10:33:24.706--Thread(Thread[main,5,main])--Detected database platform: org.eclipse.persistence.platform.database.JavaDBPlatform
[EL Config]: connection: 2015-10-25 10:33:24.714--ServerSession(1323434987)--Connection(1872973138)--Thread(Thread[main,5,main])--connecting(DatabaseLogin(
	platform=>JavaDBPlatform
	user name=> ""
	connector=>JNDIConnector datasource name=>java:comp/env/jdbc/database
))
[EL Config]: connection: 2015-10-25 10:33:24.715--ServerSession(1323434987)--Connection(1465346452)--Thread(Thread[main,5,main])--Connected: jdbc:derby:memory:myDB
	User: APP
	Database: Apache Derby  Version: 10.12.1.1 - (1704137)
	Driver: Apache Derby Embedded JDBC Driver  Version: 10.12.1.1 - (1704137)
[EL Config]: connection: 2015-10-25 10:33:24.715--ServerSession(1323434987)--Connection(1634387050)--Thread(Thread[main,5,main])--connecting(DatabaseLogin(
	platform=>JavaDBPlatform
	user name=> ""
	connector=>JNDIConnector datasource name=>java:comp/env/jdbc/database
))
[EL Config]: connection: 2015-10-25 10:33:24.716--ServerSession(1323434987)--Connection(1740223770)--Thread(Thread[main,5,main])--Connected: jdbc:derby:memory:myDB
	User: APP
	Database: Apache Derby  Version: 10.12.1.1 - (1704137)
	Driver: Apache Derby Embedded JDBC Driver  Version: 10.12.1.1 - (1704137)
[EL Info]: connection: 2015-10-25 10:33:24.747--ServerSession(1323434987)--Thread(Thread[main,5,main])--/file:/Users/kyle/src/jndi-se/target/classes/_myPU login successful
[EL Fine]: sql: 2015-10-25 10:33:24.784--ServerSession(1323434987)--Connection(762809053)--Thread(Thread[main,5,main])--CREATE TABLE EMPLOYEE (ID BIGINT NOT NULL, FIRSTNAME VARCHAR(255), LASTNAME VARCHAR(255), PRIMARY KEY (ID))
[EL Fine]: sql: 2015-10-25 10:33:24.85--ClientSession(1027495011)--Connection(1688470144)--Thread(Thread[main,5,main])--INSERT INTO EMPLOYEE (ID, FIRSTNAME, LASTNAME) VALUES (?, ?, ?)
	bind => [1, Jane, Doe]
[EL Fine]: sql: 2015-10-25 10:33:24.877--ServerSession(1323434987)--Connection(640808588)--Thread(Thread[main,5,main])--SELECT ID, FIRSTNAME, LASTNAME FROM EMPLOYEE WHERE (ID = ?)
	bind => [1]
Employee{id=1, firstName='Jane', lastName='Doe'}

The complete source code can be obtained from my GitHub repository.