7. Comprehensive Query

So far, we've quickly demonstrated the following features

Object Fetcher, i.e. querying arbitrarily shaped data structures, are what are shown in Fetch Association and Recursive Query.
Conditions, especially Dynamic table join and Implicit subquery
Sorting, including dynamic sorting
Paging queries

Now, let's combine all of the above features into a relatively powerful query

Again, there are two ways to develop

Query dynamic entities
Query static DTOs

Query Dynamic Entities

Java
Kotlin

public class BookRepository {

    private static final BookTable T = BookTable.$;

    private final JSqlClient sqlClient;

    public BookRepository(JSqlClient sqlClient) {
        this.sqlClient = sqlClient;
    }

    public Page<Book> findBooks(
        int pageIndex,
        int pageSize,
        @Nullable String sortCode,
        @Nullable Fetcher<Book> fetcher,
        @Nullable String name,
        @Nullable BigDecimal minPrice,
        @Nullable BigDecimal maxPrice,
        @Nullable String storeName,
        @Nullable String storeWebsite,
        @Nullable String authorName    
    ) {
        return sqlClient
            .createQuery(T)
            .where(T.name().ilikeIf(name))
            .where(T.price().betweenIf(minPrice, maxPrice))
            .where(T.store().name().ilikeIf(storeName))
            .where(T.store().website().ilikeIf(storeWebsite))
            .where(
                T.authors(author ->
                    Predicate.or(
                        author.firstName().ilikeIf(authorName),
                        author.lastName().ilikeIf(authorName)
                    )
                )
            )
            .orderBy(
                Order.makeOrders(
                    T,
                    sortCode != null ? 
                        sortCode :
                        "name asc, edition desc"
                )
            )
            .select(T.fetch(fetcher))
            .fetchPage(pageIndex, pageSize);
    }
}

class BookRepository(
    private val sqlClient: KSqlClient
) {
    fun findBooks(
        pageIndex: Int,
        pageSize: Int = 10,
        sortCode: String = "name asc, edition desc",
        fetcher: Fetcher<Book>? = null,
        name: String? = null,
        minPrice: BigDecimal? = null,
        maxPrice: BigDecimal? = null,
        storeName: String? = null,
        storeWebsite: String? = null,
        name: String? = null
    ): Page<Book> =
        sqlClient
            .createQuery(Book::class) {
                where(table.name `ilike?` name)
                where(table.price.`between?`(minPrice, maxPrice))
                where(table.store.name `ilike?` storeName)
                where(table.store.website `ilike?` storeWebsite)
                where += table.authors {
                    or(
                        firstName `ilike?` authorName,
                        lastName `ilike?` authorName
                    )
                }
                orderBy(table.makeOrders(sortCode))
                select(table.fetch(fetcher))
            }
            .fetchPage(pageIndex, pageSize)
}

info

Here, the 'findBooks' method has too many parameters. This doesn't matter to Kotlin, but it may not be a good design for Java.

If your project is based on the Java language, you can choose to reduce the number of parameters in the Java language by distilling all or some of the parameters into an object.

However, this does not provide substantial benefit to the example presentation here, therefore, we do not do so in order to maintain a high degree of consistency between the Java and Kotlin examples.

