Complex Calculation

@Transient Annotation

Jimmer entities can use @org.babyfish.jimmer.sql.Transient to define a property unrelated to the database table structure.

Java
Kotlin

BookStore.java
package com.example.model;

import org.babyfish.jimmer.sql.*;

public interface BookStore {
    
    ...Omit other properties...

    @Transient
    Object customData();
}

BookStore.kt
package com.example.model

import org.babyfish.jimmer.sql.*

interface BookStore {

    ...Omit other properties...

    @Transient
    val customData: Any?
}

Here, no parameters are specified for the @Transient annotation, so the current data is just custom user data unrelated to any ORM behavior.

caution

Only when parameters are specified for the @Transient annotation will the current property be a complex calculation property.

So what are the parameters of the @Transient annotation?

Jimmer provides an interface for complex calculation properties:

Java: org.babyfish.jimmer.sql.TransientResolver<ID, V>
Kotlin: org.babyfish.jimmer.sql.kt.KTransientResolver<ID, V>

This interface allows users to customize the data calculation process.

Users develop a class that implements this interface and have it managed by Spring.

How to implement this class will be explained in detail later, but for the purpose of expression here, let's assume the user class that implements this interface is CustomerDataResolver. The @Transient annotation parameters should be written as:

If the project is a single project where entity classes can reference this class, then @Transient(CustomerDataResolver.class) or @Transient(CustomerDataResolver::class).
If the project has multiple subprojects where entity classes cannot reference this class, then @Transient(ref = "customerDataResolver").

Where the string "customerDataResolver" represents the name of this object in the Spring context.

Scalar Calculation: `BookStore.avgPrice`

In this section, we will add a calculation property BookStore.avgPrice to BookStore with the type java.math.BigDecimal.

Define Resolver for `avgPrice`

Each complex calculation property corresponds to a TransientResolver implementation class.

Before defining the calculation property BookStore.avgPrice, let's first define BookStoreAvgPriceResolver:

Java
Kotlin

BookStoreAvgPriceResolver.java
package com.example.business.resolver;

import org.babyfish.jimmer.sql.*;
import org.babyfish.jimmer.sql.TransientResolver;
import org.springframework.stereotype.Component;

@Component 
public class BookStoreAvgPriceResolver implements TransientResolver<Long, BigDecimal> {
    
    @Override
    public Map<Long, BigDecimal> resolve(Collection<Long> ids) {
        To be implemented later
    }

    @Override
    public BigDecimal getDefaultValue() {
        return BigDecimal.ZERO; 
    }
}

BookStoreAvgPriceResolver.kt
package com.example.business.resolver

import org.babyfish.jimmer.sql.*
import org.babyfish.jimmer.sql.kt.KTransientResolver
import org.springframework.stereotype.Component

@Component
class BookStoreAvgPriceResolver : KTransientResolver<Long, BigDecimal> {

    override fun resolve(ids: Collection<Long>): Map<Long, BigDecimal> {
        To be implemented later
    }

    override fun getDefaultValue(): BigDecimal = 
        BigDecimal.ZERO
}

The base interface TransientResolver/KTransientResolver has two generic parameters:
- The 1st generic parameter: The type of the id property of the entity to which the calculation property belongs.
  
  In this example, BookStore.avgPrice to be defined belongs to the BookStore entity whose id type is long, so the generic parameter here is Long.
- The 2nd generic parameter: The return data type of the calculation property.
  
  In this example, the type of BookStore.avgPrice to be defined is BigDecimal, so the generic parameter here is BigDecimal.
resolve is a method of the base interface that must be implemented for users to complete calculation through it.

info
The parameter type of the resolve method is Collection<Long> instead of Long; its return type is Map<Long, BigDecimal>.
This is very important. It means that BookStore.avgPrice is not calculated one by one against BookStore.id, but is calculated in batches against multiple BookStore.id at a time.
This is designed to prevent the N+1 problem caused by calculation properties.
This design is almost the same as MappedBatchLoader in the GraphQL field, which is the standard programming model for all similar domains.
getDefaultValue is an optional method of the base interface to implement.

