HelloKoding

Practical coding guides

jOOQ Example with MySQL

This tutorial will walk you through the process of creating a jOOQ example with MySQL.

What you’ll need

  • JDK 1.7+
  • Maven 3+
  • MySQL 5.6+

Stack

  • Java
  • jOOQ

Init project structure, dependencies and database

Project structure

├── src
│   └── main
│       └── java
│           └── com
│               └── hellokoding
│                   └── jooq
│                       ├── model
│                       │   ├── tables
│                       │   │   ├── records
│                       │   │   │   └── AuthorRecord.java
│                       │   │   └── Author.java
│                       │   ├── DefaultCatalog.java
│                       │   ├── Keys.java
│                       │   ├── Library.java
│                       │   └── Tables.java
│                       └── Application.java
├── create_db.sql
└── pom.xml

Files and sub-directories in com/hellokoding/jooq/modeldirectory will be auto generated by jOOQ. We only play with pom.xml, create_db.sql and com/hellokoding/jooq/Application.java

Project dependencies

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.hellokoding</groupId>
	<artifactId>hello-jooq</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>Hello jOOQ</name>
	<description>Hello jOOQ</description>

	<properties>
		<maven.compiler.source>1.7</maven.compiler.source>
		<maven.compiler.target>1.7</maven.compiler.target>
	</properties>

	<dependencies>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>6.0.3</version>
		</dependency>
		<dependency>
			<groupId>org.jooq</groupId>
			<artifactId>jooq</artifactId>
			<version>3.8.3</version>
		</dependency>
		<dependency>
			<groupId>org.jooq</groupId>
			<artifactId>jooq-meta</artifactId>
			<version>3.8.3</version>
		</dependency>
		<dependency>
			<groupId>org.jooq</groupId>
			<artifactId>jooq-codegen</artifactId>
			<version>3.8.3</version>
		</dependency>
	</dependencies>
	
	<build>
		<plugins>
			<plugin>
				<groupId>org.jooq</groupId>
				<artifactId>jooq-codegen-maven</artifactId>
				<version>3.8.3</version>

				<!-- The plugin should hook into the generate goal -->
				<executions>
					<execution>
						<goals>
							<goal>generate</goal>
						</goals>
					</execution>
				</executions>

				<dependencies/>

				<configuration>
					<jdbc>
						<driver>${jdbc.driver}</driver>
						<url>${jdbc.url}</url>
						<user>${jdbc.user}</user>
						<password>${jdbc.password}</password>
					</jdbc>

					<generator>
						<database>
							<name>org.jooq.util.mysql.MySQLDatabase</name>
							<includes>.*</includes>
							<excludes></excludes>
							<inputSchema>library</inputSchema>
						</database>
						<target>
							<packageName>com.hellokoding.jooq.model</packageName>
							<directory>src/main/java</directory>
						</target>
					</generator>
				</configuration>
			</plugin>

			<plugin>
				<groupId>org.codehaus.mojo</groupId>
				<artifactId>exec-maven-plugin</artifactId>
				<version>1.5.0</version>
				<executions>
					<execution>
						<goals>
							<goal>exec</goal>
						</goals>
					</execution>
				</executions>
				<configuration>
					<systemProperties>
						<systemProperty>
							<key>jdbc.driver</key>
							<value>${jdbc.driver}</value>
						</systemProperty>
						<systemProperty>
							<key>jdbc.user</key>
							<value>${jdbc.user}</value>
						</systemProperty>
						<systemProperty>
							<key>jdbc.password</key>
							<value>${jdbc.password}</value>
						</systemProperty>
						<systemProperty>
							<key>jdbc.url</key>
							<value>${jdbc.url}</value>
						</systemProperty>
					</systemProperties>
				</configuration>
			</plugin>
		</plugins>
	</build>

	<profiles>
		<profile>
			<id>default</id>
			<activation>
				<activeByDefault>true</activeByDefault>
			</activation>
			<properties>
				<jdbc.user>hellokoding</jdbc.user>
				<jdbc.password>hellokoding</jdbc.password>
				<jdbc.url>jdbc:mysql://localhost:3306/library?serverTimezone=UTC</jdbc.url>
				<jdbc.driver>com.mysql.cj.jdbc.Driver</jdbc.driver>
			</properties>
		</profile>
	</profiles>
	

</project>

jooq-codegen-maven plugin’s used for generate domain and DAO objects in com/hellokoding/jooq/model

Create database

Run below script to create a sample MySQL database

create_db.sql

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(1, 'Hello', 'Koding');
INSERT INTO author(2, 'jOOQ', 'Example');

Run

Application.java

package com.hellokoding.jooq;

import org.jooq.DSLContext;
import org.jooq.Record;
import org.jooq.Result;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;

import java.sql.Connection;
import java.sql.DriverManager;

import static com.hellokoding.jooq.model.Tables.*;

public class Application {
    public static void main(String[] args) throws Exception {
        String user = System.getProperty("jdbc.user");
        String password = System.getProperty("jdbc.password");
        String url = System.getProperty("jdbc.url");
        String driver = System.getProperty("jdbc.driver");

        Class.forName(driver).newInstance();
        try (Connection connection = DriverManager.getConnection(url, user, password)) {
            DSLContext dslContext = DSL.using(connection, SQLDialect.MYSQL);
            Result<Record> result = dslContext.select().from(AUTHOR).fetch();

            for (Record 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);
            }
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }
}

System.property()’s bound to systemProperty of exec-maven-plugin configuration in pom.xml

Run with this command mvn clean compile exec:java -Dexec.mainClass=com.hellokoding.jooq.Application -Dexec.cleanupDaemonThreads=false

Source code

https://github.com/hellokoding/hellokoding-courses/tree/master/java-examples/java-jooq-mysql

Follow HelloKoding