Table of contents
  1. Accessing SQL Databases With Vaadin-on-Kotlin
    1. Basic CRUD ORM
    2. About vok-orm
    3. Persisting Simple Objects Into Tables
    4. Forms
    5. Using vok-orm With Vaadin Grid
      1. Showing entities in Grid
        1. Grid Filters
      2. Showing an Arbitrary Output of Any SQL SELECT Command
      3. Sorting, Paging and SQL Filters
    6. Chaining Data Providers
    7. Exporting Data From DataProviders


Accessing SQL Databases With Vaadin-on-Kotlin

Vaadin-on-Kotlin provides first-class support for the following SQL databases out-of-the-box:

All other SQL databases may or may not work. Care has been taken to only use the SQL92 syntax, but we only test and officially support the four of the above-mentioned databases.

NoSQL Note: Only SQL databases which provide appropriate JDBC drivers are currently supported. There is no direct support for NoSQL databases, but you can easily integrate any NoSQL database with VoK. Please read Accessing NoSQL or REST data sources for more information.

Note for experienced Java developers: Experienced Java developers will notice that VoK is not using JPA nor Hibernate to access the database. The reason is that there are inherent issues with the abstraction that JPA mandates - you can read more about the topic in the Why Not JPA article.

Basic CRUD ORM

The above scary acronym stands for inserting, querying and deleting rows from your database, and mapping those rows into Kotlin objects so that they are easy to work with.

The “CRUD” stands for Create, read, update and delete - the four basic operations performed on a collection of entities mapped to the database, such as a collection of Persons.

The ORM stands for Object-Relational Mapping and stands for mapping database rows into Kotlin objects, for easier use from within your application. VoK does not use JPA but instead features a new, vastly simplified database access layer called vok-orm.

About vok-orm

vok-orm is a very simple object-relational mapping library, built around the following ideas:

  • Simplicity is the most valued property; working with plain SQL commands is preferred over having a type-safe query language.
  • Kotlin objects merely capture JDBC ResultSet rows, by the means of invoking appropriate setters (based on the column name) via Java reflection.
  • The entities are just plain objects: they do not track modifications as JPA entities do, they do not automatically store modified values back into the database. They are never runtime-enhanced and can be final.
  • A switch from one type of database to another never happens. We understand that the programmer wants to exploit the full potential of the database, by writing SQLs tailored for that particular database. vok-orm should not attempt to generate SELECTs on behalf of the programmer (except for the very basic ones related to CRUD); instead it should simply allow SELECTs to be passed as Strings, and then map the result to an object of programmer’s choosing.

Because of its simple design principles, vok-orm supports not just mapping tables to Kotlin classes, but it allows mapping of any complex SELECT with joins and everything, even VIEWs, into Kotlin classes. Naturally this allows you to use any SELECT inside of a Vaadin Grid component which is a very powerful combination.

Persisting Simple Objects Into Tables

Please read the Usage examples chapter of the vok-orm documentation on how to write Kotlin classes that correspond to a particular SQL database table, and how to create rows in that particular database tables.

In this tutorial, we will modify the vok-helloworld-app project: it contains all moving parts but not much of an actual code which makes it ideal for experimenting. Just run git clone https://github.com/mvysny/vok-helloworld-app and open the project in your IDE and you’re good to go.

Note: please read the Tutorial on information on these files.

Let us have a Person table with the following columns:

Column Type Meaning
id Long? The primary key, automatically generated by the database, not null. The Kotlin type is nullable since we don’t know the ID yet when the person is just being created.
name String The full name of the person, not null.
age Int The age, not null.
dateOfBirth LocalDate? The date of birth, nullable.
alive Boolean Whether the person is alive or deceased.
maritalStatus MaritalStatus? The Marital status. Demoes binding to enum constants.
modified Instant? When the record was last modified in the database

Let’s first create a migration script which will prepare the database for us. Create a file named web/src/main/resources/db/migration/V01__CreatePerson.sql, with the following DDL script, depending on your database:

The PostgreSQL DDL script which creates such table is simple:

create table Person (
    id bigserial primary key,
    name varchar(400) not null,
    age integer not null,
    dateOfBirth date,
    alive boolean not null,
    maritalStatus varchar(200),
    modified timestamp not null
)

The MySQL/MariaDB DDL script:

create table Person (
    id bigint primary key auto_increment,
    name varchar(400) not null,
    age integer not null,
    dateOfBirth date,
    alive boolean not null,
    maritalStatus varchar(200),
    modified timestamp(3) not null
)