For the resolve method, if the length of the returned Map is less than the length of the ids parameter collection passed in, it means some data does not have calculation results. The calculation value corresponding to each such data is considered null.

However, if the calculation property (BookStore.avgPrice in this example) is non-null, it will cause problems. Users can override getDefaultValue() to return a non-null default value to resolve this issue.
caution
For calculation properties that do not allow null, TransientResolver must:
- Either ensure the keySet of the Map returned by the resolve method contains all parameters
- Or override getDefaultValue to return a non-null default value

Implement Resolver for `avgPrice`

Java
Kotlin

BookStoreAvgPriceResolver.java
package com.example.business.resolver;

import org.babyfish.jimmer.sql.*;
import org.babyfish.jimmer.sql.ast.tuple.Tuple2;
import org.springframework.stereotype.Component;

@Component
public class BookStoreAvgPriceResolver implements TransientResolver<Long, BigDecimal> {

    private final JSqlClient sqlClient;

    // Constructor injection
    public BookStoreAvgPriceResolver(JSqlClient sqlClient) {
        this.sqlClient = bookStoreRepository; 
    }
    
    @Override  
    public Map<Long, BigDecimal> resolve(Collection<Long> ids) {
        return Tuple2.toMap(
            sqlClient
                .createQuery(table) 
                .where(table.storeId().in(storeIds)) ❶
                .groupBy(table.storeId()) ❷
                .select(
                    table.storeId(),  
                    table.price().avg() ❸
                )
                .execute()
        );
    }

    ...Omit other methods...
}

BookStoreAvgPriceResolver.kt
package com.example.business.resolver

import org.babyfish.jimmer.sql.*
import org.babyfish.jimmer.sql.kt.*
import org.springframework.stereotype.Component

@Component
class BookStoreAvgPriceResolver(
    // Constructor injection
    private val sqlClient: KSqlClient  
) : KTransientResolver<Long, BigDecimal> {

    override fun resolve(ids: Collection<Long>): Map<Long, BigDecimal> =
        sqlClient
            .createQuery(Book::class) {
                where(table.store.id valueIn storeIds) ❶
                groupBy(table.store.id) ❷
                select(
                    table.store.id,
                    avg(table.price).asNonNull() ❸ 
                )
            }
            .execute()
            .associateBy({it._1}) {
                it._2
            }

    ...Omit other functions...
}

❶ Filter the foreign key STORE_ID in the BOOK table to limit the query scope. Only calculate the average price of books under the bookstores that need calculation at the moment, not all bookstores in the database.
❷ Group by the foreign key STORE_ID in the BOOK table.
❸ Calculate the average of book prices within each group.

avg: Calculate the average of Book.price within the group.

note
In the Kotlin code there is an asNonNull().
According to SQL standards, if the aggregate function avg is not used with group, its return value can be null without original data. So in Kotlin avg is defined to return a nullable type.
However, when the aggregate function avg is used with group, it cannot return null. So asNonNull() is called to get a non-null expression.

Define `avgPrice`

Now that the BookStoreAvgPriceResolver class is complete, we can add the calculation property avgPrice to the BookStore entity:

Java
Kotlin

BookStore.java
package com.example.model;

import com.example.business.resolver.BookStoreAvgPriceResolver; ❶

import org.babyfish.jimmer.sql.*;

public interface BookStore {
    
    ...Omit other properties...

    @Transient(BookStoreAvgPriceResolver.class) ❷
    BigDecimal avgPrice();
}

BookStore.kt
package com.example.model

import com.example.business.resolver.BookStoreAvgPriceResolver ❶

import org.babyfish.jimmer.sql.*

interface BookStore {

    ...Omit other properties...

    @Transient(BookStoreAvgPriceResolver::class) ❷
    val avgPrice: BigDecimal
}

