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

Java: Exploring Preferences API

BACKGROUND

In any written scripts or rich client apps, there is almost a need to persist the user preferences or app configurations.

Most of the time, we, the proud developers, handle that situation in very ad-hoc manner. When storing in a file, we use different formats: from old-boring XML, to cool-kid JSON, to even cooler-kid YAML or the kindergarten-kid key=value property. Then, we have to decide where to write the file to, whether to use C:\ and screw your non-windows users, whether to use backslashes to construct the file path or forward slashes because we are sick and tired escaping the effing backslashes.

The long story short is… yes, we, the proud developers, can do all that… or, as one of my current project peers like to say, “make it configurable” on literally everything to the point it’s pretty close of becoming a drinking game now.

But, the point I want to make here is… we are consistent on being inconsistent.

SOLUTION

Java provides the Preferences API as an attempt to solve this mess. Using this API, the developers do not need to know where or how to store the user preferences or app configurations. Rather, it relies on the native API to store the data: registry for Windows, .plist for Mac and XML for Unix/Linux.

The most interesting part is… the Preferences API has been around since JDK 1.4.

Code wise, it doesn’t get any simpler than this:-

// create new configuration or reference existing configuration
Preferences preferences = Preferences.userNodeForPackage(WuTangClan)

// insert/update 3 key/value pairs
preferences.put('key1', 'value1')
preferences.put('key2', 'value2')
preferences.put('key3', 'value3')

// returns 'value2'
println preferences.get('key2', '-')

// returns '-'
println preferences.get('invalid', '-')

// remove by key
preferences.remove('key3')

// delete everything
preferences.removeNode()

But, where and how exactly do Mac and Windows store this data?

There are several ways to get an instance of Preferences.

Preferences.userNodeForPackage(WuTangClan)

Mac

If WuTangClan class file is located under wu.tang.clan.config package, the configuration file is created at ~/Library/Preferences/wu.tang.clan.plist with the following content:-

{    "/wu/tang/clan/" = {
        "config/" = {
            "key1" = "value1";
            "key2" = "value2";
        };
    };
}

64-bit Windows + 64-bit JVM

Configuration is stored in the registry with the following key:-

HKEY_CURRENT_USER\SOFTWARE\JavaSoft\Prefs\wu\tang\clan\config

Preferences.userRoot().node(‘path’)

Example 1

Let’s assume we have this:-

Preferences.userRoot().node('wu')

Mac

The configuration is created at ~/Library/Preferences/com.apple.java.util.prefs.plist with the following content:-

{    "/" = {
        ...
        
        "wu/" = {
            "key1" = "value1";
            "key2" = "value2";
        };
        
        ...
    };
}

This file also contains configurations from other installed software.

64-bit Windows + 64-bit JVM

Configuration is stored in the registry with the following key:-

HKEY_CURRENT_USER\SOFTWARE\JavaSoft\Prefs\wu

Example 2

How about this?

Preferences.userRoot().node('wu/tang')

// ... OR ...

Preferences.userRoot().node('wu').node('tang')

Mac

The configuration still resides under ~/Library/Preferences/com.apple.java.util.prefs.plist with the following content:-

{    "/" = {
        ...
        "wu/" = {
            "tang/" = {
                "key1" = "value1";
                "key2" = "value2";
            };
        };
        ...
    };
}

64-bit Windows + 64-bit JVM

Configuration is stored in the registry with the following key:-

HKEY_CURRENT_USER\SOFTWARE\JavaSoft\Prefs\wu\tang

Example 3

How about this?

Preferences.userRoot().node('wu/tang/clan')

// ... OR ...

Preferences.userRoot().node('wu').node('tang').node('clan')

Mac

Now, the shit is about to get real here.

Mac, for some reason, creates a stub under ~/Library/Preferences/com.apple.java.util.prefs.plist with the following content:-

{    "/" = {
        ...
        "wu/" = { "tang/" = { "clan/" = { }; }; };
        ...
    };
}

The actual configuration now resides under ~/Library/Preferences/wu.tang.clan.plist:-

{    "/wu/tang/clan/" = {
        "key1" = "value1";
        "key2" = "value2";
    };
}

It appears when the path reaches certain depth, Mac will create the separate configuration file for it.

64-bit Windows + 64-bit JVM

Configuration is stored in the registry with the following key:-

