Использование библиотеки Ktorm

Использование библиотеки Ktorm #

Библиотека ktorm предоставляет средства для выполнения типичных SQL-запросов к базам данных на основе общего интерфейса доступа JDBC. Данные запросы генерирутся на основе вызовов к библиотеке, что позволяет контролировать структуру запроса на уровне компилятора, что значительно снижает количество возможных ошибок. Стоит отметить, что данная библиотека не предоставляет программные интерфейсы для выполнения сложных операций через контролируемый API, но предоставляет возможность по выполнению сырого SQL в таких случаях.

Подключение библиотеки к приложению #

В файл gradle.properties добавьте версию библиотеки:

ktormVersion=3.5.0

В файл build.gradle добавьте подключение библиотеки в список зависимостей:

dependencies {
    implementation group: "org.ktorm", name: "ktorm-core", version: ktormVersion
    implementation group: "org.ktorm", name: "ktorm-support-mysql", version: ktormVersion
}

Помимо ядра библиотеки также подключаем диалект MySQL, т.к. диалекта для H2 в официальной поставке библиотеки нет, но синтаксис её SQL значительно совпадает с H2.

Подключение к базе данных #

Для подключения к базе данных используется метод Database.connect. Ему необходимо передать:

  • URL для подключения к базе данных.
  • Название JDBC-драйвера для выполнения подключения.
  • Имя пользователя и пароль для подключения.
  • Диалект SQL для выполнения операций.

Для базы данных H2 подключение будет выглядеть следующим образом:

import org.ktorm.database.Database
import org.ktorm.support.mysql.MySqlDialect

fun connectToDatabase() = Database.connect(
    url = H2DatabaseManager.JDBC_CONNECTION,
    driver = "org.h2.Driver",
    user = "sa",
    dialect = MySqlDialect(),
)

Данный код зависит от строки подключения, которая определена в классе H2DatabaseManager.

Данный метод возвращает экземпляр класса Database, через который будут осуществляться запросы к базе данных.

Данный метод следует разместить в файле DatabaseManager.kt

Описание таблиц #

Для создания SQL-выражений библиотеке Ktorm требуется описание таблиц, которые доступны в базе данных приложения. Для описания таблиц используется следующая нотация:

object DepartmentTable : Table<Nothing>("DEPARTMENT") {
    val id = int("ID").primaryKey()
    val name = varchar("NAME")
    val location = varchar("LOCATION")
}

object EmployeeTable : Table<Nothing>("EMPLOYEE") {
    val id = int("ID").primaryKey()
    val name = varchar("NAME")
    val job = varchar("JOB")
    val managerId = int("MANAGER_ID")
    val hireDate = date("HIRE_DATE")
    val salary = long("SALARY")
    val departmentId = int("DEPARTMENT_ID")
}

Прошу обратить внимание на указание названий столбцов и таблиц в верхнем регистре. При использовании Flydb все названия колонок и таблиц приводятся к верхнему регистру.

Созданные объекты наследуются от класса Table, которому передаётся название таблицы, используемой в базе данных. В качестве типа указывается встроенный в Kotlin-тип Nothing. Это нормально, т.к. мы не планируем использовать преобразование строк данных в объекты.

Колонки определяются как свойства в указанных объектах. Тип свойств определяется функциями, предоставляемыми пакетом org.ktorm.schema. В данном пакете есть описание для всех базовых типов, которые могут хранится в базе данных. Каждой функции передаётся название колонки в верхнем регистре.

Данные функции-генераторы возвращают описание колонок в виде сущности класса Column.

Детальная информация по возможностям описания схемы базы данных находится в официальной документации.

Выборки из базы данных #

Для выполнения выборки из базы данных используется следующий доменный язык описания запросов:

database
    .from(EmployeeTable)
    .select(EmployeeTable.name)
    .where { (EmployeeTable.departmentId eq 1) and (EmployeeTable.name like "%vince%") }
    .forEach { row ->
        println(row[EmployeeTable.name])
    }

В рамках данного выражения происходит:

  1. Формирование SQL-запроса на строготипизированном языке программирования.
  2. Вставка параметров в данный запрос.
  3. Выполнение данного запроса относительно базы данных.
  4. Обработка результата в лямбда-выражении, переданном блоку forEach.

Методу select() можно либо передать список колонок, либо ничего не передавать. В этом случае произойдёт выборка всех колонок.

Для обработки результатов запроса можно воспользоваться методами map, flatMap, mapNotNull и т.д. Т.к. в качестве выполнения запроса нам передаётся итератор по объектам QueryResultSet, который необходимо преобразовать либо к списку элементов, либо к нужному элементу.

Для доступа к элементам можно воспользоваться либо методами по получению элементов по индексу getInt, getFloat и т.д., либо индексным доступом, где индексом выступает описание колонки: row[EmployeeTable.name]. Рекомендуется использовать данный вариант, хоть он и требует чуть большего ввода от программиста. В замен мы получаем понятный код.

Операции по получению списка элементов #

При обращении к хранилищу данных рекомендуется описывать каждый элемент в виде отдельной операции. Это позволит отделить обработчики HTTP-запросов от логики по изменению данных внутри приложения.

