Since Google announced Kotlin as the official Android language, Kotlin has taken off. It’s not just Android that Kotlin has the edge when it comes to server-side development. Kotlin is a good choice because of its support for air safety, method extension, coroutines, and many other nice features, as well as its near-perfect compatibility with Java.

However, after switching to Kotlin, are you still using MyBatis? MyBatis, as a Java SQL mapping framework, is widely used in China, but it has also received a lot of ridicule. With MyBatis, you’ll have to put up with the odd operation of writing SQL in XML, the hassle of jumping between XML and Java interface files, and the messy @param annotation of passing multiple parameters into XML (or are you using Map? Even worse, there is no basic type verification and parameter names are easy to write wrong. Even when coexisting with Kotlin, some strange questions arise,

At this point, you might want an ORM framework that’s Kotlin’s own. It takes advantage of all of Kotlin’s great features, allowing us to write more Kotlin code. It should be lightweight and just need to add dependencies and use it without the hassle of configuration files. It is better to generate SQL automatically, not to write every SQL itself like MyBatis, but to retain the ability to accurately control SQL, not as difficult to tune SQL as Hibernate.

If you really feel that way, Ktorm might be for you. Ktorm is an efficient and concise Kotlin ORM framework written directly based on pure JDBC. It provides strongly typed and flexible SQL DSLS and convenient sequence apis to reduce the repetitive effort of operating databases. Of course, all SQL is automatically generated. The purpose of this article is to introduce Ktorm and help us get started quickly.

You can get more detailed usage documentation on Ktorm’s website, or issue on GitHub if you run into problems. If Ktorm is helpful to you, please leave your star on GitHub and join us to build Kotlin’s elegant ORM solution.

Ktorm website: Ktorm. liuwJ. me/ GitHub address: github.com/vincentlauv…

Hello, Ktorm!

Remember the first program we wrote when we first learned how to program? Let’s start with Ktorm’s “Hello, World” and learn how to quickly build a project that uses Ktorm.

Ktorm has been published to The Maven central repository and JCenter, so if you use Maven, you first need to add a dependency to the POP.xml file:

12345Copy the code
 
<dependency>    <groupId>me.liuwj.ktorm</groupId>    <artifactId>ktorm-core</artifactId>    <version>${ktorm.version}</version></dependency>Copy the code

Or gradle:

1Copy the code
 
compile "me.liuwj.ktorm:ktorm-core:${ktorm.version}"Copy the code

Before using Ktorm, we need it to be able to understand our table structure. SQL > create table T_DEPARTMENT table T_employee table T_department table T_employee table

123456789101112131415Copy the code
 
create table t_department( id int not null primary key auto_increment, name varchar(128) not null, location varchar(128) not null); create table t_employee( id int not null primary key auto_increment, name varchar(128) not null, job varchar(128) not null, manager_id int null, hire_date date not null, salary bigint not null, department_id int not null);Copy the code

In general, Ktorm uses the Kotlin object keyword to define an object that inherits the Table class to describe the Table structure. The two tables in the above example can be defined in Ktorm like this:

123456789101112131415Copy the code
 
object Departments : Table<Nothing>("t_department") {    val id by int("id").primaryKey()    // Column<Int>    val name by varchar("name")         // Column<String>    val location by varchar("location") // Column<String>}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")}Copy the code

As you can see, Departments and Employees both inherit from Table and specify the Table name in the constructor. The Table class also has a generic parameter that is the type of the entity class to which the Table is bound. We don’t need to bind to any entity class here, so we can specify Nothing. Columns in the table are defined as member attributes in the table object using the keywords val and BY. Column types are defined using functions such as int, LONG, VARCHAR, and date, which correspond to the corresponding types in SQL.

With the table structure defined, we can connect to the Database using the database.connect function and perform a simple query:

1234567Copy the code
 
fun main() {    Database.connect("jdbc:mysql://localhost:3306/ktorm", driver = "com.mysql.jdbc.Driver")    for (row in Employees.select()) {        println(row[Employees.name])    }}Copy the code

This is the simplest Ktorm project. There are only three or four lines of code in this main function, but when you run it, it connects to the database, automatically generates a SQL SELECT * from T_EMPLOYEE, queries all the employee records in the table, and prints out their names. Because the query object returned by the select function implements the Iterable

interface, you can use the for-each syntax here. Of course, any extension function for Iteralble is also available, such as the Map/Filter/Reduce family of functions provided by the Kotlin library.

SQL DSL

Let’s add a little more filtering to the query above:

12345Copy the code
 
val names = Employees    .select(Employees.name)    .where { (Employees.departmentId eq 1) and (Employees.name like "%vince%") }    .map { row -> row[Employees.name] }println(names)Copy the code

The generated SQL is as follows:

123Copy the code
 
select t_employee.name as t_employee_name from t_employee where (t_employee.department_id = ?) and (t_employee.name like ?)Copy the code

This is Kotlin’s magic. Writing queries using Ktorm is so simple and natural that the SQL generated almost matches Kotlin’s code. Also, Ktorm is strongly typed, the compiler checks your code before it runs, and the IDE can do intelligent hints and auto-complete on your code.

It is also very easy to implement condition-based dynamic queries, because it is pure Kotlin code, using if statements directly is better than MyBatis in XML

tag.

1234567891011Copy the code
 
val names = Employees    .select(Employees.name)    .whereWithConditions {        if (someCondition) {            it += Employees.managerId.isNull()        }        if (otherCondition) {            it += Employees.departmentId eq 1        }    }    .map { it.getString(1) }Copy the code

Aggregate query:

123456Copy the code
 
val t = Employeesval salaries = t .select(t.departmentId, Avg (t.salary).groupby (T.mentid).having {avg(t.salary) greater 100.0}.associate {it.getint (1) to it.getDouble(2) }Copy the code

The Union:

123456789Copy the code
 
Employees    .select(Employees.id)    .unionAll(        Departments.select(Departments.id)    )    .unionAll(        Departments.select(Departments.id)    )    .orderBy(Employees.id.desc())Copy the code

SQL > alter table join;

123456789101112131415161718Copy the code
 
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)        )    }Copy the code

