Exposed DAO

You have already learned how to use the Exposed framework simply by using DSL. It's time to learn how Exposed works with the Data Access Object (DAO) pattern. Simply put, the DAO is an abstract interface for manipulating entities in a database.

By mapping application calls to the persistence layer, the DAO provides some specific data operations without exposing details of the database. This isolation supports the single responsibility principle.

Defining tables

Let's say we want to create an application for storing notes. For this application, we only need one table to store note data:

We define a table that will store note data. Recall that we can use IntIdTable when working with entities with an auto-incrementing integer primary key:

object Notes : IntIdTable() {
    val title: Column<String> = varchar("title", 50)
    val content: Column<String> = text("content")
}

Data Access Object

An entity instance (or a row in the table) is defined as a class instance. We usually call this class Data Access Object. The class should match the ID type of the table. Since we used IntIdTable, we extend IntEntity:

import org.jetbrains.exposed.v1.core.dao.id.EntityID
import org.jetbrains.exposed.v1.dao.IntEntity
import org.jetbrains.exposed.v1.dao.IntEntityClass

class Note(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<Note>(Notes)

    var title by Notes.title
    var content by Notes.content
}

Note that we need to create a companion object in which we specify the table used. We map the class properties to the table columns using delegates (by).

If you want to use a primary key other than an integer type, Exposed supports various IdTable subclasses covering types like Long or UInt. For example, to use a UUID, you can simply use the specific UUIDTable class:

import org.jetbrains.exposed.v1.core.dao.id.UUIDTable

object Notes : UUIDTable() {
    val title: Column<String> = varchar("title", 50)
    val content: Column<String> = text("content")
}

Do not confuse the primary key with fields that store unique values. To determine such a field, simply call the method uniqueIndex after the field definition, where we can pass an optional argument to set the index name:

object Notes : UUIDTable() {
    val title: Column<String> = varchar("title", 50).uniqueIndex("unique_title")
    val content: Column<String> = text("content")
}

We also need to change the DAO according to the new ID using UUIDEntity:

import org.jetbrains.exposed.v1.dao.UUIDEntity
import org.jetbrains.exposed.v1.dao.UUIDEntityClass
import java.util.UUID

class Note(id: EntityID<UUID>) : UUIDEntity(id) {
    companion object : UUIDEntityClass<Note>(Notes)

    var title by Notes.title
    var content by Notes.content
}

Comparison in practice

Let's compare the execution of standard operations as if we were doing them directly or using the DAO:

1. Creating an entity

   Directly	Using the DAO
   val insertStatement = Notes.insert { it[title] = "My first note" it[content] = "Lorem ipsum dolor sit amet." }	val note = Note.new { title = "My first note" content = "Lorem ipsum dolor sit amet." }

   We should note that the result of executing the Notes.insert method will not be a created entity, but an insertStatement. In the case of the DAO, we immediately get the desired entity as a result, with which we can work conveniently.

2. List of entities

   Directly	Using the DAO
   val notes = Notes .selectAll() .map { it[Notes.title] to it[Notes.content] }	val notes = Note .all() .map { it.title to it.content }

   Notes.selectAll returns Query, whereas Note.all() returns SizedIterable<Note> – just like a list of notes.

3. Find entity by ID

   Directly	Using the DAO
   val note = Notes .selectAll() .where { Notes.id eq 1 } .firstOrNull()	val note = Note.findById(1)

   As you can see, there is no confusion in this case.

4. Find entity by other fields

   Directly	Using the DAO
   val note = Notes .selectAll() .where { Notes.title eq "Exposed" } .firstOrNull()	val note = Note .find { Notes.title eq "Exposed" } .firstOrNull()

5. Updating an entity

   Directly	Using the DAO
   Notes.update({ Notes.id eq 1 }) { it[content] = "Eget libero sed aliquet." }	// previously created note: Note note.content = "Eget libero sed aliquet."

   When working with the DAO, you just need to set a new field value for an entity that has already been created or found. Working directly, you always need to write a condition for searching for an entity.

   If your entities are meant to be read-only, you can use ImmutableEntityClass in the companion object and define properties with val instead of var.

   You can also search for an entity by ID and update it in a single transaction when working with DAO using:

   Note.findByIdAndUpdate(1) { note ->
       note.content = "Eget libero sed aliquet."
   }
   

6. Deleting an entity

   Directly	Using the DAO
   Notes.deleteWhere { Notes.id eq 1 }	// previously created note: Note note.delete()

   Similar to the update: when you work with the DAO, you can delete an entity that has already been created or found. Working directly, you always need to write a condition for deletion.

Conclusion

As you can see, using DAO is much more convenient than working with tables directly. This way, we can avoid writing the same code every time. Moreover, the code becomes more clear, so there will be fewer bugs when writing it.