Appendices

H2 Database

H2 (project page, or on Wikipedia) is a SQL database implemented in Java with couple of features that are very handy especially for testing:

• Firstly, H2 is all packed in a single JAR which weights way under 2 MB and contains features you would not expect in such a cute packaging.
• It can store its tables both in-memory and on the disk. Using in-memory storage is great for tests, because it’s fast (for reasonable database size) and also naturally disappears after the test is run.
• It supports both embedded mode – running in-process with the application itself – and client-server mode where it can run standalone and you can connect to it from different process. For automated tests I prefer embedded mode, but when I experiment I use client-server mode, so I can check the state of my database independently from my demo application.
• It contains various tools – most prominent of which is web-based SQL console. This is what you can use to check the database in server mode while it’s being modified by an application.
• Naturally, it contains JDBC driver.
• SQL support is rich enough to mimic much more evolved/expensive databases, I used it to test JPA applications that were originally developed for Oracle or Microsoft SQL Server – no changes besides the JPA configuration were required.
• While it does not support any scripting (like Oracle’s PL/SQL or PostgreSQL PL/pgSQL) you can plug triggers and procedures developed in Java. Maybe I’d not use it if the database contained a lot of code, but when you need to handle couple of simple procedures and some triggers, it can be done.

Starting it

You can download it as a Windows Installer or multi-platform ZIP, but all you really need is the JAR file. If you manage your application’s dependencies you probably have it somewhere on the disk already. So just locate it and run it. If everything is set up properly, just pressing Enter on the JAR in some file manager will do. If not, try the following command:

$ java -jar $HOME/.m2/repository/com/h2database/h2/1.4.190/h2-1.4.190.jar

Of course, adjust the JAR location and name, and if you don’t have java on your PATH (yet!) do something about it. When everything clicks your default browser will open and you can log into your default database (user sa, empty password). You can learn more in this Quickstart. I probably forgot to mention that it is well documented too!

Using it as a devel/test DB

I have a long relationship with H2 and I can only recommend it. In 100% of projects we used it as a developer database – if for nothing else than for running fast commit tests at least – everything was better and smoother. There is some overhead involved when we had to manage alternative schema and test data for H2 as well, but a lot of it can be automated (Groovy is my language of choice for this). But this overhead was always offset by the benefits for testing and the fact that we looked at our schema through the perspective of two different RDBMS (production one and H2) actually often helped too.

Project example

Setting up project with JPA, Querydsl and H2 as a database may seem daunting task for a newcomer, but it’s not really that difficult. I will show both Maven 3.x and Gradle 4.x projects using Java 8.

Querydsl and Maven

Maven is supported out-of-the-box with a plugin as documented here.

First we need to declare our dependencies (provided scope for APT JAR is enough).

 1 <dependency>
 2   <groupId>com.querydsl</groupId>
 3   <artifactId>querydsl-apt</artifactId>
 4   <version>${querydsl.version}</version>
 5   <scope>provided</scope>
 6 </dependency>
 7 
 8 <dependency>
 9   <groupId>com.querydsl</groupId>
10   <artifactId>querydsl-jpa</artifactId>
11   <version>${querydsl.version}</version>
12 </dependency>
13 
14 <dependency>
15   <groupId>org.slf4j</groupId>
16   <artifactId>slf4j-log4j12</artifactId>
17   <version>1.6.1</version>
18 </dependency>

Next we need to configure the Maven APT plugin.

 1 <project>
 2   <build>
 3   <plugins>
 4     ...
 5     <plugin>
 6       <groupId>com.mysema.maven</groupId>
 7       <artifactId>apt-maven-plugin</artifactId>
 8       <version>1.1.3</version>
 9       <executions>
10         <execution>
11           <goals>
12             <goal>process</goal>
13           </goals>
14           <configuration>
15             <outputDirectory>target/generated-sources/java</outputDirectory>
16             <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
17           </configuration>
18         </execution>
19       </executions>
20     </plugin>
21     ...
22   </plugins>
23   </build>
24 </project>