If it is a single project, the BookStoreAvgPriceResolver class can be referenced here.
Define the calculation property BookStore.avgPrice, and specify the class introduced at ❶ for its @Transient annotation to tell Jimmer the calculation rule for the calculation property.

caution
If it is a multi-project where the code structure has been split, the import statement at ❶ is invalid. In this case, ❷ must be @Transient(ref = "bookStoreAvgPriceResolver").
That is, use the name of this object in the Spring context.

Fetch `avgPrice`

Java
Kotlin

List<BookStore> stores = bookStoreRepository.findAll(
    Fetchers.BOOK_STORE_FETCHER
        .name()
        .avgPrice()
);
System.out.println(stores);

val stores = bookStoreRepository.findAll(
    newFetcher(BookStore::class).by {
        name()
        avgPrice()
    }
)
println(stores)

Printed result:

[
    {
        "id":2,
        "name":"MANNING",
        "avgPrice":80.333333333333
    },
    {
        "id":1,
        "name":"O'REILLY",
        "avgPrice":57.944444444444
    }
]

Executed SQL:

/* Step 1: Query aggregate root objects, i.e. BookStore */  
select tb_1_.ID, tb_1_.NAME from BOOK_STORE as tb_1_

/* Step 2: Calculate `avgPrice` property for BookStore objects with id 1 and 2 */
select
    tb_1_.STORE_ID, 
    /* highlight-next-line */
    avg(tb_1_.PRICE)
from BOOK tb_1_ 
where
    /* highlight-next-line */
    tb_1_.STORE_ID in (
        ? /* 2 */, ? /* 1 */  
    )
group by
    /* highlight-next-line */  
    tb_1_.STORE_ID

Associative Calculation: `BookStore.newestBooks`

Clarify Requirements

In the previous section we demonstrated the calculation property BookStore.avgPrice, which is obviously a non-associative property.

In this section, we will add a calculation property BookStore.newestBooks to BookStore with the type java.util.List<Book>, which is clearly an association property.

To explain why this example needs to add a calculation property BookStore.newestBooks, let's first look at the characteristics of the original association property BookStore.books:

Java
Kotlin

Book store = bookStoreRepository.findNullable(
    1L,  
    Fetchers.BOOK_STORE_FETCHER
        .name()
        .books(
            Fetchers.BOOK_FETCHER
                .name()
                .edition()
        )
);
System.out.println(store);

val store = bookStoreRepository.findNullable(
    1L,
    newFetcher(BookStore::class).by {
        name()
        books {
            name()
            edition()
        }
    }
)  
println(store)

The query result is:

{
    "id":1,
    "name":"O'REILLY", 
    "books":[
        {
            "id":6,
            "name":"Effective TypeScript",
            "edition":3
        },
        {
            "id":5,
            "name":"Effective TypeScript",
            "edition":2
        },
        {
            "id":4,
            "name":"Effective TypeScript",
            "edition":1
        },
        {
            "id":3,
            "name":"Learning GraphQL",
            "edition":3  
        },
        {
            "id":2,
            "name":"Learning GraphQL",
            "edition":2
        },
        {
            "id":1,
            "name":"Learning GraphQL",
            "edition":1
        },
        {
            "id":9, 
            "name":"Programming TypeScript",
            "edition":3
        },
        {
            "id":8,
            "name":"Programming TypeScript",
            "edition":2
        },
        {
            "id":7,
            "name":"Programming TypeScript",
            "edition":1
        }
    ]
}

We can see that in the original BookStore.books association, there are many books with the same name in the bookstore.

For example, there are a total of three books named "Effective TypeScript", with different edition values: 3, 2, 1.

Now we want to create a new calculation property BookStore.newestBooks through calculation. It ensures that the returned book collection has no duplicate name problem. For each unique book name, only the book with the highest edition, i.e. the maximum edition, is taken.

Define Resolver for `newestBooks`

Each complex calculation property corresponds to a TransientResolver implementation class.

