Association Path Chaining

How Jimmer solves the dynamic join problem will be discussed in subsequent docs. This article first introduces the join style in Jimmer.

Basic Concepts

Firstly, Jimmer does not support cartesian products which is exists in theoretical and should be carefully avoided in actual projects.

Thus, Jimmer's SQL DSL does not need to support multiple from clauses. All cross-table operations are unified as join.

In Jimmer, all table joins are implicit, formed by association paths of arbitrary length, and are considered as multiple table joins. For example:

Java

public List<Book> findBooksByStoreCityName(@Nullable String storeCityName) {

    BookTable table = Tables.BOOK_TABLE;

    return sqlClient
        .createQuery(table)
        .whereIf(
            storeCityName != null && !storeCityName.isEmpty(),
            table.store().city().name().eq(name) 
        )
        .orderBy(table.name().asc(), table.edition().desc())
        .select(table)
        .execute();
}

Kotlin

fun findBooksByStoreCityName(name: storeCityName?): List<Book> =  
    sqlClient
        .createQuery(Book::class) {
            storeCityName?.takeIf { it.isNotEmpty() }?.let {
                where(table.store.city.name eq it)
            }
            orderBy(table.name.asc(), table.edition.desc())
            select(table)
        }
        .execute()

info

In the above code, table.store().city() in Java or table.store.city in Kotlin is the association path.

The association path length is unlimited. Each association reference corresponds to a SQL join operation.

from BOOK tb_1_
// highlight-next-line  
inner join BOOK_STORE tb_2_ // `.store`
    on tb_1_.STORE_ID = tb_2_.ID 
// highlight-next-line
inner join CITY tb_3_ // `.city`
    on tb_2_.CITY_ID = tb_3_.ID

• For findBooksByStoreCityName(null), the generated SQL is:

  select
      tb_1_.ID,
      tb_1_.NAME, 
      tb_1_.EDITION,
      tb_1_.PRICE,
      tb_1_.STORE_ID
  from BOOK tb_1_  
  order by
      tb_1_.NAME asc,
      tb_1_.EDITION desc

• For findBooksByStoreCityName("ChengDu"), the generated SQL is:

  select
      tb_1_.ID,
      tb_1_.NAME,
      tb_1_.EDITION,
      tb_1_.PRICE,
      tb_1_.STORE_ID 
  from BOOK tb_1_
  // highlight-next-line
  inner join BOOK_STORE tb_2_ // `.store`
      on tb_1_.STORE_ID = tb_2_.ID
  // highlight-next-line
  inner join CITY tb_3_ // `.city`
      on tb_2_.CITY_ID = tb_3_.ID
  where
      // `.name eq "ChengDu"` 
      // highlight-next-line
      tb_3_.NAME = ? /* ChengDu */ 
  order by
      tb_1_.NAME asc,
      tb_1_.EDITION desc

Outer Joins

The association paths .store and .city in the above example represent inner joins.

It's also easy to express outer joins, taking left outer join as an example:

Java

table.store().city(JoinType.LEFT) 

Kotlin

table.store.`city?`  

caution

To fully leverage Kotlin's strengths and optimize its developer experience, Jimmer provides slightly different APIs for Java and Kotlin with the same essence.

However, outer joins are the only exception where Java and Kotlin APIs behave differently:

• Java DSL Use JoinType to represent join type, can be INNER (default), LEFT, RIGHT or FULL

• Kotlin DSL

  • DSL properties same as entity properties represent inner join

  • DSL properties with a ? suffix compared to entity properties represent left outer join

  That is, Kotlin DSL does not support RIGHT and FULL. This sacrifice is a careful trade-off to gain null safety integration in Kotlin, which is more important.

  This detail will be discussed in Kotlin Join Features.