JOOQ - Object Oriented Querying

Giriş

Bazı notlar

JOOQ Nedir?

Açıklaması şöyle

jOOQ (Java Object Oriented Querying) is a popular Java database library that builds type-safe SQL queries through its fluent API. jOOQ includes generators that generate Java code from the database. It also has code generators for Data Access Objects (DAO) and POJOs to effectively complete CRUD operations.

Açıklaması şöyle

jOOQ will sound familiar for C# developers using LINQ. It is a tool that generates structures and methods based on your database Metadata. With that approach you can query your database with SQL-like syntax from within your Java code. It means you don't have to write so much boilerplate, you don't have to replicate database structure.

As well, your IDE will be able to give you more hints about available fields and types as well as informing you when your code becomes desynchronised with the changes made in the database.

Neden JOOQ

Açıklaması şöyle

Code Generation: jOOQ generates Java classes, JPA-annotated entity classes, interfaces, or even XML from the database metadata.

Type Safety SQL: jOOQ treats SQL like what it is in the first place: A language. Through its unique and modern fluent API design techniques, jOOQ embeds SQL as an internal domain-specific language directly in Java, making it easy for developers to write and read code that almost feels like actual SQL.

Database First: With jOOQ, database and data comes first.

Domain-Specific Language: jOOQ provides a DSL that replicates the particular SQL dialect used by the database vendors.

JPAstreamer  Nedir

Bu kütüphane de JDBC'den daha kolay bir arayüz sağlıyor. Bir örnek burada

JDBI Nedir?

JOOQ vs JDBI için bir yazı burada

JDBI 3

Açıklaması şöyle

What is Jdbi3?

Jdbi is an open-source Java library (Apache license) that uses lambda expressions and reflection to provide a friendlier, higher-level interface than JDBC to access the database. Jdbi 3 is the third major release which introduces enhanced support for Java 8, countless refinements to the design and implementation, and enhanced support for modular plugins.

JDBI 3 için bir yazı burada

JOOQ Code Generation

Açıklaması şöyle

The file generation can be done in multiple ways, but the most reliable way is to let jOOQ connect to your database and read out all the information needed to create the corresponding classes. Unfortunately, this reveals two issues:

Maven

jooq-codegen plugin eklenir

Vertx için kod üretebilir

Gradle

Bir örnek burada

Kullanım

Spring ile kullanım için bir örnek burada

DENSE_RANK + PARTITION_BY

Örnek

Şöyle yaparız

ctx.select(EMPLOYEE.FIRST_NAME, EMPLOYEE.LAST_NAME, EMPLOYEE.SALARY,
           OFFICE.CITY, OFFICE.COUNTRY,
           OFFICE.OFFICE_CODE, 
           denseRank().over().partitionBy(OFFICE.OFFICE_CODE)
              .orderBy(EMPLOYEE.SALARY.desc()).as("salary_rank"))
   .from(EMPLOYEE)
   .innerJoin(OFFICE)
   .on(OFFICE.OFFICE_CODE.eq(EMPLOYEE.OFFICE_CODE))
   .fetch();

Örnek - Sayfalama

Şöyle yaparız

Map<Office, List<Employee>> result = ctx.select().from(
  select(OFFICE.OFFICE_CODE, OFFICE...,
         EMPLOYEE.FIRST_NAME, EMPLOYEE...,
         denseRank().over().orderBy(OFFICE.OFFICE_CODE, OFFICE.CITY).as("rank"))
  .from(OFFICE)
  .join(EMPLOYEE)
  .on(OFFICE.OFFICE_CODE.eq(EMPLOYEE.OFFICE_CODE)).asTable("t"))
  .where(field(name("t", "rank")).between(start, end))
  .fetchGroups(Office.class, Employee.class);

Örnek - multiset 

Şöyle yaparız

fun findAll(): Flux<PersonWithAddresses> {                       //1
  val people: SelectJoinStep<Record5<Long?, String?, String?, LocalDate?, MutableList<Address>>> = //2
    ctx.select(
      PERSON.ID,
      PERSON.FIRST_NAME,
      PERSON.LAST_NAME,
      PERSON.BIRTHDATE,
      DSL.multiset(                                              //2
        DSL.select(
          PERSON_ADDRESS.ADDRESS_ID,
          PERSON_ADDRESS.address.FIRST_LINE,
          PERSON_ADDRESS.address.SECOND_LINE,
          PERSON_ADDRESS.address.ZIP,
          PERSON_ADDRESS.address.CITY,
          PERSON_ADDRESS.address.STATE,
          PERSON_ADDRESS.address.COUNTRY,
        ).from(PERSON_ADDRESS)
           .where(PERSON_ADDRESS.PERSON_ID.eq(PERSON.ID))
      ).convertFrom { it.map(addressMapper) }                   //3
  ).from(PERSON)
  return Flux.from(people)                                      //4
             .map(personWithAddressesMapper)                    //3
}

Açıklaması şöyle. multiset ile bir kişinin tüm adresleri birleştiriliyor

1. Return a regular Project Reactor's Flux

2. Use multiset, see below.

3. Convert the row to an ordinary Java object via a function

4. The magic happens here: wrap the regular query in a Flux for people is a Project Reactor Publisher

SQL Cümlelerini Loglama

Açıklaması şöyle

jOOQ already has a LoggingConnection (see also the manual), which acts as a JDBC proxy Connection to log all SQL statements that are executed by any JDBC client (including Hibernate, MyBatis, JdbcTemplate, native JDBC, etc.).

Starting from jOOQ 3.18.0, 3.17.7, and 3.16.13, a LoggingConnection is now also available for R2DBC clients to log all reactive queries. While some R2DBC drivers already do their own DEBUG logging, some of them don’t, so this utility will be very useful to jOOQ users or anyone else working with R2DBC.