jOOQ - A bit different ORM framework

dslc.select(USER.LAST_NAME, USER.FIRST_NAME)
    .from(USER)
    .where(USER.ROLE.eq(1)) // the USER.ROLE is of the type VARCHAR → compiler-time error

dslc.select(USER.LAST_NAME, USER_FIRSTNAME) // the USER.LAST_NAME was renamed to USER.SURNAME → compiler-time error
    .from(USER)
    .where(USER.ROLE.eq("admin"));

dslc.select(field("last_name"), field("first_name"))
    .from(table("user"))
    .where(field("role").eq("admin"))
    .orderBy(field("last_name"), field("first_name"));

dslc.select(AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME)
    .from(AUTHOR)
    .where(
        (AUTHOR.LAST_NAME.eq("London").or(AUTHOR.FIRST_NAME.like("J%"))
        .and(AUTHOR.YEAR_OF_BIRTH.between(1800, 1900))
    );

dslc.select(AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME)
    .from(AUTHOR)
    .where(
        AUTHOR.LAST_NAME.eq("London").or(AUTHOR.FIRST_NAME.like("J%")),
        AUTHOR.YEAR_OF_BIRTH.between(1800, 1900)));

dslc.select(AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME, BOOK.TITLE)
    .from(AUTHOR)
    .join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
    .where(AUTHOR.ID.eq(1));

dslc.select(AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME, BOOK.TITLE)
    .from(AUTHOR)
    .join(BOOK).onKey() // in case the foreign key is well-configured
    .where(AUTHOR.ID.eq(1));

dslc.select(BOOk.author().LAST_NAME, AUTHOR.author().FIRST_NAME, BOOK.TITLE) // jOOQ can do an implicit JOIN
    .from(BOOK)
    .where(AUTHOR.ID.eq(1));

dslc.select(BOOK.TITLE).from(BOOK)
    .leftSemiJoin(BOOK_TO_BOOK_STORE).onKey();

SELECT book.title FROM BOOK
WHERE EXISTS (
    SELECT 1 FROM book_to_book_store
    WHERE book_to_book_store.book_id = book.id)

// alternative using jOOQ without the semi-join
dslc.select(BOOK.TITLE).from(BOOK)
    .whereExists(
        dslc.selectOne()
            .from(BOOK_TO_BOOK_STORE)
            .where(BOOK_TO_BOOK_STORE.BOOK_ID.eq(BOOK.ID)));

dslc.select(BOOK.TITLE).from(BOOK)
.leftAntiJoin(BOOK_TO_BOOK_STORE).onKey();

dslc.select(BOOK.ID, BOOK.TITLE).from(BOOK)
    .fetchInto(Book::class);

dslc.select(BOOK.ID, BOOK.TITLE.as("bookTitle")).from(BOOK)
    .fetch(Records.mapping(Book::new));

dslc.insertInto(AUTHOR, AUTHOR.FIRST_NAME, AUTHOR.LAT_NAME)
    .values("Jack", "London")
    .execute();

dslc.update(AUTHOR)
    .set(AUTHOR.FIRST_NAME, "Jack")
    .where(AUTHOR.ID.eq(3))
    .execute();

dslc.delete(AUTHOR)
    .where(AUTHOR.ID.eq(100))
    .execute();

// CREATE
var book = dslc.newRecord(BOOK);
book.setTitle("1984");
book.store();
// ID is autogenerated and populated automatically
var bookId = book.getId();
// RETRIEVE
var book2 = dslc.fetchSingle(BOOK, BOOK.ID.eq(bookId));
// UPDATE
book2.setPublishedIn(2000);
book2.store();
// DELETE
book2.delete();

var book = new Book();
book.setTitle("1984");
bookDao.insert(book);
// ID is autogenerated and populated automatically
var bookId = book.getId();
// RETRIEVE
var book2 = bookDao.fetchOneById(bookId);
bookDao.update(book2);
// DELETE
bookDao.delete(book2);

SELECT percentile_cont(0.5) WITHING GROUP (ORDER BY book.published_in) FROM book;

dslc.select(median(BOOK.PUBLISHED_IN)).from(BOOK); // there is no PostgreSQL median

CREATE TYPE book_type AS ENUM ('NOVEL', 'POEM', 'ESSAY'); -- PostgreSQL enumerations

dslc.celect(BOOK.TITLE).from(BOOK).where(BOOK.TYPE.eq(BookTypeEnum.NOVEL));

CREATE TABLE book (
   tags text[]
);

public class Book implements Serializable { // jOOQ generated class
    private String[] tags;
    // getters and setters
}

public record Book(int id, String title) {}
public record Name(String first, String last) {}
public record Author(int id, Name name, Book[] books) {}

// this generates duplicated author first and surnames and requires further processing in Java
dslc.select(AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, BOOK.ID, BOOK.TITLE)
    .from(AUTHOR)
    .join(BOOK).onKey()
    .fetch();

// column mapping
dslc.select(
        AUTHOR.ID,
        row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).as("name"))
        array(
            dslc.select(row(BOOK.ID, BOOK.TITLE))
                .from(BOOK)
                .where(BOOK.AUTHOR_ID.eq(AUTHOR_ID))).as("books"))
                .from(AUTHOR)
    .fetchInto(Author::class);

// constructor mapping
dslc.select(
        AUTHOR.ID,
        row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).mapping(Name::new))
        array(
            dslc.select(row(BOOK.ID, BOOK.TITLE))
                .mapping(Book::class, Book::new)
                .from(BOOK)
                .where(BOOK.AUTHOR_ID.eq(AUTHOR_ID)))
    .from(AUTHOR)
    .fetchInto(Author::class);

var jsonFormat = new JSONFORMAT()
    .recordFormat(JSONFormat.RecordFormat.OBJECT)
    .format(true);
System.out.println(query.fetch().formatJSON(jsonFormat));

System.out.println(query.fetch());