Before defining the calculation property BookStore.newestBooks, let's first define BookStoreNewestBooksResolver:

Java
Kotlin

BookStoreNewestBooksResolver.java
package com.example.business.resolver;

import org.babyfish.jimmer.sql.*;
import org.springframework.stereotype.Component;

@Component
public class BookStoreNewestBooksResolver implements TransientResolver<Long, List<Long>> { ❶
    
    @Override
    public Map<Long, List<Long>> resolve(Collection<Long> ids) { ❷
        ...To be implemented later...
    }

    @Override
    public List<Long> getDefaultValue() {
        Collections.emptyList(); 
    }

package com.example.business.resolver

import org.babyfish.jimmer.sql.kt.*
import org.springframework.stereotype.Component

@Component
class BookStoreNewestBooksResolver : KTransientResolver<Long, List<Long>> { ❶

    override fun resolve(ids: Collection<Long>): Map<Long, List<Long>> { ❷
        ...To be implemented later...
    }

    override fun getDefaultValue(): List<Long> =
        emptyList()
}

❶ The base interface TransientResolver/KTransientResolver has two generic parameters:
- The 1st generic parameter: The type of the id property of the entity to which the calculation property belongs.
  
  In this example, BookStore.newestBooks to be defined belongs to the BookStore entity whose id type is long, so the generic parameter here is Long.
- The 2nd generic parameter: The return data type of the calculation property.
  
  In this example, the type of BookStore.newestBooks to be defined is List<Book>.
  - Since it is a collection type, the generic parameter contains List here.
  - Book is an entity type. Jimmer stipulates that the entity type here needs to be replaced with its id type, and Book.id is of type long.
  In summary, the 2nd generic parameter is List<Long>.
❷ resolve is a method of the base interface that must be implemented for users to complete calculation through it.

info
The parameter type of the resolve method is Collection<Long> instead of Long; its return type is Map<Long, List<Long>>.
This is very important. It means that BookStore.newestBooks is not calculated one by one against BookStore.id, but is calculated in batches against multiple BookStore.id at a time.
This is designed to prevent the N+1 problem caused by calculation properties.
This design is almost the same as MappedBatchLoader in the GraphQL field, which is the standard programming model for all similar domains.
❸ getDefaultValue is an optional method of the base interface to implement.

For the resolve method, if the length of the returned Map is less than the length of the ids parameter collection passed in, it means some data does not have calculation results. The calculation value corresponding to each such data is considered null.

However, if the calculation property (BookStore.newestBooks in this example) is non-null, it will cause problems. Users can override getDefaultValue() to return a non-null default value to resolve this issue.
caution
If calculation properties do not allow null, for their TransientResolver implementations:
- Either ensure the keySet of the Map returned by the resolve method contains all parameters
- Or override getDefaultValue to return a non-null default value

Implement Resolver for `newestBooks`

Java
Kotlin

package com.example.business.resolver;

import java.util.Collections;

import org.babyfish.jimmer.sql.*;
import org.babyfish.jimmer.sql.ast.tuple.Tuple2;
import org.springframework.stereotype.Component;

@Component
public class BookStoreNewestBooksResolver implements TransientResolver<Long, List<Long>> {

    private final JSqlClient sqlClient;

    // Constructor injection
    public BookStoreAvgPriceResolver(JSqlClient sqlClient) {
        this.sqlClient = sqlClient;
    }
    
    @Override
    public Map<Long, List<Long>> resolve(Collection<Long> ids) {
        return Tuple2.toMultiMap(
            sqlClient
                .createQuery(table)
                .where(
                    Expression.tuple( ❶
                        table.name(),  
                        table.edition()
                    ).in(
                        sqlClient.createSubQuery(table) ❷
                            .where(table.storeId().in(ids)) ❸
                            .groupBy(table.name()) ❹
                            .select(
                                table.name(),
                                table.edition().max() ❺  
                            )
                    )
                )
                .select(
                    table.storeId(),
                    table.id()
                )
                .execute()
        );
    }

