Introduction
Almost all access to SQL-based databases is performed through the JDBC API. It provides a standardized way for Java programs to interact with databases, enabling seamless communication and data manipulation. It acts as an intermediary layer, shielding the application from the underlying complexities of different database systems.
It also makes use of the SQL language which is a standard to unify the interactions with the database, from inserting, updating, querying, and deleting data.
This SQL language, together with the JDBC API, abstracts away the differences in the database systems. It contains features to increase the performance like prepared statements which also help in securing your application by using parameters and not just ‘pasting’ values into the SQL query.
The API itself is rather old and still relies on a lot of manual actions and statements from the developer. Resources like result sets, statements, and connections need to be closed after we don’t need them anymore. This results in a lot of boilerplate code.
The SQL language itself is seen as problematic by a lot of developers. The SQL Language is completely different from Java itself. And thus not everyone is keen on learning something besides Java. Especially since there are frameworks these days that provide a solution that works completely within Java.
But the traditional ORM frameworks like Hibernate bring a lot of other challenges and problems as I have described in the previous blog – Why ORM and Data frameworks are not your best option. So be careful in what features and functionalities you use in your application.
In this blog, we look at the Exposed library written in Kotlin. It is a lightweight SQL library on top of the JDBC driver for the Kotlin language. Exposed has two flavors of database access: typesafe SQL wrapping DSL and lightweight Data Access Objects (DAO).
It is an alternative for Java-based solutions like Hibernate when you are working with Kotlin. Also, JetBrains recently announced that it will increase the efforts into the framework, see this blog.
Installation
Since the Exposed framework has 2 flavours, the dependencies that you should add are separated into different artifacts.
The core dependency contains all the basic code and functionality which is independent of the JDBC or DAO flavour. Depending on the flavour you want to use, you should add the dependency you need.
When using Gradle as the build system, you can add the following dependencies.
dependencies {
implementation "org.jetbrains.exposed:exposed-core:${exposed.version}"
implementation "org.jetbrains.exposed:exposed-dao:${exposed.version}"
implementation "org.jetbrains.exposed:exposed-jdbc:${exposed.version}"
}
In the case of Maven, the following snippet gives you all the information you need.
<dependency>
<groupId>org.jetbrains.exposed</groupId>
<artifactId>exposed-core</artifactId>
<version>${exposed.version}</version>
</dependency>
<dependency>
<groupId>org.jetbrains.exposed</groupId>
<artifactId>exposed-dao</artifactId>
<version>${exposed.version}</version>
</dependency>
<dependency>
<groupId>org.jetbrains.exposed</groupId>
<artifactId>exposed-jdbc</artifactId>
<version>${exposed.version}</version>
</dependency>
Defining the DB connection, which can be based on a standard DataSource object or use Connection Pooling provided by a third-party library like HikariCP, just requires calling the org.jetbrains.exposed.sql.Database.Companion#connect() function. This will make sure that all parts of the framework will use the DB connection from this setting. You can create for example following
object DBSettings {
private val config = HikariConfig().apply {
jdbcUrl = "jdbc:postgresql://localhost:5432/postgres"
driverClassName = "org.postgresql.ds.PGSimpleDataSource"
username = "postgres"
password = "mysecretpassword"
maximumPoolSize = 10
}
val dataSource by lazy {
HikariDataSource(config)
}
val db by lazy {
Database.connect(dataSource)
}
}
Where you obviously retrieve the connection parameters from an external location instead of just hardcoding them in the code as in the previous example.
Typesafe access
One of the challenges we have when working with databases is creating SQL statements and mapping them to instances. We can either execute a SQL query which is basically just a String and map each field by hand to our properties of the objects. This is can very tedious. Or we define some ‘entities’ like in ORM frameworks and let the framework generate the SQL query. But that can lead to unoptimised queries, especially when you need very complex queries.
The Exposed framework, like some other frameworks in Java and Kotlin, try to approach this in a very lightweight way that handles the mapping for you so that your code is more type-safe (is a certain database field used as a String or Number?) and name changes in database columns and code can be handled transparently.
Within the JDBC feature of Exposed, you can define Singletons in Kotlin that extend from the Table class and define the correspondence between the table and code. Due to the expressiveness of Kotlin, these definitions can be highly readable. For example, an instance that maps to a table ‘Users’ that has some fields and a foreign key to the table ‘Cities’ can be defined as
object Users : Table() {
val id = integer("id").autoIncrement() // Column<Int>
val name = varchar("name", length = 50) // Column<String>
val cityId = (integer("city_id").references(Cities.id)).nullable() // Column<Int?>
override val primaryKey = PrimaryKey(id, name = "PK_User_ID") // name is optional here
}
Due to the ‘inheritance’ from Table, each singleton defined as in the example, gets a lot of functions to perform some actions.
Like inserting into the table, and retrieving the newly assigned id using the auto-increment functionality of the database on the fly.
val amsterdamId = Cities.insert {
it[name] = "Amsterdam"
} get Cities.id
Or loop over each record in the table
for (city in Cities.selectAll()) {
println("${city[Cities.id]}: ${city[Cities.name]}")
}
You can see that in each of these use cases, we only refer to the property of the singleton Users and thus the actual table field name is only defined once in the configuration of the table itself.
But the JDBC feature also allows to create more complex queries, for example
(Users innerJoin Cities).slice(Users.name, Cities.name).select { Users.name.eq("Emma") }.forEach {
println("${it[Users.name]} works in ${it[Cities.name]}")
}
This joins the 2 tables, based on the information within the definition (look at .references(Cities.id) earlier on), selects the 2 name columns, adds a where clause on the user name, and prints out the information in this case.
So this JDBC feature of the Exposed framework solves a few important issues of working with the JDBC API, namely the mapping and the boilerplate code that is required when using the JDBC API purely.
Note however that the mapping of the result sets is done using a Map-like structure where the key represents the column name and the value the database field value. People who are used to working with ORM frameworks will find this a bit awkward but Kotlin gives you a highly readable syntax for it. Most of all, you as the developer are still in control of the queries that are generated and thus performance issues are less likely.
The DAO or ORM solution.
The DAO features of the Exposed Framework take a slightly different approach to the same problem space by making use of ‘Entities’. So you need to define your table mappings and how you run queries differently. This means that both features are not compatible, or at least, combining both approaches did not work quite well for me. They can be used in the same application, but the definition of the table is slightly different and an Entity object is also needed.
object Users : IntIdTable() {
val name = varchar("name", length = 50) // Column<String>
val city = reference("city", Cities)
}
class User(id: EntityID<Int>) : IntEntity(id) {
companion object : IntEntityClass<User>(Users)
var name by Users.name
var city by City referencedOn Users.city
}
Here Users contains the definition of the table and assumes an autoincrement Primary Key for the field id. The User class defines an instance of a record where we even have an object for City and not just the foreign key value.
An insert statement can be written as
val amsterdam = City.new {
name = "Amsterdam"
}
which results in an INSERT SQL statement at the moment we access the database again or at the end of the transaction. So it is not flushed to the database immediately.
Retrieving data is also done differently than in the JDBS feature case. An important fact that you need to know is that joins are executed lazily which might not be the optimal case for some of your queries. For example, when retrieving the record for ‘Emma’ and the city she works in, the retrieval of the City name is done separately and after the retrieval of the User record itself.
User.find { Users.name.eq("Emma") }.forEach {
println("${it.name} works in ${it.city.name}")
}
As you see, here again, the inheritance brings some useful methods into the objects like the find method.
Of course, an eager-loading way of working is possible by explicitly defining the joins that need to be made. This again is a positive point for this framework concerning the eager and lazy loading issues you can have with the classic ORM tools like Hibernate. The next snippet shows the same query but now with joining the tables eagerly.
// Eager loading
val query = Users.innerJoin(Cities).select(
Users.name.eq("Emma")
)
User.wrapRows(query).forEach {
println("${it.name} works in ${it.city.name}")
}
Code examples
You can find the complete example project at this location of my Kotlin Project on GitHub.
If you want to learn more abput the Exposed framework itself, this is the main page of the Wiki for the project.
Conclusion
The Exposed framework is solving the main issues of working with the JDBC API in a completely different way the other frameworks like Hibernate. The lightweight approach to avoid mapping and typesafe access is not unique, but due to the expressiveness of the Kotlin language, results in a DSL-like solution that is highly readable and understandable.
Because the JDBC feature of the framework, tries to stay as close as possible to the JDBC API itself, it makes use of a Map-like structure for the transferred data. If you like real objects for records or result set items, you can make use of the DAO functionality of the framework. This resembles the ORM frameworks but is much easier to use and more performant in the execution.
Training and Support
Interested in a training about efficient ORM tools or Hibernate usage in your project? In need of an expert to help you solve a problem, feel free to contact me.
Do you need a specific training session on Jakarta EE, Quarkus, Kotlin or MicroProfile? Have a look at the training support that I provide on the page https://www.atbash.be/training/ and contact me for more information.