Рассмотрим пример операции на получение списка данных:

data class Employee(val id: Int, val name: String, val hireDate: LocalDate)

class ListEmployeesForDepartmentOperation(
    private val database: Database,
) {
    fun list(departmentId: Int): List<Employee> =
        database
            .from(EmployeeTable)
            .select(EmployeeTable.id, EmployeeTable.name, EmployeeTable.hireDate)
            .where { EmployeeTable.departmentId eq departmentId }
            .mapNotNull { row ->
                try {
                    Employee(
                        row[EmployeeTable.id]!!,
                        row[EmployeeTable.name]!!,
                        row[EmployeeTable.hireDate]!!,
                    )
                } catch(npe: NullPointerException) {
                    null
                }
            }

}

Для возвращения данных из запроса мы ввели класс данных Employe. Он предоставляет доступ к полям, которые были успешно получены из базы данных.

Для решения проблемы отсутствия необходимых данных используется явное преобразование к типам данных без null с отлавливанием соответствующей ошибки.

Затем данную операцию можно использовать в соответствующем обработчике HTTP-запроса:

private val departmentIdLens = Path.int().of("departmentId")

fun listEmployees(
    listEmployeesOperation: ListEmployeesForDepartmentOperation,
    htmlView: BiDiBodyLens<ViewModel>,
): HttpHandler = { request ->
    val departmentId = departmentIdLens(request)
    val employees = listEmployeesOperation.list(departmentId)
    Response(Status.OK).with(htmlView of ListEmployeesVM(departmentId, employees))
}

Операция по получению конкретного элемента #

Обычные запросы на получение данных из базы предполагают возвращение списка однородных значений. Однако нам часто приходится выполнять запросы к базе данных на получение одного элемента. В этом случае можно воспользоваться следующим шаблоном:

data class Employee(val id: Int, val name: String, val hireDate: LocalDate)

class FetchEmployeeOperation(
    private val database: Database,
) {
    fun fetch(employeeId: Int): Employee? =
        database
            .from(EmployeeTable)
            .select(EmployeeTable.id, EmployeeTable.name, EmployeeTable.hireDate)
            .where { EmployeeTable.id eq employeeId }
            .mapNotNull { row ->
                row[EmployeeTable.id]?.let { id ->
                    row[EmployeeTable.name]?.let { name ->
                        row[EmployeeTable.hireDate]?.let { hireDate ->
                            Employee(id, name, hireDate)
                        }
                    }
                }
            }
            .firstOrNull()

}

При реализации данной операции для решения проблемы отсутствия значения использовались ?.let-выражения. Данный подход подходит для получения ограниченного набора данных. Но не требует обработки исключительных ситуаций как предыдущий код.

Выделение операций по преобразованию данных #

Как видно из примеров выше, при выполнении разных запросов зачастую операции по преобразованию списка строк в объекты могут дублироваться между запросами. В таком случае можно вынести операцию по преобразованию данных внутрь объекта-результата:

data class Employee(val id: Int, val name: String, val hireDate: LocalDate) {
    companion object {
        fun fromResultSet(QueryResultSet): Employee? =
            try {
                Employee(
                row[EmployeeTable.id]!!,
                row[EmployeeTable.name]!!,
                row[EmployeeTable.hireDate]!!,
            )
            } catch(npe: NullPointerException) {
                null
            }
    }
}

В результате 2 предыдущих запроса могут быть упрощены:

database
    .from(EmployeeTable)
    .select(EmployeeTable.id, EmployeeTable.name, EmployeeTable.hireDate)
    .where { EmployeeTable.departmentId eq departmentId }
    .mapNotNull(Employee::fromResultSet)

database
    .from(EmployeeTable)
    .select(EmployeeTable.id, EmployeeTable.name, EmployeeTable.hireDate)
    .where { EmployeeTable.id eq employeeId }
    .mapNotNull(Employee::fromResultSet)
    .firstOrNull()

Выборка из нескольких таблиц #

Ktorm предоставляет удобные средства для выполнения выборок из нескольких таблиц. При этом поддерживается выполнение нескольких запросов к одной и той же таблице. Для последнего можно привлекать механизм создания псевдонимов:

data class Names(val name: String?, val managerName: String?, val departmentName: String?)

val emp = EmployeeTable.aliased("emp")
val mgr = EmployeeTable.aliased("mgr")
val dept = DepartmentTable.aliased("dept")

val results = database
    .from(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 { row ->
        Names(
            name = row[emp.name],
            managerName = row[mgr.name],
            departmentName = row[dept.name]
        )
    }

Псевдонимы можно использовать и для формирования других запросов.

Вставка данных в таблицы #

Ktorm предоставляет средства для вставки данных в таблицы. Для этих целей используется следующий синтаксис:

database.insert(EmployeeTable) {
    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)
}

Изменение данных #

Ktorm также предоставляет возможность по обновлению данных в таблице. Будьте внимательны: не забывайте указать блок для идентификации строк, которые надо обновить.