HKEY_CURRENT_USER\SOFTWARE\JavaSoft\Prefs\wu\tang\clan

Preferences.systemNodeForPackage(WuTangClan) or Preferences.systemRoot().node(‘path’)

Instead of storing the configuration at user level, we may also store the configuration at system level.

Mac

Instead of storing under ~/Library/Preferences, the configuration is stored under /Library/Preferences.

On top of that, based on Java Development Guide for Mac, the configuration is only persisted if the user is an administrator.

The really weird part is the code will not throw any exceptions due to insufficient permission.

64-bit Windows + 64-bit JVM

Instead of storing under HKEY_CURRENT_USER\[path], the configuration is stored under HKEY_LOCAL_MACHINE\[path].

Best Practices

I’m not sure if this is a best practice, but my personal preference is to specify my own string path through Preferences.userRoot().node(..).

Preferences.userNodeForPackage(..) worries me because if I refactor my code by moving the class files around, it may not find the existing configuration due to changed path.

When specifying the string path, do make sure the path value is rather unique to prevent reading an existing configuration from other installed software.

Spring + Ehcache: XML-less Spring Configuration for Ehcache 2.x vs Ehcache 3.x

BACKGROUND

The documentation on the web regarding Ehcache 3.x configuration using Spring is rather lacking. There is apparently a very distinct difference in Spring Java-based configuration between Ehcache 2.x vs Ehcache 3.x.

Spring + Ehcache 2.x

Dependency:-

<dependency>
    <groupId>net.sf.ehcache</groupId>
    <artifactId>ehcache</artifactId>
    <version>2.10.3</version>
</dependency>

Spring configuration:-

@Configuration
@EnableCaching
class Config {
    @Bean
    CacheManager cacheManager() {
        return new EhCacheCacheManager(ehCacheManager())
    }

    @Bean(destroyMethod = 'shutdown')
    net.sf.ehcache.CacheManager ehCacheManager() {
        CacheConfiguration cacheConfiguration = new CacheConfiguration(
                name: 'person',
                maxEntriesLocalHeap: 5,
                timeToLiveSeconds: 5
        )

        net.sf.ehcache.config.Configuration config = new net.sf.ehcache.config.Configuration()
        config.addCache(cacheConfiguration)

        return new net.sf.ehcache.CacheManager(config)
    }
}

Spring + Ehcache 3.x

Dependency:-

<dependency>
    <groupId>org.ehcache</groupId>
    <artifactId>ehcache</artifactId>
    <version>3.3.1</version>
</dependency>
<dependency>
    <groupId>javax.cache</groupId>
    <artifactId>cache-api</artifactId>
    <version>1.0.0</version>
</dependency>

Spring configuration:-

import org.ehcache.config.CacheConfiguration
import org.ehcache.config.builders.CacheConfigurationBuilder
import org.ehcache.config.builders.ResourcePoolsBuilder
import org.ehcache.core.config.DefaultConfiguration
import org.ehcache.expiry.Duration
import org.ehcache.expiry.Expirations
import org.ehcache.jsr107.EhcacheCachingProvider
import org.springframework.cache.annotation.EnableCaching
import org.springframework.cache.jcache.JCacheCacheManager
import org.springframework.context.annotation.Bean
import org.springframework.context.annotation.Configuration

import javax.cache.CacheManager
import javax.cache.Caching
import java.util.concurrent.TimeUnit

@Configuration
@EnableCaching
class Config {
    @Bean
    JCacheCacheManager jCacheCacheManager() {
        return new JCacheCacheManager(cacheManager())
    }

    @Bean(destroyMethod = 'close')
    CacheManager cacheManager() {
        CacheConfiguration cacheConfiguration = CacheConfigurationBuilder.newCacheConfigurationBuilder(
                Object,
                Object,
                ResourcePoolsBuilder.heap(5)).
                withExpiry(Expirations.timeToLiveExpiration(new Duration(5, TimeUnit.SECONDS))).
                build()

        Map<String, CacheConfiguration> caches = ['person': cacheConfiguration]

        EhcacheCachingProvider provider = (EhcacheCachingProvider) Caching.getCachingProvider()
        DefaultConfiguration configuration = new DefaultConfiguration(caches, provider.getDefaultClassLoader())

        return provider.getCacheManager(provider.getDefaultURI(), configuration)
    }
}

Groovy + GPars: Handling Concurrency

