© The Author(s), under exclusive license to APress Media, LLC, part of Springer Nature 2023
K. S. P. Reddy, S. UpadhyayulaBeginning Spring Boot 3https://doi.org/10.1007/978-1-4842-8792-7_7

7. Working with JOOQ

Siva Prasad Reddy Katamreddy1   and Sai Subramanyam Upadhyayula2
(1)
Hyderabad, India
(2)
Rotterdam, The Netherlands
 
JOOQ (Java Object Oriented Querying) is a persistence framework that embraces SQL. JOOQ provides the following features :

  • Typesafe SQL using DSL API

  • Typesafe database object referencing using code generation

  • Easy-to-use API for querying and data fetching

  • SQL logging and debugging

This chapter covers using the Spring Boot JOOQ Starter, using the JOOQ Maven Codegen plugin to generate code from the database schema, and performing various operations.

Introduction to JOOQ

JOOQ is a Java persistence framework that provides a Fluent API to write typesafe SQL queries. JOOQ provides code generation tools to generate code based on the database schema, and you can use that generated code to build typesafe queries.

Listing 7-1 shows code to query a database. In the coming sections, we will dig deeper and see how to implement this in your Spring blog application.
String userName = "root";
String password = "admin";
String url = "jdbc:mysql://localhost:3306/test";
Class.forName('com.mysql.jdbc.Driver');
Connection conn = DriverManager.getConnection(url, userName, password);
DSLContext jooq = DSL.using(conn, SQLDialect.MYSQL);
Result<Record> result = jooq.select().from(POST).fetch();
for (Record r : result)
{
    Integer id = r.getValue(POST.ID);
    String title = r.getValue(POST.TITLE);
    String content = r.getValue(POST.CONTENT);
    System.out.println("Id: " + id + " title: " + title + " content: " + content);
}
Listing 7-1

Using the JOOQ DSL API

This code snippet created a database named Connection and instantiated the DSLContext object, which is the entry point for using JOOQ QueryDSL. Using the DSLContext object, it queried the POST table and iterated through the resultset.

You can integrate JOOQ with the Spring Framework so that you don't have to manually create a connection and instantiate DSLContext. See www.jooq.org/doc/3.17/manual-single-page/#code-generation learn how to integrate Spring with JOOQ.

Spring Boot provides JOOQ Starter to get up and running with JOOQ quickly. You do this by leveraging its autoconfiguration mechanism.

Using Spring Boot’s JOOQ Starter

Spring Boot provides a starter called spring-boot-starter-jooq , which allows you to integrate with JOOQ quickly. This section shows how to use spring-boot-starter-jooq via a step-by-step approach.

Configuring Spring Boot JOOQ Starter

Create a Spring Boot Maven-based project and add the spring-boot-starter-jooq dependency along with H2 and MySQL driver dependencies .
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jooq</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

This example uses the H2 in-memory database first. Later you will see how to use MySQL.

Database Schema

Create a simple database with two tables named POSTS and COMMENTS. Create the database creation script called src/main/resources/data.sql, as shown in Listing 7-2.
DROP TABLE IF EXISTS COMMENTS;
DROP TABLE IF EXISTS POSTS;
CREATE TABLE POSTS
(
   ID int NOT NULL AUTO_INCREMENT,
   TITLE varchar(50) NOT NULL,
   DESCRIPTION varchar(500) NOT NULL,
   BODY LONGTEXT DEFAULT NULL,
   SLUG varchar(60) DEFAULT NULL,
   POST_STATUS ENUM ('DRAFT','PUBLISHED'),
   CREATED_ON datetime DEFAULT NULL,
   UPDATED_ON datetime DEFAULT NULL,
   PRIMARY KEY (ID)
);
CREATE TABLE COMMENTS
(
   ID int NOT NULL AUTO_INCREMENT,
   POST_ID int NOT NULL,
   TITLE varchar(200) NOT NULL,
   AUTHOR_NAME varchar(200) NOT NULL,
   BODY LONGTEXT DEFAULT NULL,
   CREATED_ON datetime DEFAULT NULL,
   UPDATED_ON datetime DEFAULT NULL,
   PRIMARY KEY (ID),
   FOREIGN KEY (POST_ID) REFERENCES POSTS(ID)
);
Listing 7-2

The src/main/resources/data.sql File

Code Generation Using the JOOQ Maven Codegen Plugin

