Formula

Simple formula properties are properties declared with @org.babyfish.jimmer.sql.Formula. There are two usages:

Formula properties based on Java/Kotlin calculation
Formula properties based on SQL calculation

note

Simple formula properties are designed for simple and fast calculations. For complex calculations, use Complex Formula Properties instead.

In Define Entity, we defined two fields for Author: firstName and lastName.

Next, let's add a new property fullName to Author:

fullName = firstName + ' ' + lastName

Then we use two different ways, Java/Kotlin-based calculation and SQL-based calculation, to implement Author.fullName.

1. Java/Kotlin-based Calculation

Depending on simple properties

Java
Kotlin

Author.java
package com.example.model;

import org.babyfish.jimmer.sql.*;

@Entity
public interface Author {

    @Formula(dependencies = {"firstName", "lastName"})
    default String fullName() {
        return firstName() + ' ' + lastName(); 
    }

    ...Omit other properties...
}

Author.kt
package com.example.model

import org.babyfish.jimmer.sql.*

@Entity
interface Author {

    @Formula(dependencies = ["firstName", "lastName"])
    val fullName: String
        get() = "$firstName $lastName"

    ...Omit other properties...
}

It is easy to see that Java/Kotlin-based simple formula properties have the following characteristics:

The property is not abstract (requires default keyword in Java) and directly provides calculation logic implementation.
@Formula's dependencies is specified, indicating that this property depends on Author.firstName and Author.lastName.

That is, the dynamic entity must ensure the presence of both firstName and lastName properties to calculate fullName.

The usage is:

Java
Kotlin

Author author = authorRepository.findNullable(
    1L,
    Fetchers.AUTHOR_FETCHER
        // Query id (implicit + forced) and fullName  
        .fullName()
);
System.out.println(author);

val author = authorRepository.findNullable(
    1L,
    newFetcher(Author::class).by {
        // Query id (implicit + forced) and fullName
        fullName()
    }
);
println(author)

The executed SQL is:

select
    tb_1_.ID,
    /* highlight-start */  
    tb_1_.FIRST_NAME,
    tb_1_.LAST_NAME
    /* highlight-end */
from AUTHOR as tb_1_
where tb_1_.ID = ?  

fullName is a formula property without corresponding field in the database, but depends on firstName and lastName. So this SQL queries FIRST_NAME and LAST_NAME to make its dependent properties present.

Next, let's see what is printed:

{"id":1,"fullName":"Eve Procello"}

We can see that after Jackson serialization (the toString method of entity objects is a shortcut for serialization), there is only fullName but no firstName and lastName.

This is because the object fetcher causes firstName and lastName to be indirectly fetched due to fetching fullName, but they are not directly fetched.

In this case, although the dynamic object has firstName and lastName, they are marked as invisible to Jackson and will not appear in the Jackson serialization result.

note

If the object fetcher directly fetches firstName and lastName, they will definitely appear in the serialization result. Readers can try it themselves, no need to repeat here.

Depending on embedded object

Suppose there is an Embeddable type

Java
Kotlin

NameInfo.java
@Embeddable
public interface NameInfo {
    String firstName();
    String lastName();
}

NameInfo.kt
@Embeddable
interface NameInfo {
    val firstName: String
    val lastName: String
}

If an entity uses this Embeddable type, then the entity properties can depend on its internal properties, for example

Java
Kotlin

Author.java
@Entity
public interface Author {

    NameInfo nameInfo();

    @Formula(dependencies = {"nameInfo.firstName", "nameInfo.lastName"})
    // or it can be written as: @Formula(dependencies = "nameInfo")
    default String fullName() {
        return nameInfo().firstName() + ' ' + nameInfo().lastName();
    }

    ...other properties omitted...
}

Author.kt
@Entity
interface Author {

    val nameInfo: NameInfo

    @Formula(dependencies = ["nameInfo.firstName", "nameInfo.lastName"])
    // or it can be written as: @Formula(dependencies = ["nameInfo"])
    ...other properties omitted...
    val fullName: String
        get() = "${nameInfo.firstName} ${nameInfo.lastName}"
}

info

The usage and execution effect are completely the same as above, no need to repeat.

Depending on association properties

Java
Kotlin

Book.java
@Entity
public interface Book {

    @ManyToMany
    List<Author> authors();

    @Formula(dependencies = "authors")
    default int authorCount() {
        return authors().size();
    }

    @Formula(dependencies = {"authors.firstName", "authors.lastName"})
    default List<String> authorNames() {
        return authors()
            .stream()
            .map(author -> author.firstName() + ' ' + author.lastName())
            .collect(Collectors.toList());
    }

    ...other properties omitted...
}

Book.kt
@Entity
public interface Book {

    @ManyToMany
    val authors: List<Author>

    @Formula(dependencies = "authors")
    val authorCount: Int
        get() = authors.size

    @Formula(dependencies = ["authors.firstName", "authors.lastName"])
    val authorNames: List<String>
        get() = authors.map { "${it.firstName} ${it.lastName}" }