This assures that the Q-classes will be generated during the build. There is also alternative processor to generate these from Hibernate annotations. Most example projects for this book have pom.xml files like this.

Unrelated to Querydsl is the plugin configuration for generating JPA metadata. Here you can use various providers to do so. In querydsl-basic/pom.xml I use EclipseLink’s annotation processor.

IDE support

As per instructions, for Eclipse to recognize the generated classes one should run:

1 mvn eclipse:eclipse

I have no idea how the regeneration of Q-classes works as I don’t use Eclipse.

In IntelliJ IDEA the generated classes are recognized when the Maven project is imported. To generate the classes after an entity source was changed we need to set up annotation processor in Settings > Build, Execution, Deployment > Compiler > Annotation Processors.

Querydsl and Gradle

Next I will show an example of Gradle project. I didn’t use any particular plugin for Querydsl although there are some out there, but the situation around Gradle and its plugins is much more volatile hence I decided to do it without a plugin. If you’re familiar with Gradle it should be easy to modify example scripts.

I experimented with various scripts in querydsl-basic project that – as mentioned above with Maven – generates also JPA metadata. Probably most typical Gradle script would be build-traditional.gradle – let’s go over it bit by bit:

 1 plugins {
 2   id 'java'
 3 }
 4 
 5 repositories {
 6   jcenter()
 7 }
 8 
 9 configurations {
10   querydslApt
11   jpaMetamodelApt
12 }

So far nothing special, java plugin will be sufficient, we just need additional configuration for each generate task (querydslApt and jpaMetamodelApt).

1 sourceCompatibility = 1.8
2 targetCompatibility = 1.8
3 
4 tasks.withType(JavaCompile) {
5   options.encoding = 'UTF-8'
6 }

Next we just configure Java version and encoding for compilation (this should always be explicit). Next we’ll set up dependencies:

 1 ext {
 2   querydslVersion = '4.1.4'
 3   hibernateVersion = '5.2.2.Final'
 4   eclipseLinkVersion = '2.6.2'
 5   h2Version = '1.4.190'
 6   logbackVersion = '1.2.3'
 7   testNgVersion = '6.11'
 8 }
 9 
10 dependencies {
11   compileOnly 'javax:javaee-api:7.0'
12   compile "com.querydsl:querydsl-jpa:$querydslVersion"
13   compile "org.hibernate:hibernate-entitymanager:$hibernateVersion"
14   compile "org.eclipse.persistence:org.eclipse.persistence.jpa:$eclipseLinkVersion"
15   compile "com.h2database:h2:$h2Version"
16   compile "ch.qos.logback:logback-classic:$logbackVersion"
17 
18   testCompile "org.testng:testng:$testNgVersion"
19 
20   querydslApt "com.querydsl:querydsl-apt:$querydslVersion"
21   jpaMetamodelApt "org.eclipse.persistence:" +
22     "org.eclipse.persistence.jpa.modelgen.processor:$eclipseLinkVersion"
23 }

Dependencies needed only for generator tasks are in their respective configurations – we don’t need these to run our programs after build.

 1 sourceSets {
 2   generated {
 3     java {
 4       srcDirs = ["$buildDir/generated-src"]
 5     }
 6   }
 7   test {
 8     // This is required for tests to "see" generated classes as well
 9     runtimeClasspath += generated.output
10   }
11 }

