Notes from experimenting with simpleflatmapper

When I was a C# developer, I adored the different Micro-ORMs that were available. Massive was one of the first ones I heard about and used. The idea that I could just write straight SQL and get objects out without any other configuration I loved. When I got back into the JVM world, I always wondered if there were such a thing out there.

Well I’ve finally found a decent one. The library is called Simpleflatmapper. With no XML configuration file, I can get from SQL to object with out any problem. This is amazing!

Let’s see some code. In these examples I will be using Kotlin as the JVM language, but this should work with Java as well. There is a code repository for this as well.

In the examples I have a postgres database running with the following table setup

CREATE TABLE test(id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, data text);
INSERT INTO test(data) VALUES('1'),('2'),('3'),('4');

So here is my first shot just trying to iterate over those

import org.simpleflatmapper.jdbc.JdbcMapperFactory
import java.util.stream.Stream

fun main(args: Array<String>) {
    Class.forName("org.postgresql.Driver") // load JDBC driver

    getData().forEach { println(it) }
}

// Data class representing a row. Did I mention how neat Kotlin is?
data class DataRow(val id: Int, val data: String)


/*
    This is the SimpleFlatMapper part. This creates a class 
    that knows how to map over the DataRow objects. This is needed because
    looking up certain fields and other reflection like tasks can be cached for
    performance reasons. So we have something that can be smart about 
    doing those for us
 */
val mapper = JdbcMapperFactory.newInstance().newMapper(DataRow::class.java)

fun getData() : Stream<DataRow> {
    val connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/postgres", "postgres", "password");
    val statement = connection.prepareStatement("SELECT id, data FROM test")
    val resultSet = statement.executeQuery()
    return mapper.stream(resultSet)
}

If you ran this you could see the following:

DataRow(id=1, data=1)
DataRow(id=2, data=2)
DataRow(id=3, data=3)
DataRow(id=4, data=4)

Neat!

But of course that isn’t everything. We need to clean up after ourselves. Database connections on the JVM have a lot of cruft that can be left over. The code above has the resultSet, the statement and the connection that need to be closed manually. Yes, it sucks we can’t just have it do it all for us, but sometimes you just need to be a good janitor.

First let’s experiment to see how bad the problem really is. So let’s get a base line by running this SQL on the database to see what a “quiet” database looks like.

SELECT query, count(*) FROM pg_stat_activity group by query;

You should get something that looks like this:

Now let’s modify the main loop to do the query 10 times and wait for user input so we can inspect the database.

fun main(args: Array<String>) {
    Class.forName("org.postgresql.Driver")

    for(i in 1..10) {
        getData().forEach { println(it) }
    }

    println("holding")
    readLine()
}

You should now see something like this

ACK! We have 10 open query sessions. That’s not going to scale very well. And just to prove how fast it won’t scale, instead of 10, do 100. There should be an exception stating that there are too many open connections.

Let’s fix this. The Java Stream has an event for this kind of thing. The Stream knows when it has been closed and allows a cleanup job to run if needed. To subscribe to this we will create an onClose handler.

The code for that looks something like this:

fun getData() : Stream<DataRow> {
    val connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/postgres", "postgres", "password");

    val statement = connection.prepareStatement("SELECT id, data FROM test")

    val resultSet = statement.executeQuery()

    return mapper.stream(resultSet)
                 .onClose {
                    println("closing connection")
                    resultSet.close()
                    statement.close()
                    connection.close()
                 }
}

And if we run that….well we still have the same issue.

The above code fixes the creator of the stream, but we also need to handle this kind of thing from the user side. In other words, who ever is actually using the stream needs to signal, “Hey, I’m done, go do your cleanup thing now”. Again, this would be nice if this is all handled automatically, but certain things just need to be explicit, and cleanup routines are usually explicit.

This is because a Stream is “resourceful”. I.e. it can hold on to resources that are needed as it iterates through sometimes. While it would be nice if it would just cleanup after it was done, there isn’t really anyway for the stream itself to know when the user is done iterating through it. Now, in normal Java you have something called try with resource. But in Kotlin, it’s actually fairly straight forward with the use statement. For example this is how our main loop will now look:

fun main(args: Array<String>) {
    Class.forName("org.postgresql.Driver")

    for(i in 1..10) {
        getData().use {
            it.forEach { println(it) }
        }
    }

    println("holding")
    readLine()
}

That use block is what is the important piece. This is how we signal to the stream of where to clean up those resources. Once that block has finished running, it will close out the stream which will fire the close signal.

Now if you run this piece, you should get to the holding line. And you should see this table as the result

MUCH better, even after running through the loop, we have no active connections to the database.

So there you have it. I like this setup a lot and can start using this in my projects. I’m always a fan of these simpler ways, and especially if I can toss out on the huge libraries that ORMs are. I’m sure they are helpful somehow, but I’ve always get burned or stuck somewhere when I use them.