Bind raw record to POJO

[adrien....@live.fr]

Hi.

Because of previously discussed limits of JOOQ, I'm trying to find a way to use some parts of JOOQ in order to use a SETOF-returning-function:

Here is the PostgreSQL part:

test=> \d test
                          Table "public.test"
┌────────┬─────────┬───────────────────────────────────────────────────┐
│ Column │  Type   │                     Modifiers                     │
├────────┼─────────┼───────────────────────────────────────────────────┤
│ id     │ integer │ not null default nextval('test_id_seq'::regclass) │
│ a      │ integer │                                                   │
└────────┴─────────┴───────────────────────────────────────────────────┘
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
Number of child tables: 2 (Use \d+ to list them.)

test=> \sf get_test

CREATE OR REPLACE FUNCTION public.get_test(id_p integer)
 RETURNS SETOF test
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY SELECT * FROM test WHERE id = id_p;
END;
$function$

Here is something I'm trying to do:

            String sql = "SELECT get_test(1)";
            Result<Record> result = create.fetch(sql);

            System.out.println(result);

            for (Record r : result) {
                System.out.println(r);
            }

            List<TestRecord> l = result.into(TestRecord.class);

The println part shows me something cool:
+--------+
|get_test|
+--------+
|(1,1)   |
+--------+

but I'm really not able to put these data in a POJO or in a Record: all fields of my TestRecord are remaining to NULL.

Is there a way I could bind the returned data to the object I want to manipulate?


Adrien.

[Lukas Eder]

Hello,

You're almost there!

Replace this:

String sql = "SELECT get_test(1)";

With this:

String sql = "SELECT * FROM get_test(1)";

The rest of the code should be the same.

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

[adrien....@live.fr]

I sent that too soon, I apologize for the noise.
I don't remove that post, just in case it could help someone who is doing the same mistake:

SELECT my_function(...) is not the way to do it; it should be done with:
SELECT * FROM my_function(...)

Adrien.

[Lukas Eder]

;-)

There's no such thing as noise. Thanks for the update!

--

[adrien....@live.fr]

Hello.

I tried another version of that test:

test=> \d testarray
                           Table "public.testarray"
┌────────┬───────────┬────────────────────────────────────────────────────────┐
│ Column │   Type    │                       Modifiers                        │
├────────┼───────────┼────────────────────────────────────────────────────────┤
│ id     │ integer   │ not null default nextval('testarray_id_seq'::regclass) │
│ a      │ integer[] │ not null                                               │
└────────┴───────────┴────────────────────────────────────────────────────────┘
Indexes:
    "testarray_pkey" PRIMARY KEY, btree (id)

test=> \sf get_testarray

CREATE OR REPLACE FUNCTION public.get_testarray(id_p integer)
 RETURNS SETOF testarray
 LANGUAGE plpgsql
AS $function$
BEGIN
  RETURN QUERY SELECT * FROM testarray WHERE id = id_p;
END;
$function$

Here is the code I run:

            String sql = "SELECT * FROM get_testarray(?)";
            Result<Record> result = create.fetch(sql, 1);

            System.out.println("Result: ");
            System.out.println(result);

            List<TestarrayRecord> l = result.into(TestarrayRecord.class);

The array part makes the whole thing explode:

                                     
Result:
+----+-------+
|  id|a      |
+----+-------+
|   1|{1,2,3}|
+----+-------+
org.jooq.exception.MappingException: An error ocurred when mapping record to class com.ovh.osp.sql.tables.records.TestarrayRecord
    at org.jooq.impl.DefaultRecordMapper$RecordToRecordMapper.map(DefaultRecordMapper.java:430)
    at org.jooq.impl.DefaultRecordMapper$RecordToRecordMapper.map(DefaultRecordMapper.java:418)
    at org.jooq.impl.DefaultRecordMapper.map(DefaultRecordMapper.java:331)
    at org.jooq.impl.ResultImpl.into(ResultImpl.java:1502)
    at test.Test.main(Test.java:57)
