Join Features Specific to Kotlin

Dilemma

In order to take full advantage of Kotlin's language features and optimize its development experience, Jimmer provides different APIs for Java and Kotlin, but they are essentially the same.

However, outer joins are the only exception. For this detail, the behaviors of Java API and Kotlin API are different:

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

• Kotlin DSL

  • Properties with the same name as entity properties represent inner joins

  • Properties with a ? after the entity properties name represent left joins

  That is, Kotlin DSL does not support RIGHT and FULL. This sacrifice is a careful trade-off, in exchange for a more important feature for Kotlin: perfectly combining Kotlin's null safety and SQL DSL.

Getting Started with DSL's Null Safety

The entity BookStore is defined as follows:

@Entity
interface BookStore {

    val name: String

    val website: String?

    // Omit other code
}

The precompiled code generates:

public val KNonNullProps<BookStore>.name: KNonNullPropExpression<String>
    get() = get("name")

public val KNullableProps<BookStore>.name: KNullablePropExpression<String>
    get() = get("name")

public val KProps<BookStore>.website: KNullablePropExpression<String>
    get() = get("website")

Please ignore the details of the generated code for now. We can see many types containing NonNull or Nullable.

tip

In Jimmer's Kotlin SQL DSL, almost all AST types have both NonNull and Nullable implementations.

This means Kotlin SQL DSL has null safety features equivalent to Kotlin language itself. SQL-style query code has complete null safety self-checking capabilities.

Let's look at a simple example first:

val tuples = sqlClient
    .createQuery(BookStore::class) {
        select(
            table.name,
            table.website ❶
        )
    }
    .execute()
for ((name, website) in books) { ❷
    println("Length of name: ${name.length}")
    // Compile error  
    println("Length of website: ${website.length}") ❸
}

1. Since BookStore.website is nullable, the type of the second column selected at ❶ is String? instead of String. The final return type of the query is List<Tuple2<String, String?>>.

2. ❷ Loops through each tuple queried, destructuring into variables name and website. Since tuples is of type List<Tuple2<String, String?>>, here website is of type String?.

3. ❸ Performs .length operation on potentially null website, causing compile error.

To fix this compile error, modify code at ❸ to change . to ?.:

println("Length of website: ${website?.length}")

Null Safety in Table Joins

In the above, we learned the simplest null safety in SQL DSL through a very simple example.

Now, let's combine null safety with table join operations:

Inner Join

val tuples = sqlClient
    .createQuery(BookStore::class) {
        select(
            table.name, 
            table.store.name ❶
        )
    }
    .execute()
for ((name, storeName) in books) { ❷
    println("Length of name: ${name.length}")
    println("Length of storeName: ${storeName.length}") ❸
}

• ❶ Uses inner join to get parent object's name. The final return type of the query is List<Tuple2<String, String>>.

• ❷ Destructures into variables storeName of type String, non-nullable. So code at ❸ compiles.

Left Join

Let's modify the code to change inner join to outer join:

val tuples = sqlClient
    .createQuery(BookStore::class) {
        select(
            table.name,
            table.`store?`.name ❶
        ) 
    }
    .execute()
for ((name, storeName) in books) { ❷
    println("Length of name: ${name.length}")
    // Compile error
    println("Length of storeName: ${storeName.length}") ❸
}

• ❶ Uses left join to get parent object.

  The precompiled code in BookProps.kt includes:

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

  Unlike inner join store, outer join store? returns a table of type KNullableTable, i.e. nullable table. This is the effect of left join in SQL.

  The precompiled code in BookStoreProps.kt includes:

  public val KNonNullProps<BookStore>.name: KNonNullPropExpression<String>
      get() = get("name") ❹
  
  public val KNullableProps<BookStore>.name: KNullablePropExpression<String>
      get() = get("name") ❺

  The returned KNullableTable inherits from KNullableProps instead of KNonNullProps.

  So in the DSL, accessing name matches ❺ instead of ❹.

  That is, just BookStore.name being non-null is not enough, need to also consider if the table it is accessed from is non-null.

  Ultimately, Jimmer determines the second column in ❶ is String?, so the return type is List<Tuple2<String, String?>>.

• ❷ Destructures into storeName of type String?, nullable. This must cause compile error at ❸.

To fix this compile error, modify code at ❸ to change . to ?.:

println("Length of storeName: ${storeName?.length}") ❸