Here we defined new source set for generated classes. I’m not sure what’s the best practice, whether different set for each generator would be better or not and why, so I’ll leave it like this. Next the generator tasks – they are both very similar and definitely could be refactored and put into plugins, but that’s beyond the scope of this book and my current experience:

 1 task generateQuerydsl(type: JavaCompile, group: 'build',
 2   description: 'Generates the QueryDSL query types')
 3 {
 4   source = sourceSets.main.java
 5   classpath = configurations.compile + configurations.querydslApt
 6   options.compilerArgs = [
 7     '-proc:only',
 8     '-processor', 'com.querydsl.apt.jpa.JPAAnnotationProcessor'
 9   ]
10   destinationDir = sourceSets.generated.java.srcDirs.iterator().next()
11 }
12 
13 task generateJpaMetamodel(type: JavaCompile, group: 'build',
14   description: 'Generates metamodel for JPA Criteria (not QueryDSL)')
15 {
16   source = sourceSets.main.java
17   classpath = configurations.compile + configurations.jpaMetamodelApt
18   options.compilerArgs = [
19     '-proc:only',
20     '-processor',
21     'org.eclipse.persistence.internal.jpa.modelgen.CanonicalModelProcessor',
22     '-Aeclipselink.persistencexml=src/main/resources/META-INF/persistence.xml',
23     '-Aeclipselink.persistenceunits=demo-el'
24   ]
25   destinationDir = sourceSets.generated.java.srcDirs.iterator().next()
26 }

Finally we want these tasks to interplay nicely with the rest of the build:

 1 compileJava {
 2   dependsOn generateQuerydsl
 3   dependsOn generateJpaMetamodel
 4   source generateQuerydsl.destinationDir
 5   source generateJpaMetamodel.destinationDir
 6 }
 7 
 8 compileGeneratedJava {
 9   dependsOn generateQuerydsl
10   dependsOn generateJpaMetamodel
11   options.warnings = false
12   classpath += sourceSets.main.runtimeClasspath
13 }
14 
15 test {
16   useTestNG()
17 }

That’s it. This all works, but I encountered a minor problem with Hibernate if we rely on entity automatic discovery. This happens only within a single JAR in which the persistence.xml was found. However, Gradle builds projects to a different structure than Maven and keeps resources and classes as separate directories – where a directory as a classpath entry effectively means separate JAR. So if you download the project go to manuscript/examples/querydsl-basic and run:

1 gradle -b build-traditional.gradle clean build

You will see a failure of DemoTest (very simple TestNG-based test class) for Hibernate test. This can be fixed by listing the classes in persistence.xml – which would actually be compliant with how JPA works in Java SE environment – or you need somehow make build/resources and build/classes be one.

This actually is not that complicated and that’s what gradle.build is like in the end (including a long explanation comment in it). All we need to do is add three lines into sources sets:

1 sourceSets {
2   // these three lines is the only difference against querydsl-traditional.build
3   main {
4     output.resourcesDir = "$buildDir/classes/java/main"
5   }
6   //... the rest is the same

This is a very subtle change, but very effective and I didn’t notice any other negative side effects. I can’t comment on the effect of this change on more complicated big projects though. I further tried to make even more drastic change and declare generated source directory as part of Java main source set:

1 sourceSets {
2   main {
3     // This is actually not recommended, read on...
4     java.srcDir file(generatedSrc)

This also works, sort of, but the build is not able to detect changes properly and always runs the generation, even when everything is up-to-date. You can try this build, it’s in build-extreme.gradle, but it seems to be obviously not well behaved Gradle build.

IntelliJ IDEA support

Gradle support in IntelliJ IDEA is obviously constantly getting better and depending on the used version of the tools (I used Gradle 4.0, 4.1 and 4.2 on IDEA 2017) it’s not always easy to figure out how well it should work. Recently I saw a breathtaking presentation of Kotlin DSL support in Gradle (build file named gradle.build.kts) but I simply wasn’t able to reproduce it on my projects build from scratch. The same I remember for reasonable support for Gradle DSL years ago, but this seems to be much better now.

We can either import an existing sources as new IDEA module from external Gradle model or create new Gradle module. In either case after Gradle project refresh IDEA understands the project model quite well. There is a Settings option for Gradle called “Create separate module per source set” which is enabled by default but I personally like it disabled to get just one IDEA module per demo (like with Maven).

There is also interesting settings in Gradle/Runner called “Delegate IDE build/run actions to gradle” and this one I actually prefer. This completely bypasses IDEA’s internal build and calls Gradle build for any build/compilation and to run application or tests as well.

As we discussed the hiccup with Hibernate’s scan for entities this affects IDEA’s internal build mechanism even with the right Gradle build. While with Maven IDEA builds into its target directory with Gradle IDEA uses its own out directory because reportedly Gradle’s and IDEA’s way of building are way different and would not work if they stepped on each other’s toes. This is exactly what that “Delegate…” setting option fixes. Builds are pretty fast (it’s Gradle after all!) and it feels good when you know that you use the same build in IDE and on your CI as well.

Bugs discovered while writing this book

To demonstrate the dark side of JPA, I’ll point to a couple of bugs I discovered while writing this book (or those that lead to it). With a single ORM you have no choice to switch – with many you always trade some bugs (most of the relevant ones known to you already) for new bugs. If you have good test coverage, you may be able to make a qualified decision. Otherwise you’ll find the bugs later and randomly (or your users will).

Some bugs are closed already, I added them to show it makes sense to report them. Other bugs, on the other hand, give you the feeling it doesn’t make sense to report anything – they are open for years and nothing is happening. ORM and JPA are not different from any other projects and with open source implementation you’ll get some transparency at least. Not all bugs are reported by me originally, but if I participated, commented and created a test case they are included. In any case these are all bugs that were encountered on a single Java project.

• JPA 2.1 FUNCTION not supported? (Hibernate) I reported this bug after trying this JPA 2.1 feature successfully on EclipseLink. Hibernate provides custom functions in HQL/JPQL, but this requires additional configuration. Whether this is against the standard JPA 2.1 is questionable (section 4.6.17.3 on custom functions does not say “no additional configuration must be required” after all) but Hibernate’s grammar has also a bug and does not support function calls without arguments.
• Wrong SQL generated for delete with where crossing tables (Hibernate) Open in Apr 2015, but older linked bug was open in May 2012. Works on EclipseLink.
• Bad SQL result for method countDistinct with join from criteria API This got reported for EclipseLink, however, it is not a JPA 2.1 bug as the specification explicitly says: “The use of DISTINCT with COUNT is not supported for arguments of embeddable types or map entry types.” It is just so unexpected it got reported by someone. This is a big blow for many reasons. You either cannot use embeddables, or you cannot count such cases in the query. This means you actually have to list it (you can use DISTINCT then) and then check the size of the result. This may not be possible for long lists. Finally, while in SQL you can go around this using SELECT in FROM clause, JPA does not allow this (yet). By the way, Hibernate supports this (I mean distinct in count, not select in from clause), even against explicit “not supported”. It seems to be a violation of JPA specification (although handy) and it may become a problem when migrating to another provider. (Question is what “not supported” means exactly in terms of words like MUST or MUST NOT used in RFCs. I understand leaving the feature in your ORM, maybe some compatibility switch/property could control the behaviour.)
• Stream API gets no results (EclipseLink) This one is fixed already. It caused that streams based on lazy lists were empty. It was easy to work around it – just stream the copy of the list – but it was annoying and unexpected. Reason for the bug was the fact that IndirectList (lazy list implementation) extended from Vector, (probably some legacy reasons, JPA does not explicitly mentions Vector as specially supported collection), but ignored invariants of the superclass and its protected fields. This shows two things – first, when you extend something you should follow through completely with its contract, and second, Vector was badly designed back in 1995 or so. Oh, and third, prefer delegation instead of extension, of course. That way you can often avoid both previous pitfalls.
• Abstract MappedSuperclass in separate JAR is not weaved statically (EclipseLink) This relates to modularity and may be fixed in version 2.7.
• JPQL Case operator is not thread-safe (EclipseLink) CASE in queries cause randomly either ArrayIndexOutOfBoundsException in EclipseLink stack or SQLException because the CASE clause is serialized wrongly in the SQL. As a concurrency problem it shows very randomly, more likely for bigger/longer queries. No response from EclipseLink team so far, so we just synchronize around queries where we absolutely need to use CASE.
• H2 dialect generates FOR UPDATE before LIMIT/OFFSET (EclipseLink) This one is about particular database vendor SQL support. In H2 you need to say FOR UPDATE after LIMIT/OFFSET combo, but EclipseLink does not respect this syntactic detail. Fix is currently available only as a patch, you can implement your own custom H2Platform (very small class) and use that one.
• Deleting in a ManyToManyRelation with a JoinTable with referencedColumn != name in JoinColumn is not working (EclipseLink) This one is rather tricky and we hit it when we migrated one table from composite PK to a single one. First we just introduced new IDENTITY column, we marked it as @Id in entity and everything worked fine… except for a @ManyToMany collection that was mapped using the two @JoinColumns from the original id (and in DB it was still an ID). This collection did not delete the entries from association table (not mapped as an entity). This actually did not relate to the number of columns, merely to the fact that they were not @Id columns anymore. JPA specification does not mention any exception like this.
• ON for LEFT JOIN across association tables not generated in SQL (EclipseLink) When using additional ON conditions for LEFT JOIN these don’t get generated into SQL if the LEFT JOIN follows @ManyToMany or @OneToMany with association table. Works fine if the collection is mapped by a single FK on the entity contained in the collection. Workaround is to explicitly map the association table or using different way to get to the result – possibly more queries, because LEFT JOIN is difficult to rewrite without losing some results. Should work according to specification and works fine with Hibernate.
• Coalesce ignores converters (EclipseLink) When you want to coalesce value of converted column like LocalDate to some default value (e.g. LocalDate.now()) the query fails on JDBC driver level (JTDS), or later when it encounters a row where it needs to apply the default (in H2), or possibly succeeds if no such row is found – which is probably even more sneaky. In any case, value is passed into JDBC without conversion and is treated as raw object, not as a date type. Funny enough, if we try to call it with java.sql.Date instead, EclipseLink complaints right away that the types in coalesce are not compatible. Works fine with Hibernate.

List of acronyms

• API – application programming interface
• APT – Java Annotation Processing Tool (can be used to generate sources from metadata)
• BLOB – binary large object
• CLOB – character large object
• CPU – central processing unit (processor)
• CQRS – command query responsibility segregation (pattern)
• CRUD – create, read, update and delete
• DB – database
• DBA – database administrator
• DCI – data, context and interaction (pattern)
• DDD – domain-driven design
• DDL – data definition language or data description language (e.g. CREATE statements)
• DML – data manipulation language (e.g. SELECT and UPDATE statements)
• DRY – don’t repeat yourself (principle)
• DSL – domain-specific language
• DTO – data transfer object
• EE – enterprise edition (typically meaning Java EE or older J2EE)
• EJB – Enterprise JavaBean
• EM – entity manager (or class EntityManager)
• EMF – entity manager factory (or class EntityManagerFactory)
• E-R – entity-relationship (model)
• FK – foreign key
• GC – garbage collection (or collector, from context)
• GORM – Grails Object Relational Mapper
• HTTP – Hypertext Transfer Protocol
• ID – identifier
• IDE – integrated development environment
• JAR – Java Archive
• JDBC – Java Database Connectivity
• JPA – Java Persistence API
• JPQL – Java Persistence Query Language
• JSR – Java Specification Requests
• JTA – Java Transaction API
• JVM – Java virtual machine
• LOB – large object
• OO – object-oriented
• OOP – object-oriented programming
• ORM (also O/RM, or O/R mapping) – object-relational mapping
• OSGi – Open Service Gateway Initiative
• OSIV – Open Session in View (antipattern)
• OSS – open-source software
• OWASP – Open Web Application Security Project
• PK – primary key
• POM – Project Object Model (in Maven)
• QL – query language (in general)
• RDBMS – relational database management system
• SpEL – Spring Expression Language
• SQL – Structured Query Language
• SRP – single responsibility principle
• SVN – Subversion
• UI – user interface
• URL – Uniform Resource Locator
• WAR – Web application ARchive
• XML – Extensible Markup Language