PROBLEM

Let’s assume given a list of user IDs (ex: 1, 2, 3, 4, and 5), we need to query 2 data sources to get the names and the addresses before returning a list of Employee objects. The Employee class looks like this:-

@ToString(includePackage = false)
class Employee {
    Long id
    String name
    String address
}

To simplify the example, let’s assume the name lookup takes 2 seconds to run and the address lookup takes 4 seconds to run.

And now, we have the following code:-

class App {
    String nameLookup(Integer id) {
        log("Name lookup: ${id}")
        Thread.sleep(2000)
        "User ${id}"
    }

    String addressLookup(Integer id) {
        log("Addr lookup: ${id}")
        Thread.sleep(4000)
        "Address ${id}"
    }

    void log(String message) {
        println "${Thread.currentThread()} - ${new Date().format('HH:mm:ss')} - ${message}"
    }

    List<Employee> lookup(List<Integer> userIds) {
        ???
    }

    void run() {
        def start = System.currentTimeMillis()

        def employees = lookup([1, 2, 3, 4, 5])

        def end = System.currentTimeMillis()

        println '---------------------------------'
        println "Total time in seconds: ${(end - start) / 1000}"
        println '---------------------------------'

        employees.each {
            println it
        }
    }

    static void main(String[] args) {
        new App().run()
    }
}

We are going to explore multiple solutions to implement lookup(..) to run as fast as possible.

ATTEMPT 1

The simplest and the most straightforward approach is to perform the task synchronously.

Code:-

List<Employee> lookup(List<Integer> userIds) {
    userIds.collect { id ->
        new Employee(
                id: id,
                name: nameLookup(id),
                address: addressLookup(id)
        )
    }
}

Output:-

Thread[main,5,main] - 20:54:09 - Name lookup: 1
Thread[main,5,main] - 20:54:11 - Addr lookup: 1
Thread[main,5,main] - 20:54:15 - Name lookup: 2
Thread[main,5,main] - 20:54:17 - Addr lookup: 2
Thread[main,5,main] - 20:54:21 - Name lookup: 3
Thread[main,5,main] - 20:54:23 - Addr lookup: 3
Thread[main,5,main] - 20:54:27 - Name lookup: 4
Thread[main,5,main] - 20:54:29 - Addr lookup: 4
Thread[main,5,main] - 20:54:33 - Name lookup: 5
Thread[main,5,main] - 20:54:35 - Addr lookup: 5
---------------------------------
Total time in seconds: 30.129
---------------------------------
Employee(1, User 1, Address 1)
Employee(2, User 2, Address 2)
Employee(3, User 3, Address 3)
Employee(4, User 4, Address 4)
Employee(5, User 5, Address 5)

Since everything runs synchronously in one thread, it takes about 30 seconds to complete.

ATTEMPT 2

In this attempt and the rest of the attempts, we are going to use GPars, which is a concurrency and parallelism library.

<dependency>
    <groupId>org.codehaus.gpars</groupId>
    <artifactId>gpars</artifactId>
    <version>1.2.1</version>
</dependency>

In this attempt, we are going to use GParsPool.executeAsyncAndWait(..).

Code:-

List<Employee> lookup(List<Integer> userIds) {
    (List<Employee>) GParsPool.withPool {
        userIds.collect { id ->
            def results = GParsPool.executeAsyncAndWait(
                    this.&nameLookup.curry(id),
                    this.&addressLookup.curry(id)
            )

            new Employee(
                    id: id,
                    name: results[0],
                    address: results[1]
            )
        }
    }
}

Output:-

Thread[ForkJoinPool-1-worker-2,5,main] - 20:54:40 - Addr lookup: 1
Thread[ForkJoinPool-1-worker-1,5,main] - 20:54:40 - Name lookup: 1
Thread[ForkJoinPool-1-worker-2,5,main] - 20:54:44 - Name lookup: 2
Thread[ForkJoinPool-1-worker-1,5,main] - 20:54:44 - Addr lookup: 2
Thread[ForkJoinPool-1-worker-1,5,main] - 20:54:48 - Name lookup: 3
Thread[ForkJoinPool-1-worker-2,5,main] - 20:54:48 - Addr lookup: 3
Thread[ForkJoinPool-1-worker-1,5,main] - 20:54:52 - Addr lookup: 4
Thread[ForkJoinPool-1-worker-2,5,main] - 20:54:52 - Name lookup: 4
Thread[ForkJoinPool-1-worker-2,5,main] - 20:54:56 - Addr lookup: 5
Thread[ForkJoinPool-1-worker-1,5,main] - 20:54:56 - Name lookup: 5
---------------------------------
Total time in seconds: 20.184
---------------------------------
Employee(1, User 1, Address 1)
Employee(2, User 2, Address 2)
Employee(3, User 3, Address 3)
Employee(4, User 4, Address 4)
Employee(5, User 5, Address 5)

