Getting Started with jOOQ : Building SQL Queries in Java

Getting Started with jOOQ : Building SQL Queries in Java


Hi, I’m Thorben Janssen from thoughts-on-java.org, In todays video, I will who you how to build SQL Queries with jOOQ. JPA and Hibernate are a great fit to implement persist and update use cases, and simple queries. But most applications need a lot more than that. You need to be able to use the full feature set of SQL to implement your queries. That’s why JPA supports native queries. But it’s not comfortable to use. You probably don’t want to provide your query as a simple String or to handle the differences between the various database dialects yourself. Other libraries are much better suited to
implement complex SQL queries. One of them is jOOQ. It provides you with a Java DSL that enables you to build SQL queries in a comfortable and type-safe way. It abstracts the technical
difficulties of plain JDBC and handles the subtle differences of the various SQL dialects. In this video, I will give you a basic introduction to jOOQ so that you can start using it in your application. And in my next video, I will show you how to integrate it with Hibernate. Before we get started, please make sure you
subscribed to my channel and click the bell icon, so that you get new videos every week. Before you can use jOOQ in your project, you need to add a few dependencies to it. Here you can see the maven dependencies of the jOOQ community edition, which you can use with open-source databases. If you use other databases, like Oracle or MS SQL Server, you need to get one of jOOQ’s commercial licenses. I will add a link to the different licensing options to the video description. After you added the required dependencies, we should talk about jOOQ’s code generation capabilities. This step is optional but I highly recommend it. If you want to improve developer productivity and write your queries in a type-safe way, you should use jOOQ’s code generator. It creates Java classes that map your tables, sequences, stored procedures and more. You can use these classes to define your queries and to process the selected results. But more about that later. jOOQ provides you with a set of code generators that you can use on the command line, within Eclipse and as a Maven plugin. They can generate jOOQ’s metamodel classes based on an existing database, an SQL script or your entity mappings. I will use the Maven plugin in this video and add the links to the other code generators to the video description. Let’s generate the classes for a simple example. I use a PostgreSQL database with a simple test database containing the tables author, book, book_author and publisher. Here is an example of a Maven build configuration that calls the code generator within Maven’s generate goal. The generator connects to the public schema of the jooq database on a PostgreSQL server on localhost. It writes the generated classes to the package org.thoughts.on.java.db in the folder target/generated-sources/jooq. After you run the Maven build, you can find a set of classes in the packages org.thoughts.on.java.db, org.thoughts.on.java.db.tables and org.thoughts.on.java.db.tables.records. The classes in the org.thoughts.on.java.db package provide convenient access to the schema and all tables, sequences, keys, and indexes. We don’t need these classes in this example. We will only use the classes in the org.thoughts.on.java.db.tables package to reference tables and their columns in SQL queries and classes in the org.thoughts.on.java.db.tables.records package to handle the results of these queries. OK, we now have everything we need to implement our queries. The great thing about jOOQ is that the DSL is very similar to SQL’s syntax. So, if you’re familiar with SQL, you will have no problems to write your queries with jOOQ. It all starts with the creation of a DSLContext which you need to initialize with a JDBC Connection and the SQLDialect you want to use. In this example, I’m using a PostgreSQL 9.4 database on localhost. You can then use the DSLContext to create your queries. Let’s start with a simple query that retrieves all records from the book table. As you can see, the definition of such a query is easy, and the code looks almost like SQL. The parameter-less select method on the DSLContext defines a projection that includes all columns and the from method defines from which database table these records shall be selected. Here you can see an advantage of the code generation. Instead of providing the name of the book table as a String, you can use a static attribute of the generated Book class. That class represents the book table and provides you with a strongly typed, static attribute for each column of the table. That enables code completion in your IDE. And if you integrate the code generation step into your build process, you can also be sure that your code always matches your table model. In case the table model changes, the code generator will update the Java classes, and your IDE will show you compile time errors at all code snippets you need to update. After you have defined your query, you need to execute it and retrieve the result. In this example, I do that by calling the fetch method. In the next step, you can process the result. The query returned a collection of Record interfaces. Each of them represents a record of the query result. As you can see in the code snippet, you can retrieve the value of each field in the result record by calling the get method with an attribute of the generated Book class that references one of the selected database columns. This attribute also provides the required type information to avoid any type casts. If you don’t use the code generator or use an alias in your projection, you can also provide a String with the name of the result set column. That was easy. But your queries are probably a little more complex. So, let’s create a slightly less simple query with jOOQ. This time, I want to select the first name, last name and the number of books written by all authors whose last name starts with “Jan” and ends with “en”. You can see again, that the Java code looks
extremely similar to the SQL statement you want to create. The select method defines the projection. I use the generated Author class to reference the firstname and lastname columns of the author table. jOOQ’s DSL class provides lots of methods that enable you to call SQL functions. I use it here to call SQL’s count function and to define the alias bookCount for that field. Then I define the FROM clause of the query. The from method returns a SelectJoinStep interface which enables you to define different types of join clauses or to combine the results of multiple queries with set operators, like UNION or INTERSECT. This is a huge advantage compared to JPA’s JPQL queries which I normally show on this blog. jOOQ enables you to use all SQL features so that you can benefit from the powerful query capabilities provided by your database. Let’s continue by specifying the WHERE clause. You can do that by calling the where method with a String, an SQL query part, or one or more Conditions. I prefer to define my query in a type-safe way, so I use the generated Author class to reference the lastname column and to define the like expression. As you can see in the code snippet, I don’t define a bind parameter and just set the String “Jan%en” as its value. jOOQ automatically adds a bind parameter to the query and sets the provided value as the bind parameter value. OK, almost done. We just need to add a GROUP BY clause for the columns firstname and lastname. Similar to the definition of the previous clauses, you can do that by calling the groupBymethod with references to the 2 database columns. That’s all you need to do to define the query. The call of the fetch method executes the query and returns a Result interface which contains a collection of strongly typed Record interfaces. As in the previous example, you can then use that interface and jOOQ’s generated classes to process the query result. OK, that’s it for today. If you want to learn more about Hibernate, you should join the free Thoughts on Java Library. It gives you free access to a lot of member-only content like a cheat for this video and an ebook about the java 8 support in Hibernate 5. I’ll add the link to it to the video description below. And if you like today’s video, please give it a thumbs up and subscribe below. Bye

6 thoughts on “Getting Started with jOOQ : Building SQL Queries in Java”

  1. How do you see jooq working with JPA? What I mean is one a replacement for the other? Do you use both and decide on a code by code, query by query basis what works best?

  2. Manoj Periathambi

    Thank you sir, How do you compare Jooq with QueryDSL? I thought both tried to solve the same problem. Can you comment on this please?

Leave a Reply

Your email address will not be published. Required fields are marked *