Introduction to Jooq (part 2)

This article is a continuation of the previous article Introduction to Jooq (Part 1). In the previous article, we got a general feel about, what it is like to use Jooq  to write SQL queries. In this article, we will be using Jooq to connect to mysql database and run a few queries with it.

In our database, we will be having 1 small tables student which store the student’s name and student’s id

Open your mysql prompt and type in the following commands to create a database and corresponding tables.
create database jooq
use jooq

create table student (studentId int auto_increment, studentName varchar(30) not null, primary key (studentId));

Now , let us add some values to the student table, run the following command on the MySQL’s prompt.

insert into student values (1, "Chandler Bing"), (2, "Ross Geller"), (3, "Joe Tribbiyani");


Now, inside your project directory, add the following pom file.

	<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xss:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.jooq</groupId>
	<artifactId>tut</artifactId>
	<packaging>jar</packaging>
	<version>1.0</version>
	<name>tut</name>
	<dependencies>
	<dependency>
	<groupId>org.jooq</groupId>
	<artifactId>jooq</artifactId>
	<version>3.9.4</version>
	</dependency>
	<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>6.0.6</version>
	</dependency>
	</dependencies>
	<build>
	<plugins>
	<plugin>
	<groupId>org.apache.maven.plugins</groupId>
	<artifactId>maven-compiler-plugin</artifactId>
	<version>2.5.1</version>
	<inherited>true</inherited>
	<configuration>
	<source>1.8</source>
	<target>1.8</target>
	<useIncrementalCompilation>false</useIncrementalCompilation>
	</configuration>
	</plugin>
	<plugin>
	<groupId>org.codehaus.mojo</groupId>
	<artifactId>exec-maven-plugin</artifactId>
	<version>1.2.1</version>
	<executions>
	<execution>
	<goals>
	<goal>java</goal>
	</goals>
	</execution>
	</executions>
	<configuration>
	<mainClass>com.jooq.Main</mainClass>
	</configuration>
	</plugin>
	</plugins>
	</build>
	</project>

view raw
jooq_2.pom.xml
hosted with ❤ by GitHub

Generating Jooq class files

To generate jooq class files, you need to download jooq jar and mysql-connector jar files.

For downloading Jooq jar files : Jooq Download

For download MySQL jar files:  MySQL Connector Download

After you have downloaded both of these jar files, copy the mysql connector jar file into jOOQ-3.9.4/jOOQ-lib folder

Of course, you might have to change the version number to the jOOQ version you are using.

	<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
	<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.9.2.xsd">
	<jdbc>
	<driver>com.mysql.jdbc.Driver</driver>
	<url>jdbc:mysql://localhost/attendance</url>
	<user>root</user>
	<password>root</password>
	</jdbc>
	<generator>
	<name>org.jooq.util.JavaGenerator</name>
	<database>
	<name>org.jooq.util.mysql.MySQLDatabase</name>
	<inputSchema>jooq</inputSchema>
	<includes>.*</includes>
	<excludes></excludes>
	</database>
	<target>
	<packageName>com.jooqGenerated</packageName>
	<directory>/home/ps06756/Tutorial/Jooq-2/src/main/java/</directory>
	</target>
	</generator>
	</configuration>

view raw
jooq_config.xml
hosted with ❤ by GitHub

You will have to change the user, password and directory according to your database and PC configuration.

After that place this xml file inside the jOOQ-3.9.4/jOOQ-lib/ directory and run the following comand
java -cp jooq-3.9.4.jar:jooq-meta-3.9.4.jar:jooq-codegen-3.9.4.jar:mysql-connector-java-5.0.8-bin.jar: org.jooq.util.GenerationTool /home/ps06756/Tutorial/Jooq-2/jooq.xml

Change the path of jooq.xml file according to your needs.
After this command is run successfully, you will see that jOOQ has created some java files, inside the src/main/java/com/jOOQGenerated/ directory

Project Source

Now, create a new file with the following path relative to the project root src/main/java/com/jooq/Main.java

	package com.jooq;
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.SQLException;
	import org.jooq.SQLDialect;
	import org.jooq.DSLContext;
	import org.jooq.Configuration;
	import org.jooq.impl.DefaultConfiguration;
	import org.jooq.impl.DSL;
	import static com.jooqGenerated.Tables.*;
	import org.jooq.Record;
	import org.jooq.Result;
	public class Main {
	public static void main(String[] args) {
	try {
	Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
	try ( Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/jooq?" +
	"user=root&password=root");) {
	Configuration config = new DefaultConfiguration();
	config.set(connection);
	config.set(SQLDialect.MYSQL);
	DSLContext create = DSL.using(config);
	System.out.println(create.select(STUDENT.STUDENTNAME).from(STUDENT).where(STUDENT.STUDENTID.eq(1)).fetchOne(STUDENT.STUDENTNAME));
	}
	}
	catch(Exception ex) {
	ex.printStackTrace();
	}
	}
	}

view raw
Main.java
hosted with ❤ by GitHub

Now, let us compile the project.

mvn compile

Run the project,
mvn exec:java

You should see “Chandler Bing” as your output, after running the last command.

Explanation

In the above Main.java file, we created a connection using traditional JDBC interface and then instantiated a Configuration object and told it to use the connection previously opened by us. We also tell the configuration, which database type we are using by specifying the SQLDialect.

In Jooq, we use DSLContext to run queries, therefore using the configuration previously made, we create a new DSLContext and use it to run a pretty simple query and print the result.

This completes the part 2 of the introductory series on Jooq, Stay tuned for more articles on Jooq.

Code used in this article can be accessed via the following repository: Introduction to Jooq