Possible support for JPA @Table and @Column(table = "") annotations in jOOQ

[Eugeny Karpov]

Hello. I've been thinking about possible support for @Table and @Column(table = "") annotations in jOOQ. At my work we could use this feature since it's often necessary to join many tables in a query and fetch several fields from different tables with the same naming. Currently we name conflicting columns in a query with different aliases, but for me it's a quite dirty solution (personally I don't like aliases :) ). 

I've written some code in my fork and made use for @Column(table = "tableName", name = "id"), but I've faced with a problem that if developer doesn't specify table property for one of conflicting fields in POJO, then the result of mapping is kinda unpredictable. It's not a problem in JPA implementations because there is always 'default' table - a table of the entity. But it's not a case for jOOQ where there is no any 'default' tables for POJOs.

Some examples:

public class User {

    @Column(name = "id", table = "user")

    private Long id;

    @Column(name = "id", table = "category")

    private Long secondId;

}

In this example mapping is good - id will be from USER table and secondId from CATEGORY table.

But in this example (developer accidentally forgot to specify table for the first conflicting field) mapping result is kinda unpredictable for the ID field:

public class User {

    @Column(name = "id")

    private Long id;

    @Column(name = "id", table = "category")

    private Long secondId;

}

Behavior in this example will be just like in current version of jOOQ with conflicting column names. 

So here is a question: is correct using of @Table and @Column(table = "") annotations even possible in jOOQ since there is no mandatory entities with default tables? 

[Lukas Eder]

Hi Eugeny,

Thank you very much for your message. I will comment inline

2017-04-22 18:17 GMT+02:00 Eugeny Karpov <michmanh...@gmail.com>:

Hello. I've been thinking about possible support for @Table and @Column(table = "") annotations in jOOQ. At my work we could use this feature since it's often necessary to join many tables in a query and fetch several fields from different tables with the same naming. Currently we name conflicting columns in a query with different aliases, but for me it's a quite dirty solution (personally I don't like aliases :) ). 

Au contraire, it's actually the perfect solution if you're interested in an academic argument. :)

Everyone who prefers relational algebra over SQL will agree that there is no reason why we should have "duplicate attribute names" in a relation for attributes that do not have the same meaning. This is why in relational algebra, the rename operation is so important:

https://en.wikipedia.org/wiki/Relational_algebra#Rename_.28.CF.81.29

"Unfortunately", SQL allows for duplicate column names in top-level selects (not in nested selects!).

So, I know what you mean. Renaming all "accidentally" ambiguous column names to make them non-ambiguous is impractical and tedious in SQL for those simple queries where column qualification is still available (beware: don't be fooled into believing that this is straight-forward for a database. As soon as your query becomes mildly complex, this qualification will be lost!)

 

I've written some code in my fork and made use for @Column(table = "tableName", name = "id"), but I've faced with a problem that if developer doesn't specify table property for one of conflicting fields in POJO, then the result of mapping is kinda unpredictable. It's not a problem in JPA implementations because there is always 'default' table - a table of the entity. But it's not a case for jOOQ where there is no any 'default' tables for POJOs.

Some examples:

public class User {

    @Column(name = "id", table = "user")

    private Long id;

    @Column(name = "id", table = "category")

    private Long secondId;

}

In this example mapping is good - id will be from USER table and secondId from CATEGORY table.

But in this example (developer accidentally forgot to specify table for the first conflicting field) mapping result is kinda unpredictable for the ID field:

public class User {

    @Column(name = "id")

    private Long id;

    @Column(name = "id", table = "category")

    private Long secondId;

}

Behavior in this example will be just like in current version of jOOQ with conflicting column names. 

So here is a question: is correct using of @Table and @Column(table = "") annotations even possible in jOOQ since there is no mandatory entities with default tables? 

Absolutely, it is even desireable! We have a pending feature request for this:

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

So, if you already have a working solution, we'd love to see your contribution! Here are our contribution guidelines:

https://github.com/jOOQ/jOOQ/blob/master/CONTRIBUTING.md

Let me know if you need any help.

Thanks,

Lukas

[Eugeny Karpov]

Hi Lukas. Thanks for the detailed answer and clarification. 

I'll make a PR when I'll finish the code.