    ...Omit other code...
}

package com.example.business.resolver

import org.babyfish.jimmer.sql.kt.*
import org.springframework.stereotype.Component

@Component
class BookStoreNewestBooksResolver(
    // Constructor injection
    private val sqlClient: KSqlClient
) : KTransientResolver<Long, List<Long>> {

    override fun resolve(ids: Collection<Long>): Map<Long, List<Long>> =
        sqlClient
            .createQuery(Book::class) {
                where(
                    tuple( ❶
                        table.name,
                        table.edition  
                    ) valueIn subQuery(Book::class) { ❷
                        where(table.store.id valueIn storeIds) ❸
                        groupBy(table.name) ❹
                        select(
                            table.name,
                            max(table.edition).asNonNull() ❺
                        )
                    }
                )
                select(
                    table.store.id,
                    table.id
                )
            }
            .execute()
            .groupBy({it._1}) {
                it._2
            }

    ...Omit other code...
}

❶ Book.name and Book.edition form an SQL tuple.
❷ The tuple has two columns of type String and int. The subquery also has two columns of type String and int. The two match perfectly and can use the in operator.
❸ Limit the query scope to only calculate the latest version of books that need to be queried at the moment, not all bookstores in the database.

Imposing calculation scope limiting conditions on the subquery performs better than imposing them on the parent query.
❹ Group by book name, so books of the same name must belong to the same group.
❺ For each group of books with the same name, find the maximum edition.

Define `newestBooks`

Now that the BookStoreNewestBooksResolver class is complete, we can add the calculation property newestBooks to the BookStore entity:

Java
Kotlin

package com.example.model;

import com.example.business.resolver.BookStoreNewestBooksResolver; ❶

import org.babyfish.jimmer.sql.*;

public interface BookStore {
    
    ...Omit other properties...

    @Transient(BookStoreNewestBooksResolver.class) ❷
    List<Book> newestBooks();
}

package com.example.model

import com.example.business.resolver.BookStoreNewestBooksResolver ❶

import org.babyfish.jimmer.sql.*

interface BookStore {

    ...Omit other properties...

