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();
}
}
--
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
