Pagination Safety

Pagination safety is a feature designed for pagination, but table join functionality provides underlying support.

Problems with Collection Associations

Here, we first introduce two types of associations: reference associations and collection associations.

• Associations decorated with @OneToOne or @ManyToOne are called reference associations.

• Associations decorated with @OneToMany or @ManyToMany are called collection associations.

Please refer to the example:

Collection JOIN Causes Duplicate Results

If we call the entity object corresponding to the first table being queried the aggregate root object, reference associations and collection associations differ in:

• Joining tables via a reference association does not produce duplicate records of the current main object in the SQL query result. For example:

  select 
      b.id as root_id,
      b.name as root_name,
      s.id as associated_id,
      s.name as associated_name
  from book b
  inner join book_store s
      on b.store_id = s.id
  where b.id = 1;

  The query result has no duplicate aggregate root objects:

  root_id	root_name	associated_id	associated_name
  1	Learning GraphQL	1	O'REILLY

• Joining tables via a collection association produces duplicate records of the current main object in the SQL query result:

  select
      b.id as root_id, 
      b.name as root_name,
      a.id as associated_id,
      a.first_name as associated_first_name,
      a.last_name as associated_last_name
  from book b
  inner join book_author_mapping m
      on b.id = m.book_id 
  inner join author a
      on m.author_id = a.id
  where b.id = 1;

  Query result may have duplicate aggregate root objects:

  root_id	root_name	associated_id	associated_first_name	associated_last_name
  1	Learning GraphQL	1	Eve	Procello
  1	Learning GraphQL	2	Alex	Banks

Harms of Duplicate Results

Table joins based on collection associations lead to duplicate data, which causes the following problems:

1. Bugs if the developer forgets deduplication.

2. Even if the developer does not forget deduplication, using java.util.LinkedHashSet for deduplication post factum is not ideal.

   Because the fact that the original database result contains duplicates cannot be altered, and extra network transfer and JVM data processing costs are incurred.

3. Most importantly, unfriendly to pagination queries.

Paginating table join results at the SQL level is often not what people want. More often, people want pagination applied on aggregate root objects.

Take Hibernate for example. In this case, Hibernate has to abandon SQL-level pagination and use memory-level pagination instead. This performs very poorly that Hibernate logs a warning to alert developers. If you have experience with Hibernate, the log below would give you a headache:

firstResult/maxResults specified with collection fetch; applying in memory

tip

Note that Jimmer's other feature Object Fetcher does not use table join to fetch collection associations, so it does not have this problem. Feel free to use it.

In summary, using collection joins in top-level queries has obvious drawbacks, but it remains valuable to use collection joins in subqueries.

Therefore, Jimmer SQL DSL has the following characteristics:

info

1. Table joins based on collection associations are prohibited in top-level queries.
2. Table joins based on collection associations remain allowed in subqueries, update statements and delete statements.

Implicit Subqueries

tip

For collection associations, JOIN is actually not recommended. Implicit subqueries are more recommended.

If you still want to JOIN collection association properties, please continue reading below.

Table and TableEx

There are two kinds of table objects in Jimmer SQL DSL, Table<E> and TableEx<E>.

• Table

  Features: Can only join via reference associations, cannot join via collection associations.

  Java type: org.babyfish.jimmer.sql.ast.table.Table<E>

  Kotlin type: org.babyfish.jimmer.sql.kt.ast.table.KTable<E>

• TableEx

  Features: Can join via any associations.

  Java type: org.babyfish.jimmer.sql.ast.table.TableEx<E>

  Kotlin type: org.babyfish.jimmer.sql.kt.ast.table.KTableEx<E>

Generated Code

To implement strongly typed SQL DSL, Jimmer uses Annotation Processor (Java) or KSP (Kotlin) to generate SQL DSL related source code based on user-defined entity interfaces.

Take the Book entity interface at the beginning of this article as an example. The following two types would be generated automatically:

Java

Generated Java Code

/*
 * BookTable.java 
 */
package org.babyfish.jimmer.sql.example.model;

import java.lang.Integer;
import java.math.BigDecimal;
import javax.persistence.criteria.JoinType;
import org.babyfish.jimmer.sql.ast.Expression;
import org.babyfish.jimmer.sql.ast.PropExpression;
import org.babyfish.jimmer.sql.ast.table.Table;
import org.babyfish.jimmer.sql.ast.table.spi.AbstractTableWrapper;

public class BookTable extends AbstractTableWrapper<Book> {
    public BookTable(Table<Book> table) {
        super(table);
    }

