On a recent Spring project I decided to give JOOQ a try. Once I figured out how to configure my pom.xml file to run both Flyway and JOOQ, and got used to the JOOQ syntax and object model, I soon decided that I would never go back to using an ORM. Where an ORM strives to hide SQL from you, JOOQ embraces SQL giving you a DSL that wraps your database in easy-to-use and type safe Java objects. If you want to give JOOQ a try, this blog is meant to get you up and running quickly with Spring Boot, JOOQ, and Flyway. Full source code for this project can be found here.
1. Configuring the pom.xml file.
Let’s start with the configuration of the pom.xml file. We need to make sure that Flyway runs before JOOQ so that JOOQ has the latest database from which to generate code. And both need the properties used to connect to the database. I prefer yml, so I use a library to load the application.yml properties. So the build order is:
- Load the properties
- Run flyway
- Run JOOQ Code gen
First we import the required libraries
Then we configure the build order
The properties are configured at src/main/resources/db/application.yml
2. Configuring Flyway
Next we configure flyway. In this script we’ll create a single table for illustrative purposes.
3. Create a domain object
Next we’ll make a POJO that we will convert to JSON to pass to the front end. Note JOOQ can automatically generate a set of POJOs for you. If the JSON model that is returned is the same as the database, this can save time. In my case there were differences between the JSON the front end was expecting and the database tables, so I created my own POJOs. With lombok the writing of these POJOs is fairly painless.
4. Setup Model Mapper
In order to allow services to autowire the ModelMapper, we add the Bean to the Application.
5. Create the service.
The Book Service will connect through JOOQ to access the data in the database. The JOOQ objects returned from a query will be mapped into our domain object using Model Mapper.
6. Create a controller
Finally we create a controller that uses our service to get data from the database and returns JSON.
And that’s it. You should now have a basic project stood up using Spring Boot, Flyway, JOOQ, and ModelMapper. Again full source code can be found here.