Finally, the H2 script:

create table Person (
    id bigint primary key auto_increment,
    name varchar not null,
    age integer not null,
    dateOfBirth date,
    alive boolean not null,
    maritalStatus varchar,
    modified timestamp not null
)

MaritalStatus Note: We will store the enum name into the database, e.g. “Single”, instead of just ordinal value of the constant (e.g. 0). The ordinal is easy to accidentally change by the programmer, e.g. by reordering the enum constants. The data would still load, but it would silently show incorrect information which is disastrous.

Create the web/src/main/kotlin/com/example/vok/Person.kt file with the Kotlin class which will map to this table is as follows:

package com.example.vok

import com.github.vokorm.*
import com.gitlab.mvysny.jdbiorm.Dao
import java.time.Instant
import java.time.LocalDate

enum class MaritalStatus { Single, Married, Divorced, Widowed }

data class Person(
    override var id: Long? = null,
    var name: String = "",
    var age: Int = 0,
    var dateOfBirth: LocalDate? = null,
    var alive: Boolean = true,
    var maritalStatus: MaritalStatus? = null,
    var modified: Instant? = null
) : KEntity<Long> {
    override fun save(validate: Boolean) {
        modified = Instant.now()
        super.save(validate)
    }

    companion object : Dao<Person, Long>(Person::class.java)
}

By implementing the KEntity interface the Kotlin class gains capability to create/update itself into the database; by having the companion object to extend the Dao class the Kotlin class gains the lookup capabilities. You can paste the following example into the WelcomeView.kt file:

package com.example.vok

import com.github.vok.karibudsl.flow.*
import com.github.vokorm.*
import com.vaadin.flow.component.orderedlayout.VerticalLayout
import com.vaadin.flow.router.Route

@Route("")
class WelcomeView: VerticalLayout() {
    init {
        button("Demo") {
            onClick {
                val person = Person(name = "John Doe", age = 42, alive = false, maritalStatus = MaritalStatus.Single)
                person.save()  // since ID is null, this will create the person and populate the ID
                println(Person.findAll())  // will print [Person(id=1, name=John Doe, age=42 etc)]
                println(Person.getById(person.id!!))  // will print Person(id=1, name=John Doe, age=42 etc)
                person.name = "Agent Smith"
                person.save()   // will update the person in the database, also updating the `modified` field
                println(Person.findById(25L)) // will print null since there is no such person yet
                Person.deleteAll()   // will delete all personnel
                Person.deleteById(42L)   // will delete a person with ID of 42
                println(Person.count()) // will print 0 since we deleted everything
                println(Person.findBy { "name = :name1 or name = :name2"("name1" to "John Doe", "name2" to "Agent Smith") })   // will print []
                Person.deleteBy { (Person::name eq "Agent Smith") }
                Person.getBy { "name = :name"("name" to "Agent Smith") }   // will fetch exactly one matching person, failing if there is no such person or there are more than one.
                Person.findSpecificBy { "name = :name"("name" to "Agent Smith") } // will fetch one matching person, failing if there are more than one. Returns null if there is none.
            }
        }
    }
}

To run the app, just type ./gradlew web:appRun in your console, or run the web module in Tomcat. Then, just browse http://localhost:8080 and click the “Demo” button. For more information please read the vok-orm documentation.

Finding Persons: If we want to load a list of persons from the database, the very important thing is to have a zero-arg constructor for the Person class. This can be achieved either by providing default values for all parameters, or explicitly declaring the zero-arg constructor. Otherwise the code will fail in runtime: vok-orm will try to construct a Person instance for every row returned, using a zero-arg constructor.

Forms

Forms allows the user to enter the values of a newly created record, or edit the values of already existing ones. Validation is typically employed, to guide the user to enter meaningful data.

We will use Vaadin Binder to bind form components to properties of the Person Kotlin class. A source code of the form is shown below; just create a file named web/src/main/kotlin/com/example/vok/PersonEditor.kt with the following contents:

package com.example.vok

import com.github.vok.karibudsl.flow.*
import com.github.vokorm.findAll
import com.vaadin.flow.component.HasComponents
import com.vaadin.flow.component.orderedlayout.VerticalLayout

class PersonEditor : VerticalLayout() {
    private val binder = beanValidationBinder<Person>()
    var person: Person? = null
        set(value) {
            field = value
            if (value != null) binder.readBean(value)
        }

