Schema Definition

To use SQL DSL, we need to let Ktorm know our schemas. Assuming we have two tables, t_department and t_employee, their schemas are given in SQL below, how do we descript these two tables with Ktorm?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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
);

Table Objects

Generally, we can define a Kotlin object extending Table to descript our table schemas in Ktorm. The following code defines the two tables with Ktorm:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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")
}

We can see that both Departments and Employees are extending from Table whose constructor accepts a table name as the parameter. There is also a generic type parameter in Table class, that is the entity class’s type that current table is binding to. Here we don’t bind to any entity classes, so Nothing is OK.

Columns are defined as properties in table objects by Kotlin’s val and by keyword, their types are defined by type definition functions, such as int, long, varchar, date, etc. Commonly, these type definition functions follow the rules below:

• They are all Table class’s extension functions that are only allowed to be used in table object definitions.
• Their names are corresponding to the underlying SQL types’ names.
• They all accept a parameter of string type, that is the column’s name.
• Their return types are Table<E>.ColumnRegistration<C>, in which E is the entity class, C is the type of current column. We can chaining call the primaryKey function on ColumnRegistration to declare the current column as a primary key.

ColumnRegistration implements the ReadOnlyProperty interface, so we can use it as a property delegate via Kotlin’s by keyword. Therefore, in the definition val name by varchar("name"), although the return type of varchar is ColumnRegistration<String>, the val name property’s type is Column<String>. For the same reason, the val managerId by int("manager_id") property’s type is Column<Int>.

In general, we define tables as Kotlin singleton objects, but we don’t really have to stop there. For instance, assuming that we have two tables that are totally the same, they have the same columns, but their names are different. In this special case, do we have to copy the same column definitions to each table? No, we don’t. We can reuse our codes by subclassing:

1
2
3
4
5
6
7
8
9
10
11
12
13

sealed class Employees(tableName: String) : Table<Nothing>(tableName) {
    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")
}

object RegularEmployees : Employees("t_regular_employee")

object FormerEmployees : Employees("t_former_employee")

For another example, sometimes our table is one-off, we don’t need to use it twice, so it’s not necessary to define it as a global object, for fear that the naming space is polluted. This time, we can even define the table as an anonymous object inside a function:

1
2
3
4
5
6
7

val t = object : Table<Nothing>("t_config") {
    val key by varchar("key").primaryKey()
    val value by varchar("value")
}

// Get all configs as a Map<String, String>
val configs = t.select().associate { row -> row[t.key] to row[t.value] }

Flexible usage of Kotlin’s language features is helpful for us to reduce duplicated code and improve the maintainability of our projects.

SqlType

SqlType is an abstract class which provides a unified abstraction for data types in SQL. Based on JDBC, it encapsulates the common operations of obtaining data from a ResultSet and setting parameters to a PreparedStatement. In the section above, we defined columns by column definition functions, eg. int, varchar, etc. All these functions have an implementation of SqlType behind them. For example, here is the implementation of int function:

1
2
3
4
5
6
7
8
9
10
11
12
13

fun <E : Any> BaseTable<E>.int(name: String): BaseTable<E>.ColumnRegistration<Int> {
    return registerColumn(name, IntSqlType)
}

object IntSqlType : SqlType<Int>(Types.INTEGER, typeName = "int") {
    override fun doSetParameter(ps: PreparedStatement, index: Int, parameter: Int) {
        ps.setInt(index, parameter)
    }

    override fun doGetResult(rs: ResultSet, index: Int): Int? {
        return rs.getInt(index)
    }
}

IntSqlType is simple, it just obtaining int query results via ResultSet.getInt and setting parameters via PreparedStatement.setInt.

Here is a list of SQL types supported in Ktorm by default:

Extend More Data Types

Sometimes, Ktorm built-in data types may not satisfy your requirements. For example, you may want to save a JSON column to a table, many relational databases have supported JSON data type, but raw JDBC haven’t yet, nor Ktorm doesn’t support it by default. Now you can do it by yourself:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

class JsonSqlType<T : Any>(type: java.lang.reflect.Type, val objectMapper: ObjectMapper) 
    : SqlType<T>(Types.VARCHAR, typeName = "json") {
        
    private val javaType = objectMapper.constructType(type)

    override fun doSetParameter(ps: PreparedStatement, index: Int, parameter: T) {
        ps.setString(index, objectMapper.writeValueAsString(parameter))
    }

    override fun doGetResult(rs: ResultSet, index: Int): T? {
        val json = rs.getString(index)
        if (json.isNullOrBlank()) {
            return null
        } else {
            return objectMapper.readValue(json, javaType)
        }
    }
}

The class above is a subclass of SqlType, it provides JSON data type support via the Jackson framework. Now we have JsonSqlType, how can we use it to define a column? Looking back the int function’s implementation above, we notice that the registerColumn function was called. This function is exactly the entry provided by Ktorm to support datatype extensions. We can also write an extension function like this:

1
2
3
4
5
6
7

fun <E : Any, C : Any> BaseTable<E>.json(
    name: String,
    typeReference: TypeReference<C>,
    objectMapper: ObjectMapper = sharedObjectMapper
): BaseTable<E>.ColumnRegistration<C> {
    return registerColumn(name, JsonSqlType(typeReference.referencedType, objectMapper))
}

The usage is as follows:

1
2
3

object Foo : Table<Nothing>("foo") {
    val bar by json("bar", typeRef<List<Int>>())
}

In this way, Ktorm is able to read and write JSON columns now. Actually, this is one of the features of the ktorm-jackson module, if you really need to use JSON columns, you don’t have to repeat the code above, please add the dependency to your project:

Maven：

1
2
3
4
5

<dependency>
    <groupId>me.liuwj.ktorm</groupId>
    <artifactId>ktorm-jackson</artifactId>
    <version>${ktorm.version}</version>
</dependency>

Or Gradle：

1

compile "me.liuwj.ktorm:ktorm-jackson:${ktorm.version}"