This is the part 1 of a study note based on the official jOOQ document. It describes the reasons for being of jOOQ and a simple sample.
Reason for Being
SQL is big, complex and important enough to deserve a JAVA API just for it.
Why not JDBC and plain SQL:
- No typesafety
- No syntax safety
- No bind value index safety
- Verbose SQL String concatenation
- Boring bind value indexing techniques
- Verbose resource and exception handling in JDBC
- A very “stateful”, not very object-oriented JDBC API, which is hard to use
Various best practices have been implemented in jOOQ
- Typesafe database objects
- Typesafe DSL for SQL construction
- Covenient execution and result fetching
- SQL dialect abstraction
- SQL logging and debugging
jOOQ can work with Hibernate, JDBC and Spring Data. In those scenarios, it can be used as a SQL builder with or without code generation. It can execute jOOQ or plain SQL queries. It support CRUD operations.
Additionally, jOOQ ships with a lot of tools including execute listener, logging, stored procedures, batch execution, data importing and exporting.
A Quick Tutorial
We use Java 11 to create a simple sample console application.
Step 1: Database and Data
Run the following SQL to create database, table and insert data.
1
2
3
4
5
6
7
8
9
10
11
12
13
|
CREATE DATABASE `library`;
USE `library`;
CREATE TABLE `author` (
`id` int NOT NULL,
`first_name` varchar(255) DEFAULT NULL,
`last_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO author (id, first_name, last_name)
VALUES (1 , 'George' , 'Orwell');
INSERT INTO author (id, first_name, last_name)
VALUES (2 , 'Paulo' , 'Coelho');
|
Step 2: Generator Config
Create a library.xml file with the following content:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.11.0.xsd">
<!-- Configure the database connection here -->
<jdbc>
<driver>com.mysql.jdbc.Driver</driver>
<url>jdbc:mysql://localhost:3306/library</url>
<user>root</user>
<password>mysql-password</password>
</jdbc>
<generator>
<name>org.jooq.codegen.JavaGenerator</name>
<database>
<name>org.jooq.meta.mysql.MySQLDatabase</name>
<inputSchema>library</inputSchema>
<includes>.*</includes>
<excludes></excludes>
</database>
<target>
<!-- The package of generated classes within the destination directory) -->
<packageName>test.generated</packageName>
<!-- The directory of your generated classes. Using Maven directory layout here -->
<directory>/path/to/your/project/src/main/java</directory>
</target>
</generator>
</configuration>
|
Step 3: Code Generation
Download jOOQ and MySQL Connector/J. However, to use Java 11, we need to download and use some old modules. This migration guide and this SO thread have the details.
For code generation, it uses the following two:
- JAXB in the packages
javax.xml.bind.*
- the JavaBeans Activation Framework (JAF) in
javax.activation
All can be downloaded from mvn repository. Put all required jars (see the following command) in a single folder.
java -cp jooq-3.11.11.jar:jooq-meta-3.11.11.jar:jooq-codegen-3.11.11.jar:mysql-connector-java-8.0.16.jar:jaxb-api.jar:jaxb-core.jar:jaxb-impl.jar:activation.jar:. org.jooq.codegen.GenerationTool library.xml
It should generate a set of Java files based on the database data structure.
Step 4: The Project File
The build.gradle file has the following dependencies:
1
2
3
4
5
6
7
8
9
10
11
|
implementation group: 'org.jooq', name: 'jooq', version: '3.11.11'
implementation group: 'org.jooq', name: 'jooq-meta', version: '3.11.11'
implementation group: 'org.jooq', name: 'jooq-codegen', version: '3.11.11'
implementation group: 'mysql', name: 'mysql-connector-java', version: '8.0.16'
// for Java 11 and jOOQ 3.11.11 or before
implementation group: 'javax.xml.bind', name: 'jaxb-api', version: '2.3.1'
implementation group: 'javax.activation', name: 'javax.activation-api', version: '1.2.0'
implementation group: 'javax.annotation', name: 'javax.annotation-api', version: '1.3.2'
|
Step 5: The Application
After coping generated files to the project, the sample application should be up and running with the following content.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
package test.jooq
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;
import static test.generated.Tables.*;
import java.sql.*;
public class Main {
public static void main(String[] args) throws SQLException {
String userName = "root";
String password = "mysql-password";
String url = "jdbc:mysql://localhost:3306/library";
// Connection is the only JDBC resource that we need
// PreparedStatement and ResultSet are handled by jOOQ, internally
Connection conn = DriverManager.getConnection(url, userName, password);
try (conn) {
var create = DSL.using(conn, SQLDialect.MYSQL);
var result = create.select().from(AUTHOR).fetch();
for (var r : result) {
Integer id = r.getValue(AUTHOR.ID);
String firstName = r.getValue(AUTHOR.FIRST_NAME);
String lastName = r.getValue(AUTHOR.LAST_NAME);
System.out.println("ID: " + id + " first name: " + firstName + " last name: " + lastName);
}
}
// For the sake of this tutorial, let's keep exception handling simple
catch (Exception e) {
e.printStackTrace();
}
}
}
|