Introduction to Jooq framework

 

Introduction

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.

Extreme Flexibility

Jooq is extremely flexible, when it comes to it’s usage. Jooq can be used in the following ways :

  1. SQL Building. : Generate only SQL query in Jooq and execute it using some other JPA solution
  2. Code generation : Write SQL Query in Jooq and run it with Jooq
  3. SQL execution : Don’t write SQL  in Jooq, but use SQL generated else where and run it using Jooq.
  4. 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)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: