CODEWITHSUNDEEP
sqljooq
Stefan Falk
Stefan Falk

Reputation: 25487

How to fetch joined table results in jOOQ?

I am having one little special case in my database where I need to fetch data from two different tables, which have a one-to-one relationship, in order to get a useful result. The fetch looks something like this:

SELECT 
  time_period.from_day, time_period.to_day, store_time_period.valid_for_days 
FROM time_period, store_time_period 
WHERE 
  -- .. condition

Of course I am using JOIN in my actual code - this is just an example. My question is if and how I can write the fetched data in jOOQ into a POJO. I am not sure if there is a way to let jOOQ generated this POJO for me, but if I have to write it myself I assume that I'd need something like an adapter as well like org.jooq.Converter as a converter for data types.

I see that there is the possibility of a RecordMapperProvider but this only seems to handle single, already known tables.

So, what is the best way to accomplish something like this with jOOQ?

Upvotes: 4

Views: 1307

Answers (1)

Bampfer
Bampfer

Reputation: 2220

You can fetch into a (non-generated) POJO without needing an adapter or converter. See this page from the jOOQ online documentation for examples.

jOOQ will map columns from the query to POJO fields based on their names. The names don't have to be exact matches to the column names; DefaultRecordMapper docs have some examples that show how underscores and capitalization are ignored.

In your case, you are creating the POJO specifically to fetch these results. So you can name the POJO fields the same as the query columns. But if the names have to be different you can use JPA annotations to tell jOOQ which column to put in each field:

@Column(name = "TITLE")
public String myTitle;

(If that's still not good enough there are at least two other ways to do it: you can use a constructor, or fetch the results into a HashMap instead.)

Upvotes: 1

Related Questions