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
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.
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.
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
Now, create a new file with the following path relative to the project root
Now, let us compile the project.
Run the project,
You should see “Chandler Bing” as your output, after running the last command.
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