Last month, with Compose Multiplatform going 1.0, I've been wanting to build a simple application for myself. It's barely more than a personal note taking app.
And for once, I wanted to build a native application instead of a webapp. When you need some kind of SQL database interaction in Kotlin, exposed seems to be pretty much the norm. However, I've already written a bit about why I don't quite like it personally (It has a lot to do with code duplication). And because I wanted to learn a bit more about the Java Persistence API while staying minimal and local, I've decided to go for some good old hibernate and a SQLite setup.
This blog exposes some of the things I've found out while implementing my project. Maybe they're useful for someone else too :). Kotlin, Hibernate and SQLite doesn't seem to be a very present combination online as of now.
You can find a sample project applying the tips I'm describing over here.
Some early context
Some issues and integration challenges between Kotlin and Hibernate are rather well documented. I won't spend time on those here, you can get past them directly by cloning the sample and running it.
In short, you probably have to use the
noarg and / or
allopen gradle plugins for various reasons described here better than I would.
90% of the magic fits in those couple lines to be added in your
I would also recommend avoiding using data classes, because the way
hashcode messes with hibernate.
Avoiding XML at all cost
For some reason, when searching for Hibernate results that don't use the whole Spring framework, search results and documentation pages are full of XML configuration.
It's 2022, I really want to avoid XML everywhere I can.
Turns out you can also create a
hibernate.properties file instead of the
hibernate.cfg.xml you'll see everywhere, use the same XML values and it will work out just fine. Here is my properties file :
It will be picked up automatically if placed in your
resources folder in case you instantiate a default
Last but not least, you can programatically insert your entities as part of your
Configuration to avoid putting them in the properties file.
val sessionFactory = Configuration() .addAnnotatedClass(Person::class.java) .addAnnotatedClass(Entry::class.java) .buildSessionFactory()
Generated Id and Null values
Let's start with something simple, but quite annoying. We have a simple `Entity`, with a required value and some
We want to create a person called
Bob, and save him in the database. Well, something that simple is actually quite annoying to do in Kotlin, because
id is not known yet, and yet we have to provide it. We can't just use a random
id to avoid override existing persons. We could use a nullable
Int? but then we still have to provide a null value every time, to create persons. For example
val bob = Person(null, "bob"). Quite ugly though...
There is a simple solution that solves most of out problems though. We can simply place the
Id at the end and forget about it. We can actually do the same for many more values that get set by the database during save, like timestamps.
That way, we can simple create people like this :
val bob = Person("Bob"), while getting a completely filled object when it's being grabbed from the database.
Granted, you still have to handle nullable values in your software but you know they'll be null prior to saving, and won't any more once fetched from the database.
Working with unique constraints in SQLite
with hibernate, it's pretty simple to define
unique constraints. In the case of our persons, we want only one person called Bob in our database. Surprisingly, the following
Entity definition will not generate a database model that will respect that unique constraint when using SQLite :
The piece of code above will save three different entries, each with an identical name, and different ids.
The reason for this is that to create to set that unique constraint, hibernate first creates the table, and THEN only alters it to add the constraint. Here is the resulting SQL, coming from the logs:
Hibernate: create table Person (id integer, name varchar(255) not null, primary key (id)) Hibernate: alter table Person add constraint UK_is4vd0f6kw9sw4dxyie5cy9fa unique (name)
Nothing wrong with this, except that SQLite does not support ALTER requests modifying constraints on tables!
I created a Stack Overflow question about it, which didn't meet much success :).
In the end, the easiest solution I found to cope with that issue is to use Flyway in my app and set the database schema myself. It does bring a bit more support but overall I am happy with the change because thanks to Flyway I have a standard way to handle database migrations in my app in the future.
You can look at the complete sample to see how Flyway is integrated in the app, but here is how the SQL looks like :
Essentially, we need to add the dependency and run Flyway early in our app before setting up Hibernate.
Nothing mind breaking :)
SQLite not setting TIMESTAMP values in the database
Right, with the two points above we'd expect to be sorted right? Not quite.
When adding the
created_at value, it turns out we created a new problem for ourselves. Here is how the first version of my SQL looked like :
Well, we have a little issue with this :
- I don't want NULL values there. Except that's what I set as default in my kotlin Entity above, remember?
- I still do want a NON NULL constraint for that timestamp. Creation time is important
- Even if I relax the NULL constraint, I can't benefit from the
DEFAULT CURRENT_TIMESTAMPpart of my SQL because my entry will always come with the value set to NULL.
So what I really want is something like "set the value to the current timestamp when saving, and make sure it's not NULL".
I found a way of doing exactly this :
Grant it, it looks a little convoluted, but it avoids me having to set the timestamp myself in software and instead rely on the database, which sounds like a better idea. Especially since I'm using Flyway anyways.
That's it! This is a list of random tips I found out when using this, I admit, uncommon setup those days. But hey, all in all it's harder to make it simpler in terms of dependencies than with SQLite and Hibernate.
Again, if you want to have a running setup with all of the small tips I listed here, you can go over to the sample.
Hope this helps someone somewhere someday!