How to call a postgresql function from the JOOQ models in a select statement?

Question

I have a function in my postgresql database which takes in JSONB paramaters. I created a custom binding and forced type to map the JSONB type of postgres to Gson's JsonElement (I've copied the converter and my gradle build config below). I'm calling the function as part of a select statement using JOOQ's models (which expects parameters of type Object) and get an error

Exception in thread "main" org.jooq.exception.DataAccessException: SQL [select "tact_shard_0"."records"."id", "public"."merge_fields_json"("old_json" := ?, "new_json" := ?) from "shard_0"."records" where "shard_0"."records"."record_type" = ?]; ERROR: function public.merge_fields_json(old_json => character varying, new_json => character varying) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.

I get an error regardless of if I pass a Json string or a Gson JsonElement. Is this because the custom type/converter don't apply to function parameters?

My entire gradle build config:

apply plugin: "java"
apply plugin: "application"

mainClassName = "com.vault.server.VaultServer"

sourceCompatibility = JavaVersion.VERSION_1_8
targetCompatibility = JavaVersion.VERSION_1_8

defaultTasks = ['clean', 'build']

sourceSets {
    main {
        java { srcDir 'src' }
        resources { srcDir 'resources' }
    }
    test {
        java { srcDir 'test' }
        resources { srcDir 'resources' }
    }
    integTest {
        java.srcDir file('integrationTest')
        resources.srcDir file('resources')
        compileClasspath += sourceSets.main.output + sourceSets.test.output
        runtimeClasspath = output + compileClasspath
    }
}

repositories { mavenCentral() }

dependencies {
    compile "log4j:log4j:1.2.17"
    compile "org.apache.commons:commons-lang3:3.3.2"
    compile "org.jooq:jooq:3.8.4"
    compile "org.jooq:jooq-meta:3.8.4"
    compile "org.jooq:jooq-codegen:3.8.4"
    compile "com.google.guava:guava:17.0"
    compile "org.apache.httpcomponents:httpclient:4.3.4"
    compile "com.fasterxml.jackson.core:jackson-databind:2.4.1.1"
    compile "commons-io:commons-io:2.4"
    compile "org.eclipse.jetty.aggregate:jetty-all-server:8.1.15.v20140411"
    compile "org.yaml:snakeyaml:1.13"
    compile "redis.clients:jedis:2.6.0"
    compile 'org.apache.commons:commons-collections4:4.0'
    compile 'com.google.code.gson:gson:2.3.1'
    compile 'org.postgresql:postgresql:9.4.1208.jre7'
    compile 'org.apache.commons:commons-dbcp2:2.0.1'
    compile group: 'javax.ws.rs', name: 'jsr311-api', version: '1.1.1'
    compile group: 'com.sun.jersey', name: 'jersey-bundle', version: '1.19'
    compile group: 'com.sun.jersey', name: 'jersey-server', version: '1.19'
    compile group: 'com.sun.jersey', name: 'jersey-core', version: '1.19'
    compile group: 'com.sun.jersey', name: 'jersey-servlet', version: '1.19'
    compile group: 'com.sun.jersey', name: 'jersey-json', version: '1.19'
    compile group: 'com.sun.jersey', name: 'jersey-client', version: '1.19'
    compile group: 'com.sun.jersey', name: 'jersey-grizzly2', version: '1.19'
    compile group: 'javax.servlet', name: 'javax.servlet-api', version: '3.0.1'
    testCompile group: 'org.glassfish.jersey.containers', name: 'jersey-container-grizzly2-servlet', version: '2.21'
    testCompile group: 'com.sun.jersey.jersey-test-framework', name: 'jersey-test-framework-core', version: '1.19'
    testCompile group: 'com.sun.jersey.jersey-test-framework', name: 'jersey-test-framework-external', version: '1.19'
    testCompile group: 'com.sun.jersey.jersey-test-framework', name: 'jersey-test-framework-http', version: '1.19'
    testCompile group: 'com.sun.jersey.jersey-test-framework', name: 'jersey-test-framework-grizzly2', version: '1.19'
    testCompile group: 'org.glassfish.jersey.test-framework.providers', name: 'jersey-test-framework-provider-jetty', version: '2.6'
    testCompile group: 'com.sun.jersey.test.framework', name: 'jersey-test-framework', version: '1.0.3.1'
    testCompile "org.hamcrest:hamcrest-library:1.3"
    testCompile "junit:junit:4.11"
    testCompile "org.mockito:mockito-all:1.8.4"
    testCompile "org.easymock:easymock:3.4"
    testCompile "cglib:cglib:3.2.4"
    testCompile "org.objenesis:objenesis:2.4"
}

