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 | create table t_department( |
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 | object Departments : Table<Nothing>("t_department") { |
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 theprimaryKey
function onColumnRegistration
to declare the current column as a primary key.
ColumnRegistration
implements theReadOnlyProperty
interface, so we can use it as a property delegate via Kotlin’s by keyword. Therefore, in the definitionval name by varchar("name")
, although the return type ofvarchar
isColumnRegistration<String>
, theval name
property’s type isColumn<String>
. For the same reason, theval managerId by int("manager_id")
property’s type isColumn<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 | sealed class Employees(tableName: String) : Table<Nothing>(tableName) { |
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 | val t = object : Table<Nothing>("t_config") { |
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 | fun <E : Any> BaseTable<E>.int(name: String): BaseTable<E>.ColumnRegistration<Int> { |
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:
Function Name | Kotlin Type | Underlying SQL Type | JDBC Type Code (java.sql.Types) |
---|---|---|---|
boolean | kotlin.Boolean | boolean | Types.BOOLEAN |
int | kotlin.Int | int | Types.INTEGER |
long | kotlin.Long | bigint | Types.BIGINT |
float | kotlin.Float | float | Types.FLOAT |
double | kotlin.Double | double | Types.DOUBLE |
decimal | java.math.BigDecimal | decimal | Types.DECIMAL |
varchar | kotlin.String | varchar | Types.VARCHAR |
text | kotlin.String | text | Types.LONGVARCHAR |
blob | kotlin.ByteArray | blob | Types.BLOB |
bytes | kotlin.ByteArray | bytes | Types.BINARY |
jdbcTimestamp | java.sql.Timestamp | timestamp | Types.TIMESTAMP |
jdbcDate | java.sql.Date | date | Types.DATE |
jdbcTime | java.sql.Time | time | Types.TIME |
timestamp | java.time.Instant | timestamp | Types.TIMESTAMP |
datetime | java.time.LocalDateTime | datetime | Types.TIMESTAMP |
date | java.time.LocalDate | date | Types.DATE |
time | java.time.Time | time | Types.TIME |
monthDay | java.time.MonthDay | varchar | Types.VARCHAR |
yearMonth | java.time.YearMonth | varchar | Types.VARCHAR |
year | java.time.Year | int | Types.INTEGER |
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 | class JsonSqlType<T : Any>(type: java.lang.reflect.Type, val objectMapper: ObjectMapper) |
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 | fun <E : Any, C : Any> BaseTable<E>.json( |
The usage is as follows:
1 | object Foo : Table<Nothing>("foo") { |
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 | <dependency> |
Or Gradle:
1 | compile "me.liuwj.ktorm:ktorm-jackson:${ktorm.version}" |