Insert:

12345678Copy the code
 
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}Copy the code

Update:

123456789Copy the code
 
Employees.update {    it.job to "engineer"    it.managerId to null    it.salary to 100    where {        it.id eq 2    }}Copy the code

Delete:

1Copy the code
 
Employees.delete { it.id eq 4 }Copy the code

This is the SQL DSL provided by Ktorm. With this DSL, we can write queries using pure Kotlin code, without having to write SQL in XML or concatenate SQL strings in code. Also, strongly typed DSLS give us some additional benefits, such as exposure to low-level errors at compile time, and intelligent hints and auto-completion from the IDE. Most importantly, the SQL it generates corresponds almost one-to-one to our Kotlin code, so even though our SQL is generated automatically, we still have absolute control over it.

This DSL covers almost all of the common SQL uses in our work, such as unions, syntables, aggregations, and even some support for nested queries. Of course, there are certainly usages that are not currently supported, such as special syntax in some databases, or very complex queries (such as related subqueries). This is actually quite rare, but if it does happen, Ktorm offers some solutions:

  • Ktorm makes it easy to extend SQL DSLS to support special syntax in certain databases. These extensions are mainly provided in separate JAR packages, such asktorm-support-mysql. Of course, we can also write our own extensions.
  • In cases where this is really not possible, Ktorm can also use native SQL for queries directly, with some handy extension function support added.

Refer to the Ktorm documentation for more SQL DSL usage.

Entity classes bind to columns

We’ve already covered SQL DSL, but Ktorm is far from being an ORM framework if there is only DSL. Next we’ll introduce the concept of entity classes and see how you can bind a table in a database to an entity class, which is at the heart of the ORM framework: object-relational mapping.

Using the previous department table T_DEPARTMENT and employee table T_EMPLOYEE as examples, we create two Ktorm entity classes to represent the business concepts of department and employee respectively:

1234567891011121314151617Copy the code
 
interface Department : Entity<Department> {    companion object : Entity.Factory<Department>()    val id: Int    var name: String    var location: String}interface Employee : Entity<Employee> {    companion object : Entity.Factory<Employee>()    val id: Int?    var name: String    var job: String    var manager: Employee?    var hireDate: LocalDate    var salary: Long    var department: Department}Copy the code

As you can see, the Entity classes in Ktorm all inherit from the Entity

interface, which injects some common methods into the Entity classes. The attributes of the entity class can be directly defined using the var or val keyword. The type of the attribute and whether it is empty or not can be determined as required.

Contrary to your intuition, the entity class in Ktorm is not a Data class, or even a normal class, but an interface. This was the design requirement of Ktorm, and by defining the entity class as interface, Ktorm was able to do something special, which you’ll see later.

As we all know, interfaces cannot be instantiated. Since entity classes are defined as interfaces, how can we create an entity object? It’s easy, just pretend it has a constructor like this:

1Copy the code
 
val department = Department()Copy the code

As you may have noticed, when we defined the entity-class interface above, we added an accompanying object to both interfaces. This adjoint overrides the Kotlin invoke operator, so it can be called directly like a function using parentheses. Inside Ktorm, we used the JDK’s dynamic proxy to create entity objects.

Remember the two table objects we defined in the previous section? Now that we have the entity class, the next step is to bind the entity class to the previous table object. This binding is actually very simple, just after the declaration of the column to continue the chain call to the bindTo function or references function, the following code changes the previous two table objects, complete the ORM binding:

