What’s Ktorm?
Ktorm is a lightweight and efficient ORM Framework for Kotlin directly based on pure JDBC. It provides strong-typed and flexible SQL DSL and convenient sequence APIs to reduce our duplicated effort on database operations. All the SQLs, of course, are generated automatically. Ktorm is open source under the license of Apache 2.0, and its code can be found on GitHub. Leave your star if it’s helpful to you: vincentlauvlwj/Ktorm
For more documentation, go to our site: https://ktorm.liuwj.me.
Features
- No configuration files, no XML, no third-party dependencies, lightweight, easy to use.
- Strong typed SQL DSL, exposing low-level bugs at compile time.
- Flexible queries, fine-grained control over the generated SQLs as you wish.
- Entity sequence APIs, writing queries via sequence functions such as
filter,map,sortedBy, etc., just like using Kotlin’s native collections and sequences. - Extensible design, write your own extensions to support more operators, data types, SQL functions, database dialects, etc.
Quick Start
Ktorm was deployed to maven central and jcenter, so you just need to add a dependency to your pom.xml file if you are using maven:
<dependency>
<groupId>me.liuwj.ktorm</groupId>
<artifactId>ktorm-core</artifactId>
<version>${ktorm.version}</version>
</dependency>
Or Gradle:
compile "me.liuwj.ktorm:ktorm-core:${ktorm.version}"
Firstly, create Kotlin objects to describe your table schemas:
object Departments : Table<Nothing>("t_department") {
val id by int("id").primaryKey()
val name by varchar("name")
val location by varchar("location")
}
object Employees : Table<Nothing>("t_employee") {
val id by int("id").primaryKey()
val name by varchar("name")
val job by varchar("job")
val managerId by int("manager_id")
val hireDate by date("hire_date")
val salary by long("salary")
val departmentId by int("department_id")
}
Then, connect to your database and write a simple query:
fun main() {
Database.connect("jdbc:mysql://localhost:3306/ktorm", driver = "com.mysql.jdbc.Driver")
for (row in Employees.select()) {
println(row[Employees.name])
}
}
Now you can run this program, Ktorm will generate a SQL select * from t_employee, selecting all employees in the table and printing their names. You can use the for-each loop because the query object returned by the select function implements the Iterable<T> interface. Any other extension functions on Iterable<T> are also available, eg. map/filter/reduce provided by Kotlin standard lib.
SQL DSL
Let’s add some filter conditions to the query:
val names = Employees
.select(Employees.name)
.where { (Employees.departmentId eq 1) and (Employees.name like "%vince%") }
.map { row -> row[Employees.name] }
println(names)
Generated SQL:
select t_employee.name as t_employee_name
from t_employee
where (t_employee.department_id = ?) and (t_employee.name like ?)
That’s the magic of Kotlin, writing a query with Ktorm is easy and natural, the generated SQL is exactly corresponding to the origin Kotlin code. And moreover, it’s strong-typed, the compiler will check your codes before it runs, and you will be benefited from the IDE’s intelligent sense and code completion.
Dynamic query based on conditions:
val names = Employees
.select(Employees.name)
.whereWithConditions {
if (someCondition) {
it += Employees.managerId.isNull()
}
if (otherCondition) {
it += Employees.departmentId eq 1
}
}
.map { it.getString(1) }
Aggregation:
val t = Employees
val salaries = t
.select(t.departmentId, avg(t.salary))
.groupBy(t.departmentId)
.having { avg(t.salary) greater 100.0 }
.associate { it.getInt(1) to it.getDouble(2) }
Union:
Employees
.select(Employees.id)
.unionAll(
Departments.select(Departments.id)
)
.unionAll(
Departments.select(Departments.id)
)
.orderBy(Employees.id.desc())
Joining:
data class Names(val name: String, val managerName: String?, val departmentName: String)
val emp = Employees.aliased("emp")
val mgr = Employees.aliased("mgr")
val dept = Departments.aliased("dept")
val results = emp
.leftJoin(dept, on = emp.departmentId eq dept.id)
.leftJoin(mgr, on = emp.managerId eq mgr.id)
.select(emp.name, mgr.name, dept.name)
.orderBy(emp.id.asc())
.map {
Names(
name = it.getString(1),
managerName = it.getString(2),
departmentName = it.getString(3)
)
}
Insert:
Employees.insert {
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
}
Update:
Employees.update {
it.job to "engineer"
it.managerId to null
it.salary to 100
where {
it.id eq 2
}
}
Delete:
Employees.delete { it.id eq 4 }
Refer to detailed documentation for more usages about SQL DSL.
Entities and Column Binding
In addition to SQL DSL, entity objects are also supported just like other ORM frameworks do. We need to define entity classes firstly and bind table objects to them. In Ktorm, entity classes are defined as interfaces extending from Entity<E>:
interface Department : Entity<Department> {
val id: Int
var name: String
var location: String
}
interface Employee : Entity<Employee> {
val id: Int?
var name: String
var job: String
var manager: Employee?
var hireDate: LocalDate
var salary: Long
var department: Department
}
Modify the table objects above, binding database columns to entity properties:
object Departments : Table<Department>("t_department") {
val id by int("id").primaryKey().bindTo { it.id }
val name by varchar("name").bindTo { it.name }
val location by varchar("location").bindTo { it.location }
}
object Employees : Table<Employee>("t_employee") {
val id by int("id").primaryKey().bindTo { it.id }
val name by varchar("name").bindTo { it.name }
val job by varchar("job").bindTo { it.job }
val managerId by int("manager_id").bindTo { it.manager.id }
val hireDate by date("hire_date").bindTo { it.hireDate }
val salary by long("salary").bindTo { it.salary }
val departmentId by int("department_id").references(Departments) { it.department }
}
Naming Strategy: It’s highly recommended to name your entity classes by singular nouns, name table objects by plurals (eg. Employee/Employees, Department/Departments).
Now that column bindings are configured, so we can use those convenient extension functions for entities. For example, finding an employee by name:
val vince = Employees.findOne { it.name eq "vince" }
println(vince)
The findOne function accepts a lambda expression, generating a select sql with the condition returned by the lambda, auto left joining the referenced table t_department . Generated SQL:
select *
from t_employee
left join t_department _ref0 on t_employee.department_id = _ref0.id
where t_employee.name = ?
Some other find* functions:
Employees.findAll()
Employees.findById(1)
Employees.findListByIds(listOf(1))
Employees.findMapByIds(listOf(1))
Employees.findList { it.departmentId eq 1 }
Employees.findOne { it.name eq "vince" }
Save entities to database:
val employee = Employee {
name = "jerry"
job = "trainee"
manager = Employees.findOne { it.name eq "vince" }
hireDate = LocalDate.now()
salary = 50
department = Departments.findOne { it.name eq "tech" }
}
Employees.add(employee)
Flush property changes in memory to database:
val employee = Employees.findById(2) ?: return
employee.job = "engineer"
employee.salary = 100
employee.flushChanges()
Delete a entity from database:
val employee = Employees.findById(2) ?: return
employee.delete()
Detailed usages of entity APIs can be found in the documentation of column binding and entity findings.
Entity Sequence APIs
In addition to the find* functions, Ktorm also provides a set of APIs named Entity Sequence, which can be used to obtain entity objects from databases. As the name implies, its style and use pattern are highly similar to the sequence APIs in Kotlin standard lib, as it provides many extension functions with the same names, such as filter, map, reduce, etc.
To create an entity sequence, we can call the extension function asSequence on a table object:
val sequence = Employees.asSequence()
Most of the entity sequence APIs are provided as extension functions, which can be divided into two groups, they are intermediate operations and terminal operations.
Intermediate Operations
These functions don’t execute the internal queries but return new-created sequence objects applying some modifications. For example, the filter function creates a new sequence object with the filter condition given by its parameter. The following code obtains all the employees in department 1 by using filter:
val employees = Employees.asSequence().filter { it.departmentId eq 1 }.toList()
We can see that the usage is almost the same as kotlin.sequences.Sequence, the only difference is the == in the lambda is replaced by the eq function. The filter function can also be called continuously, as all the filter conditions are combined with the and operator.
val employees = Employees
.asSequence()
.filter { it.departmentId eq 1 }
.filter { it.managerId.isNotNull() }
.toList()
Generated SQL:
select *
from t_employee
left join t_department _ref0 on t_employee.department_id = _ref0.id
where (t_employee.department_id = ?) and (t_employee.manager_id is not null)
Use sortedBy or soretdByDescending to sort entities in a sequence:
val employees = Employees.asSequence().sortedBy { it.salary }.toList()
Use drop and take for pagination:
val employees = Employees.asSequence().drop(1).take(1).toList()
Terminal Operations
Terminal operations of entity sequences execute the queries right now, then obtain the query results and perform some calculations on them. The for-each loop is a typical terminal operation, and the following code uses it to print all employees in the sequence:
for (employee in Employees.asSequence()) {
println(employee)
}
Generated SQL:
select *
from t_employee
left join t_department _ref0 on t_employee.department_id = _ref0.id
The toCollection functions (including toList, toSet, etc.) are used to collect all the elements into a collection:
val employees = Employees.asSequence().toCollection(ArrayList())
The mapColumns function is used to obtain the results of a column:
val names = Employees.asSequenceWithoutReferences().mapColumns { it.name }
Additional, if we want to select two or more columns, we can change to mapColumns2 or mapColumns3, then we need to wrap our selected columns by Pair or Triple in the closure, and the function’s return type becomes List<Pair<C1?, C2?>> or List<Triple<C1?, C2?, C3?>>.
Employees
.asSequenceWithoutReferences()
.filter { it.departmentId eq 1 }
.mapColumns2 { Pair(it.id, it.name) }
.forEach { (id, name) ->
println("$id:$name")
}
Generated SQL:
select t_employee.id, t_employee.name
from t_employee
where t_employee.department_id = ?
Other familiar functions are also supported, such as fold, reduce, forEach, etc. The following code calculates the total salary of all employees:
val totalSalary = Employees.asSequence().fold(0L) { acc, employee -> acc + employee.salary }
Sequence Aggregation
The entity sequence APIs not only allow us to obtain entities from databases just like using kotlin.sequences.Sequence, but they also provide rich support for aggregations, so we can conveniently count the columns, sum them, or calculate their averages, etc.
The following code obtains the max salary in department 1:
val max = Employees
.asSequenceWithoutReferences()
.filter { it.departmentId eq 1 }
.aggregateColumns { max(it.salary) }
Also, if we want to aggregate two or more columns, we can change to aggregateColumns2 or aggregateColumns3, then we need to wrap our aggregate expressions by Pair or Triple in the closure, and the function’s return type becomes Pair<C1?, C2?> or Triple<C1?, C2?, C3?>. The example below obtains the average and the range of salaries in department 1:
val (avg, diff) = Employees
.asSequenceWithoutReferences()
.filter { it.departmentId eq 1 }
.aggregateColumns2 { Pair(avg(it.salary), max(it.salary) - min(it.salary)) }
Generated SQL:
select avg(t_employee.salary), max(t_employee.salary) - min(t_employee.salary)
from t_employee
where t_employee.department_id = ?
Ktorm also provides many convenient helper functions implemented based on aggregateColumns, they are count, any, none, all, sumBy, maxBy, minBy, averageBy.
The following code obtains the max salary in department 1 using maxBy instead:
val max = Employees
.asSequenceWithoutReferences()
.filter { it.departmentId eq 1 }
.maxBy { it.salary }
Additionally, grouping aggregations are also supported, we just need to call groupingBy before calling aggregateColumns. The following code obtains the average salaries for each department. Here, the result’s type is Map<Int?, Double?>, in which the keys are departments’ IDs, and the values are the average salaries of the departments.
val averageSalaries = Employees
.asSequenceWithoutReferences()
.groupingBy { it.departmentId }
.aggregateColumns { avg(it.salary) }
Generated SQL:
select t_employee.department_id, avg(t_employee.salary)
from t_employee
group by t_employee.department_id
Ktorm also provides many convenient helper functions for grouping aggregations, they are eachCount(To), eachSumBy(To), eachMaxBy(To), eachMinBy(To), eachAverageBy(To). With these functions, we can write the code below to obtain average salaries for each department:
val averageSalaries = Employees
.asSequenceWithoutReferences()
.groupingBy { it.departmentId }
.eachAverageBy { it.salary }
Other familiar functions are also supported, such as aggregate, fold, reduce, etc. They have the same names as the extension functions of kotlin.collections.Grouping, and the usages are totally the same. The following code calculates the total salaries for each department using fold:
val totalSalaries = Employees
.asSequenceWithoutReferences()
.groupingBy { it.departmentId }
.fold(0L) { acc, employee ->
acc + employee.salary
}
Detailed usages of entity sequence APIs can be found in the documentation of entity sequence and sequence aggregation.
