Sorry, just saw this reply. Yeah this is the same question as that. On Wednesday, August 17, 2016 at 5:48:08 AM UTC-7, Lukas Eder wrote: > > 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: > > @Overridepublic void sql(BindingSQLContext<JsonElement> ctx) throws > SQLException { > > ctx.render().visit(DSL.val(ctx.convert(converter()).value())).sql("::jsonb"); > // > ^^^^^^^ fix here} > > > 2016-08-10 19:33 GMT+02:00 <[email protected] <javascript:>>: > >> 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] <javascript:>. >> For more options, visit https://groups.google.com/d/optout. >> > >
-- 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.
