This article is part of our Academy Course titled jOOQ – Type safe DB querying.
jOOQ is a good choice in a Java application where SQL and the specific relational database are important. It is an alternative when JPA / Hibernate abstract too much, JDBC too little. It shows, how a modern domain-specific language can greatly increase developer productivity, internalising SQL into Java.
In this course, we’ll see how we can efficiently query databases using jOOQ. Check it out here!
1. Setup of the course project
In this section, we’ll learn how to properly set up the course project, which you can obtain from here: https://github.com/jOOQ/jOOQ/tree/master/jOOQ-examples/jOOQ-academy.
In order to run this course’s examples directly from GitHub, you will need to install:
- Git (http://git-scm.com)
- Maven (http://maven.apache.org)
- Java 8 (http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html)
If you already have your favourite IDE installed (e.g. Eclipse, IntelliJ, or NetBeans), you can easily import the jOOQ Academy Maven project directly from GitHub. All you have to do to install the database and run the jOOQ code generator is call:
mvn clean install
2. The example H2 database
The whole course will run queries against a simple H2 example database, whose source code can be obtained from here: https://github.com/jOOQ/jOOQ/blob/master/jOOQ-examples/jOOQ-academy/src/main/resources/db-h2.sql
You don’t have to manually install anything. The sample database is installed automatically when you build the jOOQ Academy Maven project with Maven. For the reference, we’ll reproduce the database here:
DROP TABLE IF EXISTS book_to_book_store
;
DROP TABLE IF EXISTS book_store
;
DROP TABLE IF EXISTS book
;
DROP TABLE IF EXISTS author
;
DROP SEQUENCE IF EXISTS s_author_id
;
CREATE SEQUENCE s_author_id START WITH 1
;
CREATE TABLE author (
id INT NOT NULL,
first_name VARCHAR(50),
last_name VARCHAR(50) NOT NULL,
date_of_birth DATE,
CONSTRAINT pk_t_author PRIMARY KEY (ID)
)
;
CREATE TABLE book (
id INT NOT NULL,
author_id INT NOT NULL,
title VARCHAR(400) NOT NULL,
published_in INT,
rec_timestamp TIMESTAMP,
CONSTRAINT pk_t_book PRIMARY KEY (id),
CONSTRAINT fk_t_book_author_id FOREIGN KEY (author_id) REFERENCES author(id),
)
;
CREATE TABLE book_store (
name VARCHAR(400) NOT NULL,
CONSTRAINT uk_t_book_store_name PRIMARY KEY(name)
)
;
CREATE TABLE book_to_book_store (
book_store_name VARCHAR(400) NOT NULL,
book_id INTEGER NOT NULL,
stock INTEGER,
CONSTRAINT pk_b2bs PRIMARY KEY(book_store_name, book_id),
CONSTRAINT fk_b2bs_bs_name FOREIGN KEY (book_store_name)
REFERENCES book_store (name)
ON DELETE CASCADE,
CONSTRAINT fk_b2bs_b_id FOREIGN KEY (book_id)
REFERENCES book (id)
ON DELETE CASCADE
)
;
INSERT INTO author VALUES (next value for s_author_id, 'George', 'Orwell', '1903-06-25')
;
INSERT INTO author VALUES (next value for s_author_id, 'Paulo', 'Coelho', '1947-08-24')
;
INSERT INTO book VALUES (1, 1, '1984' , 1948, null)
;
INSERT INTO book VALUES (2, 1, 'Animal Farm' , 1945, null)
;
INSERT INTO book VALUES (3, 2, 'O Alquimista', 1988, null)
;
INSERT INTO book VALUES (4, 2, 'Brida' , 1990, null)
;
INSERT INTO book_store (name) VALUES
('Amazon'),
('Barnes and Noble'),
('Payot')
;
INSERT INTO book_to_book_store VALUES
('Amazon', 1, 10),
('Amazon', 2, 10),
('Amazon', 3, 10),
('Barnes and Noble', 1, 1),
('Barnes and Noble', 3, 2),
('Payot', 3, 1)
;
DROP ALIAS IF EXISTS count_books
;
CREATE OR REPLACE ALIAS count_books AS $$
int countBooks(Connection c, int authorID) throws SQLException {
try (PreparedStatement s = c.prepareStatement("SELECT COUNT(*) FROM book WHERE author_id = ?")) {
s.setInt(1, authorID);
try (ResultSet rs = s.executeQuery()) {
rs.next();
return rs.getInt(1);
}
}
}
$$
;
As you can see, we’re using very common SQL features:
- Tables with primary keys and foreign keys
- Sequences for ID generation
- Stored procedures
3. The code generator
jOOQ generates Java code from your database meta information. This means that the above schema will produce classes for each of the above tables, which look something like this:
A class holding static references to all tables
public class Tables {
public static final Author AUTHOR = // [...]
public static final Book BOOK = // [...]
public static final BookStore BOOK_STORE = // [...]
public static final BookToBookStore BOOK_TO_BOOK_STORE = // [...]
} Each table has references to all of its columns
public class Author extends TableImpl<AuthorRecord> {
public final TableField<AuthorRecord, Integer> ID = // [...]
public final TableField<AuthorRecord, String> FIRST_NAME = // [...]
public final TableField<AuthorRecord, String> LAST_NAME = // [...]
public final TableField<AuthorRecord, Timestamp> DATE_OF_BIRTH = // [...]
} These are the most important generated artefacts. We’ll look into generated records, POJOs, DAOs, as well as generated objects for sequences and stored procedures later on.
