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] <javascript:>>:
>
>> 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] <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.
