JOOQ(Java Object Oriented Querying)

 build.gradle file for jooq configuration:

plugins {
    id 'org.springframework.boot' version '2.7.0'
    id 'io.spring.dependency-management' version '1.0.11.RELEASE'
    id 'java'

    id 'nu.studer.jooq' version '7.1.1'
}

group = 'com.example'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '11'

configurations {
    compileOnly {
        extendsFrom annotationProcessor
    }
}

repositories {
    mavenCentral()
}

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-jooq'
    implementation 'org.springframework.boot:spring-boot-starter-web'
    compileOnly 'org.projectlombok:lombok'
    runtimeOnly 'org.postgresql:postgresql'
    annotationProcessor 'org.projectlombok:lombok'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'

    jooqGenerator 'org.postgresql:postgresql:42.3.1'
    jooqGenerator 'jakarta.xml.bind:jakarta.xml.bind-api:3.0.1'
    jooqGenerator("org.jooq:jooq-meta-extensions-liquibase:3.16.6")
}

jooq {
    version = '3.16.4'  // the default (can be omitted)
    edition = nu.studer.gradle.jooq.JooqEdition.OSS  // the default (can be omitted)

    configurations {
        main {  // name of the jOOQ configuration
            generateSchemaSourceOnCompilation = true  // default (can be omitted)

            generationTool {
                jdbc {
                    driver = 'org.postgresql.Driver'
                    url = 'jdbc:postgresql://localhost:5432/demo2'
                    user = 'postgres'
                    password = '112358'
                    properties {
                        property {
                            key = 'ssl'
                            value = 'false'
                        }
                    }
                }
                generator {
                    name = 'org.jooq.codegen.DefaultGenerator'
                    database {
                        name = 'org.jooq.meta.postgres.PostgresDatabase'
                        inputSchema = 'public'
                        forcedTypes {
                            forcedType {
                                name = 'varchar'
                                includeExpression = '.*'
                                includeTypes = 'JSONB?'
                            }
                            forcedType {
                                name = 'varchar'
                                includeExpression = '.*'
                                includeTypes = 'INET'
                            }
                        }
                    }
                    generate {
                        deprecated = false
                        records = true
                        immutablePojos = true
                        fluentSetters = true
                    }
                    target {
                        packageName = 'nu.studer.sample'
                        directory = 'build/generated-src/jooq/main'  // default (can be omitted)
                    }
                    strategy.name = 'org.jooq.codegen.DefaultGeneratorStrategy'
                }
            }
        }
    }
}

tasks.named('test') {
//    useJUnitPlatform()
}

1. fetch.get(index).component(): bize hemin indexdeki melumati qaytarir

package com.example.demo2.repository;

import lombok.RequiredArgsConstructor;
import org.jooq.DSLContext;
import org.jooq.Record4;
import org.jooq.Result;
import org.springframework.stereotype.Repository;

import static nu.studer.sample.Tables.AUTHOR;
import static nu.studer.sample.Tables.BOOK;

@Repository
@RequiredArgsConstructor
public class AuthorRepository {

    private final DSLContext context;

    public String foo() {
        Result<Record4<String, String, Integer, String>> fetch = context.select(AUTHOR.FIRST_NAME,
                        AUTHOR.LAST_NAME,
                        BOOK.ID,
                        BOOK.TITLE)
                .from(AUTHOR)
                .join(BOOK)
                .on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
                .orderBy(BOOK.ID.asc())
                .fetch();
        return fetch.get(0).component1();
    }
}
2. fetch.getValues: bize hemin fieldin listini qaytarir
package com.example.demo2.repository;

import lombok.RequiredArgsConstructor;
import org.jooq.DSLContext;
import org.jooq.Record4;
import org.jooq.Result;
import org.springframework.stereotype.Repository;

import java.util.List;

import static nu.studer.sample.Tables.AUTHOR;
import static nu.studer.sample.Tables.BOOK;

@Repository
@RequiredArgsConstructor
public class AuthorRepository {

    private final DSLContext context;

    public List<String> foo() {
        Result<Record4<String, String, Integer, String>> fetch = context.select(AUTHOR.FIRST_NAME,
                        AUTHOR.LAST_NAME,
                        BOOK.ID,
                        BOOK.TITLE)
                .from(AUTHOR)
                .join(BOOK)
                .on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
                .orderBy(BOOK.ID.asc())
                .fetch();
        List<String> values = fetch.getValues(AUTHOR.FIRST_NAME);
        return values;
//        return fetch.get(0).component1();
    }
}
3. fetch(): returns Result<Record>. That means record list
public List<Object> foo5() {
    Author a = AUTHOR.as("a");
    Book b = BOOK.as("b");
    Result<Record> records = context.select()
            .from(a)
            .join(b).on(a.ID.eq(b.AUTHOR_ID))
            .where(a.YEAR_OF_BIRTH.gt(1920))
            .and(a.FIRST_NAME.eq("Paulo"))
            .orderBy(b.TITLE)
            .fetch();
    List<Object> objects = new ArrayList<>();
    for(Record r: records) {
        for(int i = 0; i < r.size(); i++) {
            objects.add(r.get(i));
        }
    }
    return objects;
}

4. insert:
public int foo7() {
    return context.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, AUTHOR.DATE_OF_BIRTH, AUTHOR.YEAR_OF_BIRTH, AUTHOR.ADDRESS)
            .values(4, "Parvin", "Etibarli", LocalDate.of(1991, 11, 02), 1991, "R.Rza 98")
            .execute();
}

5. fetchInto(): returns list of record.
public void foo8() {
    List<BookRecord> books = context.select().from(BOOK).fetchInto(BookRecord.class);
    System.out.println(books);
}

6. fetchMany(): returns set of result. The fetchMany() method is quaranteed to never return 
null, do don't base any logic around null checking. 
public void foo9() {
    Results results = context.select().from(BOOK).fetchMany();
    System.out.println(results);
}

7. fetchInto():
If we do not provide full field, it returns it with null.
public void foo11() {
    List<BookRecord> bookRecords = context.select(BOOK.ID, BOOK.TITLE).from(BOOK).fetchInto(BookRecord.class);
    System.out.println(bookRecords);
}
8.
public void foo12() {
    List<AuthorRecord> authorRecords = context.select(field("first_name"), field("last_name"))
            .from(table("author")).where(condition("first_name = 'Parvin'"))
            .fetchInto(AuthorRecord.class);
    System.out.println(authorRecords);
}
public void foo13() {
    Condition condition = condition("first_name = 'Parvin'");
    List<AuthorRecord> authorRecords = context.select(field("first_name"), field("last_name"))
            .from(table("author")).where(condition)
            .fetchInto(AuthorRecord.class);
    System.out.println(authorRecords);
}

9.