Deep Dive into DAO
Overview
The DAO (Data Access Object) API of Exposed, is similar to ORM frameworks like Hibernate with a Kotlin-specific API.
A DB table is represented by an object
inherited from org.jetbrains.exposed.sql.Table
like this:
Tables that contain an Int
id with the name id
can be declared like this:
Note that these Column types will be defined automatically, so you can also just leave them out. This would produce the same result as the example above:
An entity instance or a row in the table is defined as a class instance:
Basic CRUD operations
Create
Read
To get entities use one of the following
For a list of available predicates, see DSL Where expression.
Read a value from a property similar to any property in a Kotlin class:
Sort (Order-by)
Ascending order:
Descending order:
Update
Update the value of a property similar to any property in a Kotlin class:
Note: Exposed doesn't make an immediate update when you set a new value for Entity, it just stores it on the inner map. "Flushing" values to the database occurs at the end of the transaction, or before the next
select *
from the database.
Search for an entity by its id and apply an update:
Search for a single entity by a query and apply an update:
Delete
Referencing
many-to-one reference
Let's say you have this table:
And now you want to add a table referencing this table (and other tables!):
Now you can get the film for a UserRating
object, filmRating
, in the same way you would get any other field:
Now if you wanted to get all the ratings for a film, you could do that by using the filmRating.find
function, but it is much easier to just add a referrersOn
field to the StarWarsFilm
class:
You can then access this field on a StarWarsFilm
object, movie
:
Now imagine a scenario where a user only ever rates a single film. If you want to get the single rating for that user, you can add a backReferencedOn
field to the User
class to access the UserRating
table data:
You can then access this field on a User
object, user1
:
Optional reference
You can also add an optional reference:
Now secondUser
will be a nullable field, and optionalReferrersOn
should be used instead of referrersOn
to get all the ratings for a secondUser
.
many-to-many reference
In some cases, a many-to-many reference may be required. Let's assume you want to add a reference to the following Actors table to the StarWarsFilm class:
Create an additional intermediate table to store the references:
Add a reference to StarWarsFilm
:
Note: You can set up IDs manually inside a transaction like this:
Parent-Child reference
Parent-child reference is very similar to many-to-many version, but an intermediate table contains both references to the same table. Let's assume you want to build a hierarchical entity which could have parents and children. Our tables and an entity mapping will look like
As you can see NodeToNodes
columns target only NodeTable
and another version of via
function were used. Now you can create a hierarchy of nodes.
Eager Loading
Available since 0.13.1. References in Exposed are lazily loaded, meaning queries to fetch the data for the reference are made at the moment the reference is first utilised. For scenarios wherefore you know you will require references ahead of time, Exposed can eager load them at the time of the parent query, this is prevents the classic "N+1" problem as references can be aggregated and loaded in a single query. To eager load a reference you can call the "load" function and pass the DAO's reference as a KProperty:
This works for references of references also, for example if Actors had a rating reference you could:
Similarly, you can eagerly load references on Collections of DAO's such as Lists and SizedIterables, for collections you can use the with function in the same fashion as before, passing the DAO's references as KProperty's.
NOTE: References that are eagerly loaded are stored inside the transaction cache; this means that they are not available in other transactions and thus must be loaded and referenced inside the same transaction. As of 0.35.1, enabling keepLoadedReferencesOutOfTransaction
in DatabaseConfig
will allow getting referenced values outside the transaction block.
Eager loading for Text Fields
Some database drivers do not load text content immediately (for performance and memory reasons) which means that you can obtain the column value only within the open transaction.
If you desire to make content available outside the transaction, you can use the eagerLoading param when defining the DB Table.
Advanced CRUD operations
Read entity with a join to another table
Let's imagine that you want to find all users who rated second SW film with more than 5. First of all, we should write that query using Exposed DSL.
After that all we have to do is to "wrap" a result with User entity:
Auto-fill created and updated columns on entity change
See example by @PaulMuriithi here.
Use queries as expressions
Imagine that you want to sort cities by how many users each city has. In order to do so, you can write a sub-query which counts users in each city and order by that number. Though in order to do so you'll have to convert Query
to Expression
. This can be done using wrapAsExpression
function:
Add computed fields to entity class
Imagine that you want to use a window function to rank films with each entity fetch. The companion object of the entity class can override any open function in EntityClass
, but to achieve this functionality only searchQuery()
needs to be overriden. The results of the function can then be accessed using a property of the entity class:
Entities mapping
Fields transformation
As databases could store only basic types like integers and strings it's not always conveniently to keep the same simplicity on DAO level. Sometimes you may want to make some transformations like parsing json from a varchar column or get some value from a cache based on value from a database. In that case the preferred way is to use column transformations. Assume that we want to define unsigned integer field on Entity, but Exposed doesn't have such column type yet.
transform
function accept two lambdas to convert values to and from an original column type. After that in your code you'll be able to put only UInt
instances into uint
field. It still possible to insert/update values with negative integers via DAO, but your business code becomes much cleaner. Please keep in mind what such transformations will aqure on every access to a field what means that you should avoid heavy transformations here.