123456789101112131415Copy the code
 
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 }}Copy the code

Naming conventions: It is strongly recommended that entity classes be named with singular nouns and table objects be named with plural nouns, such as Employee/Employees and Department/Departments.

Comparing the two table objects with before the modification, we can see two differences:

  1. TableClass, we need to specify the type of the entity class so that Ktorm can bind the table object to the entity class; Before, we set it toNothingIndicates that it is not bound to any entity class.
  2. After each column declaration function is called, all chained calls are madebindTo 或 referencesThe function binds the column to an attribute of the entity class; Without this call, no properties are bound.

The significance of column binding is that when an entity object is retrieved from the database by a query (as in the findList function), Ktorm populates a column’s data into its bound properties based on our binding configuration. When updating changes in an entity object to the database (such as the flushChanges function), Ktorm synchronizes changes to an attribute to the column in which it is bound, based on our binding configuration.

Once the column binding is complete, we can use various convenient extension functions for the entity class. For example, get employees by name:

12Copy the code
 
val vince = Employees.findOne { it.name eq "vince" }println(vince)Copy the code

The findOne function takes a lambda expression as an argument, uses the return value of the lambda as a condition, generates a query SQL, and automatically leaves the associated table T_department. The generated SQL is as follows:

1234Copy the code
 
select * from t_employee left join t_department _ref0 on t_employee.department_id = _ref0.id where t_employee.name = ?Copy the code

Other find* family of functions:

123456Copy the code
 
Employees.findAll()Employees.findById(1)Employees.findListByIds(listOf(1))Employees.findMapByIds(listOf(1))Employees.fin dList { it.departmentId eq 1 }Employees.findOne { it.name eq"vince" }Copy the code

Save entity object to database:

12345678910Copy the code
 
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)Copy the code

Update in-memory entity object changes to database:

1234Copy the code
 
val employee = Employees.findById(2) ? : returnemployee.job ="engineer"employee.salary = 100employee.flushChanges()Copy the code

Delete entity object from database:

12Copy the code
 
val employee = Employees.findById(2) ? : returnemployee.delete()Copy the code

For more information on the use of the entity API, refer to the documentation on column binding and entity queries.

As you can see, we can use these convenient functions just by binding the table object to the entity class. Most of the operation of adding, deleting, modifying and checking the entity object can be done with a single function call, but Ktorm can do much more than that.

Entity Sequence API

In addition to find*, Ktorm provides an API called Entity Sequence for retrieving entity objects from a database. As the name suggests, it is similar in style and usage to the Kotlin library’s sequence API and its extension functions of the same name, such as Filter, Map, reduce, and so on.

To get a sequence of entities, we can call the asSequence extension function on the table object:

1Copy the code
 
val sequence = Employees.asSequence()Copy the code

Most of Ktorm’s entity sequence API is provided in the form of extension functions, which can be roughly divided into two categories: intermediate operations and termination operations.

In the middle of operation

Instead of performing a query in a sequence, these operations modify and create a new sequence object, such as the filter function, which creates a new sequence object using the specified filter criteria. The following uses filter to get all employees in department 1:

1Copy the code
 
val employees = Employees.asSequence().filter { it.departmentId eq 1 }.toList()Copy the code

As you can see, the usage is almost exactly the same as kotlin.Sequence, except that the equal sign == in the lambda expression is replaced by the eq function here. The filter function can also be used consecutively, where all filters are concatenated using the and operator, for example:

12345Copy the code
 
val employees = Employees    .asSequence()    .filter { it.departmentId eq 1 }    .filter { it.managerId.isNotNull() }    .toList()Copy the code

Generating SQL:

1234Copy the code
 
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)Copy the code

Order elements in a sequence using sortedBy or sortedByDescending:

1Copy the code
 
val employees = Employees.asSequence().sortedBy { it.salary }.toList()Copy the code

Use the drop and take functions for paging:

1Copy the code
 
val employees = Employees.asSequence().drop(1).take(1).toList()Copy the code

Termination of operations

The termination of the entity sequence immediately executes a query, retrieves the execution result of the query, and then performs some computation. The for-each loop is a typical termination operation, so let’s use the for-each loop to print out all the employees in the sequence:

123Copy the code
 
for (employee in Employees.asSequence()) {    println(employee)}Copy the code

The generated SQL is as follows:

123Copy the code
 
select * from t_employee left join t_department _ref0 on t_employee.department_id = _ref0.idCopy the code

Methods like toCollection, toList, etc. are used to save elements in a sequence as a collection:

1Copy the code
 
val employees = Employees.asSequence().toCollection(ArrayList())Copy the code

The mapColumns function is used to get the result of the specified column:

