1. Basic Query

There are two concepts in Jimmer

• Query multiple columns

• Query one object column and specify multiple properties of the queried objects

They are completely different concepts

Query multiple columns

Java

BookTable table = BookTable.$;

List<Tuple3<Long, String, Integer>> tuples = sqlClient
    .createQuery(table)
    .where(table.name().eq("GraphQL in Action"))
    .orderBy(table.edition().desc())
    .select(
        table.id(),
        table.name(),
        table.edition()
    )
    .execute();

Kotlin

val tuples = sqlClient
    .createQuery(Book::class) {
        where(table.name eq "GraphQL in Action")
        orderBy(table.edition.desc())
        select(
            table.id,
            table.name,
            table.edition
        )
    }
    .execute()

In Jimmer, all queries except queries that return only one column are multi-column queries. This example queries three columns, so the return type is 'Tuple3<T1, T2, T3>'.

The following data is obtained

[
    {
        "_1" : 12,
        "_2" : "GraphQL in Action",
        "_3" : 3
    }, {
        "_1" : 11,
        "_2" : "GraphQL in Action",
        "_3" : 2
    }, {
        "_1" : 10,
        "_2" : "GraphQL in Action",
        "_3" : 1
    }
]

Specify properties of the queried object

Java

BookTable table = BookTable.$;

List<Book> books = sqlClient
    .createQuery(table)
    .where(table.name().eq("GraphQL in Action"))
    .orderBy(table.edition().desc())
    .select(
        table.fetch(
            BookTableFetcher.$
                // `id()` is implicit and is always queried
                .name()
                .edition()
        )
    )
    .execute();

Kotlin

val books = sqlClient
    .createQuery(Book::class) {
        where(table.name eq "GraphQL in Action")
        orderBy(table.edition.desc())
        select(
            table.fetchBy {
                // `id()` is implicit and is always queried
                name()
                edition()
            }
        )
    }
    .execute()

The result is

[
    {
        "id" : 12,
        "name" : "GraphQL in Action",
        "edition" : 3
    }, 
    {
        "id" : 11,
        "name" : "GraphQL in Action",
        "edition" : 2
    }, 
    {
        "id" : 10,
        "name" : "GraphQL in Action",
        "edition" : 1
    } 
]

It's easy to see that although this query has only one column, we can precisely control the format of this object.

In this example, not all the properties of the Book object are queried ('price' and many-to-one association 'store' are not queried), because the Jimmer entity type 'Book' supports dynamic properties, the number of properties queried for the object does not affect the return type, and it is always of the 'Book' type.

tip

Jimmer entity objects are dynamic and can be typed uniformly to represent objects of any format (format is also called shape).

Therefore, we do not need to use a multi-column query to control the return format under Jimmer, but we should use a single-column query to return a column of objects and flexibly control the object format.

This results in Jimmer making less use of tuple-based multi-column queries and being more object-oriented.

Combine both

Java

BookTable table = BookTable.$;

List<Tuple2<Book, Integer>> tuples = sqlClient
    .createQuery(table)
    .where(table.name().eq("GraphQL in Action"))
    .orderBy(table.edition().desc())
    .select(
            table.fetch(
                    BookFetcher.$
                            .allScalarFields()
            ),
            Expression.numeric().sql(
                    Integer.class,
                    "row_number() over(partition by %e order by %e desc)",
                    new Expression<?>[] { table.storeId(), table.price() }
            )
    )
    .execute();

Kotlin

val tuples = sqlClient
    .createQuery(Book::class) {
        where(table.name eq "GraphQL in Action")
        orderBy(table.edition.desc())
        .select(
            table.fetchBy {
                allScalarFields()
            },
            sql(Int::class, "row_number() over(partition by %e order by %e desc)") {
                expression(table.storeId)
                expression(table.price)
            }
        )
    }
    .execute()

The result is

[
    {
        "_1":{
            "id":12,
            "name":"GraphQL in Action",
            "edition":3,
            "price":80
        },
        "_2":3
    },
    {
        "_1":{
            "id":11,
            "name":"GraphQL in Action",
            "edition":2,
            "price":81
        },
        "_2":2
    },
    {
        "_1":{
            "id":10,
            "name":"GraphQL in Action",
            "edition":1,
            "price":82
        },
        "_2":1
    }
]

This example queries two columns

• The first column is the object type, and the object format is 'allScalarFields' (contains all non-associated attributes)*

• The second column is a native SQL expression that calls the underlying database parse function ('%e' indicates a DSL expression that is used to implant the native SQL code)

It's clearly a combination of the two.