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

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>