CODEWITHSUNDEEP
javajooq
user3626048
user3626048

Reputation: 736

JOOQ get POJO object with another POJO - foreign key in table

I'm new at using JOOQ and I have problem but I can't find solution. I have simple database with 2 tables: Sellers and Clients - sql below:

CREATE TABLE Sellers
(
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(255) NOT NULL,
);


CREATE TABLE Clients
(
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(255) NOT NULL,
seller_id int,
FOREIGN KEY (seller_id) REFERENCES Sellers(id)
);

Client has foreign key and it defines which Seller is assigned to him.

I would like to get clients from database using JOOQ but using join() also get Seller object to each of client. Is it possible? If so how to do that? Here's my POJO objects:

public class Seller {
    private final SimpleIntegerProperty id = new SimpleIntegerProperty();
    private final SimpleStringProperty name = new SimpleStringProperty();

    ...
    //setters and getters here
    ...
}

public class Client {
    private final SimpleIntegerProperty id = new SimpleIntegerProperty();
    private final SimpleStringProperty name = new SimpleStringProperty();
    private final SimpleIntegerProperty sellerId = new SimpleIntegerProperty();
    //private Seller seller; //not working
    ...
    //setters and getters here
    ...
}

And here's my JOOQ code to get clients:

context.select()
    .from(CLIENTS)
    .join(SELLERS)
    .on(CLIENTS.ID.eq(SELLERS.ID))
    .fetchInto(Client.class);

What should I change to get what I want?

Upvotes: 1

Views: 1926

Answers (1)

homerman
homerman

Reputation: 3569

check out the conversation here between Lukas Eder (the author of jOOQ) and some other jOOQ users. Garrett Wilson's use case looks very similar to yours (where you have Client:Seller, he has Book:Author).

there's quite a bit said, but by design it seems jOOQ isn't geared toward automatically hydrating a Seller instance within your fetched Client record. this is the classic N+1 problem associated with ORMs (i.e. multiple queries against your table of sellers triggered by a query for clients).

one proposal is to to break your join up into discrete queries:

select * from Client where ...
select * from Seller where id in (select seller_id from Client where ...)

... and then do your client.setSeller() type logic somewhere in your application. in this case you avoid the N+1 problem, and you don't need to rely on writing custom any RecordMapper types.

i'm no jOOQ expert, but the approach i used in the past when i had columns from hierarchical types being returned in one query worked out pretty well for me:

  1. define a custom record mapper to map your jOOQ Record to your domain type/POJO
  2. define a custom RecordMapperProvider and use it to create DSLContext singleton that gets passed around to whatever components do your querying (e.g. through dependency injection)
  3. using the aforementioned DSLContext, compose your query and use fetchInto() to designate your target type (which should have been accounted for in your RecordMapperProvider)

in the conversation referenced earlier, Lukas drops a link to the documentation about using custom RecordMapperProvider instances, so this might very well be idiomatic.

hope that helps.

Upvotes: 1

Related Questions