database.update(EmployeeTable) {
    set(it.job, "engineer")
    set(it.managerId, null)
    set(it.salary, 100)
    where {
        it.id eq 2
    }
}

Обощие или частные операции #

При разработке операций над базой данных может возникнуть вопрос: насколько гранулярными необходимо делать операции. Например: стоит ли делать отдельный запрос на получение одного элемента, если есть запрос на получение списка элементов? Или стоит ли делать запрос на получение части коллекции, если есть возможность получить всю коллекцию?

В обоих случаях ответ отрицательный: если количество элементов в запросе изменяется в зависимости от передаваемых в запрос параметров (по логике, а не по фактуре), то необходимо разрабатывать отдельные запросы.

Тогда стоит задать вопрос: предположим, что нам необходимо получить имена сотрудников, но у нас уже есть запрос, который возвращает полноценные объекты, описывающего наших сотрудников? В данном случае лучшим вариантом будет разработка отдельного запроса на получение данных. В этом случае объём передачи информации от хранилища в приложение будет миниальным.

Таким образом почти всегда стоит разработать отдельные запросы для каждой операции.

Количество операций для обработчика запросов #

Обработчики запросов должны предоставлять доступ к документам, которые заинтересуют пользователей. Для формирования этих документов зачастую необходимо выполнить несколько запросов к базе данных. И следовательно встаёт вопрос: сколько всего запросов может быть использовано внутри обработчика?

Ответ на самом деле достаточно простой: если внутри обработчика не происходит дополнительная обработка информации от запросов, включая условную или циклическую логику относительно данных, полученных из разных запросов. Если необходима любая обработка данных, это означает, что необходимо внести новую операцию, где будет происходить обработка данных нужным образом.

Для операций изменения обычно необходимо минимум 2 запроса: на получение изначальных данных и на выполнение изменения данных.

Хранилщие операций #

При таком потенциально большом количестве операций встаёт вопрос об их удобном инициализировании и использовании. Одним из вариантов для решения этой задачи может явиться создания хранилища для операций. В его задачу будет входить инициализация операций в правильном порядке и передача ссылки на операцию заинтересованным обработчикам.

flowchart TB router["Маршрутизатор"] handler1["Обработчик"] handler2["Обработчик"] holder["Хранилище операций"] operation1["Операция"] operation2["Операция"] operation3["Операция"] operation4["Операция"] holder --> operation1 holder --> operation2 holder --> operation3 holder --> operation4 operation2 --> operation3 operation2 --> operation4 router --> holder router --> handler1 router --> handler2 handler1 --> operation1 handler2 --> operation2

С точки зрения реализации это может выглядеть следующим образом:

class OperationHolder(database: Database) {
    val fetchEmployeeOperation = FetchEmploeeOperation(database)
    val listEmployeesOperation = ListEmploeesOperation(database)
}

Данное хранилище операций можно использовать при инициализации маршрутов внутри маршрутизатора:

fun dynamicRoutes(
    operationHolder: OperationHolder,
    htmlView: BiDiBodyLens<ViewModel>,
) = routes(
    "/emploee" bind Method.GET to showEmployeList(
        operationHolder.listEmploeesOperation,
        htmlView,
    ),
    "/employee/employeeId" bind Method.GET to showEmployee(
        operationHolder.fetchEmployeeOperation,
        htmlView,
    ),
)

Задача #

  1. Подключите библиотеку Ktorm к вашему приложению.
  2. Добавьте операцию по подключению базы данных Ktorm к базе данных H2. Разместите её в пакете domain.
  3. Вызовите функцию по подключению к базе данных после выполнения миграций схем базы данных.
  4. Добавьте описание схемы таблицы треугольников, чтобы можно было обращаться к её полям. Обратите внимание, что название полей должно быть в верхнем регистре. Разместите её в пакете domain.tables.
  5. Создайте операцию по получению списка треугольников. Обратите внимание, что в базе данных все стороны треугольника всегда указаны. Разместите её в пакете domain.operations.
  6. Создайте хранилище операций и создайте экземпляр данной операции внутри хранилища. Разместите хранилище в domain.operations.
  7. Создайте экземпляр хранилища операций в фукнции инициализации веб-сервера.
  8. Используйте операцию по получения списка треугольников внутри обработчика запросов на показ списка треугольников вместо передачи списка треугольников. Разместите её в пакете domain.operations.
  9. Передайте хранилище операций в качестве аргумента в динамическое хранилище запросов. Получите из него операцию и передайте соответствюущему обработчику запросов.
  10. Создайте операцию по получению одного треугольника из списка. Используйте данную операцию в обработчике запросов на отображении информации о конкретном треугольнике.
  11. Добавьте описание схемы таблицы ромбов в приложении, чтобы можно было обращаться к её полям.
  12. Создайте операцию по подсчёту количества фигур, треугольников и ромбов, в системе.
  13. Создайте обработчик запроса /figureCount, на котором выведите информацию:
    • О количестве треугольников.
    • О количестве ромбов.
    • О количестве фигур. Где разумно разместить подсчёт общего количества фигур?

© A. M. Васильев, 2022, CC BY-SA 4.0, andrey@crafted.su