Estimate project
← All posts

Hibernate or Exposed: Picking the Best Persistence Tool for Kotlin – Round 2

The second article of the series puts Hibernate and Exposed side by side when it comes to storing and retrieving data and handling transactions.

15 min read
KotlinKtorKotlinServerSidePersistence

It’s time for round two of the Hibernate vs Exposed showdown! You already know how Hibernate and Exposed differ in their design. You’ve seen how to configure both frameworks in different environments. You can map database tables to the matching abstractions in your application code. You can also have the schema generated automatically from your classes.

Still, none of this gives your application any actual business functionality yet. Real business value comes from being able to query your data, save it, modify it, and define transactions.

And no worries – that’s precisely what this article is about.

Data Persistence and Retrieval

First, let’s look at the different approaches to DML (data manipulation language) and querying that each framework offers.

Hibernate Criteria API

One way Hibernate lets you query data is through the Criteria API. Criteria queries are, in essence, a type-safe DSL layered on top of Hibernate entities that lets you write SQL queries in code. Well, not quite SQL queries – since, as noted before, Hibernate hides low-level SQL behind entities. So with the Criteria API, you’re still working with entities and using the vocabulary of entities rather than talking to database tables directly.

Building a Criteria query usually begins with a CriteriaBuilder, after which you specify the elements and parameters of the query, going further down the Criteria API’s object graph.

Here’s an example of using the Criteria API with our well-known BoardGame Hibernate entity:

val dune = BoardGame("Dune: Imperium", 1, 4, 120)
val everdell = BoardGame("Everdell", 1, 4, 100)

// first we need to save some entities to database
session.persist(dune)
session.persist(everdell)

// actual Criteria API usage starts here
val criteriaBuilder = session.criteriaBuilder
val criteria = criteriaBuilder.createQuery(BoardGame::class.java)
val root = criteria.from(BoardGame::class.java)
criteria.select(root)
criteria.where(criteriaBuilder.greaterThan(root.get("playTimeInMinutes"), 60))
criteria.orderBy(criteriaBuilder.desc(root.get<String>("title")))

val resultList = session.createQuery(criteria).resultList

The Criteria query above just picks every board game whose play time exceeds 60 minutes and orders the results by title, descending. That said, “just” may not be quite the right word. As you may have spotted, building even this fairly simple query required creating criteriaBuilder, criteria, and root objects. What’s more, the where and orderBy clauses needed rather verbose expressions – with field names passed in as plain strings.

This is only my own view, and you’re entirely free to see it differently, but to me the Criteria API is rather hard and clunky to work with. If you’d like to dig deeper, though, have a look at the Criteria chapter of the Hibernate documentation.

Hibernate Query Language – HQL

Hibernate also gives you another way to query the database. If you’re ready to give up type-safety in exchange for expressiveness and simplicity, Hibernate Query Language (HQL) may be worth a look.

HQL is an object-oriented query language that closely resembles SQL – except that, rather than querying the database tables underneath, you query your entities.

As for kinds of HQL queries, there are two main approaches:

  • Inline queries – given as strings to the createQuery method at runtime. Any mistakes in the query only surface when it runs.
  • Named queries – defined up front with XML or annotations, then referred to by name in the createQuery method. Hibernate parses these queries when the application starts, so some problems can be detected much sooner.

This section shows how to write simple HQL queries and run them.

We’ll begin by retrieving every board game with a play time above 60 minutes, ordered by title descending – this time with HQL:

val resultList = session.createQuery(
   "FROM BoardGame WHERE playTimeInMinutes > :playtime ORDER BY title DESC",
   BoardGame::class.java
)
.setParameter("playtime", 60)
.resultList

That was an inline query. Next, let’s declare the same query as a named query. To start, we define the named query on the BoardGame entity:

@NamedQuery(
   name = "find_boardgames_with_playtime_at_least",
   query = "FROM BoardGame WHERE playTimeInMinutes > :playtime ORDER BY title DESC"
)
@Entity
@Table(name = "board_games")
open class BoardGame(
...

After that, we can query the database with it:

val resultList = session.createNamedQuery(
   "find_boardgames_with_playtime_at_least",
   BoardGame::class.java
)
.setParameter("playtime", 60)
.resultList

This only scratches the surface of HQL. The language is quite powerful, so if you want to learn it more thoroughly, the HQL chapter of the Hibernate documentation is an excellent starting point.

Spring Data JPA repositories

Although strictly speaking it falls outside the scope of this article, I’d like to quickly mention one more way of querying the database with Hibernate – one available only in Spring.

Spring Data JPA repositories are not a distinct querying mechanism in Hibernate, but an abstraction layer on top of the existing JPA query APIs, integrated with Spring.

It isn’t a native Hibernate feature, but I didn’t think I could leave it out of an article comparing Hibernate and Exposed – particularly since Spring Data JPA repositories are so popular with developers, and since you get them with Hibernate, but not with Exposed.

To find out more, take a look at the Spring documentation about JPA repositories.

Exposed DSL

As noted before, the central abstraction Exposed uses for working with database tables is the Table object. Tables in Exposed, however, serve not only to define the schema – they are also how you query and modify data.

Given our BoardGamesTable from the previous article, this is how you could run different operations on it, covering CRUD (create, read, update, delete) as well as more complex queries:

// insert
val boardGameId = BoardGamesTable.insert {
   it[title] = "Dune: Imperium"
   it[minPlayers] = 1
   it[maxPlayers] = 4
   it[playTimeInMinutes] = 120
} get BoardGamesTable.id

// add some ratings...

// select all
BoardGamesTable.selectAll().forEach {
   println("Board game: id = ${it[BoardGamesTable.id]}, title = ${it[BoardGamesTable.title]}, ...")
}

// select average rating for board game
val averageRatingAlias = RatingsTable.rating.avg().alias("average_rating")

RatingsTable.select(RatingsTable.boardGameId, averageRatingAlias)
   .groupBy(RatingsTable.boardGameId)
   .forEach {
       println("Board game id = ${it[RatingsTable.boardGameId]}, average rating = ${it[averageRatingAlias]}")
   }

// update
BoardGamesTable.update({ BoardGamesTable.id eq boardGameId }) {
   it[title] = "Dune: Imperium - Uprising"
}

// select single row
val singleBoardGame = BoardGamesTable.select(
   BoardGamesTable.title,
   BoardGamesTable.minPlayers,
   BoardGamesTable.maxPlayers
)
.where(BoardGamesTable.id eq boardGameId)
.single()

// delete
BoardGamesTable.deleteWhere { id eq boardGameId }

Remember that with the Exposed Table DSL, the properties map only to the real database columns of that particular table.

This means that to retrieve ratings for a board game, we’d have to query the RatingsTable explicitly. Another option is to join the tables so that each ResultRow from RatingsTable is enriched with some related board game details:

RatingsTable
   .innerJoin(BoardGamesTable)
   .select(BoardGamesTable.title, RatingsTable.rating)
   .where(RatingsTable.boardGameId eq boardGameId)
   .forEach {
       println("Rating for board game ${it[BoardGamesTable.title]}: ${it[RatingsTable.rating]}")
   }

Exposed DAO

You probably recall the BoardGame entity from the Mapping & Schema Definition – Exposed DAO section of the previous article. In that section, I pointed out the companion object of type LongEntityClass declared within the entity. This companion object is what gives our entity class its various database manipulation and querying abilities.

Below are examples of different operations on BoardGame entities – mirroring the BoardGamesTable operations from the previous section:

// insert
val boardGame = BoardGame.new {
   title = "Dune: Imperium"
   minPlayers = 1
   maxPlayers = 4
   playTimeInMinutes = 120
}

// add some ratings...

// select all
val allBoardGames = BoardGame.all().toList()

// select average rating for board game
BoardGame.all().with(BoardGame::ratings) //'with' function to eagerly load the ratings
   .map { boardGame -> Pair(
       boardGame,
       boardGame.ratings.map { it.rating }.average()
   )}
   .forEach {
       println("Board game '${it.first.title}', average rating = ${it.second}")
   }

// update
BoardGame.findByIdAndUpdate(
   boardGame.id.value,
   { it.title = "Dune: Imperium - Uprising" }
)

// or
boardGame.title = "Dune: Imperium - Uprising"

// select single row
BoardGame.findById(boardGame.id)

// delete
boardGame.delete()

Some remarks about the snippet above:

  • You might have noticed that even though the DAO works with objects instead of raw table rows, its API closely resembles the table DSL. Because of that, DSL users thinking about moving to DAO may find it familiar.
  • Computing the average rating for each board game no longer requires any joins, because the BoardGame entity has a ratings property that loads the related ratings. The drawback is that we can’t calculate the average in the database with a custom query anymore – DAO works on entity objects and their properties. The average has to be calculated in code once the data has been fetched.
  • Even so, such queries can still be executed with the DSL – choosing DAO in one place doesn’t stop you from using the DSL in other parts of your application.
  • You may also have seen the with(BoardGame::ratings) call applied to the result of the BoardGame.all() query. It loads the ratings of every BoardGame entity eagerly, because we know up front that we’ll need them.
  • Entities can be updated in two ways. You can call the BoardGame.findByIdAndUpdate() function, or you can assign a new value straight to the entity’s property. The first option fits cases where you know the ID of the entity to update but haven’t loaded it from the database. The second fits better when the entity object is already loaded and you can modify it directly.

There’s one more point worth stressing about Exposed DAO entities. Hibernate entities are meant to be plain Java (or Kotlin) objects that are as independent of the framework as possible, but Exposed DAO entities are not like that.

They are closely tied to the framework:

  • They have to extend particular base classes.
  • They have to point to a matching Exposed Table.
  • Every property they have is delegated to a column defined in the Table.
  • Lastly, they can’t work without a configured database connection.

This has significant consequences. Using Exposed DAO entities as your domain model – that is, in your business logic layer – can make unit tests hard to write. The reason is that each test would need a (real or in-memory) database to be started and a database connection to be set up.

That’s why the usual recommendation is to avoid using Exposed DAO entities as domain models and to keep them confined to the persistence layer instead.

Transaction Handling

When you build business processes and workflows into your applications, you frequently need some parts of the logic to run atomically – either completely or not at all. Such a unit of work is called a transaction. Luckily for developers, relational databases have their own notion of transactions, which roughly matches what we expect from business logic. That could be one reason why our code tends to depend so much on database transaction mechanisms.

Therefore, when you pick a particular persistence framework – an abstraction layer on top of your database – you probably expect it to let you manage and control those transactions. Luckily, both frameworks covered here do support this, although they go about it in somewhat different ways.

Let’s look at what each of them provides.

Hibernate Transaction API

Hibernate offers two distinct mechanisms for transaction management:

  • JDBC – manages transactions directly through the underlying java.sql.Connection
  • JTA – manages transactions through JTA (Java Transaction API)

In non-JPA applications (for example plain JVM apps), the default transaction coordination mode is JDBC.

To change this configuration, you can put the following lines into your hibernate.properties file:

hibernate.transaction.coordinator_class=jta
hibernate.transaction.jta.platform=<SOME_JTA_PLATFORM_IMPL>

Keep in mind that you’ll have to provide a custom JTA platform for the application to work, because the default JTAPlatform implementation in Hibernate is… NoJtaPlatform. As a result, you may see errors such as: “Could not locate TransactionManager to suspend any current transaction.”

Whichever way you configure the transaction coordinator, Hibernate’s standard, unified transaction management API is available to you.

Given a SessionFactory, this is how you execute code inside a transaction:

val session = sessionFactory.openSession()

try {
   session.transaction.begin()

   // perform some DB operations

   session.transaction.commit()
} catch (e: Exception) {
   if (session.transaction.status == ACTIVE || session.transaction.status == MARKED_ROLLBACK) {
       session.transaction.rollback()
   }

   // further handle the exception
} finally {
   session.close()
}

There’s also a shorter, declarative syntax, at the price of a bit less control:

sessionFactory.inTransaction { session ->
   // perform some DB operations
}

On top of basic control over the transaction lifecycle (begin, commit, rollback), Hibernate lets you configure transactions further to fit your needs. Let’s go through some of the options used most often.

First up: transaction isolation levels. If the concept is new to you, the PostgreSQL documentation gives a good overview.

The cleanest and most official way to change the transaction isolation level is to set it in hibernate.properties:

hibernate.connection.isolation=REPEATABLE_READ

For the accepted values and formats, see the official Hibernate documentation about configuring the connection isolation level.

This setting is static – it affects every session and transaction across your application. You can still alter it in code on the Connection instance with Connection.setTransactionIsolation(). Be aware, though, that the change will apply to every session (and transaction) served by that Connection instance.

Switching the isolation level dynamically at runtime for each session can be done in theory, but it isn’t officially documented, and I find it unreliable and a bit of a hack. For that reason, I won’t show how to do it. If you’re curious anyway, check out the Session.doWork() method, which exposes the underlying Connection directly, and look for community examples online.

Hibernate also lets you control whether a transaction is read-only. This is set per session. Entities in a session can be modified by default. However, when the session is made read-only, Hibernate turns off dirty checking for that session. In other words, you can still change entity properties in memory, but the changes won’t be written to the database.

With a session in hand, you can make it read-only as follows:

session.isDefaultReadOnly = true

Note that Hibernate manages this read-only setting entirely on its own. It neither interacts with nor relies on any read-only features the underlying database may have.

Exposed transaction block

In Exposed, every CRUD operation has to run inside a transaction. Exposed’s key construct for handling transactions is the transaction block.

Executing database operations in a transaction is this easy:

transaction {
   // some DSL or DAO operations go here
}

The transaction block can return values too, so if you need to keep working with data produced by the transaction, you can write something like this:

val boardGame = transaction {
   BoardGame.findById(boardGameId)
}

doSomethingOutsideOfTransaction(boardGame)

The transaction block uses the default database unless told otherwise – meaning the one most recently connected through Database.connect(), or the one assigned to TransactionManager.defaultDatabase. That’s entirely enough when your application works with only one database. If it doesn’t, you can state explicitly which database a particular transaction should use:

val db = Database.connect("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1", driver = "org.h2.Driver")

transaction(db) {
   // DB operations here
}

Apart from choosing the database, the Exposed transaction block provides a number of configuration options, among them transaction isolation, read-only mode, max attempts, and query timeout.

Here’s a transaction block configured with every one of those options:

transaction(
   transactionIsolation = Connection.TRANSACTION_REPEATABLE_READ, // values from java.sql.Connection
   readOnly = true // false by default
) {
   maxAttempts = 5 // number of retries on SQLException
   queryTimeout = 10 // in seconds

   // DSL or DAO operations here
}

Keep in mind that, apart from maxAttempts, Exposed doesn’t handle any of these parameters itself. They are handed over to the underlying JDBC driver and depend wholly on its support. So if a given JDBC driver lacks support for some of these features, they just won’t have any effect.

Exposed also offers more advanced transaction handling features that I didn’t cover here – for example nested transactions, savepoints, and Kotlin coroutines support. I strongly recommend reading about them in the Transactions chapter of the Exposed documentation.

Spring @Transactional annotation

So you’ve gone through the entire Transaction Handling section of this article and you’re still asking yourself: “What happened to the @Transactional annotation I use every day?”

Well, it comes from the Spring framework. There are two reasons I didn’t bring it up in the earlier subsections.

First, even though people often link the @Transactional annotation with Hibernate, it isn’t really a Hibernate feature. In fact, you could have one of two @Transactional annotations in mind:

  • jakarta.transaction.Transactional from the JTA standard,
  • org.springframework.transaction.annotation.Transactional, Spring’s own implementation of the former.

And this blog post focuses neither on Spring nor on JTA.

Second, since both Hibernate and Exposed can be integrated with Spring, you can apply the @Transactional annotation with either of them in a Spring-based project. It works smoothly with both transaction handling mechanisms.

To make this tangible, let’s see how the @Transactional annotation behaves in practice with each framework.

To begin with, a Spring service calling a JPA repository backed by Hibernate:

@Service
open class BoardGamesService(
   private val boardGamesRepository: BoardGamesRepository
) {
   @Transactional
   open fun rateBoardGame(title: String, rating: Int) {
       val boardGame = boardGamesRepository.findByTitle(title) ?: throw EntityNotFoundException()
       val rating = Rating(rating, Instant.now(), boardGame)
       boardGame.ratings.add(rating)
   }
}

Next, a comparable snippet for a Spring service that relies on Exposed DAOs for persistence:

@Service
open class BoardGamesService {
   @Transactional
   open fun rateBoardGame(title: String, rating: Int) {
       val boardGame = BoardGame.find { BoardGamesTable.title eq title }.firstOrNull()
           ?: throw EntityNotFoundException()

       Rating.new {
           this.rating = rating
           this.boardGame = boardGame
       }
   }
}

As both examples show, all it took to make the code run in a transaction was annotating the function with @Transactional. No explicit call to a Hibernate session or an Exposed transaction block is required.

The Spring @Transactional annotation comes with a fair number of configuration options as well. See the official Spring documentation about transaction management for details.

Summary

That wraps things up for today. Here’s a short recap of the key takeaways from this article.

Querying

Hibernate provides powerful and flexible ways to query (Criteria API, HQL, Spring Data JPA), but they come with verbosity and occasional fragility (for example, string-based HQL).

The Exposed DSL is more concise, type-safe, and expressive – particularly if SQL semantics feel natural to you.

Transaction Handling

Both frameworks support transactions well, but they follow different philosophies.

Hibernate sticks to the traditional session/transaction pattern (with Spring’s @Transactional making things smoother).

The transaction block in Exposed is straightforward, fits Kotlin nicely, and allows fine-grained configuration right away. The Spring @Transactional annotation works with it too.