Introduction to Jooq framework
Jooq is a Java framework and has emerged as a good alternative to traditional JPA solutions like Hibernate. One of the main advantages of using Jooq is that it generates type safe SQL queries. It provides a fluent API, which makes it super easy to write complicated SQL queries.
Instead of using plain old JDBC which provides a lot of scope for writing “bad” code which may work now but is totally unusable in production code. Jooq is quite sophisticated and has plugged in a lot of holes found in plain JDBC.
In this article, we will introduce Jooq and see how we can get started with Jooq and get a feel, for what it feels like using Jooq in any project.
Advantages of using Jooq
Type Safe SQL Queries
Jooq provides it’s own custom classes which correspond to each table in our database. Thus, instead of writing SQL queries in the form of string, we write them using Jooq’s API and thus avoid most of the error, that comes with writing “naive” SQL queries.
for ex, Let’s say I have a table called student and want to select all student’s whose studentId = 1.
If I use plain old JDBC, I will write it like this :
I will have to create the connections, take care of closing them, make sure there are no connection leaks and write a lot of boilerplate code. Now suppose, if someone changes the field name “studentName” and splits it into “studentFirstName” and “studentLastName”, then I am in trouble. I will have to scroll through the whole code base and change the code, wherever I am using “studentName” field.
Now let us see, how will the same code look in Jooq
The code written in Jooq is quite typesafe, as you will be using the Jooq generated classes to write SQL queries, and whenever you choose to change your database schema, all the changes will be reflected back in the Java Code, and you will instantly know what all broke because of the change in database schema.
Jooq is extremely flexible, when it comes to it’s usage. Jooq can be used in the following ways :
- SQL Building. : Generate only SQL query in Jooq and execute it using some other JPA solution
- Code generation : Write SQL Query in Jooq and run it with Jooq
- SQL execution : Don’t write SQL in Jooq, but use SQL generated else where and run it using Jooq.
- Jooq for CRUD : If you don’t want to write your own SQL Queries, you can use Jooq directly for CRUD operations
In the above example, I had used Jooq for SQL execution, but I could also use it as SQL generator and then run that SQL query with other JPA solutions like Hibernate, Spring-JDBC etc, as shown in the below code.
To create a simple project using Jooq, view the Introduction to Jooq (part 2)