    init {
        isMargin = false
        textField("Name") {
            bind(binder).bind(Person::name)
        }
        textField("Age") {
            bind(binder).toInt().bind(Person::age)
        }
        datePicker("Date Of Birth") {
            bind(binder).bind(Person::dateOfBirth)
        }
        checkBox("Alive") {
            bind(binder).bind(Person::alive)
        }
        comboBox<MaritalStatus>("Marital Status") {
            setItems(*MaritalStatus.values())
            bind(binder).bind(Person::maritalStatus)
        }
        button("Save Person") {
            onClick {
                val person = person!!
                if (binder.validate().isOk && binder.writeBeanIfValid(person)) {
                    person.save()
                    println(Person.findAll())
                }
            }
        }
    }
}

fun HasComponents.personEditor(block: PersonEditor.()->Unit = {}) = init(PersonEditor(), block)

This will create a form as a reusable component which we can then use in the WelcomeView as follows:

package com.example.vok

import com.vaadin.flow.component.orderedlayout.VerticalLayout
import com.vaadin.flow.router.Route

@Route("")
class WelcomeView: VerticalLayout() {
    init {
        personEditor {
            person = Person()
        }
    }
}

The form will allow you to create a new person, or edit an existing one. However, the user can now enter invalid data, such as negative numbers for age etc.

We will use so-called JSR303 validation annotations, which will make the beanValidationBinder validate the bean for us. Edit the Person class as follows:

package com.example.vok

import com.github.vokorm.*
import com.gitlab.mvysny.jdbiorm.Dao
import org.hibernate.validator.constraints.*
import java.time.*
import jakarta.validation.constraints.*

enum class MaritalStatus { Single, Married, Divorced, Widowed }

data class Person(
    override var id: Long? = null,
    @field:NotNull
    @field:Length(min = 2)
    var name: String = "",
    @field:Min(1)
    var age: Int = 0,
    @field:Past
    var dateOfBirth: LocalDate? = null,
    var alive: Boolean = true,
    var maritalStatus: MaritalStatus? = null,
    var modified: Instant? = null
) : KEntity<Long> {
    override fun save(validate: Boolean) {
        modified = Instant.now()
        super.save(validate)
    }

    companion object : Dao<Person, Long>(Dao::class.java)
}

Important: Make sure to attach those annotations to field! If you would just write @Min(1), the annotation would be applied to the getter instead to the field, and beanValidationBinder would ignore it.

Now, typing in incorrect values will make the field go red and show the validation errors; the “Save” button will also not create a Person instance if the values are invalid.

Using vok-orm With Vaadin Grid

Vaadin Grid is a very powerful component which allows you to show a lazy-loaded list of rows on a web page. It allows the user to:

  • efficiently scroll the list, lazy-loading more data as they are scrolled into the viewport,
  • sorting by one or more columns (shift-click the caption to add sorting columns)
  • filtering from code
  • VoK provides means to auto-generate filter components and auto-populate them into the Grid, which provides you with a simple means to allow the user to filter as well.

You can find more information about how to use Vaadin Grid with Vaadin-on-Kotlin at the Using Grids guide page.

Showing entities in Grid

We will start with the most basic Grid which will show the list of Person. By default the Grid shows all columns, therefore we need to restrict the columns a bit:

package com.example.vok

import eu.vaadinonkotlin.vaadin10.vokdb.*
import com.github.mvysny.karibudsl.v10.*
import com.github.vokorm.db
import com.vaadin.flow.component.orderedlayout.VerticalLayout
import com.vaadin.flow.data.renderer.NativeButtonRenderer
import com.vaadin.flow.router.Route

@Route("")
class WelcomeView: VerticalLayout() {
    init {
        db {
            (5..30).forEach { Person(name = "p$it", age = it).save() }
        }
        setSizeFull()
        grid(dataProvider = Person.dataProvider) {
            setSizeFull()

            columnFor(Person::id)
            columnFor(Person::name)
            columnFor(Person::age)
            columnFor(Person::dateOfBirth)
            columnFor(Person::maritalStatus)
            columnFor(Person::alive)
            // example of a custom renderer which converts value to a displayable string.
            columnFor(Person::modified, converter = { it.toString() })
            addColumn(NativeButtonRenderer<Person>("Delete", { item -> item.delete(); this@grid.refresh() }))
        }
    }
}

This is a full-blown Grid with lazy-loading and SQL-based (so not in-memory) sorting working out-of-the-box.

Grid Filters

