jOOQ and autogeneration, how to avoid UDT Records inside table POJOs

Question

I define a type T and a view V in a PostgreSQL database.

CREATE TYPE my_type AS
(
  mt_column1 smallint NOT NULL
);

CREATE VIEW my_view
AS SELECT
   some_column_id integer
   ARRAY(SELECT
      ROW(an_int)::my_type
      FROM a_table
   ) AS my_view_types
  FROM a_regular_table 
  WHERE my_condition_hold);

Using the code generation on release 3.7 I get both an UDT record class MyTypeRecord and a table record class MyViewRecord and the UDT POJO class MyType and table POJO class MyView.

The MyView generated class has an array of MyTypeRecord.

public class MyView extends Object implements Serializable, Cloneable, IMyView {

    private static final long serialVersionUID = 1984808170;

    private final Long           some_column_id;
    private final MyTypeRecord[] my_view_types;
}

while in a POJO I would expect an array of POJOs, e.g.:

    private final MyType[] my_view_types;

Another interesting fact is that the pojo and the record for the type are in the udt folder, while for the view they are in the tables folder: maybe this can help to find a solution/explanation.

Is there a way to make the View a pojo-only conversion at generation time?

---

Upon request, I attached a working example that generates the records and POJOs as I described. It is shared with FileDropper at this link.

---

I also report one possible trick to avoid this issue, to be used iff you are really desperate. As reported in this stackoverflow question/answer, jOOQ even if we assign a POJO instead of the record, will not be able to automatically convert the array of records into the record class MyTypeRecord. Hence, you can parse the array of ROWs to json using function array_to_json. In my example would be:

CREATE VIEW my_view
AS SELECT
   some_column_id integer
   array_to_json(ARRAY(SELECT        
        ROW(an_int)::my_type         
      FROM a_table
   ))::json AS my_view_types
  FROM a_regular_table 
  WHERE my_condition_hold);

This should be converted automatically by jOOQ to a JSON if you register this binding.

Exactitude

I now see from class JavaGenerator that UDT POJOs are generated after table POJOs. Maybe this is a reason for not having them in a table POJO. – Exactitude

Answer 1

This is a bug in the jOOQ code generator:
https://github.com/jOOQ/jOOQ/issues/5103

It appears only in PostgreSQL, when generating POJOs for tables with composite type arrays. I currently don't see a workaround.

Sharlenesharline

Hey Lukas, thanks for the hint. I for now map everything into a json. Requires some extra mapping, but works (and I lose something in performance). A remark on your sentence "While the currently being generated code still works in PostgreSQL, users prefer not to have any jOOQ record dependencies in their generated POJOs.". It didn't work also the mapping from DB to POJO using the DSLContext, because of inability to convert the row into the record (same issue from here) – Exactitude

@JeanValjean: Hmm, that issue is supposed to be resolved. Are you sure it still prevails? Can you perhaps show an example to reproduce it (e.g. as a new question on Stack Overflow, or on the user group) – Sharlenesharline

Yeah, I assumed the same from the comments on the aforementioned link. I'll update the example that I attached to this question and I'll post the stacktrace to the user group you reported – Exactitude

Answer 2

The reason it is doing what it is doing is because a View does not have a PrimaryKey associated with it, at least not with most databases, I can't think of a single one that would report back a PrimaryKey for a view.

You can specify the primary key to the generate using either the <syntheticPrimaryKeys> or you can use <overridePrimaryKeys> as described in the advanced generator configuration section of the manual.

Relevant parts of jooq-meta configuration:

<!-- A regular expression matching all columns that participate in "synthetic" primary keys,
       which should be placed on generated UpdatableRecords, to be used with

        - UpdatableRecord.store()
        - UpdatableRecord.update()
        - UpdatableRecord.delete()
        - UpdatableRecord.refresh()

       Synthetic primary keys will override existing primary keys. -->
  <syntheticPrimaryKeys>SCHEMA\.TABLE\.COLUMN(1|2)</syntheticPrimaryKeys>

<!-- All (UNIQUE) key names that should be used instead of primary keys on
       generated UpdatableRecords, to be used with

        - UpdatableRecord.store()
        - UpdatableRecord.update()
        - UpdatableRecord.delete()
        - UpdatableRecord.refresh()

        If several keys match, a warning is emitted and the first one encountered will be used.

        This flag will also replace synthetic primary keys, if it matches. -->
  <overridePrimaryKeys>MY_UNIQUE_KEY_NAME</overridePrimaryKeys>

Schopenhauerism

How would it be in my example? – Exactitude

your example is an incomplete strawman at this point, you would need to post a more concrete example, but the documentation is pretty clear on what to do to define what you want the primary key to be on the view. – Schopenhauerism

try something and come back with a specific question why my answer did not work. – Schopenhauerism

I did a try. Apart that I now have the @Id annotation on the view's POJO, I don't see any changes with regards to my question. – Exactitude

Perhaps, I'm wondering if this answer may help! – Exactitude

well without providing something more specific than this strawman example with one field you are probably not going to get a specific answer. – Schopenhauerism

I'll make a more specific example, but believe me the number of fields doesn't matter, one, ten or one hundred is the same – Exactitude

I will have time to work on helping you with a solution when I get home from work tonight and I can't get to chat from work either. – Schopenhauerism

I added a link to a tar.gz file containing an example with a schema and the corresponding autogenerated classes – Exactitude

Let us continue this discussion in chat. – Schopenhauerism