For the record, I have created a feature request for adding a new jooq-postgres module, which would include all of these PostgreSQL specific features: https://github.com/jOOQ/jOOQ/issues/5507
I think that might be quite well received with the community. 2016-08-22 22:06 GMT+01:00 Lukas Eder <[email protected]>: > Oh that's great to know, thanks for letting us know about the > HStoreConverter. I should definitely have a closer look at that package. > Might be a good candidate to add some sugar on top in a new jooq-postgres > module... > > 2016-08-21 23:48 GMT+01:00 Leigh Whiting <[email protected]>: > >> 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]>: >>> >>>> 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/cus >>>>>> tom-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]. >>>> 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. >> > > -- 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.