    public Expression<Long> id() {
        return get("id");
    }

    public PropExpression.Str name() {
        return get("name");
    }

    public PropExpression.Num<Integer> edition() {
        return get("edition");
    }

    public PropExpression.Num<BigDecimal> price() {
        return get("price");
    }

    public BookStoreTable store() {
        return join("store");
    }

    public BookStoreTable store(JoinType joinType) {
        return join("store", joinType);
    }
}

/*
 * BookTableEx.java
 */ 
package org.babyfish.jimmer.sql.example.model;

import javax.persistence.criteria.JoinType;
import org.babyfish.jimmer.sql.ast.table.TableEx;

public class BookTableEx extends BookTable implements TableEx<Book> {
    public BookTableEx(TableEx<Book> table) {
        super(table);
    }

    public AuthorTableEx authors() {
        return join("authors");
    }

    public AuthorTableEx authors(JoinType joinType) {
        return join("authors", joinType);
    }
}

Kotlin

Generated Kotlin Code

package org.babyfish.jimmer.example.kt.sql.model

import java.math.BigDecimal
import org.babyfish.jimmer.sql.ast.Selection
import org.babyfish.jimmer.sql.kt.ast.expression.KNonNullPropExpression
import org.babyfish.jimmer.sql.kt.ast.expression.KNullablePropExpression
import org.babyfish.jimmer.sql.kt.ast.table.KNonNullTable
import org.babyfish.jimmer.sql.kt.ast.table.KNonNullTableEx
import org.babyfish.jimmer.sql.kt.ast.table.KNullableTable
import org.babyfish.jimmer.sql.kt.ast.table.KNullableTableEx
import org.babyfish.jimmer.sql.kt.ast.table.KTable
import org.babyfish.jimmer.sql.kt.ast.table.KTableEx

/*
 * Extension for Table<Book>  
 */
public val KTable<Book>.id: KNullablePropExpression<Long>
    get() = get("id")

public val KNonNullTable<Book>.id: KNonNullPropExpression<Long>
    get() = get("id")

public val KTable<Book>.name: KNullablePropExpression<String>
    get() = get("name")

public val KNonNullTable<Book>.name: KNonNullPropExpression<String>
    get() = get("name")

public val KTable<Book>.edition: KNullablePropExpression<Int>
    get() = get("edition")

public val KNonNullTable<Book>.edition: KNonNullPropExpression<Int>
    get() = get("edition")

public val KTable<Book>.price: KNullablePropExpression<BigDecimal>
    get() = get("price")

public val KNonNullTable<Book>.price: KNonNullPropExpression<BigDecimal>
    get() = get("price")

public val KNullableTable<Book>.store: KNullableTable<BookStore>
    get() = join("store")

public val KNonNullTable<Book>.store: KNonNullTable<BookStore>
    get() = join("store")

public val KTable<Book>.`store?`: KNullableTable<BookStore>
    get() = outerJoin("store")

/*
 * Extension for TableEx<Book>
 */

public val KNullableTableEx<Book>.authors: KNullableTableEx<Author>
    get() = join("authors")

public val KNonNullTableEx<Book>.authors: KNonNullTableEx<Author>
    get() = join("authors")

public val KTableEx<Book>.`authors?`: KNullableTableEx<Author>
    get() = outerJoin("authors")

Observing the two auto-generated types, we can see:

• BookTableEx inherits BookTable.
• BookTable does not support collection associations, but supports regular fields and reference associations (which is store in this example).
• BookTableEx adds support for collection associations (which is authors in this example).

Therefore, jimmer-sql API follows this pattern:

• Top-level queries can only be created based on Table.
• Subqueries, update statements and delete statements can be created based on either Table or TableEx.

Next, we demonstrate the contrast between top-level queries and subqueries with examples.

Top-Level Queries Must Be Based on Table

So your code looks like:

Java

BookTable table = Tables.BOOK_TABLE;

List<Book> books = sqlClient
    .createQuery(table)
    .where(
        table
            .name() // Can access regular field name  
            .eq("Book Name")
    )
    .where(
        table
            .store() // Can also join reference association store
            .name()
            .eq("Store Name")
    )
    /*
     * However, cannot use "table.authors()" because `authors()`
     * is defined in `BookTableEx` instead of `BookTable`.
     *  
     * That is, joining collection associations is prohibited  
     * at compile time in top-level queries.
     */ 
    .select(table)
    .execute();

Kotlin

