Table of contents
Accessing SQL Databases With Vaadin-on-Kotlin
Vaadin-on-Kotlin provides first-class support for the following SQL databases out-of-the-box:
- H2 Database - a 100% Java database which can be quick-started as an in-memory database; perfect for writing tests for your app.
- PostgreSQL
- MariaDB
- MySQL
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 Person
s.
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, andbeanValidationBinder
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 a Grid header, dedicated for the filter components; then just create filter components and register them to the filter header bar. The filter components will listen for changes, then they will construct the final filter which will then be passed to the DataProvider
:
val nameFilter = FilterTextField()
val ageFilter = NumberRangePopup()
val aliveFilter = BooleanFilterField()
val dateOfBirthFilter = DateRangePopup()
val maritalStatusFilter = enumFilterField<MaritalStatus>()
val dataProvider = Person.dataProvider
val createdFilter = DateRangePopup()
grid(dataProvider = dataProvider) {
setSizeFull()
val filterBar = appendHeaderRow()
columnFor(Person::name) {
nameFilter.addValueChangeListener { updateFilter() }
filterBar.getCell(this).component = nameFilter
}
columnFor(Person::age) {
ageFilter.addValueChangeListener { updateFilter() }
filterBar.getCell(this).component = ageFilter
}
columnFor(Person::dateOfBirth) {
dateOfBirthFilter.addValueChangeListener { updateFilter() }
filterBar.getCell(this).component = dateOfBirthFilter
}
columnFor(Person::maritalStatus) {
maritalStatusFilter.addValueChangeListener { updateFilter() }
filterBar.getCell(this).component = maritalStatusFilter
}
columnFor(Person::alive) {
aliveFilter.addValueChangeListener { updateFilter() }
filterBar.getCell(this).component = aliveFilter
}
}
fun updateFilter() {
var c: Condition = Condition.NO_CONDITION
if (!nameFilter.isEmpty) {
c = c.and(Person::name.exp.likeIgnoreCase(nameFilter.value.trim() + "%"))
}
c = c.and(ageFilter.value.asIntegerInterval().contains(Person::age.exp))
if (!aliveFilter.isEmpty) {
c = c.and(Person::alive.exp.`is`(aliveFilter.value))
}
c = c.and(dateOfBirthFilter.value.contains(Person::dateOfBirth.exp, BrowserTimeZone.get))
if (!maritalStatusFilter.isAllOrNothingSelected) {
c = c.and(Person::maritalStatus.exp.`in`(maritalStatusFilter.value))
}
c = c.and(createdFilter.value.contains(Person::created.exp, BrowserTimeZone.get))
dataProvider.filter = c
}
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 callsetFilter()
, overwriting any filter you provided earlier.- That’s why you should use
Person.dataProvider.withFilter { Person::age between 20..60 }
. ThewithFilter()
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 thesetFilter()
.
This module provides a default set of filter components intended to be used with Vaadin Grid, to perform filtering of the data shown in the Grid:
- A
NumberFilterPopup
which allows the user to specify a numeric range of accepted values, which may be potentially open. - A
DateRangePopup
which allows the user to specify a date range of accepted values. - A
BooleanComboBox
which is betterly suited for filtering than aCheckbox
since it has three states:true
: filters beans having thetrue
value in given property;false
: filters beans having thefalse
value in given property;null
which disables the filter. - An
enumComboBox()
function which allows the user to filter for a particular enum constant.
In addition, you can use any Vaadin field component:
- A
TextField
for starts-with or full-text filtering. - A
ComboBox
with pre-populated values, to mimicenumComboBox()
when there’s a limited set of values present in the column. - Possibly others.
FilterBar.configure()
configures all filter fields by default as follows:
- the width is set to 100%
- the clear button is made visible for
TextField
andComboBox
. HasValueChangeMode.setValueChangeMode
is set toValueChangeMode.LAZY
: not to bombard the database with EAGER, but also not to wait until the focus is lost from the filter - not a good UX since the user types in something and waits and waits and waits with nothing going on.
You can override the configure()
function to modify this behaviour.
Note that the filter components need an implementation of the FilterFactory
to properly generate filter objects for a particular database backend. By default the [DataLoaderFilterFactory] is used: it produces vok-dataloader
-compatible Filter
instances which are accepted by vok-framework-v10-vokdb module. This detail is hidden within the asFilterBar()
function.
There is no support for JPA.
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 thedp
to the Grid and fill the search-everywhere filter intochainedDP
), the Grid will always polldp
for data. The problem is thatdp
never delegates data fetching tochainedDP
and hence the filter set tochainedDP
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() {
private val personDeptNameFilter: TextField
private val personNameFilter = FilterTextField()
private val deptNameFilter = FilterTextField()
private val dataProvider = PersonDept.dataProvider
init {
setSizeFull(); content { align(center, middle) }; isMargin = false; isSpacing = true
personDeptNameFilter = textField {
addValueChangeListener { updateFilter() }
}
// 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 = dataProvider) {
setSizeFull()
val filterBar = appendHeaderRow().asFilterBar()
columnFor(PersonDept::personName) {
personNameFilter.addValueChangeListener { updateFilter() }
filterBar.getCell(this).component = personNameFilter
}
columnFor(PersonDept::deptName) {
deptNameFilter.addValueChangeListener { updateFilter() }
filterBar.getCell(this).component = deptNameFilter
}
}
}
private fun updateFilter() {
var c: Condition = Condition.NO_CONDITION
val normalizedFilter = personDeptNameFilter.trim().toLowerCase()
if (value.isNotBlank()) {
c = c.and(PersonDept::personName.exp.startsWithIgnoreCase(normalizedFilter).or(PersonDept::deptName.exp.startsWithIgnoreCase(normalizedFilter)))
}
if (personNameFilter.value.isNotBlank()) {
c = c.and(PersonDept::personName.exp.startsWithIgnoreCase(personNameFilter.value))
}
if (deptNameFilter.value.isNotBlank()) {
c = c.and(PersonDept::deptName.exp.startsWithIgnoreCase(deptNameFilter.value))
}
dataProvider.filter = c
}
}
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()
orListDataProvider.items
to fetch the data. This will return all items as they were provided to theListDataProvider
constructor, not applying any filters set by either the Grid or the search-everywhereTextField
.
You can then export the beans into, say, a CSV.