Record.get and Postgres array type

[Max Kremer]

Hi,

  I have a SQL result set that includes a varchar array as one of the columns. I'm mapping over the Result<Record> returned by fetch() and manually populating a pojo but I'm having trouble populating the String[] array field of the pojo.

d.fetch("select array_agg(some_field) as theArray from sometable group by some_field")
                .stream()
                .map(r -> {
                    SalesTouchPoint stp = new SalesTouchPoint();
                    stp.setAudience(r.getValue("people", Integer.class));
                    stp.setEpc(r.getValue("epc", Double.class));
                    stp.setTouchPoints(r.getValue("theArray", String[].class));
                    return stp;
                    })
                .collect(Collectors.toList());    

    

As you can see from the above code I'm using Record.getValue (or Record.get) to access the column "theArray" from the result set. I'm getting a class cast exception:

Caused by: org.jooq.exception.DataTypeException: Cannot convert from [Ljava.lang.String;@5a6782c3 (class java.lang.String) to class [Ljava.lang.String;
 at org.jooq.tools.Convert$ConvertAll.fail(Convert.java:1118) ~[na:na]
 at org.jooq.tools.Convert$ConvertAll.from(Convert.java:1007) ~[na:na]
 at org.jooq.tools.Convert.convert0(Convert.java:316) ~[na:na]
 at org.jooq.tools.Convert.convert(Convert.java:308) ~[na:na]
 at org.jooq.tools.Convert.convert(Convert.java:380) ~[na:na]
 at org.jooq.impl.AbstractRecord.get(AbstractRecord.java:260) ~[na:na]
 at org.jooq.impl.AbstractRecord.getValue(AbstractRecord.java:1222) ~[na:na]

Everything I've read in the docs suggests that JOOQ handles SQL array types just fine. What am I doing wrong?

[Lukas Eder]

Hi Max,

In principle, that sort of thing should work, but perhaps the PostgreSQL JDBC driver isn't really delivering an array but a wrapper for an array for your query? What's the DDL of sometable?

Cheers,

Lukas

--
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 jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Max Kremer]

Hi Lukas,

  The schema for the table is not relevant in this case because it doesn't contain the array field, the array field is constructed via array_agg aggregation function.  Simple example:

select array_agg(some_field) as theArray 

from sometable 

group by some_field

CREATE TABLE sometable (

     some_field varchar(30)

);

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[Max Kremer]

Also tried this:

String[] foo = r.get(field("distinctpath", SQLDataType.VARCHAR.getArrayDataType()));

No luck. Still getting a class cast exception

[ClassCastException: java.lang.String cannot be cast to [Ljava.lang.String;]

[Max Kremer]

Ok I think I know what's going on. The issue is our cache implementation. When caching is not part of the equation the array stuff works fine.

We use the MockDataProvider with REDIS to do out cache (I'm sure you recall this technique and associated blog post). Anyway, there may be a bug in the formatJSON() method of ResultImpl with regard to how it handles array-type columns. Something with regard to serialization/de-serialization of the Result  to/from JSON. I will investigate and post back here or on Github if I find a bug.

[Lukas Eder]

OK, thanks for the update. Yes, please do post any bug you may find to github. Will be very happy to review, then.

Thanks,
Lukas

2017-09-06 18:03 GMT+02:00 Max Kremer <mkr...@trialfire.com>:

Ok I think I know what's going on. The issue is our cache implementation. When caching is not part of the equation the array stuff works fine.

We use the MockDataProvider with REDIS to do out cache (I'm sure you recall this technique and associated blog post). Anyway, there may be a bug in the formatJSON() method of ResultImpl with regard to how it handles array-type columns. Something with regard to serialization/de-serialization of the Result  to/from JSON. I will investigate and post back here or on Github if I find a bug.

--

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 jooq-user+unsubscribe@googlegroups.com.