Play-Java sbt jOOQ seed

This seed makes use of the sbt-jooq plugin. The older version of this seed uses the meta-build method (without plugin). You may find it under the branch play2.7_java11_no-plugin.

How to run

1. The database used in the seed is PostgreSQL 12.3. Change your driver and DB username + password, jdbc.url, and generator.database.inputSchema in /conf/jooq-codegen.xml.
2. Also change application.conf accordingly.
3. Change database dialect (the SQLDialect enum) in jooq.Database and jooq.JooqDBModule to use database-specific features.
4. Run the sql scripts to generate database schema.
5. Run the task jooqCodegen which would read the schema and generate classes corresponding to the database tables into the folder /target/scala-2.13/src_managed/main.
6. Now the project should compile properly with these jOOQ generated classes.

Notes

1. The seed was tested using Java 14.0.1, Play 2.8.2, PostgreSQL 12.3.

2. The seed makes use of PostgreSQL's CITEXT. To work with PostgreSQL's CITEXT, you need to configure a <forcedType> for the code-generator to generate the right type (String instead of Object) and also set in application.conf the connection parameter stringtype = unspecified. You can find more details here. This usage is shown in the seed.

3. The seed makes use of jOOQ's "returning" feature which automatically refreshes all columns of the record that was just inserted/updated.

   • This is very useful when you have columns that you want to rely on the database to generate the default value for. Using this setting, the columns that are auto-generated by DB is "synchronized" automatically.

   • Since PostgreSQL supports this feature, this is done in a single query. The setting can be found in /conf/jooq-settings.xml under the <returnAllOnUpdatableRecord>true</returnAllOnUpdatableRecord> tag.

4. The custom class jooq.Database is injected when play.db.Database is requested, in place of the original play.db.DefaultDatabase provided out-of-the-box by Play.

   • This is to provide additional methods that would provide you with with a DSLContext instead of a java.sql.Connection for convenience.

   • In addition, at the start of a transaction, the Connection is put into a ThreadLocal so that we can inject DAOs directly. The DAOs have 2 constructors:

     • One for DI: requiring a Provider<DSLContext>. Calling the Provider<DSLContext>.get() will always give us a DSLContext based on the latest Connection (however be careful when reasoning in nested transaction, the same DAO instance will use different Connection in the outer and inner transaction).
     • Another constructor for manual instantiation: useful to demarcate nested transaction (for example you want to instantiate 2 different DAOs manually to differentiate between the inner and outer DAOs), and also for use in places where you don't want to use DI.

   • When using the transactional methods in jooq.Database, if a jooq.NoRollbackException was thrown, the transaction would still be committed (and exception rethrown). This may be useful in cases where after having done some important updates/inserts, you want to do some view processing in the same transaction before exiting the transaction. With this, you can wrap the view processing part with try-catch and re-throw with NoRollbackException to make sure the inserts/updates are committed. It should be noted, however, this is in contrast with the practice of layering normally seen when applying Domain Driven Design (where service layer typically returns DTOs and close the transaction and the view layer would operate on these DTOs returned by service layer).

5. By default, jOOQ generates Pojos for each table. These pojo classes are especially useful for quick mapping to JSON. They are named after the table by default.

   • On the other hand, the entities/aggregates in DDD are usually named after the table as well. For e.g. a Person aggregate is also named Person, which clashes with the default naming of the Pojo class generated by jOOQ.

   • I find that renaming the Pojo classes to something else removes this confusion and also highlights the fact that they are Pojos.

   • As such, a custom org.jooq.codegen.GeneratorStrategy is included to postfix the word "Pojo" to pojo classes generated by jOOQ. This GeneratorStrategy class is defined at /src/jooq-codegen/java/play.java.jooq.codegen.PostfixPojoClassGeneratorStrategy.

   • You can hardcode the full classname in jooq-codegen.xml or provide a sbt settingKey for the sbt-jooq plugin to resolve the value.

   • Why is the custom generator defined at a seemingly random folder? Logically, our custom GeneratorStrategy class needs to make it into the classpath before the command jooqCodegen is run, similar to how the JDBC driver needs to be added to the libraryDependencies setting at the JooqCodegen configuration scope. To do this, we can make use of the setting JooqCodegen / unmanagedSourceDirectories. The unmanaged source directories (the output of this setting) typically looks like this (on Windows):

     • C:\path-to-my-project-folder\src\jooq-codegen\scala-2.13

     • C:\path-to-my-project-folder\src\jooq-codegen\scala

     • C:\path-to-my-project-folder\src\jooq-codegen\java

     • Hence, any sources put under these folders should be compiled by SBT and included in the classpath for running the command jooqCodegen (you should see them under /target/scala-2.13/jooq-codegen-classes).

6. The seed shows usages of Java enums that are mapped to PostgreSQL custom types. If you are using MySQL, you are most likely mapping Java enum to MySQL VARCHAR instead. You would need to change the codegen settings accordingly.

7. The seed is configured in a traditional synchronous IO style with a large thread pool, as per https://www.playframework.com/documentation/2.8.x/ThreadPools#Highly-synchronous.