configurations {
  integTestCompile { extendsFrom testCompile, compile }
  integTestRuntime { extendsFrom integTestCompile, testRuntime }
}

jar {
    // adds runtime dependencies to jar package
    from(configurations.runtime.collect { it.isDirectory() ? it : zipTree(it) }) {
        // remove all signature files
        exclude "META-INF/*.SF"
        exclude "META-INF/*.DSA"
        exclude "META-INF/*.RSA"
    }
    baseName = 'vault'
    manifest.attributes("Main-Class": mainClassName);
}

test {
  exclude 'com/vault/**'

  maxHeapSize '1024m'
  minHeapSize '512m'

}

task integTest(type: Test) {
  testClassesDir = project.sourceSets.integTest.output.classesDir
  classpath = project.sourceSets.integTest.runtimeClasspath
  exclude 'com/vault/**'

  maxHeapSize '1024m'
  minHeapSize '512m'
}

tasks.withType(Test) {
  testLogging { events 'passed' }

  if (System.getProperty('DEBUG', 'false') == 'true') {
    jvmArgs '-Xdebug',
      '-Xrunjdwp:transport=dt_socket,server=y,suspend=y,address=4000'
  }
}

task wrapper(type: Wrapper) {
    gradleVersion = '2.0'
}

task generateVersion() {
    def git_hash = new ByteArrayOutputStream()
    exec{
        executable "git"
        args "rev-parse", "--verify", "HEAD"
        standardOutput = git_hash
    }
    def version = git_hash.toString()
    new File("./resources/version").write(version)
    println "Git version is: " + version
}

build.dependsOn generateVersion

buildscript {
    repositories {
        jcenter()
    }
    dependencies {
        classpath 'nu.studer:gradle-jooq-plugin:1.0.5'
        classpath 'org.postgresql:postgresql:9.4.1208.jre7' // database-specific JDBC driver
        classpath 'com.google.code.gson:gson:2.3.1'
    }
}
apply plugin: 'nu.studer.jooq'

jooq {
    recordsDb(sourceSets.main) {
        jdbc {
            driver = 'org.postgresql.Driver'
            url = 'jdbc:postgresql://localhost:5432/'
            user = 'postgres'
            password = 'xyz'
        }
        generator {
            name = 'org.jooq.util.DefaultGenerator'
            strategy {
                name = 'org.jooq.util.DefaultGeneratorStrategy'
            }
            database {
                name = 'org.jooq.util.postgres.PostgresDatabase'
                schemata {
                    schema {
                        inputSchema = "shard_0"
                    }
                }
            }
            customTypes {
                customType {
                    name = "JsonElement"
                    converter = "com.vault.dao.PostgresJSONGsonBinding"
                }
            }
            forcedTypes {
                forcedType {
                    name = "JsonElement"
                    expression = ".*data.*|.*tags.*"
                    types = ".*"
                }
            }
            generate {
                relations = true
                deprecated = false
                records = true
                immutablePojos = true
                fluentSetters = true
            }
            target {
                packageName = 'com.vault.jooq'
                directory = 'target/generated-sources'
            }
        }
    }
}

PostgresJSONGsonBinding file:

package com.vault.dao;


