In this tutorial, we are going to learn how to modify our previous code and use JetBrains/Exposed in DAO manner.
In the previous post we created a simple ktor's app, added Exposed dependencies, and started with simple Person&Address relation CRUD. Now, based on the created implementation, we are going to refactor the code in order to fit DAO approach.
Setting up a test application
NOTE: Because we did all the preparation in the previous blog post, we will skip this part - if you need to set up the environment, please, go to Step 1
Persistence layer refactoring
First, a summary, then the details. We did:
- group
PersonTable&AddressTable, sinceAddressTableshouldn't be used without Person context - added
Entityfor both, - refactored the relations,
- removed
PersonRepositoryinterface and renamedPersonRepositoryImpltoPersonRepositoryin order to simplify the example, - refactored
PersonRepositoryin order to use Entities instead of Tables.
tables
PersonTable.kt
import org.jetbrains.exposed.dao.id.IntIdTable
object PersonTable : IntIdTable("person") {
val name = text("name")
val surname = text("surname")
val age = integer("age")
}
object AddressTable : IntIdTable("address") {
val personId = reference("person_id", PersonTable.id)
val street = text("street")
val house = text("house")
val apartment = text("apartment")
val city = text("city")
val postalCode = text("postal_code")
}
There is nothing much - we just moved AddressTable from a separate file to the one, common, with PersonTable.
Entities
PersonEntity.kt
import org.jetbrains.exposed.dao.IntEntity
import org.jetbrains.exposed.dao.IntEntityClass
import org.jetbrains.exposed.dao.id.EntityID
class PersonEntity(id: EntityID<Int>): IntEntity(id) {
companion object : IntEntityClass<PersonEntity>(PersonTable)
var name by PersonTable.name
var surname by PersonTable.surname
var age by PersonTable.age
val addresses by AddressEntity referrersOn AddressTable.personId
}
class AddressEntity(id: EntityID<Int>): IntEntity(id) {
companion object : IntEntityClass<AddressEntity>(AddressTable)
var street by AddressTable.street
var house by AddressTable.house
var apartment by AddressTable.apartment
var city by AddressTable.city
var postalCode by AddressTable.postalCode
var personId by AddressTable.personId
}
Entity is a new term since we want to follow the Exposed DAO approach. It states for a representation of a row in the table.\
additionally, as you can see, we added PersonEntity.addresses relation.
val addresses by AddressEntity referrersOn AddressTable.personId
..which is defined by "old" person_id column in address table.
PersonRepository refactoring
PersonRepository.kt
import org.jetbrains.exposed.dao.id.EntityID
import org.jetbrains.exposed.dao.with
import org.jetbrains.exposed.sql.SizedCollection
import org.jetbrains.exposed.sql.transactions.transaction
import pl.brightinventions.dto.*
class PersonRepository {
[...]
}
findAll
fun findAll(): List<FoundPersonWithAddressDto> = transaction {
PersonEntity
.all()
.with(PersonEntity::addresses)
.map {
FoundPersonWithAddressDto(
it.id.value,
it.name,
it.surname,
it.age,
it.addresses.map {
FoundPersonAddressDto(
it.street, it.house, it.apartment, it.city, it.postalCode
)
}
)
}
}
Based on PersonEntity, we have access to find, all, findById methods, and more. In this case, we are going to get all() of the records and map it, BUT!\
Since 0.13.1 version (so for a quite long time), we can define eager loading in order to prevent n+1 problem. By adding with(PersonEntity::addresses), our query execution scenario looks like:
10:51:51.958 [eventLoopGroupProxy-4-1] DEBUG Exposed - SELECT PERSON.ID, PERSON."NAME", PERSON.SURNAME, PERSON.AGE FROM PERSON
10:51:52.247 [eventLoopGroupProxy-4-1] DEBUG Exposed - SELECT ADDRESS.ID, ADDRESS.PERSON_ID, ADDRESS.STREET, ADDRESS.HOUSE, ADDRESS.APARTMENT, ADDRESS.CITY, ADDRESS.POSTAL_CODE FROM ADDRESS WHERE ADDRESS.PERSON_ID IN (1, 2, 3)
find
Because I wanted to show eager loading for findAll(), find method looks similar to the previous one:
fun find(id: PersonId): FoundPersonWithAddressDto? = transaction {
PersonEntity
.findById(id)
?.load(PersonEntity::addresses)
?.let {
FoundPersonWithAddressDto(
it.id.value,
it.name,
it.surname,
it.age,
it.addresses.map {
FoundPersonAddressDto(
it.street, it.house, it.apartment, it.city, it.postalCode
)
}
)
}
}
The only difference is that with is now load (for a single record).
Create, Update, Delete
fun create(person: CreatePersonDto): PersonId = transaction {
PersonEntity.new {
name = person.name
surname = person.surname
age = person.age
}.id.value
}
fun delete(id: PersonId): Unit = transaction {
PersonEntity.findById(id)?.delete()
}
fun update(id: PersonId, person: UpdatePersonDto): Unit = transaction {
PersonEntity.findById(id)?.let {
it.name = person.name
it.surname = person.surname
it.age = person.age
}
}
The thing that is worth mentioning is that update/delete needs to be done on the found entity - that's why we need to findById the record we want to remove/update.
addAddress
fun addAddress(personId: PersonId, address: CreateAddressDto) {
transaction {
PersonEntity.findById(personId)?.let {
SizedCollection(
it.addresses + address.let {
AddressEntity.new {
city = address.city
house = address.house
street = address.street
postalCode = address.postalCode
apartment = address.apartment
this.personId = EntityID(personId, PersonTable)
}
}
)
}
}
}
This one is a little tricky - in order to add a new address, we need to assign value to the personId explicitly.
Testing
GET http://localhost:8080/person \
This one should respond with a list of three persons: John, George, and Megan. Because we want to test our query execution scenario (so we added address information to the list as well), it will contain full-person data.
If we want to receive data about a particular person, we need to call\
GET http://localhost:8080/person/1,\
which will respond with full Person DTO.
Conclusion
In this article, we've learned how to refactor DSL to DAO approach in JetBrains/Exposed dependent project. In the next episode, I'm going to tackle some more advanced topics. Stay tuned!
Did you like the article? Maybe you have some other way for DAO implementation? Leave a comment below and stay in touch!
You can find the complete code over GitHub.
In the next part of this tutorial you'll learn how to implement JSON support in our JetBrains/Exposed app. Read it!
By Patryk Szlagowski, Senior Backend Developer @ Bright Inventions
Top comments (0)