Hi Lukas, Thankyou so much for taking the time to look into this! It will be very helpful to have a working solution in place.
Since posting this I've switched the converter implementation to just using the toString/fromString methods from org.postgresql.util.HStoreConverter (Postgres JDBC driver). Not as portable as your solution but fine for our cases. Thanks again, Leigh On Thursday, August 18, 2016 at 11:55:57 PM UTC+10, Lukas Eder wrote: > > Thank you very much for your patience in this matter. > > I have identified the issue and fixed it for jOOQ 3.9: > https://github.com/jOOQ/jOOQ/issues/5393 > > The fix will also be merged in 3.8.5. Indeed the Binding's Converter is > not applied correctly when serialising the nested array of UDT to the > PostgreSQL JDBC driver in String form. > > Note, your current Converter implementation doesn't take into account some > edge cases. I've created the following converter (using jOOQ 3.9 Converter > construction API), which is a bit more robust: > > public Converter<Object, Map<String, String>> converter() { > return Converter.ofNullable( > Object.class, > (Class<Map<String, String>>) (Class) Map.class, > o -> { > String[] split = o.toString().split(",\\s*"); > return split.length == 1 && split[0].equals("") > ? emptyMap() > : Stream.of(split) > .map(kv -> kv.split("=>")) > .collect(toMap(kv -> kv[0].replace("\"", ""), > kv -> kv[1].replace("\"", ""))); > } > , > m -> m.entrySet() > .stream() > .map(e -> "\"" + e.getKey() + "\"=>\"" + e.getValue() + > "\",") > .collect(joining()) > ); > } > > > 2016-07-08 2:57 GMT+02:00 Leigh Whiting <[email protected] > <javascript:>>: > >> Hi Lukas, >> >> A contrived table and UDT setup to demonstrate the issue would be as >> follows: >> >> CREATE TYPE message AS ( >> definition TEXT, >> parameters HSTORE >> ); >> >> >> CREATE TABLE person ( >> id BIGINT NOT NULL, >> messages message[], >> PRIMARY KEY (id) >> ); >> >> >> Here is the binding implementation I am using to bind HSTORE to >> Map<String, String>. It is pretty vanilla, copied straight from the Jooq >> examples (very helpful!). >> >> public class JooqHStoreToMapBinding implements Binding<Object, Map<String, >> String>> { >> >> private static final long serialVersionUID = >> SerialVersion.SERIAL_VERSION_UID; >> private Converter converter = new Converter(); >> >> @Override >> public org.jooq.Converter<Object, Map<String, String>> converter() { >> return converter; >> } >> >> @Override >> public void sql(final BindingSQLContext<Map<String, String>> ctx) throws >> SQLException { >> >> ctx.render().visit(DSL.val(ctx.convert(converter).value())).sql("::hstore"); >> } >> >> @Override >> public void register(final BindingRegisterContext<Map<String, String>> >> ctx) throws SQLException { >> ctx.statement().registerOutParameter(ctx.index(), Types.CLOB); >> } >> >> /** >> * Gets a Map and convert it to a string on a {@link PreparedStatement}. >> */ >> @Override >> public void set(final BindingSetStatementContext<Map<String, String>> >> ctx) throws SQLException { >> ctx.statement().setString(ctx.index(), >> Objects.toString(ctx.convert(converter).value(), null)); >> } >> >> /** >> * Gets a string value from a {@link ResultSet} and converts it to a Map >> */ >> @Override >> public void get(final BindingGetResultSetContext<Map<String, String>> >> ctx) throws SQLException { >> ctx.convert(converter).value(ctx.resultSet().getString(ctx.index())); >> } >> >> /** >> * Gets a string value from a {@link CallableStatement} and converts it >> to a Map >> */ >> @Override >> public void get(final BindingGetStatementContext<Map<String, String>> >> ctx) throws SQLException { >> ctx.convert(converter).value(ctx.statement().getString(ctx.index())); >> } >> >> // The following methods are not required for Postgres >> >> @Override >> public void get(final BindingGetSQLInputContext<Map<String, String>> >> ctx) throws SQLException { >> throw new SQLFeatureNotSupportedException(); >> } >> >> @Override >> public void set(final BindingSetSQLOutputContext<Map<String, String>> >> ctx) throws SQLException { >> throw new SQLFeatureNotSupportedException(); >> } >> >> >> private static class Converter implements org.jooq.Converter<Object, >> Map<String, String>> { >> >> private static final long serialVersionUID = >> SerialVersion.SERIAL_VERSION_UID; >> >> private static final Map<String, String> INSTANCE = new HashMap<>(); >> private static final char QUOTE = '"'; >> private static final String ARROW = "=>"; >> private static final char COMMA = ','; >> >> @Override >> public Object to(final Map<String, String> userObject) { >> // Convert a map into SQL values for a hstore: >> // "key1"=>"value1","key2"=>"value2" etc. >> final StringBuilder sb = new StringBuilder(); >> for (final Map.Entry<String, String> entry : >> userObject.entrySet()) { >> >> sb.append(QUOTE).append(entry.getKey()).append(QUOTE).append(ARROW) >> >> .append(QUOTE).append(entry.getValue()).append(QUOTE).append(COMMA); >> >> } >> >> return sb.toString(); >> } >> >> @Override >> public Map<String, String> from(final Object databaseObject) { >> // Convert SQL values for a hstore into a map >> final Map<String, String> map = new HashMap<>(); >> if (StringUtils.isEmpty(databaseObject.toString())) { >> return map; >> } >> >> for(final String kvPair : >> databaseObject.toString().replace("\"", "").split(",")) { >> final String[] keyAndValue = kvPair.split("=>"); >> map.put(keyAndValue[0], keyAndValue[1]); >> } >> >> return map; >> } >> >> @Override >> public Class<Object> fromType() { >> return Object.class; >> } >> >> @SuppressWarnings("unchecked") >> @Override >> public Class<Map<String, String>> toType() { >> return (Class<Map<String, String>>) INSTANCE.getClass(); >> } >> } >> } >> >> >> Finally, my code generator is setup like this to register the binding >> (gradle notation): >> >> generator() { >> name('org.jooq.util.DefaultGenerator') >> strategy { >> name('org.jooq.util.DefaultGeneratorStrategy') >> } >> >> database() { >> name('org.jooq.util.postgres.PostgresDatabase') >> inputSchema(jooqSchema) >> >> forcedTypes() { >> forcedType() { >> userType('java.util.Map<String,String>') >> binding('JooqHStoreToMapBinding') >> expression('.*Message\\.parameters') >> types('.*') >> } >> } >> } >> generate() { >> relations(true) >> deprecated(false) >> records(true) >> immutablePojos(true) >> fluentSetters(true) >> } >> target() { >> packageName("ng.${project.name}.${jooqSchema}") >> directory(outputDirectory) >> } >> } >> >> >> The code generator produces MessageRecord with a parameters field of the >> correct type (Map<String, String>) and PersonRecord with a field of type >> MessageRecord[] as expected. >> >> When the MessageRecord[] is serialised the custom binding for HSTORE >> appears not be called at all and the objects are not serialised correctly. >> >> If the table field is just a message (rather than an array) then the >> binding is applied correctly. Am I missing something with my code >> generation to apply the custom binding correctly to array elements? >> >> Thanks for taking a look at this! >> >> Regards, >> Leigh >> >> On Tuesday, July 5, 2016 at 2:48:19 AM UTC+10, Lukas Eder wrote: >>> >>> Hi Leigh, >>> >>> Thank you very much for your enquiry. Would you mind posting an example >>> of: >>> >>> - Such a PostgreSQL UDT >>> - A table that uses it as an array >>> - Your Binding implementation >>> - Your code generator setup >>> >>> Normally, you should not pass through the DefaultBinding for this case, >>> but bind your variable yourself. The very reason why you're using your own >>> binding. So, I'd like to see if there's a bug that prevents your binding >>> from being applied, or just bad code generator setup. >>> >>> Thanks >>> Lukas >>> >>> 2016-07-01 6:43 GMT+02:00 <[email protected]>: >>> >>>> Hi there! >>>> Perhaps an unusual case but one I ran into today: >>>> >>>> If I have a Postgres UDT ('MyUdt') that contains a field mapped with a >>>> custom JOOQ binding (like a hstore or json field as mentioned here: >>>> http://www.jooq.org/doc/3.8/manual/code-generation/custom-data-type-bindings/) >>>> >>>> and that UDT is used to form an array field ('MyUdt[]') on another >>>> table, then JOOQ fails to generate the correct SQL to insert into that >>>> array of UDTs. >>>> >>>> The code being triggered when binding the provided value to the SQL >>>> statement is in DefaultBinding.java:1108: >>>> >>>> else if (actualType.isArray()) { >>>> switch (dialect.family()) { >>>> case POSTGRES: { >>>> ctx.statement().setString(ctx.index(), >>>> toPGArrayString((Object[]) value)); >>>> break; >>>> } >>>> >>>> >>>> The calls below toPGArrayString assume the object and it's fields are >>>> either JOOQ records or basic types that can be mapped with toString(). At >>>> this point, the fact that a field on the object in the array has a custom >>>> binding is not considered. >>>> >>>> Using: Postgres 9.5.3, Jooq 3.8.1 >>>> >>>> Since this is quite likely a corner case experienced by only a few I'm >>>> not anticipating a fix soon - I'm planning to rework my schema and >>>> continue >>>> on for now (rather than even make a patch). >>>> >>>> Thanks for any input, >>>> >>>> Leigh >>>> >>>> -- >>>> 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. >>>> >>> >>> -- >> 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.
