v3.6.0

Infrastructure

• Upgrade Kotlin version to 1.7.22, support latest JDK versions (min 8, max 19)
• Support Java 9 modular #421

Support SQL Window Functions, by @michaelfyc in #460

Ktorm now supports SQL window functions, here is an example:

database
    .from(Employees)
    .select(
        Employees.name,
        Employees.salary,
        Employees.departmentId,
        rank().over { partitionBy(Employees.departmentId).orderBy(Employees.salary.desc()) }
    )

Generated SQL:

SELECT 
    "t_employee"."name" AS "t_employee_name", 
    "t_employee"."salary" AS "t_employee_salary", 
    "t_employee"."department_id" AS "t_employee_department_id", 
    RANK() OVER (PARTITION BY "t_employee"."department_id" ORDER BY "t_employee"."salary" DESC) 
FROM "t_employee" 

Support Case-When DSL, by @zuisong in #413

Ktorm now supports case-when DSL, for example:

database
    .from(Employees)
    .select(
        Employees.id,
        Employees.name,
        CASE(Employees.sex).WHEN(1).THEN("male").WHEN(2).THEN("female").ELSE("unknown").END()
    )

Generated SQL:

SELECT 
    "t_employee"."id" AS "t_employee_id", 
    "t_employee"."name" AS "t_employee_name", 
    CASE "t_employee"."sex" WHEN 1 THEN 'male' WHEN 2 THEN 'female' ELSE 'unknown' END 
FROM "t_employee" 

Other Optimizations & Bug Fixes

• Support insert ... returning ... for SQLite, by @2938137849 in #427
• Support SQL full join, by @KocproZ in #419
• Support MySQL & PostgreSQL default keyword for insert statements to use column default values, by @lookup-cat in #431
• Support SQL type casting syntax, by @svenallers in #415
• Rename Query#totalRecords to Query#totalRecordsInAllPages for better understandability, the origin name is now deprecated
• Disable entity sequence for tables that doesn’t bound to entities, legacy usages will get a warning after 3.6.0
• DefaultMethodHandler to use the same class loader as the method's declaring class, by @brohacz in #471
• Refactor combineConditions to avoid stack overflow errors #328
• Fix SQLite syntax error for UNIONs with subqueries #329
• Fix entity equality bug #466
• Fix bugs #386, #400, #405, #432, #433, #457

Break Changes

• Refactor SqlExpressionVisitor and its subtypes from classes to interfaces for better coding flexibility.
• Change SQL Server datetimeoffset's return type from Column<microsoft.sql.DateTimeOffset> to Column<java.time.OffsetDateTime>

v3.5.0

• Upgrade Kotlin version to 1.5.32, upgrade min JDK version to 1.8.
• New operator functions gt, gte, lt, lte, neq, synonyms for greater, greaterEq, less, lessEq, notEq.
• Compatible with JVM default methods for entity non-abstract members.
• Support using inline classes as column values and entity properties. #253
• Support PostgreSQL cube & earthdistance data type and their extension functions, by @KocproZ in #365
• Support SQLite bulk insert statement and some utility functions, by @2938137849 in #370
• Fix stack overflow bug when using large where clauses, by @ecopoesis in #328
• Deprecate the ktorm-global module which will be completely removed in the future.

v3.4.1

Fix PostgreSQL json type mismatch error when using connection pools #268

v3.4.0

Support Locking Clause for MySQL & PostgreSQL #247

Now Ktorm supports locking clause like for update, for share both for MySQL & PostgreSQL, for example:

val employee = database.employees
    .filter { it.name eq "vince" }
    .locking(LockingMode.FOR_UPDATE, wait = LockingWait.SKIP_LOCKED)
    .firstOrNull()

Generated SQL:

SELECT *
FROM t_employee
WHERE t_employee.name = ? 
LIMIT ?, ? 
FOR UPDATE SKIP LOCKED

Refer to these two functions for detailed usage:

• MySQL locking function
• PostgreSQL locking function

Support insert ... returning ... for PostgreSQL #233

With an insert ... returning ... statement, we can insert records to the database, and at the same time, retrieve some generated columns. For example:

val id = database.insertReturning(Employees, Employees.id) {
    set(it.name, "pedro")
    set(it.job, "engineer")
    set(it.salary, 1500)
    set(it.hireDate, LocalDate.now())
    set(it.departmentId, 1)
}

Returning multiple columns is also supported:

val (id, job) = database.insertReturning(Employees, Pair(Employees.id, Employees.job)) {
    set(it.name, "vince")
    set(it.job, "engineer")
    set(it.salary, 1000)
    set(it.hireDate, LocalDate.now())
    set(it.departmentId, 1)
}

Generated SQL:

insert into t_employee (name, job, salary, hire_date, department_id) 
values (?, ?, ?, ?, ?) returning id, job

There are also some other versions of xxxReturning functions, check the API docs for details:

• insertReturning
• insertOrUpdateReturning
• bulkInsertReturning
• bulkInsertOrUpdateReturning

Other Optimizations & Bug Fixes

• PostgreSQL: support onConflict { doNothing() } for insertOrUpdate & bulkInsertOrUpdate #255
• PostgreSQL: Fix type mismatch error for JsonSqlType #268
• Value semantics for Entity: add default equals & hashCode function #242
• Auto transformation between JSR-310 classes and JDBC date & time #252
• Support using unsigned integers as column types #253
• Fix null-value-ignoring bug for add function #273