Adding the possibility for the user to filter on the contents of the Grid is really easy. You need to add the filter bar to the Grid; then just create filter components and register them to the filter bar. The filter bar will make sure to listen for changes in these components, then it will construct the final filter which will then be passed to the DataProvider:

grid(dataProvider = Person.dataProvider) {
  setSizeFull()
  val filterBar = appendHeaderRow().asFilterBar()
  columnFor(Person::id) {
    filterBar.forField(NumberRangePopup(), this).inRange()
  }
  columnFor(Person::name) {
    filterBar.forField(TextField(), this).ilike()
  }
  columnFor(Person::age) {
    filterBar.forField(NumberRangePopup(), this).inRange()
  }
  columnFor(Person::dateOfBirth) {
    filterBar.forField(DateRangePopup(), this).inRange(LocalDate::class)
  }
  columnFor(Person::maritalStatus) {
    filterBar.forField(enumComboBox<MaritalStatus>(), this).eq()
  }
  columnFor(Person::alive) {
    filterBar.forField(BooleanComboBox(), this).eq()
  }
}

All filter components will be monitored for a value change events, and a proper filter will be set to the data provider upon every change. To achieve this, all built-in VoK data providers offered for all entities by the Dao interface (via the dataProvider extension property) are already configurable (that is, instances of ConfigurableFilterDataProvider).

You can also create an unremovable programmatic filter easily:

grid(dataProvider = Person.dataProvider.withFilter { Person::age between 20..60 }) {
    // ...
}

The unremovable filter will be ANDed with any additional filters set by the filter components. The important distinction here is as follows:

  • Person.dataProvider.apply { setFilter { Person::age between 20..60 } } will set a filter to the data provider. However this filter will be overwritten by a Filter computed by FilterBar when any filter component changes. That is because the filter bar will also call setFilter(), overwriting any filter you provided earlier.
  • That’s why you should use Person.dataProvider.withFilter { Person::age between 20..60 }. The withFilter() function takes an existing DataProvider and creates a new one, which delegates all data-fetching calls to the old one but always ANDs given filter with any filters set by the setFilter().

Showing an Arbitrary Output of Any SQL SELECT Command

Say that we have a join which joins Persons with their departments. Something like the following:

SELECT person.name as personName, dept.name as deptName FROM Person person, Department dept WHERE person.deptId=dept.id

To capture the outcome of this SELECT we can simply declare the following class:

data class PersonDept(var personName: String? = null, var deptName: String? = null) : Serializable

Of course the PersonDept will not be an entity (since it’s not represented by a single Table and cannot be saved nor deleted), hence it does not implement the KEntity interface. Since Dao class is only applicable to entities, we can’t reuse the Dao-induced finders.

To load instances of this particular class, we will need to write our own finder methods. We will directly use the vok-orm capabilities to map any SELECT result into an arbitrary class. In order for the automatic mapping to work, we must ensure that:

  • The SQL SELECT column names exactly match the Kotlin properties names (and beware that it’s string case-sensitive matching);
  • The SQL types are compatible with Java types of matching fields.

