Kotlin JDSL

Methods provided by Kotlin JDSL through certain support modules often return nullable types. This is because the library cannot automatically infer whether a query result is non-null or nullable based on the query structure. Specifically, the nullability depends on whether the underlying database definition or the query logic allows for null values.

Common scenarios resulting in nullable return types include:

1. Columns: A column returns null if its definition in the database is nullable.
2. Entities: An entity returns null if it is part of a nullable join, such as a leftJoin.

To avoid user confusion and the need for additional learning regarding custom naming conventions (e.g., distinguishing between methods that filter nulls vs. methods that throw exceptions), Kotlin JDSL uses the standard JPQL behavior where return types are nullable by default.

val query = jpql {
    select(
        path(BookAuthor),
    ).from(
        entity(Author::class),
        leftJoin(BookAuthor::class).on(path(Author::authorId).equal(path(BookAuthor::authorId)))
    )
}

You can treat the result of a select statement as an Entity by calling .asEntity() on it. This is useful when you want to perform a secondary query (like a select or count) on the results of a previous complex selection. To use this pattern, define a data class that matches the shape of the subquery's selection and pass it as the type argument to the select<T> function.

data class DerivedEntity(
    val employeeId: Long,
    val count: Long,
)

val query = jpql {
    val subquery = select<DerivedEntity>(
        path(Employee::employeeId).`as`(expression("employeeId")),
        count(Employee::employeeId).`as`(expression("count")),
    ).from(
        entity(Employee::class),
        join(Employee::departments),
    ).groupBy(
        path(Employee::employeeId),
    ).having(
        count(Employee::employeeId).greaterThan(1L),
    )

    select(
        count(DerivedEntity::employeeId),
    ).from(
        subquery.asEntity(),
    )
}

Kotlin JDSL supports standard JPQL set operations to combine multiple select queries. These can be used in two ways:

1. Chained Selects: Call the operation method (e.g., .union()) directly after a select query structure. The orderBy() clause applied after the set operation affects the final result set.
2. Top-Level Operations: Use the operation methods as top-level functions within a jpql {} block to combine two existing JpqlQueryable instances.

Supported Operations:

• union(): Combines results and removes duplicates.
• unionAll(): Combines results and includes duplicates.
• except(): Returns rows from the first query not present in the second (removes duplicates).
• exceptAll(): Returns rows from the first query not present in the second (includes duplicates).
• intersect(): Returns only rows present in both sets (removes duplicates).
• intersectAll(): Returns only rows present in both sets (includes duplicates).

Note on Database Compatibility:

• PostgreSQL, Oracle, SQL Server: Support all six operations.
• H2: Supports UNION, UNION ALL, INTERSECT, and EXCEPT, but not the ALL variants.
• MySQL: Only supports UNION and UNION ALL. EXCEPT and INTERSECT must be emulated via NOT EXISTS/LEFT JOIN or INNER JOIN/IN respectively.

// Example: Top-level UNION ALL
val topLevelUnionAllQuery = jpql {
    unionAll(query1, query2)
        .orderBy(path(Book::isbn).asc())
}

In Hibernate Reactive, the session scope is typically limited to the lambda block of methods like withSession or withTransaction. Accessing lazy-loaded associations outside of this active session will cause a LazyInitializationException.

Strategies for accessing associations:

1. Access inside the session scope: Perform transformations or data access within the reactive stream pipeline while the session is still active (e.g., using .onItem().transform { ... } in Mutiny).
2. Use fetch join: If you must access associations after the session has closed, you must initialize them eagerly in the query using fetchJoin.

// Strategy 1: Accessing inside the session scope
val bookAuthorSizes: Uni<List<Int>> = sessionFactory.withSession { session ->
    session.createQuery(query, context).resultList.onItem().transform { bookList ->
        bookList.map { it.authors.size } // Safe: session is active
    }
}

// Strategy 2: Using fetchJoin for access outside the session scope
val query = jpql {
    select(distinct(entity(Book::class)))
    .from(entity(Book::class), fetchJoin(Book::authors))
}
val books: Uni<List<Book>> = sessionFactory.withSession { session ->
    session.createQuery(query, context).resultList
}
// Safe to use 'books' here because authors were eagerly fetched
books.onItem().invoke { bookList ->
    bookList.forEach { println(it.authors.size) }
}