Partial updates of JSON data in Postgres (using JDBI)

With Postgres 9.5 comes the jsonb_set function, for updating a single key within a JSON column. Hooray!

A sample bit of SQL might look like this:

update mytable 
set myfield = jsonb_set(myfield,'{key, subkey}', '"new string value"'::jsonb) 
where id = 5

I’ve put a text value in the example, but the new value can be an entire JSON structure.

I’ve posted previously on using JSON and Postgres with JDBI. To use the jsonb_set function, we need to reuse the BindJson annotation covered in that post. The jsonb_set function also takes an array parameter, defining the path to the key to be set. For this I wrote a new Bind annotation:

public @interface BindTextArray {
    String value();

    public static class JsonBinderFactory implements BinderFactory {

        public Binder build(Annotation annotation) {
            return new Binder<BindTextArray, String[]>() {
                public void bind(SQLStatement sqlStatement, BindTextArray bind, String[] array) {
                    try {
                        String fieldName = bind.value();
                        Connection con = sqlStatement.getContext().getConnection();                        
                        Array sqlArray = con.createArrayOf("text", array);
			sqlStatement.bindBySqlType(fieldName, sqlArray, Types.ARRAY);
                    } catch (SQLException ex) {
                        throw new IllegalStateException("Error Binding Array",ex);

(Code based on this post:

Here’s the DAO for the SQL example above, using the new Bind annotation:

@SqlUpdate("update mytable set myfield = jsonb_set(myfield, :path,:json) where id = :id")
void updateMyTable(@Bind("id") int id, @BindTextArray("path") String[] path, @BindJson("json") String json)

As you can see, there are limitations to this functionality. We can’t update two different elements in the same JSON column, so if you want to do that, you still need to do it in code. However, the new syntax is handy if you want to update one section of your JSON document, without loading the whole thing into your code base.


Alternative Dropwizard token authentication

Out of the box, the Dropwizard auth module comes with OAuth and Basic Authentication. The documentation for implementing the related Authenticator and Authorizer is simple enough to follow, but there wasn’t much about how to implement an alternative mechanism for authentication, which I needed to do.

It turned out to be fairly fairly straightforward to write a new AuthFilter which used a non standard header for authorisation. I based the implementation on the existing OAuthCredentialAuthFilter filter. I just had to change the getCredentials header to read the header value I wanted to use.

Getting your priorities right

The authentication worked fine. Then I implemented authorisation as well. Remember:

Authentication is the mechanism for identifying the user (e.g. username + password)

Authorisation is the mechanism for determining what they are allowed to do (e.g. role membership)

I created an Authorizer implementation as per the user guide, annotated some resource methods with @RolesAllowed and even remembered to register the RolesAllowedDynamicFeature. However on testing and on running the application, DropWizard appeared to be checking the role membership BEFORE authenticating. Resulting in a 403 access denied, as the roles had not been loaded.

Time for some head scratching, debugging, and trying to register things in different order (no change) until I remembered noticing this line at the top of the AuthFilter I based mine on:


Once I added that to my custom AuthFilter, everything started happening in the correct order.

Today’s lesson: always get your priorities right!


What the F is f:table

Today I needed to knock up a quick interface to some database tables for inputting and editing data to be used in the demonstration of some data extraction software. Aha I thought; I’ll try using Grails Scaffolding. I haven’t used grails scaffolding in earnest since taking a grails training course few years back. However, today I really did just need some simple CRUD functionality.

In Grails 3, you have two options for scaffolding – annotating the controller and having the views and controller methods auto-generated at runtime, or running a command to generate them statically so that you can modify them. I chose the latter, assuming that I’d want to do some customisation.

grails generate-all com.domain.Thing

You can then inspect the generated controller and views, and make any changes necessary. And this is where it all started to go wrong. The table containing the list of existing records didn’t look very nice. I’d removed the default ‘application.css’ which comes with grails, and used bootstrap to style the app. Without the default styles, the table has no spacing, and looks pretty awful.

No problem, I just need to add class=”table” to the table and I’ll get a standard bootstrap styled table. However, the generated index.gsp doesn’t contain a table tag. All I found was this:

<f:table collection="${thingList}"/>

The <f:table/> tag was a new one to me. Google suggests this comes from the grails fields plugin, but the documentation is very sparse: Grails 3 fields plugin.
The documentation doesn’t even mention the <f:table/> which helped a bit, in that it showed how to configure which fields to show in the table but didn’t help in changing styles or other formatting.

The main grails scaffolding documentation suggests running

grails install-templates

to get local copies of the templates used in scaffolding, but this doesn’t include anything to do with the fields plugin.

More detective work led to this Stackoverflow post, and onward to the fields plugin source code.

Finally… how to customise the f:table tag:

Place a file called _table.gsp in /grails-app/views/templates/_fields/

The default file contents are here: _table.gsp

After adding this file to the project and amending to use the required styles, the <f:table/> tag can be used throughout the project with reckless abandon.

My table looks nice now, but I think this sums up why I struggle with the grails plugin ecosystem; it feels a bit half-finished to be using an undocumented tag as part of what should be a quick start process for new users.


Using JDBI with Postgres JSON data

I’ve been migrating some raw JDBC code over to JDBI, and joyfully stripping out lines of boilerplate code for preparing statements, opening record sets, sometimes remembering to close them, handling SQL exceptions which won’t ever occur anyway, and so on. Using the SQL Object API means the only code you have to write is the SQL and a ResultSetMapper to determine how to create your domain objects from the resultset. It really promotes adherence to the single responsibility principle and discourages you from mixing logic in with your database access code.

The database in question has a number of fields containing JSON data. More specifically, they use the PostgreSQL jsonb data type. This has required a little more tinkering to get working.

Inserting jsonb data

Out of the box, JDBI provides two annotations for binding parameters. The @Bind annotation binds a single named argument, and @BindBean binds bean properties with matching names. However, to insert jsonb data, you need to first create an PGobject instance and bind that. To do this, I created a new Binding annotation, following the guidance here:

The annotation code looks like this:

public @interface BindJson {
    String value();

    public static class JsonBinderFactory implements BinderFactory {
        public Binder build(Annotation annotation) {
            return new Binder<BindJson, String>() {                
                public void bind(SQLStatement q, BindJson bind, String jsonString) {
                    try {
                        PGobject data = new PGobject();
                        q.bind(bind.value(), data);                        
                    } catch (SQLException ex) {
                        throw new IllegalStateException("Error Binding JSON",ex);

To use it, annotate the json parameter with the new annotation:

@SqlUpdate("insert into my_table (id,data) VALUES (:id,:data)")
void insertJson(@Bind("id") int id, @BindJson("data") String jsonString);

And that’s it; it just works.

Querying json dynamically

I had a requirement where the parameter supplied to the query was the name of the json element to return. For example, consider the json below. I wanted to be able to paramterise a query to return either one of the key values.

   "element": {
      "key1": "value1",
      "key2": "value2",
      "key3": "value3"

Using raw JDBC it was possible (although not very pretty) to concatenate a suitable sql statement and then execute it:

String sql = "select data->'element1'->'" + subKeyName + "' as value from mytable"

This isn’t possible when the SQL string is specified as a JDBI annotation. I found some useful Postgres json processing functions, including jsonb_extract_path_text which allows you to bind parameters normally:

@SqlQuery("select jsonb_extract_path_text(data,'element1',:subKeyName) as value from mytable")
List<String> getSubKey(@Bind("subKeyName") String subKeyName)

So far I haven’t come across any other issues using JDBI with a PostgreSQL JSON data store. I’m looking forward to trying out the new jsonb functionality in PostgreSQL 9.5 which supports writing partial updates to json fields, yippee!


Enabling hibernate in Ubuntu 14.04

The new laptop (Dell Inspiron 5000) has one or two teething issues. Which is disappointing as I had installed Ubuntu on the XPS I had previously (at work) with no problems at all, so thought this laptop would be a safe bet.

At present I can’t use suspend. Although the laptop sounds like it is resuming, the screen remains blank and I have to do a hard reset. It’s a pain starting everything up again whenever I shut down, so I thought I’d try hibernate instead.

For some reason this is not available on the system tray menu. To hibernate from the terminal the command is:

sudo pm-hibernate

My laptop resumes fine after this. To add the option to the system tray menu, I edited the file


I changed the setting ResultActive to yes in the two places below:

[Disable hibernate by default in upower]

[Disable hibernate by default in logind]

I also decided it would be useful for the laptop to hibernate if I shut the lid. To do this I edited the file


I replaced the line below:




Reboot required after these changed. Now it will at least hibernate. Thanks to this blog post (and the comments) for the guidance:

Next I need to get the audio working…


Super quick Sonar/Postgres setup with docker

Maybe I am easily impressed but wow! Here is how I used docker to setup sonar on my (Ubuntu) laptop superquick.


First, set up a postgres container. The command below creates and starts a container called sonar-postgres, using the official docker postgres image.

docker run --name sonar-postgres -e POSTGRES_USER=sonar -e POSTGRES_PASSWORD=secret -d postgres

The container is created containing a sonar database and user with the supplied password. There are various options to the run command, for example to restart the container automatically. See docker run for more details. -d means detach from the container and run it in the background.

The command above does not publish any ports to the host, so we can’t psql to localhost port 5432 to see the database. However, the postgres container does “expose” port 5432 to linked containers. To have a quick peek inside, the following command creates a temporary container which executes the psql command. After entering the password you chose earlier, you are logged into the sonar database. When you exit, the container is gone.

docker run -it --link sonar-postgres:postgres --rm postgres sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U sonar'

An alternative would have been to modify the original run command to include -p 5432:5432 which would publish the ports to the host and allow direct access via psql. This was handy for me when getting started but obviously not ideal in an environment where you might have several postgres containers.


The default command to start sonar uses the built in h2 database:

docker run -d --name sonarqube -p 9000:9000 -p 9092:9092 sonarqube:5.1

It’s not good practice to use the embedded database for continued use. To set up a container which uses the postgres container created above, use the following command:

docker run -d --name sonarqube --link sonar-postgres:pgsonar -p 9000:9000 -e SONARQUBE_JDBC_USERNAME=sonar -e SONARQUBE_JDBC_PASSWORD=secret -e SONARQUBE_JDBC_URL=jdbc:postgresql://pgsonar:5432/sonar sonarqube:5.1

In the above command, the –link option links the postgres container with a hostname of “pgsonar”, which is used again in the SONARQUBE_JDBC_URL setting to tell sonar where to find the postgres database.

Tip! If the container won’t start, run the command without the -d switch, so it remains in the foreground and you can see the log output.

Once the container is started, navigate to http://localhost:9000/ and you should see the familiar sonarqube dashboard. This process felt quicker than the usual sonar install done properly, and I can definitely see myself using docker more to supercharge my dev environment!


ACPI PCC probe failed

I had one problem getting Ubuntu 14.04 to install on a Dell Inspiron 5000 series. Booting from the USB was no problem – hit F12 at startup to go to boot menu, and chose to boot from the USB drive. I didn’t have to disable Secure Boot or UEFI mode.

However, after selecting “Try Ubuntu”, all went blank. When I tried a second time in Legacy boot mode, I got a more helpful error message:

ACPI PCC probe failed

A quick google suggested this was a problem introduced Ubuntu 14.04.3. Some scary sounding fixes were suggested, but I took the easy option of downloading the 14.04.1 iso instead, which installed fine.


What I’m reading

The more time I spend working in Software Development, the more I realise how little I knew before. I remember going on a Skills Matter Spring course about 10 years ago, and realising that until that point I wasn’t really getting OO. (To excuse myself, I was self taught and relatively new to Java, having previously hacked around in ASP amongst other things).

I am a fan of software books and recently I have been re-reading a couple of old favourites – hence the new Recommended Reading page.

I’ve also realised there are plenty of classics I haven’t read yet, so it’s time to work my way through a few of these. This Recommended Reading for Developers looks like a fairly comprehensive list, so I’m starting at number one. See you in 960 pages…


Reading the timestamp data from the hibernate envers revinfo table

A short blog post in case this helps anyone!

We’re using Hibernate Envers to keep an audit trail of data changes. I thought I’d have a look into the (Postgres) database to get some stats on usage – how many records of a certain type we’re creating by month for example.

The “data_aud” tables have a “rev” field which joins to the “revinfo” table, which looks like this:

             Table "public.revinfo"
  Column   |          Type          | Modifiers 
 id        | integer                | not null
 version   | bigint                 | not null
 timestamp | bigint                 | not null
 user_name | character varying(255) | not null

I was surprised to see that the timestamp was of type bigint – the DDL was generated by Envers, not us!

Attempting to get realistic dates out of the field using Postgres functions such as


failed dismally.

I found this post which gives a working answer for Oracle: SQL query to get a human readable display of the *number* REVINFO.timestamp ?

With a bit of tweaking, here is a useful Postgres view which I created to make future querying a bit easier:

select id, version, user_name, timestamp,
    TO_DATE('01/01/1970 00:00:00','DD/MM/YYYY HH24:MI:SS')
    + (r.timestamp /1000/60/60/24 ||' day')::interval, 'YYYY/MM/DD'
  ) as rev_date, 
    TO_DATE('01/01/1970 00:00:00','DD/MM/YYYY HH24:MI:SS') 
    + (r.timestamp /1000/60/60/24 ||' day')::interval, 'YYYY/MM'
  ) as rev_month 
from revinfo r;

Gradle and the “Parent Pom”

Coincidentally, something came up I wanted to blog about, and it turns out it’s a follow up to the last blog post I wrote 9 months ago.

I’ve been increasingly unhappy with using “apply from” to apply a script plugin from a hard coded location. It’s no use if you’re offline, or not on the right VPN to access the file, or whatever. What I didn’t realise is that I could do apply all the same configuration defaults from a binary plugin. Then you get proper dependency management like you would with a Maven parent pom.

Here’s the code for the Plugin, which is just a standard gradle plugin

import org.gradle.api.Project
import org.gradle.api.Plugin
import org.apache.log4j.Logger
import org.gradle.api.GradleException

public final class CommonConfigPlugin implements Plugin<Project>{

    void apply(Project project) {
    private void configureReleasePlugin(Project project) {

        if (!project.parent) {
            project.createReleaseTag.dependsOn { project.allprojects.uploadArchives }

        //do this for all projects
        project.uploadArchives {
            ... config here ...

    private void addDependencies(Project project) {
        project.afterEvaluate {            
            project.dependencies.add("testCompile", "junit:junit:4.11")
            project.dependencies.add("compile", "log4j:log4j:1.2.14")

    private void applyPlugins(Project project) {
            apply plugin: 'java'
            apply plugin: 'maven'               

        //apply these to parent project only
        if (!project.parent) {
                apply plugin: 'sonar-runner'
                apply plugin: 'release'

Then in the main build.gradle file for any project, I add the jar as a dependency and apply the plugin.

allprojects {
    repositories {
        maven { url "http://my.repo"}
    apply plugin: "my-common-config"

buildscript {

    repositories {
        maven { url "http://my.repo"}
    dependencies {
        classpath ''

Any dependencies which the plugin has end up as dependencies in the buildscript of the project, meaning I don’t have to repeat the dependency on the release plugin, because my common config plugin already depends on it.

I’m going to put more in this common-config-plugin.