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
- Conclusion
1. Basic work with Prepared Statement — master branch
Let me quickly introduce the base project. In the gradle file we have such libraries as h2database, config4k and exposed for database connection and transactions. For testing purposes, we added dependencies: JUnit and assertk.
File AppConfig.kt loads database configs from application.conf. 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 Queries and QueriesKtTest. There is the first version of native SQL executor:
Nothing fancy here. It takes currentTransaction, creates preparedStatement from our string and executes it.
Test for this method:
As you can see we have to work directly with the ResultSet, call 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 Tables.kt:
Object Users represents table with two columns: id: Int, 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 map for 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 getValue.
All 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 ResultSet method.
The toValue function:
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 as .getValue<String>(“name”) or 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 resultSet variable.
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 QueiesKtTest.
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 data class:
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:
We changed getValue(“name”) to toEntities().
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 getValue.
First, we need to create classes that will represent our columns with types and names:
Let’s repeat 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 inline and 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 prepared statement:
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.
Conclusion
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!
Top comments (0)