MyBatis Dynamic SQL

When using embedded statements (such as an insertSelect or a subquery within a select), statement configuration must always be specified on the outermost statement.

Configuration applied to an embedded select statement will be ignored. To ensure your settings are applied, always place the configureStatement call as the last step in the outermost DSL before calling build (Java) or ending the lambda (Kotlin).

Mental Model: The outermost statement's configuration governs the entire execution context of that specific DSL tree.

val insertStatement = insertSelect {
    into(person)
    select(id, firstName, lastName, birthDate, employed, occupation, addressId) {
        from(person)
        where { id isGreaterThanOrEqualToWhenPresent null }
        // The following will be IGNORED:
        configureStatement { isNonRenderingWhereClauseAllowed = false }
    }
    // This is the correct way to configure the statement:
    configureStatement { isNonRenderingWhereClauseAllowed = true }
}

To use the library, you must define a metamodel using AlisableSqlTable. This involves creating an object that holds column definitions and a nested class representing the table structure.

Using Parameter Type Converters

You can use parameterTypeConverter on columns to map Kotlin types to database-specific formats. For example, mapping a Kotlin Boolean to a database string like "Yes" or "No".

Important: Type converters are used in general inserts, updates, and where clauses, but not in single-row insert statements that map fields directly to properties in a data class. For those, you must provide a property in your data class that performs the conversion.

import org.mybatis.dynamic.sql.AlisableSqlTable
import org.mybatis.dynamic.sql.util.kotlin.elements.column
import java.util.Date

object PersonDynamicSqlSupport {
    val person = Person()
    val id = person.id
    val firstName = person.firstName
    val lastName = person.lastName
    val birthDate = person.birthDate
    val employed = person.employed
    val occupation = person.occupation
    val addressId = person.addressId

    class Person : AlisableSqlTable<Person>("Person", ::Person) {
        val id = column<Int>(name = "id")
        val firstName = column<String>(name = "first_name")
        val lastName = column(
            name = "last_name",
            parameterTypeConverter = lastNameConverter
        )
        val birthDate = column<Date>(name = "birth_date")
        val employed = column(
            name = "employed",
            parameterTypeConverter = booleanToStringConverter
        )
        val occupation = column<String>(name = "occupation")
        val addressId = column<Int>(name = "address_id")
    }
}

// Example converter
val booleanToStringConverter: (Boolean?) -> String = { it?.let { if (it) "Yes" else "No" } ?: "No" }

// Data class for single-row inserts
data class PersonRecord(
    var id: Int? = null,
    var firstName: String? = null,
    var lastName: String? = null,
    var birthDate: Date? = null,
    var employed: Boolean? = null,
    var occupation: String? = null,
    var addressId: Int? = null
) {
    val employedAsString: String
        get() = booleanToStringConverter(employed)
}

Union Queries

Use .union() to combine multiple SELECT statements. Note that only one ORDER BY phrase is allowed for the entire union, which applies to the result set as a whole.

Multi-Select Queries

If you need to apply ORDER BY or paging (limit/offset) to the nested queries before they are merged, use multiSelect(...). This is a special case of union where individual select statements can have their own ordering and paging.

SelectStatementProvider selectStatement = multiSelect(
        select(id, animalName, bodyWeight, brainWeight)
                .from(animalData)
                .orderBy(id)
                .limit(2)
        ).union(
                selectDistinct(id, animalName, bodyWeight, brainWeight)
                .from(animalData)
                .orderBy(id.descending())
                .limit(3)
        )
        .build()
        .render(RenderingStrategies.MYBATIS3);

SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
        .from(animalData)
        .union()
        .selectDistinct(id, animalName, bodyWeight, brainWeight)
        .from(animalData)
        .orderBy(id)
        .build()
        .render(RenderingStrategies.MYBATIS3);

Every context (where, and, or, not, group) supports exactly one initial condition and any number of subsequent conditions (created via and or or). If no initial condition is provided, the first connector (and/or) is automatically stripped during rendering to prevent invalid SQL like WHERE AND id = 3.

There are four types of initial conditions:

1. Column and Criterion: A standard condition (e.g., id isEqualTo 3 or using the invoke operator).
2. Not: Negates a group or single criterion (e.g., not { id isEqualTo 3 }).
3. Exists: Executes an EXISTS sub-query.

   exists { select(foo.allColumns()).from(foo).where { foo.id isEqualTo bar.fooId } }

   To perform a NOT EXISTS, wrap the exists block in a not block.
4. Group: Groups conditions with parentheses using the group function.

   group { id isEqualTo 3; and { id isEqualTo 4 } }