This allows us to run both the name lookup and the address lookup concurrently for each user ID.

Speed gain compared to first attempt: 1.5x

ATTEMPT 3

How about collectParallel(..)?

Code:-

List<Employee> lookup(List<Integer> userIds) {
    (List<Employee>) GParsPool.withPool {
        userIds.collectParallel { Integer id ->
            new Employee(
                    id: id,
                    name: nameLookup(id),
                    address: addressLookup(id)
            )
        }
    }
}

Output:-

Thread[ForkJoinPool-2-worker-1,5,main] - 20:55:00 - Name lookup: 1
Thread[ForkJoinPool-2-worker-4,5,main] - 20:55:00 - Name lookup: 4
Thread[ForkJoinPool-2-worker-3,5,main] - 20:55:00 - Name lookup: 2
Thread[ForkJoinPool-2-worker-2,5,main] - 20:55:00 - Name lookup: 3
Thread[ForkJoinPool-2-worker-5,5,main] - 20:55:00 - Name lookup: 5
Thread[ForkJoinPool-2-worker-4,5,main] - 20:55:02 - Addr lookup: 4
Thread[ForkJoinPool-2-worker-3,5,main] - 20:55:02 - Addr lookup: 2
Thread[ForkJoinPool-2-worker-1,5,main] - 20:55:02 - Addr lookup: 1
Thread[ForkJoinPool-2-worker-2,5,main] - 20:55:02 - Addr lookup: 3
Thread[ForkJoinPool-2-worker-5,5,main] - 20:55:02 - Addr lookup: 5
---------------------------------
Total time in seconds: 6.156
---------------------------------
Employee(1, User 1, Address 1)
Employee(2, User 2, Address 2)
Employee(3, User 3, Address 3)
Employee(4, User 4, Address 4)
Employee(5, User 5, Address 5)

This allows us to run all name lookups concurrently, followed by all address lookup concurrently.

Speed gain compared to first attempt: 4.9x

ATTEMPT 4

Perhaps, another approach is to kick start all the lookups asynchronously and hold on to the returned Future objects:-

Code:-

List<Employee> lookup(List<Integer> userIds) {
    (List<Employee>) GParsPool.withPool {
        List<Future> nameFutures = userIds.collect {
            this.&nameLookup.callAsync(it)
        }

        List<Future> addressFutures = userIds.collect {
            this.&addressLookup.callAsync(it)
        }

        userIds.withIndex().collect { Integer id, Integer i ->
            new Employee(
                    id: id,
                    name: nameFutures[i].get(),
                    address: addressFutures[i].get()
            )
        }
    }
}

Output:-

Thread[ForkJoinPool-3-worker-5,5,main] - 20:55:06 - Name lookup: 5
Thread[ForkJoinPool-3-worker-4,5,main] - 20:55:06 - Name lookup: 4
Thread[ForkJoinPool-3-worker-2,5,main] - 20:55:06 - Name lookup: 2
Thread[ForkJoinPool-3-worker-3,5,main] - 20:55:06 - Name lookup: 3
Thread[ForkJoinPool-3-worker-1,5,main] - 20:55:06 - Name lookup: 1
Thread[ForkJoinPool-3-worker-3,5,main] - 20:55:08 - Addr lookup: 4
Thread[ForkJoinPool-3-worker-2,5,main] - 20:55:08 - Addr lookup: 3
Thread[ForkJoinPool-3-worker-4,5,main] - 20:55:08 - Addr lookup: 2
Thread[ForkJoinPool-3-worker-5,5,main] - 20:55:08 - Addr lookup: 1
Thread[ForkJoinPool-3-worker-1,5,main] - 20:55:08 - Addr lookup: 5
---------------------------------
Total time in seconds: 6.032
---------------------------------
Employee(1, User 1, Address 1)
Employee(2, User 2, Address 2)
Employee(3, User 3, Address 3)
Employee(4, User 4, Address 4)
Employee(5, User 5, Address 5)

