Saving arrays in Postgres using JDBI
Last time I wrote about JDBI I spoke to you about creating flexible binders for the JDBI Object API so you could bind fields from custom objects into Postgres table columns.
Today I want to show you how to save a collection to a Postgres column using JDBI. You may think this should be totally trivial, but for anything other than a totally basic use case it isn’t. For example, if you were using the Object API and wanted to save a list of tags to a Posts table, you can’t just do:
The problem is that @Bind operates on the JDBI SQLStatement, which does not accept collections that way. Behind the scenes, JDBI has to map the java collection to a database specific SQL collection. In this case, a SQL Array is needed since Postgres works with that. To accommodate for this, JDBI provides you with bindBySqlType
In our example, name is “tags”, value is a SQL Array that we need to create:
and sqlType is the SQL Array data type from the java.sql package. However, I've skipped telling you about a detail. You don’t have access to SQLStatement in your DAO so you will have to do things differently. Create a binder factory like I showed you in the previous blogpost. Here’s an example:
Now that you have a mechanism to bind a java Post object to its corresponding posts table in Postgres update your DAO:
There you have it! You’ve been able to bind an array from a java object to a postgres column using JDBI. Feel free to ask me questions or drop a comment below.
