Binding for Postgresql composite types (UDT)

[ralph.v...@gmail.com]

Hi,

How does the binder looks like for composite types? I've seen the JSON/GSON binder example in the manual but I have some difficulties to figure out what to put in the sql(), register(), get() and set() methods of the binder when using composite types. I've found various examples of using UDTs and examples of binding but I could not find a sample of using UDTs with binding.

I want to bind my composite type to my own Java object and not to the UDT object generated by Jooq. 

For example with the following database schema:

CREATE TYPE mytype AS (
  first TEXT,
  second TEXT
);


CREATE TABLE mytable (
  value mytype
);

And my own class for storing myType:

public class MyOwnType {
  private String first;
  private String second;


  public MyOwnType(String f, String s) {
    first = f;
    second = s;
  }
  ...
};

What does the binder needs to look like so that I can do

MytableRecord record = MyDsl.newRecord(MYTABLE);
record.setValue(new MyOwnType("1", "2"));
record.store();

Is there an example available somewhere or is there an easier way to do this without creating my own binder?

Thanks,

Ralph.

[Lukas Eder]

2017-08-17 21:21 GMT+02:00 <ralph.v...@gmail.com>:

I want to bind my composite type to my own Java object and not to the UDT object generated by Jooq. 

I wouldn't do that if I were you. We've fixed tons of bugs over the last releases to get this to work with all sorts of potentially nested UDTs and arrays and what not. The PostgreSQL JDBC driver is very incomplete as far as binding PostgreSQL's advanced data types are concerned. Ultimately, it just boils down to parsing / serialising things to strings.

Having said so, you can easily apply a Converter (rather than a Binding) to automatically map the generated jOOQ UDTRecord to your custom type. Implementing a Converter is also much simpler than implementing a Binding

I hope this helps

Lukas

[ralph.v...@gmail.com]

On Friday, August 18, 2017 at 3:31:15 PM UTC+2, Lukas Eder wrote:

2017-08-17 21:21 GMT+02:00 <ralph.v...@gmail.com>:

I want to bind my composite type to my own Java object and not to the UDT object generated by Jooq. 

I wouldn't do that if I were you. We've fixed tons of bugs over the last releases to get this to work with all sorts of potentially nested UDTs and arrays and what not. The PostgreSQL JDBC driver is very incomplete as far as binding PostgreSQL's advanced data types are concerned. Ultimately, it just boils down to parsing / serialising things to strings.

Ah, ok. My composite type is really simple and I don't need nested UDTs or arrays. Should that work or do you still advice against it?

 

Having said so, you can easily apply a Converter (rather than a Binding) to automatically map the generated jOOQ UDTRecord to your custom type. Implementing a Converter is also much simpler than implementing a Binding

I've tried creating a Converter but it only accepts the following converter:

public class MyTypeConverter implements Converter<Object, MyType> { ... }

(note its converting from Object and not the UDT type) that's why I though it needed a Binding instead.

In my gradle build I have this:

forcedTypes {
    forcedType {
        userType = "jooqtest.MyType"
        expression = "test.mytable.value"
        converter = "jooqtest.MyTypeConverter"
    }
}

and it all seems to work, except for the converter trying to convert from an Object instead of the JOOQ generated UDT type MytypeRecord. Am I missing something?

Thanks,

Ralph.

[ralph.v...@gmail.com]

On Saturday, August 19, 2017 at 8:25:08 PM UTC+2, Ralph van Etten wrote:

and it all seems to work, except for the converter trying to convert from an Object instead of the JOOQ generated UDT type MytypeRecord. Am I missing something?

Sorry, I've checked it again and I found an error in the database schema. It now is working as expected.

Thanks,

Ralph.

[Lukas Eder]

Thanks for the feedback, Ralph.

I'm glad to hear that you were able to resolve your problem. Some further comments about open questions inline:

2017-08-19 20:25 GMT+02:00 <ralph.v...@gmail.com>:

Ah, ok. My composite type is really simple and I don't need nested UDTs or arrays. Should that work or do you still advice against it?

Yes, because jOOQ does this for free, and any manual deserialisation is just hairy :)

(and then, as I firmly believe in Murphy's Law, there will be the day when your composite types will no longer be "really simple")

Cheers,

Lukas

[Lukas Eder]

2018-03-18 22:04 GMT+01:00 The bear in Boulder <bgi...@coyotesong.com>:

What do you need from the PostgreSQL JDBC driver? I've only looked at some of the Kerberos auth stuff but there's a (remote) possibility that I could help.

The PostgreSQL JDBC driver doesn't help at all, its support for PostgreSQL's data types is non-existent. It just transmits a string-serialized version of all sorts of types and wraps that in PGobject. Essentially, jOOQ includes a string serializer / deserializer for this PostgreSQL object notation.

I'm not sure in what context you're asking this question though. Perhaps, you could create a new discussion - in case it's not really related to this previous one...