    @Transient(BookStoreNewestBooksResolver::class) ❷
    val newestBooks: List<Book> 
}

If it is a single project, the BookStoreNewestBooksResolver class can be referenced here.
Define the calculation property BookStore.newestBooks, and specify the class introduced at ❶ for its @Transient annotation to tell Jimmer the calculation rule for the calculation property.

caution
If it is a multi-project where the code structure has been split, the import statement at ❶ is invalid. In this case, ❷ must be @Transient(ref = "bookStoreNewestBooksResolver").
That is, use the name of this object in the Spring context.

Fetch `newestBooks`

Java
Kotlin

List<BookStore> stores = bookStoreRepository.findAll(
    Fetchers.BOOK_STORE_FETCHER
        .name()
        .newestBooks( ❶

            ❷
            Fetchers.BOOK_FETCHER
                allScalarFields()
                .authors(
                    Fetchers.AUTHOR_FETCHER
                        .allScalarFields()
                )
        )
);
System.out.println(stores); 

val stores = bookStoreRepository.findAll(
    newFetcher(BookStore::class).by {
        name()
        newestBooks { ❶

            ❷
            allScalarFields()
            authors {
                allScalarFields()
            }
        }
    }
)
println(stores)

Fetch the calculation property BookStore.newestBooks
The calculation property itself is also an association property, so the shape of its associated objects can be controlled by a deeper child fetcher.

The printed result is:

[
    {
        "id":2,
        "name":"MANNING",
        "newestBooks":[
            {
                "id":12,
                "name":"GraphQL in Action",
                "edition":3, // This edition is max, no duplicate names
                "price":80,
                "authors":[
                    {
                        "id":5,
                        "firstName":"Samer",
                        "lastName":"Buna",
                        "gender":"MALE"
                    }
                ]
            }
        ]
    },
    {
        "id":1,
        "name":"O'REILLY",
        "newestBooks":[
            {
                "id":3,
                "name":"Learning GraphQL",
                "edition":3, // This edition is max, no duplicate names
                "price":51,
                "authors":[
                    {
                        "id":2,
                        "firstName":"Alex",
                        "lastName":"Banks",
                        "gender":"MALE"
                    },
                    {
                        "id":1,
                        "firstName":"Eve",
                        "lastName":"Procello",
                        "gender":"FEMALE"
                    }
                ]
            },
            {
                "id":6,
                "name":"Effective TypeScript",
                "edition":3, // This edition is max, no duplicate names
                "price":88,
                "authors":[
                    {
                        "id":3,
                        "firstName":"Dan",
                        "lastName":"Vanderkam",
                        "gender":"MALE"
                    }
                ]
            },
            {
                "id":9,
                "name":"Programming TypeScript",
                "edition":3, // This edition is max, no duplicate names
                "price":48,
                "authors":[
                    {
                        "id":4,
                        "firstName":"Boris",
                        "lastName":"Cherny",
                        "gender":"MALE"
                    }
                ]
            }
        ]
    }
]

The generated SQL is:

/* Step 1: Query aggregate root objects, i.e. BookStore */
select tb_1_.ID, tb_1_.NAME from BOOK_STORE as tb_1_

/* Step 2: For BookStore objects with id 1 and 2, calculate id sets of all Books that `newestBooks` can associate to */ 
/* highlight-start */
select
    tb_1_.STORE_ID,
    tb_1_.ID
from BOOK tb_1_
where
    (tb_1_.NAME, tb_1_.EDITION) in (
        select
            tb_3_.NAME, 
            max(tb_3_.EDITION)
        from BOOK tb_3_
        where
            tb_3_.STORE_ID in (
                ? /* 2 */, ? /* 1 */  
            )
        group by
            tb_3_.NAME
    )
/* highlight-end */

/* Step 3: For associated Book ids, query non-associative fields */
select
    tb_1_.ID, tb_1_.NAME, tb_1_.EDITION, tb_1_.PRICE
from BOOK as tb_1_
where tb_1_.ID in (?, ?, ?, ?)  

/* Step 4: For associated Books, query associable Authors */  
select
    tb_2_.BOOK_ID, tb_1_.ID, tb_1_.FIRST_NAME, tb_1_.LAST_NAME, tb_1_.GENDER
from AUTHOR as tb_1_
inner join BOOK_AUTHOR_MAPPING as tb_2_
    on tb_1_.ID = tb_2_.AUTHOR_ID
where
    tb_2_.BOOK_ID in (?, ?, ?, ?) 

tip

This example shows that when the calculation property itself is also an association property, the shape of its associated objects can be controlled by a deeper child fetcher.

Since there is a deeper child fetcher, of course both native ORM association properties and other calculation association properties can be included.

That is, in the process of querying complex data structures by object fetchers, the fetching tasks of ORM association properties and calculation association properties can be arbitrarily mixed.

The fetching task of ORM association properties is actually SQL operations.

(At least before we introduce cache, it can be considered so)
As mentioned earlier, Jimmer does not impose any restrictions on the calculation methods of calculation properties. You can even use any technology other than SQL, such as OLAP technologies, to implement the calculation process (this documentation focuses on Jimmer itself, so examples also use Jimmer to implement the calculation process).

That is, the fetching task of calculation properties is not necessarily SQL operations.

Therefore, the functionality provided by object fetchers is actually an arbitrary mix of SQL and non-SQL operations.

@Transient Annotation​

Scalar Calculation: BookStore.avgPrice​

Define Resolver for avgPrice​

Implement Resolver for avgPrice​

Define avgPrice​

Fetch avgPrice​

Associative Calculation: BookStore.newestBooks​

Clarify Requirements​

Define Resolver for newestBooks​

Implement Resolver for newestBooks​

Define newestBooks​

Fetch newestBooks​