Category Archives: Hibernate

MS SQL Server + Hibernate 5: Incorrect syntax near ‘@P0’

PROBLEM

When upgrading to Hibernate 5, the following exception is thrown:-

Caused by: java.sql.SQLException: Incorrect syntax near '@P0'.
	at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372) ~[jtds-1.3.1.jar:1.3.1]
	at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988) ~[jtds-1.3.1.jar:1.3.1]
	at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421) ~[jtds-1.3.1.jar:1.3.1]

SOLUTION

Change the MS SQL Server dialect from this…

org.hibernate.dialect.SQLServerDialect

… to this …

org.hibernate.dialect.SQLServer2012Dialect
Advertisements

Developing against H2 Database: Lesson Learned

While my production database is MS SQL Server, I have been using H2 database for rapid local development. I wrote my DDL scripts as “Transact-SQL” as possible so that I don’t need to maintain multiple DDL scripts for each database. So far, it has been working out great. My web development runs against H2’s file-based database because I want my data to persist until I manually wipe them off.

That said, I also realized that H2 does behavior a little differently from MS SQL Server. For example, I have the following Hibernate’s HQL query:-

public Collection<Request> getRequestsWithMatchingStatus(Collection<String> statusNames) {
    return sessionFactory.getCurrentSession()
            .createQuery("from Request r where r.status.name in (:statusNames)")
            .setParameterList("statusNames", statusNames)
            .list();
}

If statusNames is an empty collection, H2 will work just fine. However, MS SQL Server will throw the following exception:-

Caused by: java.sql.SQLException: Incorrect syntax near ')'.
	at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
	at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
	at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)

My test cases didn’t catch this error because they run against H2’s in-memory database.

Of course, the fix is as easy as performing a check on the collection size first:-

public Collection<Request> getRequestsWithMatchingStatus(Collection<String> statusNames) {
    if (statusNames.isEmpty()) {
        return new ArrayList<Request>();
    }

    return sessionFactory.getCurrentSession()
            .createQuery("from Request r where r.status.name in (:statusNames)")
            .setParameterList("statusNames", statusNames)
            .list();
}

That said, H2 database allows me to do local development really fast, but I also need to make sure I test it against MS SQL Server from time to time before deploying into production.

Jadira Usertype: Under JDK 6 it may not be possible to handle DST transitions correctly

PROBLEM

When saving a Hibernate entity that contains Joda Time object, Jadira UserType throws the following warning:-

[WARN ] [JavaTimeZoneWorkaroundHelper] [<clinit>:40] - Under JDK 6 it may not be possible 
to handle DST transitions correctly
[ERROR] [JavaTimeZoneWorkaroundHelper] [<clinit>:42] - Running under a Zone that uses 
daylight saving time. To avoid incorrect datetimes being stored during DST transition, 
either update to JDK 7 or use a Timezone for the JDK without Daylight Saving Time

This warning occurs when using this version:-

<dependency>
    <groupId>org.jadira.usertype</groupId>
    <artifactId>usertype.core</artifactId>
    <version>3.0.0.GA</version>
</dependency>

SOLUTION

Upgrade Jadira Usertype to the latest version, and the problem goes away:-

<dependency>
    <groupId>org.jadira.usertype</groupId>
    <artifactId>usertype.core</artifactId>
    <version>3.1.0.CR10</version>
</dependency>

Hibernate + Joda Time: Auto Registering Type

OVERVIEW

Jadira Usertype is required when using Joda Time with Hibernate 4. The Joda Time objects are annotated accordingly in the Hibernate entity, and they looks something like this:-

@Entity
@Table(name = "person")
public class Person implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "personId")
    private Long id;

    @Column
    private String name;
	
    @Column
    @Type(type = "org.jadira.usertype.dateandtime.joda.PersistentLocalDate")
    private LocalDate birthDate;

    @Column
    @Type(type = "org.jadira.usertype.dateandtime.joda.PersistentLocalDateTime")
    private LocalDateTime createdDatetime;
	
    // getters and setters
}

PROBLEM

This solution works, but it is rather tedious because I can never remember the actual @Type to write, thus I always end up copying and pasting it from past project code.

Further, that additional annotations clutter up my otherwise beautiful code.

SOLUTION

Jadira Usertype provides a clean way to auto register these Joda Time object types.

In the Hibernate configuration, add the following line:-

<bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource"/>

    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</prop>
            <prop key="hibernate.show_sql">false</prop>
            <prop key="jadira.usertype.autoRegisterUserTypes">true</prop>
        </props>
    </property>
    <property name="packagesToScan">
        <list>
            <value>com.choonchernlim.project.entity</value>
        </list>
    </property>
</bean>

Now all @Type annotations can be safely removed from the Hibernate entity:-

@Entity
@Table(name = "person")
public class Person implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "personId")
    private Long id;

    @Column
    private String name;
	
    @Column
    private LocalDate birthDate;

    @Column
    private LocalDateTime createdDatetime;
	
    // getters and setters
}