Caused by: org.jooq.exception.MappingException: An error ocurred when mapping record to +----+------+
|  id|a     |
+----+------+
|   1|{null}|
+----+------+
    at org.jooq.impl.AbstractRecord$TransferRecordState.operate(AbstractRecord.java:754)
    at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:123)
    at org.jooq.impl.AbstractRecord.intoRecord(AbstractRecord.java:701)
    at org.jooq.impl.DefaultRecordMapper$RecordToRecordMapper.map(DefaultRecordMapper.java:424)
    ... 4 more
Caused by: org.jooq.exception.DataTypeException: Cannot convert from {1,2,3} (class org.postgresql.jdbc4.Jdbc4Array) to class [Ljava.lang.Integer;
    at org.jooq.tools.Convert$ConvertAll.fail(Convert.java:873)
    at org.jooq.tools.Convert$ConvertAll.from(Convert.java:811)
    at org.jooq.tools.Convert.convert0(Convert.java:300)
    at org.jooq.tools.Convert.convert(Convert.java:292)
    at org.jooq.tools.Convert.convert(Convert.java:360)
    at org.jooq.impl.DefaultDataType.convert(DefaultDataType.java:614)
    at org.jooq.impl.AbstractRecord$TransferRecordState.operate(AbstractRecord.java:732)
    ... 7 more


Am I doing it wrong?


Adrien.

[adrien....@live.fr]

 Update: tested with JOOQ 3.5.2, but same result.

[Lukas Eder]

Hello,

Thanks for reviving this - and sorry for the delay. It looks like your previous E-Mail got lost somehow.

I can reproduce this issue and have registered it as:

https://github.com/jOOQ/jOOQ/issues/4065

It looks like the array-contained-in-setof-function-results use-case won't really work with current versions of jOOQ 3.5.x. While we'll certainly fix #4065 also for 3.5.3, it might be better to build your own 3.6.0-SNAPSHOT version, where SETOF functions are natively supported, perhaps?

Best

Lukas

2015-02-16 12:34 GMT+01:00 <adrien....@live.fr>:

 Update: tested with JOOQ 3.5.2, but same result.

--

[adrien....@live.fr]

I asked you once when the 3.6 release was planned, and you more or less told me that it will take many months.
I'm not just trying JOOQ but plan to use it in production, if it helps me, quite soon. That being considered, I can't plan to release a Java artifact built upon a SNAPSHOT release (maven won't love it anyway).

It that bug is fixed in 3.5.3, that should be enough for me to work.
Recurring question: when that version has a chance to be born? :)

BTW, I don't get something: you tell me that upgrading to 3.6 might help me, but you tagged the bug for the 3.6 milestone in you bug tracker. So I'm not sure of what the upgrade would bring me on that precise topic.


Thanks for your efficient answer :).

Adrien.

[Lukas Eder]

2015-02-16 14:25 GMT+01:00 <adrien....@live.fr>:

I asked you once when the 3.6 release was planned, and you more or less told me that it will take many months.
I'm not just trying JOOQ but plan to use it in production, if it helps me, quite soon. That being considered, I can't plan to release a Java artifact built upon a SNAPSHOT release (maven won't love it anyway).

I understand.

 

It that bug is fixed in 3.5.3, that should be enough for me to work.

Well, let's hope it can be done. :)

 

Recurring question: when that version has a chance to be born? :)

"Probably" by the end of March. Certainly by April.

 

BTW, I don't get something: you tell me that upgrading to 3.6 might help me, but you tagged the bug for the 3.6 milestone in you bug tracker. So I'm not sure of what the upgrade would bring me on that precise topic.

Unfortunately, GitHub doesn't support tagging an issue with several milestones. That is why we ususally create issues for a minor release, and then create additional issues for the patch releases when we see that something should be merged.

[Lukas Eder]

This is now fixed for jOOQ 3.6.0 and will be merged to older jOOQ versions:

https://github.com/jOOQ/jOOQ/commit/2b06039e505134f79ef770043e87b436394082ae

Cheers,

Lukas

[adrien....@live.fr]

Thanks Lukas :).