JOOQ overview, setup and code generation

Introduction

JOOQ is an acronym for Java Object oriented query. It is framework which is built on top of a functional programming, which helps increase in the readability of the code. The library generates Java classes based on the database tables and various constraints. Which let us create type safe queries through API. In this tutorial we are going to set up the SpringBoot and JOOQ project. This will let us generate classes automatically. And ultimately we will be able to test it with the DSLContext.

Dependency for JOOQ

We need to add the below dependency in the pom file of our spring boot project. For gradle please check in the mvn central repository.

	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-jooq</artifactId>
	</dependency>

Steps for making changes in database

Since, we are going to generate the classes, so for that we need to create the database schema like all the tables for which we want to generate pojo files and all the constraints etc. And then once we create build then these changes will be automatically picked by the JOOQ. And as a result JOOQ will generate classes for us. For this we are going to use mysql.

1 ) Create database

For the demonstration purpose we are going to create the university database, by using the below mysql query.

CREATE DATABASE university;

2) Create Table

JOOQ uses tables to create the pojos, and column name to create the fields in the pojos. For this we are going to create the student table by using the below mysql query.

CREATE TABLE student (
    id int,
    last_name varchar(255),
    first_name varchar(255),
    branch_name varchar(50)
);

Changes in the pom.xml

We also need to add the JOOQ plugin and give some JOOQ configuration in the pom file which mainly contains the database related information and JOOQ generate files related information like package name and when to generate the files(like, every time when we compile the whole project etc).

<plugin>
  <groupId>org.jooq</groupId>
  <artifactId>jooq-codegen-maven</artifactId>
  <executions>
    <execution>
      <id>jooq-codegen</id>
      <phase>generate-sources</phase>
      <goals>
        <goal>generate</goal>
      </goals>
      <configuration>
	<!--Insert your DB configuration-->
	<jdbc>
	  <driver>com.mysql.cj.jdbc.Driver</driver>
	  <url>jdbc:mysql://localhost:3306/university_database</url>
	  <user>root</user>
	  <password></password>
        </jdbc>
        <generator>
	  <database>
	    <name>org.jooq.meta.mysql.MySQLDatabase</name>
	    <includes>.*</includes>
	    <excludes></excludes>
	    <inputSchema>university_database</inputSchema>
	  </database>
	  <generate>
	    <pojos>true</pojos>
	    <pojosEqualsAndHashCode>true</pojosEqualsAndHashCode>
	    <javaTimeTypes>true</javaTimeTypes>
	    <fluentSetters>true</fluentSetters>
	  </generate>
	  <target>
	    <packageName>
	      com.knoldus.techhub.jooqgenerated
	    </packageName>
	    <directory>
	      src/main/java
	    </directory>
	  </target>
	</generator>
      </configuration>
    </execution>
  </executions>
</plugin>

Now to generate the JOOQ classes, we need to run the below given command. This command will generate the JOOQ classes every time we compile the whole project, this can be configured in the configuration section in the plugins in pom file.

mvn clean install

Using DSLContext to interact with the databases

we need to inject the DSLContext in the student repository in order to interact with the Student table in the university database. For example

@Autowired
private DSLContext dslContext;

Now we can use the dslContext to write the query using the JOOQ generated classes. For Example below query selects data from the university database and student table.

public List<Student> getStudents() {
    return dslContext.selectFrom(Tables.STUDENT)
            .fetchInto(Student.class);
}