v3.3.0

• sortedBy function supports lambda varargs to allow using multiple sorted columns, eg. sortedBy({ it.salary.desc() }, { it.hireDate.asc() }).
• Added bulkInsert & bulkInsertOrUpdate for PostgreSQL. #226
• Added more overloaded functions to allow specifying offset & limit separately. #198
• Fixed bug of missing catalog & schema in generated insert SQLs. #207, #209
• Changed the default transaction isolation to null (stands for the default isolation level of the underlying datastore), not REPEATABLE_READ anymore. #231
• Upgraded Kotlin version to 1.4.21.

v3.2.0

Package Name Changed to org.ktorm

Finally, we have our own domain and the official website becomes https://www.ktorm.org

Accordingly, starting from Ktorm 3.2, we have changed our group ID to org.ktorm, so you need to modify your Maven dependency:

<dependency>
    <groupId>org.ktorm</groupId>
    <artifactId>ktorm-core</artifactId>
    <version>3.2.0</version>
</dependency>

Or Gradle:

compile "org.ktorm:ktorm-core:3.2.0"

Package names are also changed to org.ktorm.*, so you also need to modify your import statements:

import org.ktorm.database.*
import org.ktorm.dsl.*
import org.ktorm.entity.*
import org.ktorm.schema.*

With the only two steps, you have completed all the migration work to version 3.2. Everything should work well as there are no other changes apart from this.

v3.1.0

Upgrade to Kotlin 1.4

To use the new features of Kotlin, we've upgraded its version to 1.4.10, which brings some changes to us:

• Explicit API mode to ensure we don't expose some internal things accidentally.

• Contract support for database.useConnection { .. } & database.useTransaction { .. } to let the compiler know our callback functions are called in place exactly once.

• Deprecation of mapColumnsN & aggregateColumnsN functions. As Kotlin 1.4 supports overload resolution by lambda return type, we don't need these functions anymore, mapColumns & aggregateColumns is enough.

  database.employees
      .filter { it.departmentId eq 1 }
      .mapColumns { tupleOf(it.id, it.name) }
      .forEach { (id, name) ->
          println("$id:$name")
      }

Entity Based Update Function

In Ktorm 3.1, we provide an update function that can update all the non-null properties of an entity object to the database. Using this function, the entity object is not required to be associated with a table first. That means, comparing to flushChanges, we don’t have to obtain an entity object from the database first before performing the update. The usage is as follows:

val employee = Employee {
    id = 5
    job = "engineer"
    salary = 100
}

database.employees.update(employee)

Generated SQL:

update t_employee set job = ?, salary = ? where id = ?

Syntax Refactoring of Insert & Update DSL

Previously, we inserted a record into the table like this:

database.insert(Employees) {
    it.name to "jerry"
    it.job to "trainee"
    it.managerId to 1
    it.hireDate to LocalDate.now()
    it.salary to 50
    it.departmentId to 1
}

Here, we used it.name to "jerry" to set the name to jerry in the closure. And the to function is a member of AssignmentsBuilder, but not the to function used to create Pair instances of Kotlin standard lib.

It is very easy for users to get confused with these two functions, so in Ktorm 3.1, we provide another set function as an alternative. The to function is marked deprecated and will be removed in the future. You can learn the new syntax here https://ktorm.liuwj.me/en/dml.html

database.insert(Employees) {
    set(it.name, "jerry")
    set(it.job, "trainee")
    set(it.managerId, 1)
    set(it.hireDate, LocalDate.now())
    set(it.salary, 50)
    set(it.departmentId, 1)
}

Other Optimizations and Bug Fixes

• Add ShortSqlType. #160
• Add MySQL IF function. #163
• Support mixed-case column names & auto case transform. #175
• Allow specify PostgreSQL insertOrUpdate conflict columns. #181
• Support select .. for update. #69
• Support catalog & schema in table definition. #89, #154, #183
• Check max column name length. #122
• Fix timestamp fraction bug. #130
• Update the syntax of defining JSON columns from json("foo", typeRef<List<Int>>) to json<List<Int>>("foo").

v3.0.0

Some break changes in Ktorm 3.0:

• Completely remove the deprecated global database APIs and provide equivalent things in a new module ktorm-global.
• Use = instead of property delegation to define columns.
• Query doesn't implement Iterable anymore.
• Support compound primary keys.

In addition to the break changes above, there are also many updates from enthusiasts in the open source community, thanks for their contributions:

• MySQL bulkInsert function supports on duplcate key update. Thank @hangingman
• PostgreSQL hstore data type and a series of operators for it. Thank @arustleund
• ktorm-jackson supports simple Jackson annotations, like @JsonProperty, @JsonAlias, @JsonIgnore. Thank @onXoot

For more details about the new version, see https://ktorm.liuwj.me/en/break-changes-in-ktorm-3.0.html

v2.7.2

Fix the bug that entities can not be inserted when Spring support enabled. vincentlauvlwj/ktorm-example-spring-boot#2

v2.7.1

Fix the bug that transactions don't commit while we are using a non-local return in the inlined useTransaction block. #90