JOOQ provides the JOOQ Maven Codegen plugin to generate database artifacts using Maven goals. This section shows how to use Maven profiles to configure the jooq-codegen-maven configuration properties based on database type. See Listing 7-3.
<profiles>
   <profile>
       <id>h2</id>
       <activation>
           <activeByDefault>true</activeByDefault>
       </activation>
       <build>
           <plugins>
               <plugin>
                   <groupId>org.codehaus.mojo</groupId>
                   <artifactId>sql-maven-plugin</artifactId>
                   <version>1.5</version>
                   <executions>
                       <execution>
                           <phase>generate-sources</phase>
                           <goals>
                               <goal>execute</goal>
                           </goals>
                       </execution>
                   </executions>
                   <dependencies>
                       <dependency>
                           <groupId>com.h2database</groupId>
                           <artifactId>h2</artifactId>
                           <version>${h2.version}</version>
                       </dependency>
                   </dependencies>
                   <configuration>
                       <driver>org.h2.Driver</driver>
                       <url>jdbc:h2:~/springbootjooq</url>
                       <srcFiles>
                           <srcFile>${basedir}/src/main/resources/reset.sql</srcFile>
                           <srcFile>${basedir}/src/main/resources/schema.sql</srcFile>
                       </srcFiles>
                   </configuration>
               </plugin>
               <plugin>
                   <groupId>org.jooq</groupId>
                   <artifactId>jooq-codegen-maven</artifactId>
                   <executions>
                       <execution>
                           <goals>
                               <goal>generate</goal>
                           </goals>
                       </execution>
                   </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:~/springbootjooq</url>
                       </jdbc>
                       <generator>
                           <name>org.jooq.codegen.JavaGenerator</name>
                           <database>
                               <name>org.jooq.meta.h2.H2Database</name>
                               <includes>.*</includes>
                               <excludes/>
                               <inputSchema>PUBLIC</inputSchema>
                           </database>
                           <target>
                               <packageName>com.apress.demo.jooq.domain</packageName>
                               <directory>gensrc/main/java</directory>
                           </target>
                       </generator>
                   </configuration>
               </plugin>
           </plugins>
       </build>
   </profile>
   <profile>
       <id>mysql</id>
       <build>
           <plugins>
               <plugin>
                   <groupId>org.codehaus.mojo</groupId>
                   <artifactId>sql-maven-plugin</artifactId>
                   <version>1.5</version>
                   <executions>
                       <execution>
                           <phase>generate-sources</phase>
                           <goals>
                               <goal>execute</goal>
                           </goals>
                       </execution>
                   </executions>
                   <dependencies>
                       <dependency>
                           <groupId>mysql</groupId>
                           <artifactId>mysql-connector-java</artifactId>
                           <version>${mysql.version}</version>
                       </dependency>
                   </dependencies>
                   <configuration>
                       <driver>com.mysql.cj.jdbc.Driver</driver>
                       <url>jdbc:mysql://localhost:3306/springblog</url>
                       <username>root</username>
                       <password>mysql</password>
                       <srcFiles>
                           <srcFile>${basedir}/src/main/resources/schema.sql</srcFile>
                       </srcFiles>
                   </configuration>
               </plugin>
               <plugin>
                   <groupId>org.jooq</groupId>
                   <artifactId>jooq-codegen-maven</artifactId>
                   <executions>
                       <execution>
                           <goals>
                               <goal>generate</goal>
                           </goals>
                       </execution>
                   </executions>
                   <dependencies>
                       <dependency>
                           <groupId>mysql</groupId>
                           <artifactId>mysql-connector-java</artifactId>
                           <version>${mysql.version}</version>
                       </dependency>
                   </dependencies>
                   <configuration>
                       <jdbc>
                           <driver>com.mysql.cj.jdbc.Driver</driver>
                           <url>jdbc:mysql://localhost:3306/springblog</url>
                           <user>root</user>
                           <password>mysql</password>
                       </jdbc>
                       <generator>
                           <name>org.jooq.codegen.JavaGenerator</name>
                           <database>
                               <name>org.jooq.meta.mysql.MySQLDatabase</name>
                               <includes>.*</includes>
                               <excludes/>
                               <inputSchema>springblog</inputSchema>
                           </database>
                           <target>
                               <packageName>com.apress.demo.jooq.domain</packageName>
                               <directory>gensrc/main/java</directory>
                           </target>
                       </generator>
                   </configuration>
               </plugin>
           </plugins>
       </build>
   </profile>
</pr ofiles>
Listing 7-3

The jooq-codegen-maven Plugin Configuration in the pom. xml File

This example configures two profiles (h2 and mysql) with the appropriate JDBC configuration parameters. It generates the code artifacts and places them in the com.apress.demo.jooq.domain package within the gensrc/main/java directory.

You can run the Maven build that activates the h2 or mysql profile as follows:
mvn clean verify -P h2 (or) mvn clean verify -P mysql

Using JOOQ DSL

DSLContext is the main entry point for the JOOQ DSL API. You will see how to implement the data persistence methods using the JOOQ DSL API.

First, you create the PostRepository class with the DSLContext object injected into the class, as shown in Listing 7-4.
@Repository
@RequiredArgsConstructor
public class PostRepository {
    private final DSLContext dslContext;
    ...
    ...
}
Listing 7-4

com.apress.demo.repository.PostRepository.java