    ...other properties omitted...
}

Execute the following code

Java
Kotlin

BookTable table = BookTable.$;

List<Book> books = sqlClient
    .createQuery(table)
    .where(table.name().eq("Learning GraphQL"))
    .orderBy(table.edition().desc())
    .select(
        table.fetch(
            BookFetcher.$
                .name()
                .edition()
                .authorCount()
                .authorNames()
        )
    )
    .execute();

val books = sqlClient
    .createQuery(Book::class) {
        where(table.name eq "Learning GraphQL")
        orderBy(table.edition().desc())
        select(
            table.fetchBy {
                name()
                edition()
                authorCount()
                authorNames()
            }
        )
    }
    .execute()

Execute, generating two SQL statements

select
    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.EDITION,
from BOOK tb_1_
where
    tb_1_.NAME = ? /* Learning GraphQL */
order by
    tb_1_.EDITION desc

select
    tb_2_.BOOK_ID,
    tb_1_.ID,
    tb_1_.FIRST_NAME,
    tb_1_.LAST_NAME
from AUTHOR tb_1_
inner join BOOK_AUTHOR_MAPPING tb_2_
    on tb_1_.ID = tb_2_.AUTHOR_ID
where
    tb_2_.BOOK_ID in (
        ? /* 3 */, ? /* 2 */, ? /* 1 */
    )
order by
    tb_1_.FIRST_NAME asc,
    tb_1_.LAST_NAME asc

The obtained data is as follows

[
    {
        "id":3,
        "name":"Learning GraphQL",
        "edition":3,
        "authorCount":2,
        "authorNames":["Alex Banks","Eve Procello"]
    }, 
    {
        "id":2,
        "name":"Learning GraphQL",
        "edition":2,
        "authorCount":2,
        "authorNames":["Alex Banks","Eve Procello"]
    }, 
    {
        "id":1,
        "name":"Learning GraphQL",
        "edition":1,
        "authorCount":2,
        "authorNames":["Alex Banks","Eve Procello"]
    }
]

2. SQL-based Calculation

Java
Kotlin

Author.java
package com.example.model;

import org.babyfish.jimmer.sql.*;

@Entity
public interface Author {

    @Formula(sql = "concat(%alias.FIRST_NAME, ' ', %alias.LAST_NAME)")
    String fullName();

    ...Omit other properties...
} 

Author.kt
package com.example.model

import org.babyfish.jimmer.sql.*

@Entity
interface Author {

    @Formula(sql = "concat(%alias.FIRST_NAME, ' ', %alias.LAST_NAME)")
    val fullName: String

    ...Omit other properties...
}

It is easy to see that SQL-based simple formula properties have the following characteristics:

The property is abstract.
@Formula's sql is specified as an SQL expression with a special token %alias

Users cannot know the alias of the current table in the final SQL in advance, so Jimmer stipulates here that %alias represents the actual table column name.

The usage is:

Java
Kotlin

Author author = authorRepository.findNullable(  
    1L,
    Fetchers.AUTHOR_FETCHER
        // Query id (implicit + forced) and fullName
        .fullName()
);
System.out.println(author);

val author = authorRepository.findNullable(
    1L,
    newFetcher(Author::class).by {
        // Query id (implicit + forced) and fullName
        fullName()
    }
);
println(author)

The generated SQL is:

select
    tb_1_.ID,
    /* Here, `%alias` in @Formula.sql is replaced with `tb_1_` */ 
    /* highlight-next-line */
    concat(tb_1_.FIRST_NAME, ' ', tb_1_.LAST_NAME)
from AUTHOR as tb_1_
where tb_1_.ID = ?

The final printed result is:

{"id":1,"fullName":"Eve Procello"}

Comparison

Compared with Java/Kotlin-based simple formula, SQL-based simple formula has one disadvantage and one advantage:

Disadvantage: If the object fetcher fetches firstName, lastName and fullName at the same time, it will cause the final SQL to query three columns:

tb_1_.FIRST_NAME, tb_1_.LAST_NAME and concat(tb_1_.FIRST_NAME, ' ', tb_1_.LAST_NAME).

Obviously, the returned data contains redundancy and is wasteful.
Advantage: Java/Kotlin-based formula properties can only be used as fields to be fetched by object fetchers, and cannot be used by SQL DSL.

SQL-based formula properties will be generated into the strongly typed SQL DSL API by the code generator, available for SQL DSL, such as:

Java's where(table.fullName().eq("Eve Procello")) or Kotlin's where(table.fullName eq "Eve Procello")

tip
Among all formula properties, only SQL-based simple formula properties can be used by SQL DSL.
It is recommended to use databases that support function indexes and use them in combination with function indexes.

Therefore, it is recommended to carefully consider whether @Formula formula properties should be based on Java/Kotlin calculation or SQL calculation.

1. Java/Kotlin-based Calculation​

Depending on simple properties​

Depending on embedded object​

Depending on association properties​

2. SQL-based Calculation​

Comparison​