import static org.jooq.tools.Convert.convert;
import java.sql.*;
import java.util.Objects;
import java.util.logging.Logger;

import org.apache.log4j.spi.LoggerFactory;
import org.jooq.*;
import org.jooq.impl.DSL;
import com.google.gson.*;

// We're binding <T> = Object (unknown JDBC type), and <U> = JsonElement (user type)
public class PostgresJSONGsonBinding implements Binding<Object, JsonElement> {

  // The converter does all the work
  @Override
  public Converter<Object, JsonElement> converter() {
    return new Converter<Object, JsonElement>() {
      @Override
      public JsonElement from(Object t) {
        return t == null ? JsonNull.INSTANCE : new Gson().fromJson("" + t, JsonElement.class);
      }

      @Override
      public Object to(JsonElement u) {
        return u == null || u == JsonNull.INSTANCE ? null : new Gson().toJson(u);
      }

      @Override
      public Class<Object> fromType() {
        return Object.class;
      }

      @Override
      public Class<JsonElement> toType() {
        return JsonElement.class;
      }
    };
  }

  // Rending a bind variable for the binding context's value and casting it to the json type
  @Override
  public void sql(BindingSQLContext<JsonElement> ctx) throws SQLException {
    ctx.render().visit(DSL.val(ctx.convert(converter()).value())).sql("::json");
  }

  // Registering VARCHAR types for JDBC CallableStatement OUT parameters
  @Override
  public void register(BindingRegisterContext<JsonElement> ctx) throws SQLException {
    ctx.statement().registerOutParameter(ctx.index(), Types.VARCHAR);
  }

  // Converting the JsonElement to a String value and setting that on a JDBC PreparedStatement
  @Override
  public void set(BindingSetStatementContext<JsonElement> ctx) throws SQLException {
    ctx.statement().setString(ctx.index(), Objects.toString(ctx.convert(converter()).value(), null));
  }

  // Getting a String value from a JDBC ResultSet and converting that to a JsonElement
  @Override
  public void get(BindingGetResultSetContext<JsonElement> ctx) throws SQLException {
    ctx.convert(converter()).value(ctx.resultSet().getString(ctx.index()));
  }

  // Getting a String value from a JDBC CallableStatement and converting that to a JsonElement
  @Override
  public void get(BindingGetStatementContext<JsonElement> ctx) throws SQLException {
    ctx.convert(converter()).value(ctx.statement().getString(ctx.index()));
  }

  // Setting a value on a JDBC SQLOutput (useful for Oracle OBJECT types)
  @Override
  public void set(BindingSetSQLOutputContext<JsonElement> ctx) throws SQLException {
    throw new SQLFeatureNotSupportedException();
  }

  // Getting a value from a JDBC SQLInput (useful for Oracle OBJECT types)
  @Override
  public void get(BindingGetSQLInputContext<JsonElement> ctx) throws SQLException {
    throw new SQLFeatureNotSupportedException();
  }
} 

Answer 1

There are two things you should correct here:

1. Your binding is probably not applied to your stored procedure

In order to fix this, you could amend your forced type declaration:

        forcedTypes {
            forcedType {
                name = "JsonElement"
                expression = ".*data.*|.*tags.*|.*\.(old|new)_json"
                types = ".*"
            }
        }

The amendment will also match function parameters called old_json and new_json. Right now, this isn't being done, which is why jOOQ defaults to binding a string.

2. Your binding should probably cast to jsonb, not to json

This might not be an issue as your current code doesn't apply the binding yet, but when it does, it's probably still not entirely correct. The current binding casts bind variables to the json data type in PostgreSQL, which isn't exactly the same as jsonb. You should probably write:

@Override
public void sql(BindingSQLContext<JsonElement> ctx) throws SQLException {
    ctx.render().visit(DSL.val(ctx.convert(converter()).value())).sql("::jsonb");
    //                                                                 ^^^^^^^ fix here
}