java - 如何在 select 语句中从 JOOQ 模型调用 postgresql 函数？

Question

我的 postgresql 数据库中有一个函数，它接收 JSONB 参数。我创建了一个自定义绑定(bind)和强制类型以将 JSONB 类型的 postgres 映射到 Gson 的 JsonElement(我已经复制了转换器和下面的 gradle 构建配置)。我正在使用 JOOQ 的模型(需要类型为 Object 的参数)将函数作为 select 语句的一部分调用并得到错误

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.

无论我传递的是 Json 字符串还是 Gson JsonElement，我都会收到错误消息。这是因为自定义类型/转换器不适用于函数参数吗？

我的整个 gradle 构建配置:

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'
            }
        }
    }
}

PostgresJSONGson绑定(bind)文件:

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

这里有两件事你应该更正:

1。您的绑定(bind)可能不适用于您的存储过程

为了解决这个问题，您可以修改强制类型声明:

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

修正案还将匹配名为old_json 和new_json 的函数参数。现在，这还没有完成，这就是为什么 jOOQ 默认绑定(bind)一个字符串。

2。您的绑定(bind)可能应该转换为 jsonb，而不是 json

这可能不是问题，因为您当前的代码尚未应用绑定(bind)，但即使应用了绑定(bind)，它也可能仍然不完全正确。当前绑定(bind)将绑定(bind)变量转换为 PostgreSQL 中的 json 数据类型，这与 jsonb 不完全相同。你可能应该写:

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