For example: (Note: replace \{ by {):

data class PersonDept(var personName: String? = null, var deptName: String? = null) {
    companion object {
        fun findAll(): List<PersonDept> = db {
            con.createQuery("SELECT person.name as personName, dept.name as deptName FROM Person person, Department dept WHERE person.deptId=dept.id")
                .executeAndFetch(PersonDept::class.java)
        }

        val dataProvider: VokDataProvider<PersonDept> get() =
            sqlDataProvider(PersonDept::class.java,
                "SELECT person.name as personName, dept.name as deptName FROM Person person, Department dept WHERE person.deptId=dept.id \{\{WHERE}} order by 1=1\{\{ORDER}} \{\{PAGING}}",
                idMapper = { it })
    }
}

Note: The sqlDataProvider function contains extensive documentation on this topic, please consult the kdoc for that class in your IDE.

The dataProvider clause will allow us to use the PersonDept class with Vaadin Grid simply, with the full power of lazy-loading, sorting and filtering:

class MyUI : UI {
    override fun init(request: VaadinRequest) {
        grid(dataProvider = PersonDept.dataProvider) {
            setSizeFull()
            val filterBar = appendHeaderRow().asFilterBar()
            columnFor(PersonDept::personName) {
                filterBar.forField(TextField(), this).ilike()
            }
            columnFor(PersonDept::deptName) {
                filterBar.forField(TextField(), this).ilike()
            }
        }
    }
}

Sorting, Paging and SQL Filters

Paging will simply work out-of-the box, since sqlDataProvider will simply replace \{\{PAGING}} with appropriate LIMIT 30 OFFSET 0 stanzas.

Sorting will also work out-of-the-box since sqlDataProvider will emit , personName ASC stanzas based on PersonDept::personName property names. This will naturally work properly since such columns are present in the SQL SELECT command.

Simple auto-generated filters will also work since they will simply filter based on proper column names.

We can of course create much more complex filters, say global filters that will find given text anywhere in the table, in all fields. Read below on how this can be done.

Chaining Data Providers

Sometimes filtering the Grid using the generated filter components is not enough: sometimes it is handy to have an additional search-everywhere TextField right above the Grid, to do a very fast coarse search. The user can then fine-tune the search by further refining the search criteria in Grid’s generated filter bar. We need to compute the filters both from the search-everywhere TextField and the Grid and AND them together.

A naive implementation would be to create one data provider, set it to the Grid and set its filter whenever the search-everywhere TextField changes. The problem with this approach is that when user types something into Grid’s auto-generated filter component, it will set the filter into the data provider as well, overwriting any filter set previously by the search-everywhere TextField.

In order to AND multiple filters, we can use the data provider chaining / delegation technique. We will take a data provider and create a new one, which will delegate the data-fetching tasks to the original data provider but will AND the query filter with any filter that’s set into it:

val dp = PersonDept.dataProvider
// the search-everywhere TextField will set the filters into `dp` by calling dp.setFilter()
val chainedDP = dp.withConfigurableFilter2()
// the withConfigurableFilter2() method creates a new data provider which delegates data-fetching calls to the original, but
// will apply its own filters as well

We can now set the chainedDP to the Grid and generate the grid filter components. The filter components will set filters to the chainedDP while the search-everywhere TextField will set filters to the dp. This way the filters won’t get overwritten by one another.

You can chain even more data providers: for example if you wish to restrict the result to a particular company only, you can type

val dp = PersonDept.dataProvider.withFilter { PersonDept::companyId eq 25L }

Important: Make sure to configure Grid with the last chained data provider (in this example, the chainedDP). If you set the data providers in reverse (say, you’ll set the dp to the Grid and fill the search-everywhere filter into chainedDP), the Grid will always poll dp for data. The problem is that dp never delegates data fetching to chainedDP and hence the filter set to chainedDP is never applied.

Following is a full example code which demonstrates this technique:

package com.example.vok

import eu.vaadinonkotlin.vaadin10.vokdb.*
import com.github.mvysny.karibudsl.v10.*
import com.vaadin.flow.component.orderedlayout.VerticalLayout
import com.vaadin.flow.router.Route

@Route("")
class WelcomeView: VerticalLayout() {
    init {
        setSizeFull(); content { align(center, middle) }; isMargin = false; isSpacing = true

        val dp = PersonDept.dataProvider
        textField {
            addValueChangeListener {
                val normalizedFilter = value.trim().toLowerCase() + "%"
                if (value.isNotBlank()) {
                    dp.setFilter { "personName ILIKE :filter or deptName ILIKE :filter"("filter" to normalizedFilter) }
                } else {
                    dp.setFilter(null)
                }
            }
        }
        // wrap 'dp' in configurable filter data provider. This is so that the filter set by the generated filter
        // components would not overwrite filter set by the custom text field filter above.
        grid(dataProvider = dp.withConfigurableFilter2()) {
            setSizeFull()
            val filterBar = appendHeaderRow().asFilterBar()
            columnFor(PersonDept::personName) {
                filterBar.forField(TextField(), this).ilike()
            }
            columnFor(PersonDept::deptName) {
                filterBar.forField(TextField(), this).ilike()
            }
        }
    }
}

Exporting Data From DataProviders

You can simply call DataProvider.getAll() which will fetch all beans from the data provider satisfying filters set by the setFilter() or withFilter(). You need to be careful though - you can easily run out of memory if the number of matching rows is huge.

Since Grid’s generated filter components calls setFilter(), the getAll() function will honor both the user-configured values in the filter components, and the search-everywhere text field in the example above.

Note: don’t use the ListDataProvider.getItems() or ListDataProvider.items to fetch the data. This will return all items as they were provided to the ListDataProvider constructor, not applying any filters set by either the Grid or the search-everywhere TextField.

You can then export the beans into, say, a CSV.