Ordinary Properties

Fetching Scalar Fields

Scalar fields refer to all non-associative fields in a database table. Taking fetching the book name as an example:

Java

BookTable book = Tables.BOOK_TABLE;

List<Book> books = sqlClient
    .createQuery(book)
    .select(
        book.fetch(
            Fetchers.BOOK_FETCHER.name()
        )
    )
    .execute();

Kotlin

val books = sqlClient
    .createQuery(Book::class) {
        where(table.edition.eq(3))
        select(
            table.fetchBy { 
                name()
            }
        )
    }
    .execute()

note

For Java, the Annotation processor will automatically generate a Fetcher class for each entity interface, which is BookFetcher in this example.

The generated SQL is:

select 
    tb_1_.ID, 
    /* highlight-next-line */
    tb_1_.NAME  
from BOOK as tb_1_
where tb_1_.EDITION = ?

note

The Java code does not call the id() method of BookFetcher, but we can see the SQL statement still queries the id property of the object.

The id property is treated specially and will always be queried, not controlled by object fetchers.

In fact, the auto-generated BookFetcher class does not even have an id() method, because it is not needed.

The printed result is (original output is compact, formatted here for readability):

[
    {
        "id":3,
        "name":"Learning GraphQL"
    },
    ...Other objects omitted...  
]

Fetching Multiple Fields

Java

BookTable book = Tables.BOOK_TABLE;

List<Book> books = sqlClient
    .createQuery(book)
    .select(
        book.fetch(
            Fetchers.BOOK_FETCHER
                .name()
                .edition()
        )
    )
    .execute();

Kotlin

val books = sqlClient
    .createQuery(Book::class) {
        where(table.edition.eq(3))
        select(
            table.fetchBy {
                name()
                edition()
            }
        )
    }
    .execute()

info

Object fetchers are immutable objects, each method call returns a new object fetcher.

In the Java code above:

• Fetchers.BOOK_FETCHER
• Fetchers.BOOK_FETCHER.name()
• Fetchers.BOOK_FETCHER.name().edition()

are three different object fetchers, each immutable.

Object fetchers can be freely shared using static variables since they are immutable.

The generated SQL is:

select
    tb_1_.ID,
    /* highlight-next-line */  
    tb_1_.NAME,
    /* highlight-next-line */
    tb_1_.EDITION   
from BOOK as tb_1_
where tb_1_.EDITION = ?

The printed result is (original output is compact, formatted here for readability):

[
    {
        "id":3,
        "name":"Learning GraphQL",
        "edition":1
    },
    ...Other objects omitted...
]

Fetching All Scalar Fields

In some scenarios, fetching all non-associative fields is a very common operation, so allScalarFields() is provided to load all non-associative fields.

Java

BookTable book = Tables.BOOK_TABLE;

List<Book> books = sqlClient
    .createQuery(book)
    .select(
        book.fetch(
            Fetchers.BOOK_FETCHER.allScalarFields()
        )
    )
    .execute(); 

Kotlin

val books = sqlClient
    .createQuery(Book::class) {
        where(table.edition.eq(3))
        select(
            table.fetchBy {
                allScalarFields()
            }
        )
    }
    .execute()

The generated SQL is:

select
    tb_1_.ID,
    // highlight-start 
    tb_1_.NAME,
    tb_1_.EDITION,
    tb_1_.PRICE 
    // highlight-end
from BOOK as tb_1_
where tb_1_.EDITION = ?

The printed result is (original output is compact, formatted here for readability):

{
    "id":3,
    "name":"Learning GraphQL", 
    "edition":3,
    "price":51.00
}
// Other objects omitted

info

allScalarFields() only loads non-associative fields. In the example we can see it queries id, name, edition, price, but does not query the association properties store and authors.

Fetching association properties will be covered in the next document.

Negative Properties

The previously discussed properties are positive properties that incrementally add fields to query. But in some cases, we only need to exclude some specific fields, while querying all other fields.

This is when negative properties can be used to exclude unneeded fields.

Java

BookTable book = Tables.BOOK_TABLE;

List<Book> books = sqlClient
    .createQuery(book)
    .select(
        book.fetch(
            Fetchers.BOOK_FETCHER
            .allScalarFields()
            .edition(false)
        )
    )
    .execute();

Kotlin

val books = sqlClient
    .createQuery(Book::class) {
        where(table.edition.eq(3))
        select(
            table.fetchBy {
                allScalarFields()
                edition(false)
            }
        )
    }
    .execute()

edition(false) uses false as a parameter, making it a negative property.

• The properties of allScalarFields() are id + name + edition + price

• edition(false) means -edition

So in combination, the finally fetched properties are id + name + price

info

• For positive properties, edition() and edition(true) are equivalent
• When most properties need to be fetched and only a few excluded, using allScalarFields with negative properties can be very useful

The generated SQL is:

select
    tb_1_.ID,
    tb_1_.NAME, 
    tb_1_.PRICE    
from BOOK as tb_1_
where tb_1_.EDITION = ? 

The printed result is (original output is compact, formatted here for readability):

[
    {
        "id":3,
        "name":"Learning GraphQL",
        "price":51.00
        // `edition` is not here        
    },
    ...Other objects omitted...
]