Hibernate: Migrating from XML-Based Configuration to Annotation-Based Configuration

Overview

At some point of time, as your project scope grows, the Hibernate mapping XML files are going to get to a point where it becomes very difficult to maintain. This is where the annotation-based configuration comes in. It took me a few years to convince myself that annotation-based configuration is the way to go. Change is hard, yet necessary.

This tutorial covers the following:-

  • Upgrade Hibernate from 3.x to 4.x.
  • Configure Spring-Hibernate integration to replace XML-based configuration with annotation-based configuration.
  • Configure Joda-Time to work properly in Hibernate 4.
  • Activate @Transactional instead of relying on AOP-based transaction.

Maven Dependencies

Change the Hibernate dependency version from 3.x to 4.x. On top of that, add org.jadira.usertype:usertype.core:[version] dependency to get Joda-Time to work properly with Hibernate 4.

Hibernate 3.x

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>3.6.9.Final</version>
</dependency>
<dependency>
    <groupId>joda-time</groupId>
    <artifactId>joda-time</artifactId>
    <version>2.3</version>
</dependency>
<dependency>
	<groupId>joda-time</groupId>
	<artifactId>joda-time-hibernate</artifactId>
	<version>1.3</version>
</dependency>

Hibernate 4.x

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>4.2.6.Final</version>
</dependency>
<dependency>
    <groupId>joda-time</groupId>
    <artifactId>joda-time</artifactId>
    <version>2.3</version>
</dependency>
<dependency>
	<groupId>joda-time</groupId>
	<artifactId>joda-time-hibernate</artifactId>
	<version>1.3</version>
</dependency>
<dependency>
    <groupId>org.jadira.usertype</groupId>
    <artifactId>usertype.core</artifactId>
    <version>3.1.0.CR8</version>
</dependency>

By the way, if you don’t know what Joda-Time is or haven’t use it by now, then you should be spanked by the Date God. With that added dependency, the @Type for date field will look a little different ( see here for more info ):-

@Entity
@Table(name = "MrMeow")
public class MrMeow implements Ideable, Serializable {
	...
	
	@Column
	@Type(type = "org.jadira.usertype.dateandtime.joda.PersistentLocalDate")
	private LocalDate whenMeowed;
}

Spring Integration

Hibernate 3.x

This configuration uses Hibernate XML mapping files to configure mappings between the domain objects and the database tables. Further, AOP is used to configure the transaction.

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource"/>
    <property name="configLocation" value="classpath:hibernate.cfg.xml"/>
</bean>

<bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
    <property name="sessionFactory" ref="sessionFactory"/>
</bean>

<tx:advice id="txAdvice">
    <tx:attributes>
        <tx:method name="*" propagation="REQUIRED"/>
    </tx:attributes>
</tx:advice>

<aop:config proxy-target-class="true">
    <aop:advisor pointcut="execution(* myproject..*.*(..))" advice-ref="txAdvice"/>
</aop:config>

Hibernate 4.x

This configuration scans myproject.domain package, which contains all Hibernate annotated domain classes. Further, it activates @Transactional so that you can place it in your code to manage the transaction.

<tx:annotation-driven transaction-manager="transactionManager"/>

<bean id="transactionManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
    <property name="sessionFactory" ref="sessionFactory"/>
</bean>

<bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource"/>

    <property name="hibernateProperties">
        <props>
			<!-- Configure your Hibernate dialect  -->
            <prop key="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</prop>
            <prop key="hibernate.show_sql">false</prop>
        </props>
    </property>
    <property name="packagesToScan">
        <list>
			<!-- Configure your domain package -->
            <value>myproject.domain</value>
        </list>
    </property>
</bean>

web.xml

Change filter class for OpenSessionInViewFilter.

Hibernate 3.x

<filter>
    <filter-name>hibernateFilter</filter-name>
	<filter-class>org.springframework.orm.hibernate3.support.OpenSessionInViewFilter</filter-class>