1Copy the code
 
val names = Employees.asSequenceWithoutReferences().mapColumns { it.name }Copy the code

There are many more functions, such as mapColumns2 and mapColumns3, which are used to fetch the results of multiple columns at the same time. In this case, we need to wrap these fields in the closure with Pair or Triple, and the return value of the function becomes List < Triple < > or List C1? , C2? , C3? > > : 

1234567Copy the code
 
Employees    .asSequenceWithoutReferences()    .filter { it.departmentId eq 1 }    .mapColumns2 { Pair(it.id, it.name) }    .forEach { (id, name) ->        println("$id:$name")}Copy the code

Generating SQL:

123Copy the code
 
select t_employee.id, t_employee.namefrom t_employee where t_employee.department_id = ?Copy the code

Other familiar sequence functions are also supported, such as fold, reduce, forEach, etc. The following uses fold to calculate the sum of all employees’ salaries:

12345Copy the code
 
val totalSalary = Employees    .asSequenceWithoutReferences()    .fold(0L) { acc, employee ->         acc + employee.salary     }Copy the code

The sequence of polymerization

Not only does the Entity Sequence API allow us to retrieve entity objects from the database in a manner similar to kotlin.sequence, it also supports rich aggregation capabilities that allow us to easily count, sum, average, and so on, for specified fields.

The aggregateColumns function is used to get the maximum salary in Department 1:

1234Copy the code
 
val max = Employees    .asSequenceWithoutReferences()    .filter { it.departmentId eq 1 }    .aggregateColumns { max(it.salary) }Copy the code

If you want to get multiple aggregate results at the same time, you can use aggregateColumns2 or aggregateColumns3 functions instead, and then we need to wrap our aggregate expressions in a closure using Pair or Triple. The function returns Pair

or Triple < C1? , C2? , C3? >. The following example retrieves the average and range of salaries in sector 1:
?>

1234Copy the code
 
val (avg, diff) = Employees    .asSequenceWithoutReferences()    .filter { it.departmentId eq 1 }    .aggregateColumns2 { Pair(avg(it.salary), max(it.salary) - min(it.salary)) }Copy the code

Generating SQL:

123Copy the code
 
select avg(t_employee.salary), max(t_employee.salary) - min(t_employee.salary) from t_employee where t_employee.department_id = ?Copy the code

In addition to using aggregateColumns directly, Ktorm provides a number of convenient auxiliary functions for sequences, which are implemented based on aggregateColumns. These are count, any, None, all, sumBy, maxBy, minBy, and averageBy.

Use the maxBy function to get the maximum salary in department 1:

1234Copy the code
 
val max = Employees    .asSequenceWithoutReferences()    .filter { it.departmentId eq 1 }    .maxBy { it.salary }Copy the code

In addition, Ktorm supports group aggregation by calling groupingBy and aggregateColumns. The following code retrieves the average salary for all departments and returns a value of type Map

, where the key is the department ID and the value is the average salary of each department:
?>

1234Copy the code
 
val averageSalaries = Employees    .asSequenceWithoutReferences()    .groupingBy { it.departmentId }    .aggregateColumns { avg(it.salary) }Copy the code

Generating SQL:

123Copy the code
 
select t_employee.department_id, avg(t_employee.salary) from t_employee group by t_employee.department_idCopy the code

Ktorm also provides a number of convenient helper functions for grouping aggregation, which are eachCount(To), eachSumBy(To), eachMaxBy(To), eachMinBy(To), and eachAverageBy(To). With these helper functions, the code above to get the average salary for all departments can be rewritten as:

1234Copy the code
 
val averageSalaries = Employees    .asSequenceWithoutReferences()    .groupingBy { it.departmentId }    .eachAverageBy { it.salary }Copy the code

In addition, Ktorm also provides aggregate, fold, and reduce function, with their kotlin. Collections. The Grouping of the corresponding function, with the same function is exactly the same. The following code uses the fold function to calculate the sum of salaries for each department:

123456Copy the code
 
val totalSalaries = Employees    .asSequenceWithoutReferences()    .groupingBy { it.departmentId }    .fold(0L) { acc, employee ->         acc + employee.salary     }Copy the code

For more information on the use of the entity sequence API, see the documentation on entity sequences and sequence aggregation.

summary

This article starts with a “Hello, World” program and introduces several features of Ktorm, including SQL DSL, entity class and column binding, entity sequence API, and so on. With Ktorm, we can easily manipulate the data persistence layer using pure Kotlin code without the annoying XML of MyBatis. At the same time, because Ktorm is a framework focused on the Kotlin language, it does not have the burden of Java compatibility, allowing us to write more elegant code using Kotlin’s various superior syntactical features. Now that the languages have switched to Kotlin, why not try the pure Kotlin framework?