Java Object Oriented Querying (jOOQ) is a popular product from Data Geekery which generates Java code from your database, and lets you build type safe SQL queries through its fluent API. Both the commercial and open source editions can be used with Spring Boot.
In order to use jOOQ type-safe queries, you need to generate Java classes from your
database schema. You can follow the instructions in the
jOOQ user manual.
If you are using the jooq-codegen-maven
plugin (and you also use the
spring-boot-starter-parent
“parent POM”) you can safely omit the plugin’s <version>
tag. You can also use Spring Boot defined version variables (e.g. h2.version
) to
declare the plugin’s database dependency. Here’s an example:
<plugin> <groupId>org.jooq</groupId> <artifactId>jooq-codegen-maven</artifactId> <executions> ... </executions> <dependencies> <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> <version>${h2.version}</version> </dependency> </dependencies> <configuration> <jdbc> <driver>org.h2.Driver</driver> <url>jdbc:h2:~/yourdatabase</url> </jdbc> <generator> ... </generator> </configuration> </plugin>
The fluent API offered by jOOQ is initiated via the org.jooq.DSLContext
interface.
Spring Boot will auto-configure a DSLContext
as a Spring Bean and connect it to your
application DataSource
. To use the DSLContext
you can just @Autowire
it:
@Component public class JooqExample implements CommandLineRunner { private final DSLContext create; @Autowired public JooqExample(DSLContext dslContext) { this.create = dslContext; } }
Tip | |
---|---|
The jOOQ manual tends to use a variable named |
You can then use the DSLContext
to construct your queries:
public List<GregorianCalendar> authorsBornAfter1980() { return this.create.selectFrom(AUTHOR) .where(AUTHOR.DATE_OF_BIRTH.greaterThan(new GregorianCalendar(1980, 0, 1))) .fetch(AUTHOR.DATE_OF_BIRTH); }
You can customize the SQL dialect used by jOOQ by setting spring.jooq.sql-dialect
in
your application.properties
. For example, to specify Postgres you would add:
spring.jooq.sql-dialect=Postgres
More advanced customizations can be achieved by defining your own @Bean
definitions
which will be used when the jOOQ Configuration
is created. You can define beans for
the following jOOQ Types:
ConnectionProvider
TransactionProvider
RecordMapperProvider
RecordListenerProvider
ExecuteListenerProvider
VisitListenerProvider
You can also create your own org.jooq.Configuration
@Bean
if you want to take
complete control of the jOOQ configuration.