</filter>
<filter-mapping>
    <filter-name>hibernateFilter</filter-name>
    <url-pattern>/*</url-pattern>
</filter-mapping>

Hibernate 4.x

<filter>
    <filter-name>hibernateFilter</filter-name>
    <filter-class>org.springframework.orm.hibernate4.support.OpenSessionInViewFilter</filter-class>
</filter>
<filter-mapping>
    <filter-name>hibernateFilter</filter-name>
    <url-pattern>/*</url-pattern>
</filter-mapping>

Domain Classes

Finally, time to annotate your Hibernate domain classes!

Helpful Note

Please make sure you don’t have JPA 1.x in your classpath in the first place. Read java.lang.NoSuchMethodError: javax/persistence/OneToMany.orphanRemoval()Z for more information.

java.lang.NoSuchMethodError: javax/persistence/OneToMany.orphanRemoval()Z

PROBLEM

You configure Hibernate using annotations and set orphanRemoval property in @OneToMany.

@Entity
@Table(name = "person")
public class Person implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "personId")
    private Long id;

    @OneToMany(mappedBy = "person", cascade = CascadeType.ALL, orphanRemoval = true)
    private Set<Car> cars = new HashSet<Car>();

	...
}

When you run the application, the application server throws the following exception:-

Caused by: java.lang.NoSuchMethodError: javax/persistence/OneToMany.orphanRemoval()Z
        at org.hibernate.cfg.AnnotationBinder.processElementAnnotations(AnnotationBinder.java:1868)
        at org.hibernate.cfg.AnnotationBinder.processIdPropertiesIfNotAlready(AnnotationBinder.java:767)

In my case, I’m getting this exception when I run on Websphere Application Server (WAS) 7.5.

SOLUTION

The orphanRemoval property in @OneToMany requires JPA 2.x to work. If you already have JPA 2.x in your classpath and you are still getting the error, then JPA 1.x must already been loaded either by your application or by the application server, causing your JPA 2.x to be ignored.

To fix this:-

  1. Add JPA 2.x in your classpath. For example, hibernate-jpa-2.0-api-1.0.1.Final.jar.
  2. Remove any trace of JPA 1.x from your classpath by scanning your dependency tree.
  3. If your application server uses JPA 1.x, you will need to remove or replace that jar with JPA 2.x.
  4. If you are not allowed to replace JPA 1.x jar on the application server (for example, you are not the admin of the server), you will need to invert the application class loader to PARENT_LAST to ensure the JPA 2.x from your application gets loaded first and JPA 1.x from the application server will get ignored.

Inverting class loader for application without replacing JPA 1.x from application server

Assuming you have JPA 2.x in your application classpath and your project structure looks like this:-

myproject
|- myproject-ear
|  |- pom.xml
|- myproject-war
|  |- src
|  |  |- main
|  |     |- java
|  |     |- resources
|  |     |- webapp
|  |- pom.xml
|- pom.xml     

… if you are deploying your application to Websphere Application Server (WAS), create deployment.xml under src/main/application in the EAR module and instruct WAS to load parent last:-

<?xml version="1.0" encoding="UTF-8"?>
<appdeployment:Deployment xmi:version="2.0" xmlns:xmi="http://www.omg.org/XMI"
                          xmlns:appdeployment="http://www.ibm.com/websphere/appserver/schemas/5.0/appdeployment.xmi"
                          xmi:id="Deployment_1">
 
    <deployedObject xmi:type="appdeployment:ApplicationDeployment" xmi:id="ApplicationDeployment_1"
                    startingWeight="1" warClassLoaderPolicy="SINGLE">
        <classloader xmi:id="Classloader_1" mode="PARENT_LAST"/>
    </deployedObject>
</appdeployment:Deployment>

In the EAR pom.xml, extend the parent POM and configure the Maven EAR Plugin:-

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

	<!-- Extend the parent POM -->
	<parent>
        <groupId>myproject</groupId>
        <artifactId>myproject</artifactId>
        <version>1.0</version>
    </parent>

    <artifactId>myproject-ear</artifactId>
    <packaging>ear</packaging>
    <name>${project.artifactId}</name>

    <build>
        <plugins>
		   <!-- Configure Maven EAR Plugin -->
            <plugin>
                <artifactId>maven-ear-plugin</artifactId>
                <version>2.4</version>
                <configuration>
                    <displayName>${project.parent.artifactId}</displayName>
                    <modules>
                        <webModule>
                            <groupId>myproject</groupId>
                            <artifactId>myproject-war</artifactId>
                            <bundleFileName>${project.parent.artifactId}.war</bundleFileName>
                            <contextRoot>${context-root}</contextRoot>
                        </webModule>
                    </modules>
                </configuration>
            </plugin>
        </plugins>
    </build>

    <dependencies>
		<!-- 
		Expose the WAR module to allow the WAR file to be packaged 
		in the EAR file by Maven EAR Plugin 
		-->
        <dependency>
            <groupId>myproject</groupId>
            <artifactId>myproject-war</artifactId>
            <version>1.0</version>
            <type>war</type>
        </dependency>
    </dependencies>
</project>

Your current project structure should look like this now:-

myproject
|- myproject-ear
|  |- src
|  |  |- main
|  |     |- application
|  |        |- deployment.xml
|  |- pom.xml
|- myproject-war
|  |- src
|  |  |- main
|  |     |- java
|  |     |- resources
|  |     |- webapp
|  |- pom.xml
|- pom.xml

Configure the parent POM to be an aggregator too:-

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>myproject</groupId>
    <artifactId>myproject</artifactId>
    <packaging>pom</packaging>
    <version>1.0</version>

	<!-- Make this parent POM to be an aggregator for both EAR and WAR modules -->
    <modules>
        <module>myproject-ear</module>
        <module>myproject-war</module>
    </modules>
</project>

When you run mvn clean package on the parent POM, the EAR file will contain the WAR file and the deployment file that inverts the class loader for this application.