Kohei Nozaki's blog: Notes of my experiments

Entries tagged [hibernate]

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

Comments [0]

Tags: eclipselink hibernate jpa jpql

Using JPA 2.1 AttributeConverter against Java8 LocalDate / LocalDateTime

Posted on Tuesday Mar 17, 2015 at 01:50PM in JPA

I created an example project using https://weblogs.java.net/blog/montanajava/archive/2014/06/17/using-java-8-datetime-classes-jpa which ran on WildFly 8.2.0.Final (Hibernate 4.3.7) and H2 / Apache Derby database.

the whole project can be obtained from https://github.com/lbtc-xxx/jpa21converter .

You don’t need to define any additional configuration in persistence.xml if you use converters in EE environment. it goes like this:

The converter for LocalDate between DATE

@Converter(autoApply = true)
public class MyLocalDateConverter implements AttributeConverter<java.time.LocalDate, java.sql.Date> {

    @Override
    public java.sql.Date convertToDatabaseColumn(java.time.LocalDate attribute) {
        return attribute == null ? null : java.sql.Date.valueOf(attribute);
    }

    @Override
    public java.time.LocalDate convertToEntityAttribute(java.sql.Date dbData) {
        return dbData == null ? null : dbData.toLocalDate();
    }
}

The converter for LocalDateTime between TIMESTAMP

@Converter(autoApply = true)
public class MyLocalDateTimeConverter implements AttributeConverter<java.time.LocalDateTime, java.sql.Timestamp> {

    @Override
    public java.sql.Timestamp convertToDatabaseColumn(java.time.LocalDateTime attribute) {
        return attribute == null ? null : java.sql.Timestamp.valueOf(attribute);
    }

    @Override
    public java.time.LocalDateTime convertToEntityAttribute(java.sql.Timestamp dbData) {
        return dbData == null ? null : dbData.toLocalDateTime();
    }
}

Entity class

@Entity
public class MySimpleTable implements Serializable {
    @Id
    @GeneratedValue
    private Long id;
    private java.time.LocalDateTime someLocalDateTime;
    private java.time.LocalDate someLocalDate;
...

Hibernate produces the DDL against H2 as follows:

create table MySimpleTable (
    id bigint not null,
    someLocalDate date,
    someLocalDateTime timestamp,
    primary key (id)
)

Using converters with @EmbeddedId

Converters doesn’t work with fields that annotated as @Id (see http://stackoverflow.com/questions/28337798/hibernate-fails-to-load-jpa-2-1-converter-when-loaded-with-spring-boot-and-sprin ) but works with @EmbeddedId class.

Entity class:

@Entity
public class MyCompositeKeyTable implements Serializable {
    @EmbeddedId
    private MyCompositeKeyEmbeddable key;
...

Embeddable class:

@Embeddable
public class MyCompositeKeyEmbeddable implements Serializable {
    @Column(nullable = false)
    private java.time.LocalDateTime someLocalDateTime;
    @Column(nullable = false)
    private java.time.LocalDate someLocalDate;
...

Produced DDL:

create table MyCompositeKeyTable (
    someLocalDate date not null,
    someLocalDateTime timestamp not null,
    primary key (someLocalDate, someLocalDateTime)
)

Comments [5]

Tags: hibernate jpa