It’s slightly better than the previous attempt, but the performance gain is rather negligible.

Speed gain compared to first attempt: 5.0x

ATTEMPT 5

What if we take the previous attempt and increase the number of threads to 10?

Code:-

List<Employee> lookup(List<Integer> userIds) {
    GParsPool.withPool 10, {
        List<Future> nameFutures = userIds.collect {
            this.&nameLookup.callAsync(it)
        }

        List<Future> addressFutures = userIds.collect {
            this.&addressLookup.callAsync(it)
        }

        userIds.withIndex().collect { Integer id, Integer i ->
            new Employee(
                    id: id,
                    name: nameFutures[i].get(),
                    address: addressFutures[i].get()
            )
        }
    }
}

Output:-

Thread[ForkJoinPool-4-worker-1,5,main] - 20:55:12 - Name lookup: 1
Thread[ForkJoinPool-4-worker-2,5,main] - 20:55:12 - Name lookup: 2
Thread[ForkJoinPool-4-worker-3,5,main] - 20:55:12 - Name lookup: 3
Thread[ForkJoinPool-4-worker-4,5,main] - 20:55:12 - Name lookup: 4
Thread[ForkJoinPool-4-worker-5,5,main] - 20:55:12 - Name lookup: 5
Thread[ForkJoinPool-4-worker-6,5,main] - 20:55:12 - Addr lookup: 1
Thread[ForkJoinPool-4-worker-8,5,main] - 20:55:12 - Addr lookup: 3
Thread[ForkJoinPool-4-worker-7,5,main] - 20:55:12 - Addr lookup: 2
Thread[ForkJoinPool-4-worker-9,5,main] - 20:55:12 - Addr lookup: 4
Thread[ForkJoinPool-4-worker-10,5,main] - 20:55:12 - Addr lookup: 5
---------------------------------
Total time in seconds: 4.016
---------------------------------
Employee(1, User 1, Address 1)
Employee(2, User 2, Address 2)
Employee(3, User 3, Address 3)
Employee(4, User 4, Address 4)
Employee(5, User 5, Address 5)

Speed gain compared to first attempt: 7.5x

And now, we have successfully improve our implementation performance from 30 seconds to 4 seconds.

UnboundID: The entry contains attribute ‘XXXX’ which is not defined in the schema

PROBLEM

Let’s assume we have the following LDIF file containing custom attribute(s), such as managedBy:-

dn: dc=MyShittyCode
objectClass: top
objectClass: domain
dc: MyShittyCode

dn: CN=ShittyEmployee,DC=MyShittyCode
objectclass: top
objectclass: person
objectclass: organizationalPerson
objectclass: inetOrgPerson
sn: ShittyEmployee
managedBy: CN=ShittyBoss,DC=MyShittyCode

When running the code on UnboundID’s In-Memory Directory Server, the following exception is thrown:-

LDAPException(resultCode=65 (object class violation), 
errorMessage='Unable to add entry 'CN=ShittyEmployee,DC=MyShittyCode' 
because it violates the provided schema:  The entry contains attribute 
managedBy which is not defined in the schema.')
	at com.unboundid.ldap.listener.InMemoryRequestHandler.addEntry(InMemoryRequestHandler.java:4055)
	at com.unboundid.ldap.listener.InMemoryRequestHandler.importFromLDIF(InMemoryRequestHandler.java:3876)
	at com.unboundid.ldap.listener.InMemoryDirectoryServer.importFromLDIF(InMemoryDirectoryServer.java:1226)
	at com.unboundid.ldap.listener.InMemoryDirectoryServer.importFromLDIF(InMemoryDirectoryServer.java:1198)

SOLUTION

The problem is caused by the fact that the default schema does not match Microsoft’s Active Directory schema. Hence, attribute(s), such as managedBy, would cause an error.

While we can set a modified schema, which is very convoluted, the easiest solution is to completely disable the schema:-

def config = new InMemoryDirectoryServerConfig(base)
config.setListenerConfigs(new InMemoryListenerConfig("myListener", null, port, null, null, null))
config.setSchema(null)

def server = new InMemoryDirectoryServer(config)
server.startListening()
server.importFromLDIF(true, "target/test-classes/unboundid-test-data.ldif")