The user can use it like this (considering that Java doesn't have a good way to simplify the invocation to a method with many parameters, just a demonstration the kotlin invocation)

val bookRepository = ...Omitted...

val page = bookRepository.findBooks(
    pageIndex = 1,
    pageSize = 2,
    name = "GraphQL",
    maxPrice = BigDecimal(60),
    authorName = "alex",
    fetcher = newFetcher(Book::class).by {
        allScalarFields()
        store {
            name()
        }
        authors {
            firstName()
            lastName()
        }
    }
)

The result is

Page{
    rows=[
        {
            "id":1,
            "name":"Learning GraphQL",
            "edition":1,
            "price":50.00,
            "store":{"id":1,"name":"O'REILLY"},
            "authors":[
                {"id":2,"firstName":"Alex","lastName":"Banks"},
                {"id":1,"firstName":"Eve","lastName":"Procello"}
            ]
        }
    ], 
    totalRowCount=3, 
    totalPageCount=2
}

Query Static DTO

Java
Kotlin

public class BookRepository {

    private static final BookTable T = BookTable.$;

    private final JSqlClient sqlClient;

    public BookRepository(JSqlClient sqlClient) {
        this.sqlClient = sqlClient;
    }

    public <V extends View<Book>> Page<V> findBooks(
        int pageIndex,
        int pageSize,
        @Nullable String sortCode,
        @Nullable Class<V> viewType,
        @Nullable String name,
        @Nullable BigDecimal minPrice,
        @Nullable BigDecimal maxPrice,
        @Nullable String storeName,
        @Nullable String storeWebsite,
        @Nullable String authorName    
    ) {
        return sqlClient
            .createQuery(T)
            .where(T.name().ilikeIf(name))
            .where(T.price().betweenIf(minPrice, maxPrice))
            .where(T.store().name().ilikeIf(storeName))
            .where(T.store().website().ilikeIf(storeWebsite))
            .where(
                T.authors(author ->
                    Predicate.or(
                        author.firstName().ilikeIf(authorName),
                        author.lastName().ilikeIf(authorName)
                    )
                )
            )
            .orderBy(
                Order.makeOrders(
                    T,
                    sortCode != null ? 
                        sortCode :
                        "name asc, edition desc"
                )
            )
            .select(T.fetch(viewType))
            .fetchPage(pageIndex, pageSize);
    }
}

class BookRepository(
    private val sqlClient: KSqlClient
) {
    fun <V: View<Book>> findBooks(
        pageIndex: Int,
        pageSize: Int = 10,
        sortCode: String = "name asc, edition desc",
        viewType: KClass<T>,
        name: String? = null,
        minPrice: BigDecimal? = null,
        maxPrice: BigDecimal? = null,
        storeName: String? = null,
        storeWebsite: String? = null,
        name: String? = null
    ): Page<V> =
        sqlClient
            .createQuery(Book::class) {
                where(table.name `ilike?` name)
                where(table.price.`between?`(minPrice, maxPrice))
                where(table.store.name `ilike?` storeName)
                where(table.store.website `ilike?` storeWebsite)
                where += table.authors {
                    or(
                        firstName `ilike?` authorName,
                        lastName `ilike?` authorName
                    )
                }
                orderBy(table.makeOrders(sortCode))
                select(table.fetch(viewType))
            }
            .fetchPage(pageIndex, pageSize)
}

info

Here, the 'findBooks' method has too many parameters. This doesn't matter to Kotlin, but it may not be a good design for Java.

If your project is based on the Java language, you can choose to reduce the number of parameters in the Java language by distilling all or some of the parameters into an object.

However, this does not provide substantial benefit to the example presentation here, therefore, we do not do so in order to maintain a high degree of consistency between the Java and Kotlin examples.

To demonstrate the invocation, let's first use the DTO language to automatically generate a DTO type that implements the View<Book> interface.

Create any file with the dto extension under the src/main/dto folder, and edit the code as follows:

export com.yourcompany.yourproject.model.Book
    -> package com.yourcompany.yourproject.model.dto

BookView {
    #allScalars
    store {
        id
        name
    }
    authors {
        id
        firstName
        lastName
    }
}

Compile the project, a java/kotlin class named BookView will be automatically generated.

The user can use it like this (considering that Java doesn't have a good way to simplify the invocation to a method with many parameters, just a demonstration the kotlin invocation)

val bookRepository = ...Omitted...

val page = bookRepository.findBooks(
    name = "GraphQL",
    maxPrice = BigDecimal(60),
    authorName = "alex",
    viewType = BookView::class
)

得到如下结果

Page{
    rows=[
        BookView(
            id=1, 
            name=Learning GraphQL, 
            edition=1, 
            price=50.00, 
            store=BookView.TargetOf_store(id=1, name=O'REILLY), 
            authors=[
                BookView.TargetOf_authors(id=2, firstName=Alex, lastName=Banks), 
                BookView.TargetOf_authors(id=1, firstName=Eve, lastName=Procello)
            ]
        )
    ], 
    totalRowCount=3, 
    totalPageCount=2
}

Query Dynamic Entities​

Query Static DTO​

Query Dynamic Entities

Query Static DTO