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

[ish...@gmail.com]

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 "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();
      }
    }

[Lukas Eder]

For the record, this seems to be the same question as the following one on Stack Overflow:

http://stackoverflow.com/q/38807777/521799

Can you confirm this?

The answer given on Stack Overflow was:

--------------------------------------

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
}

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[ish...@gmail.com]

Sorry, just saw this reply. Yeah this is the same question as that. 

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.