Fetching new Entities
Learn how to fetch data from the database using the exposed library.
We'll cover the following...
Following the REST practices, the URL for fetching all cats should be /cats, while for fetching a single cat, it should be /cats/123, where 123 is the ID of the cat we are trying to fetch all the rows from the table for. Then, we map each row to our data class.
Adding new routes to the App.kt file:
Let’s add two new routes for that in the App.kt:
get("/cats") {
...
}
get("/cats/{id}") {
...
}
The first route is very similar to the /status route we introduced earlier in this chapter. But the second round is slightly different: it uses a query parameter in the URL. You can recognize query parameters by the curly brackets around their name.
To read a query parameter, we can access the parameters map:
val id = requireNotNull(call.parameters["id"]).toInt()
If there is an ID on the URL, we need to try and fetch a cat from the database:
val cat = transaction {
CatsTable.select {
CatsTable.id.eq(id)
}.firstOrNull()
}
Here, we open a transaction and use the select statement to get a cat with an ID equal to what we were provided previously.
If an object was returned, we would convert it into JSON. Otherwise, we would return an HTTP code of 404 Not Found:
if (row == null) {
call.respond(HttpStatusCode.NotFound)
} else {
call.respond(
Cat(
row[CatsTable.id].value,
row[CatsTable.name],
row[CatsTable.age]
)
)
}