Recently I decided to write my own habit tracker. In my project for access to the database, I opted for exposed. It’s a lightweight SQL library, which I find pretty amazing and easy to use. But at some point, I faced the need to write a bit more complex query, mainly to calculate habit streakson the database side. In that case exposed FAQ has a native SQL solution, which you can implement on top of the library. It lets you execute queries and map its result. This improvement allows you to write your queries in such a manner:
What can be a real problem here is an SQL injection since the code executes the whole string as SQL statement. It doesn’t allow you to separate parameters to use them in the context of parameters, not as a SQL command. That’s why I decided to go with an old good PreparedStatement. In this tutorial, I will try to improve its use with Kotlin type system. All code is available over on GitHub, with one branch per chapter so that you can follow along.
Basic work with
Prepared Statement- master branch here
- Adding user table - userTable branch here
Adding map for
ResultSet?- mapResult branch here
- Extract values with reflection - reflect-getValue branch here
- Extract entities with reflection - reflect-dataClass branch here
- Extract values using types - types-getValue branch here
- Query parameters - types-getValue branch here
1. Basic work with Prepared Statement — master branch
Let me quickly introduce the base project. In the gradle file we have such libraries as
exposed for database connection and transactions. For testing purposes, we added dependencies:
AppConfig.kt loads database configs from
TransactionManager gets database connection and provides us with the
currentTransaction. Its method
tx allows us to run queries within a transaction.
The main focus of this tutorial is
QueriesKtTest. There is the first version of native SQL executor:
Nothing fancy here. It takes
preparedStatement from our string and executes it.
Test for this method:
As you can see we have to work directly with the
next() to obtain record and parse string value of the column with
getString() method. Let’s add a new table and see how it adds complexity to the code.
2. Adding user table — userTable branch
Let’s quickly introduce a new table in terms of exposed library. For this purpose we’ll add
Users represents table with two columns:
name: Varchar. Test in this branch is more complicated. Instead of
tx method we will use a new one:
It creates schema with our table and rollback transaction afterwards.
The test now inserts 2 user rows into the table and reads them one after another. Let’s create
ResultSet to make parsing easier.
3. Adding map for
ResultSet? — mapResult branch
This sections contains just a few tweaks. Our test lost few lines of code:
Now it directly calls
resultSet.next() now can be found in the map method. It takes transform
ResultSet as a function. In our case, it’s the same
getString with the name of the column. Basically,
getValue maps through the result set and takes its string values.
Of course, not all our query result values are strings. Let’s use some reflection to expand the capabilities of our method.
4. Extract values with reflection — reflect-getValue branch
As soon as we don’t want to create tons of method for each type that we want to extract from the result set, we’ll use reflection.
Here we compare class we want to extract with some known classes. We call
::class to obtain Kotlin
classKClass<T> value of them. In the case of a match, we call the corresponding
Note that it also takes
kClass parameter. But we don’t want to pass directly the class value. We’ll leave this part of work to Kotlin and use its magic of
inline fun and
reified type parameteres.
With reified we can access the type
T and get class of it, the compiler knows the actual type used as a typed argument.
Now we call
getValue<Int>(“id”). In our case we don’t even need to change anything in our test:
The compiler has enough information about type
T from the definition of the
Amazing! Let’s add some more reflection to be able to convert
ResultSet to data class.
5. Extract entities with reflection — reflect-dataClass branch
Let’s create data class for our
Users table in
How can we create a class with reflection? We can obtain information about the class constructor and its parameters.
With the type and name of each parameter we extract values from the result set. And to create a class instance we call the constructor with all these values as vararg arguments. One more cool thing is how we determine the
KClass values of the parameters. We use reflectionjvmErasure:
Returns the KClass instance representing the runtime class to which this type is erased to on JVM.
Here are another examples of
reified usage to convert the result set to
Note that function can be
inline only if it calls public functions. In this case, we can easily call one
inline function from another one. Here’s the updated test:
6. Extract values using types types — getValue branch
Reflection is amazing, but all its magic is in runtime. While we would rather rely on type system. Let’s try rewrite
First, we need to create classes that will represent our columns with types and names:
extract method in terms of these new types:
While we use
sealed classes here we don’t need to use else branch, because compiler can prove that all possible cases are covered.
Here with usual
reified you can use
infix notation. All these let us write code such as:
BooleanColumn(“name”) from result
Let’s create a new test and use all our result conversion functions:
These time we use
row_number() function in the query. It returns an integer value, the column name is
rn. To parse the result we can use:
7. Query parameters — queryParameters branch
We have a couple of options to extract values from the query result. But we still have no way to provide arguments to
prepared statement. Let’s now focus on that.
As in the last chapter let’s add some more types:
And match through them:
As soon as we can express our parameters, we need to update
exec function to pass these values to the
For each parameter we call its prepared statement set version and also pass its index, so don’t forget to keep the order of variables.
Also we added a method to map through the result set with our previous chapter’s from function:
Let’s check our improvements with a new test. Let’s say we want to get all records with names that are longer than some value and contain
In this case, we’ll have only one name as a result.
From now on, we are prepared for huge queries that we couldn’t express with exposed:
- SQL injection safe
- the result can be easily converted to our types
As a final result, we have two conversion versions. One uses the reflection. You are required only to specify your type, it does all conversion by itself, but not type-safe. The second uses specified columns, it is type-safe, but all conversion descriptions are on you.
Please, let me know which option you would prefer to use!