How to map jooq result to their respective entities

Question

I have this SQL query:

select question.*, 
  question_option.id 
from question 
left join question_option on question_option.question_id = question.id;

How do I map the result obtained to the entity. so that the expected result should be like Can anyone give the sample code for getting the result as above

{
"id": 2655,
"type": "MCQSingleCorrect",
"difficultyLevel": "Advanced",
"question": "Which country are you from?",
"answer": null,
"marks": 1.5,
"negativeMarks": 0.5,
"hint": null,
"explanation": null,
"booleanAnswer": null,
"passage": null,
"isPassageQuestion": null,
"audioFile": null,
"videoFile": null,
"questionFiles": [],
"tags": [],
"updatedAt": "2021-12-21T11:57:03.229136Z",
"createdAt": "2021-12-21T11:57:03.229098Z",
"questionOptions": [
    {
        "id": 2719,
        "option": "India",
        "index": 1,
        "correct": false,
        "blank": null
    },
    {
        "id": 2720,
        "option": "Newzealand",
        "index": 1,
        "correct": false,
        "blank": null
    },
    {
        "id": 2721,
        "option": "England",
        "index": 1,
        "correct": true,
        "blank": null
    },
    {
        "id": 2722,
        "option": "Australia",
        "index": 1,
        "correct": false,
        "blank": null
    }
]}

I don't understand your question. Do you want that JSON as the result or JPA Entities? — Simon Martinelli, Dec 21 '21 at 15:03
I want it in the JPA entity. If possible can you say for JSON too? — Sai Uttej, Dec 21 '21 at 15:38
Why do you need a JPA entity as an intermediate format to produce JSON? It would be much simpler to use jOOQ to produce JSON directly... — Lukas Eder, Dec 21 '21 at 16:24
I want JPA entity because after getting the query result some times I need to do some more extra operations on the data. But can you post the code to get the Jason result directly from the jooq itself in the answer section. — Sai Uttej, Dec 22 '21 at 01:52

score 0 · Answer 1 · answered Dec 21 '21 at 16:11

0

You can write the query with jOOQ and the do this:

Query result = em.createNativeQuery(query.getSQL());
query.getResultList() // or query.getSingleResult() depending what you need.

Read more here: https://www.jooq.org/doc/3.15/manual/sql-execution/alternative-execution-models/using-jooq-with-jpa/using-jooq-with-jpa-native/

JSON can be fetched directly using SQL (and also jOOQ). Here are some examples: https://72.services/use-the-power-of-your-database-xml-and-json/

answered Dec 21 '21 at 16:11

Simon Martinelli

34,053
5
48
82

I have tried the way you said using 'em'. I got duplicate entities with the same question repeating 4 times – Sai Uttej Dec 21 '21 at 18:10
Then your SQL query returns duplicates. You can remove the join to question_option because this will be done by JPA – Simon Martinelli Dec 22 '21 at 06:28
For now, it's okay if it auto loads the join. but, if I don't need the join and only need the question entity. So, what should I do so that it joins only if I want and do not if I don't need it? – Sai Uttej Dec 22 '21 at 16:06
You have to write two queries. – Simon Martinelli Dec 22 '21 at 16:13

score 0 · Accepted Answer · answered Jan 03 '22 at 07:29

I'm answering from the perspective of our comments discussion, where I suggested you don't need JPA in the middle, because you can do every mapping / projection with jOOQ directly. In this case, if you're targeting a JSON client, why not just use SQL/JSON, for example? Rather than joining, you nest your collection like this:

ctx.select(jsonObject(
      key("id", QUESTION.ID),
      key("type", QUESTION.TYPE),
      ..
      key("questionOptions", jsonArrayAgg(jsonObject(
        key("id", QUESTION_OPTION.ID),
        key("option", QUESTION_OPTION.OPTION),
        ..
      )))
    ))
   .from(QUESTION)
   .leftJoin(QUESTION_OPTION)
   .on(QUESTION_OPTION.QUESTION_ID.eq(QUESTION.ID))
   // Assuming you have a primary key here.
   // Otherwise, add also the other QUESTION columns to the GROUP BY clause
   .groupBy(QUESTION.ID)
   .fetch();

This will produce a NULL JSON array if a question doesn't have any options. You can coalesce() it to an empty array, if needed. There are other ways to achieve the same thing, you could also use MULTISET if you don't actually need JSON, but just some hierarchy of Java objects.

As a rule of thumb, you hardly ever need JPA in your code when you're using jOOQ, except if you really rely on JPA's object graph persistence features.

How to map jooq result to their respective entities

2 Answers2