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> |
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> |
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(); | |
} | |
} | |
} |
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
Recent Comments