val books = sqlClient
    .createQuery(Book::class) {
        where(
            table
                .name // Can access regular field name
                eq "Book Name" 
        )
        where(
            table
                .store // Can also join reference association `store`
                .name
                eq "Store Name"
        )
        /*
         * However, cannot use "table.authors" because the extension property `authors`
         * is defined on `TableEx<Book>` instead of `Table<Book>`.
         *  
         * That is, joining collection associations is prohibited 
         * at compile time in top-level queries.
         */
        select(table)
    }
    .execute()

info

• Java: createQuery takes parameter specified by user, can only be Table type. TableEx would error.

• Kotlin: createQuery creates table object automatically. The automatic variable table in lambda is inferred to Table instead of TableEx.

The final generated SQL:

select
    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.EDITION,
    tb_1_.PRICE,
    tb_1_.STORE_ID
from BOOK as tb_1_
inner join BOOK_STORE as tb_2_ on tb_1_.STORE_ID = tb_2_.ID
where
    tb_1_.NAME = ?
and 
    tb_2_.NAME = ? 

TableEx Allowed for Subqueries

Unlike top-level queries, subqueries, update statements and delete statements allow TableEx.

In the example below, Author.books is the same as Book.authors discussed above, which is also a many-to-many association.

Java

BookTable table = Tables.BOOK_TABLE;

// `author` used in subquery, use TableEx
AuthorTableEx author = TableExes.AUTHOR_TABLE_EX;

List<Book> books = sqlClient
    .createQuery(table)
    .where(sqlClient
        .createSubQuery(author)
        .where(
            author
                // `author` is TableEx, 
                // so collection association `books` is allowed
                .books()
                .eq(table),

            author.firstName().eq("Alex")
        )
        .exists()
    )
    .select(table)
    .execute();

Kotlin

val books = sqlClient
    .createQuery(Book::class) {

        // In parent query `table` is Table<Book>
        
        where(
            exists(
                wildSubQuery(Author::class) {

                    // Subquery's `table` overrides parent's `table`,
                    // and the Subquery's `table` is `TableEx<Author>`
                    
                    where(
                        table
                            // Subquery's `table` is TableEx,
                            // so collection association `books` is allowed
                            .books eq
                            parentTable,

                        table.firstName.eq("Alex")
                    )
                }
            )
        )
        select(table)
    }
    .execute()

info

• Java: createSubQuery takes parameter specified by user, not enforced but suggested to be TableEx type.

• Kotlin: wildSubQuery (or subQuery) creates table object automatically. The automatic variable table in lambda is inferred to TableEx instead of Table.

The final generated SQL:

select
    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.EDITION,
    tb_1_.PRICE,
    tb_1_.STORE_ID
from BOOK as tb_1_
where exists(
    select 1
    from AUTHOR as tb_2_
    // highlight-start
    inner join BOOK_AUTHOR_MAPPING as tb_3_
        on tb_2_.ID = tb_3_.AUTHOR_ID
    // highlight-end
    where
        tb_3_.BOOK_ID = tb_1_.ID
    and
        tb_2_.FIRST_NAME = ?
)  

asTableEx

Prohibiting collection associations in top-level queries is reasonable in most cases, but not all cases.

For example, the user does not query entire objects, but individual fields, and uses the SQL keyword distinct to offset the side effects of joining collection associations. This scenario is perfectly reasonable.

Therefore, prohibiting collection associations in top-level queries is a soft constraint rather than a hard constraint. It can be easily overridden.

Java

BookTable book = Tables.BOOK_TABLE;

List<Long> bookIds = sqlClient
    .createQuery(book)
    .where(
        book
            .asTableEx() ❶
            .authors()
            .firstName()
            .ilike("A%")
    )
    .select(book.id()) 
    .distinct() ❷
    .execute();

Kotlin

val bookIds = sqlClient
    .createQuery(Book::class) {
        where(
            table
                .asTableEx() ❶
                .authors
                .firstName ilike "A%"
        )
        select(table.id)
    }
    .distinct() ❷
    .execute()

info

• ❶ The developer informs Jimmer that they know what they are doing, asking Jimmer to allow joining collection associations.

• ❷ The developer takes responsibility for their own actions. If there are additional operations here, it should be distinct rather than pagination related.

The final generated SQL:

select
    distinct tb_1_.ID
from BOOK as tb_1_
/* highlight-start */  
inner join BOOK_AUTHOR_MAPPING as tb_2_
    on tb_1_.ID = tb_2_.BOOK_ID
inner join AUTHOR as tb_3_
    on tb_2_.AUTHOR_ID = tb_3_.ID
/* highlight-end */
where lower(tb_3_.FIRST_NAME) like ?