Using the jOOQ query DSL API to generate valid SQL

Using the jOOQ query DSL API to generate valid SQL is a good start for exploring the jOOQ world. Let's take a simple SQL statement, and let's express it via jOOQ. In other words, let's use the jOOQ query DSL API to express a given SQL string query into the jOOQ object-oriented style. Consider the next SQL SELECT written in the MySQL dialect:

SELECT * FROM `office` WHERE `territory` = ?

The SQL, SELECT * FROM `office` WHERE `territory` = ?, is written as a plain string. This query can be generated by jOOQ if it is written via the DSL API, as follows (the value of the territory binding variable is supplied by the user):

ResultQuery<?> query = ctx.selectFrom(table("office"))

  .where(field("territory").eq(territory));

Alternatively, if we want to have the FROM clause closer to SQL look, then we can write it as follows:

ResultQuery<?> query = ctx.select()

  .from(table("office"))                  

  .where(field("territory").eq(territory));

Most schemas are case-insensitive, but there are databases such as MySQL and PostgreSQL that prefer mostly lowercase, while others such as Oracle prefer mostly uppercase. So, writing the preceding query in Oracle style can be done as follows:

ResultQuery<?> query = ctx.selectFrom(table("OFFICE"))

  .where(field("TERRITORY").eq(territory));

Alternatively, you can write it via an explicit call of from():

ResultQuery<?> query = ctx.select()

  .from(table("OFFICE"))                  

  .where(field("TERRITORY").eq(territory));

The jOOQ fluent API is a piece of art that looks like fluent English and, therefore, is quite intuitive to read and write.

Reading the preceding queries is pure English: select all offices from the OFFICE table where the TERRITORY column is equal to the given value.

Pretty soon, you'll be amazed at how fast you can write these queries in jOOQ.

Important Note

As you'll see in the next chapter, jOOQ can generate a Java-based schema that mirrors the one in the database via a feature named the jOOQ Code Generator. Once this feature is enabled, writing these queries becomes even simpler and cleaner because there will be no need to reference the database schema explicitly, such as the table name or the table columns. Instead, we will reference the Java-based schema.

And, thanks to the Code Generator feature, jOOQ makes the right choices for us upfront almost everywhere. We no longer need to take care of queries' type-safety and case-sensitivity, or identifiers' quotation and qualification.

The jOOQ Code Generator atomically boosts the jOOQ capabilities and increases developer productivity. This is why using the jOOQ Code Generator is the recommended way to exploit jOOQ. We will tackle the jOOQ Code Generator in the next chapter.

Next, the jOOQ query (org.jooq.ResultQuery) must be executed against the database, and the result set will be mapped to a user-defined simple POJO.