When you query the database using JOOQ, you will get a Record that represents the database record and from which you can extract the required data. The following example queries the Post table, denoted as POSTS:
@Repository
public class PostRepository
{
        ...
        ...
public Optional<Post> findOnePost(Integer postId) {
   Record postRecord = dslContext.select().
           from(POSTS)
           .where(POSTS.ID.eq(postId))
           .fetchOne();
   if (postRecord != null) {
       return Optional.of(getPostEntity(postRecord));
   }
   return Optional.empty();
}
private Post getPostEntity(Record r) {
   Integer id = r.getValue(POSTS.ID, Integer.class);
   String title = r.getValue(POSTS.TITLE, String.class);
   String description = r.getValue(POSTS.DESCRIPTION, String.class);
   String body = r.getValue(POSTS.BODY, String.class);
   String slug = r.getValue(POSTS.SLUG, String.class);
   Post post = new Post();
   post.setId(id);
   post.setTitle(title);
   post.setDescription(description);
   post.setBody(body);
   post.setSlug(slug);
   return post;
}
}
In this code example, under the findOnePost() method , you run a SELECT command on the POSTS table to retrieve the posts that match the given postId. The logic inside this method can be translated into the following SQL query :
select `springblog`.`posts`.`ID`, `springblog`.`posts`.`TITLE`, `springblog`.`posts`.`DESCRIPTION`, `springblog`.`posts`.`BODY`, `springblog`.`posts`.`SLUG`, `springblog`.`posts`.`POST_STATUS`, `springblog`.`posts`.`CREATED_ON`, `springblog`.`posts`.`UPDATED_ON` from `springblog`.`posts` where `springblog`.`posts`.`ID` = 1

So now that you have viewed the SQL query, let’s see how the JOOQ DSL API correlates with the above query.

The SELECT command operation is taken care of by the dslContext.select() method from the JOOQ DSL API, followed by the from() and where() method calls to denote the rest of the SQL query. Finally, you use the fetchOne() method to fetch the result set from the database, and in the getPostEntity() method, you map the result set values from the database to your Post Entity.

Now let’s implement the methods to insert a new Post in PostRepository.java as follows:
public void addPost(Post post) {
   dslContext.insertInto(POSTS)
           .set(POSTS.TITLE, post.getTitle())
           .set(POSTS.DESCRIPTION, post.getDescription())
           .set(POSTS.BODY, post.getBody())
           .set(POSTS.SLUG, post.getSlug())
           .returning(POSTS.ID)
           .fetchOne();
}

The example uses the dsl.insertInto() method to insert a new record and specifies the returning() method to return the auto-generated primary key column value. It also calls the fetchOne() method to return the newly inserted record.

Now implement fetching a list of Posts using JOOQ DSL as follows:
public List<Post> findAllPosts() {
   List<Post> posts = new ArrayList<>();
   Result<Record> recordResult = dslContext.select().from(POSTS).fetch();
   for (Record r : recordResult) {
       posts.add(getPostEntity(r));
   }
   return posts;
}

Similar to the fineOnePost() method, the findAllPosts() method fetches all rows from the POSTS table using dsl.select().from(), which returns Result<Record>. The example loops through Result<Record> and converts the record into a Post domain object using the getPostEntity() utility method you created earlier.

Finally, let’s implement the method to delete a comment in the CommentRepository.java file :
public void deletePost(Integer postId) {
   dslContext.deleteFrom(POSTS).where(POSTS.ID.equal(postId)).execute();
}

You don’t need to make any changes to the PostService.java class, as all the changes are done inside the PostRepository.java class methods.

You have learned how to perform various operations, like inserting new records and querying and deleting records using JOOQ DSL.

Assuming you have generated code using the H2 profile, you can run the application without any further configuration. But if you have generated code using the Mysql profile, you will have to configure the following properties in application.properties :
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=admin
spring.jooq.sql-dialect=MYSQL

After adding the above properties, start the application and go to http://localhost:8080/ and add some new posts. You should see that the data is persisted successfully using JOOQ.

Next, let’s write some tests for the Repository classes , as shown in Listing 7-5.
@JooqTest
@Import(PostRepository.class)
class PostRepositoryJooqTest {
   @Autowired
   private PostRepository postRepository;
   @Test
   void findAllPosts() {
       List<Post> posts = postRepository.findAllPosts();
       assertNotNull(posts);
       assertFalse(posts.isEmpty());
   }
   @Test
   void findPostById() {
       Post post = postRepository.findOnePost(1)
               .orElseThrow(() -> new IllegalArgumentException("Cannot find any post withd id 1"));
       assertNotNull(post);
   }
   @Test
   void createPost() {
       Post post = new Post();
       post.setTitle("Test");
       post.setDescription("Test");
       postRepository.addPost(post);
       assertTrue(postRepository.findAllPosts()
               .stream()
               .anyMatch(savedPost -> savedPost.getTitle().equals(post.getTitle())));
   }
}
Listing 7-5

PostRepositoryJooqTest.java

The PostRepositoryJooqTest.java test class spins up the Spring application context with beans required to test the JOOQ-related logic, with the help of the @JooqTest. This way, you don’t need to load the whole application context to test your Repository layer. These are called test slices. You will learn more about them in Chapter 14.

Note

Use the correct SQL dialect for the database; otherwise, you may get SQL syntax errors at runtime.

For more info on JOOQ, visit www.jooq.org/learn/ .

Summary

This chapter explained how to use the JOOQ framework by using the Spring Boot JOOQ Starter. The next chapter explains how to work with JPA (with the Hibernate implementation) in your Spring Boot applications.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.224.39.74