@SqlResultSetMapping binding inner class to targetclass

Question

In my project, I have created a view that contains multiple joins. The following is part of the query from the view.

a.id,
b.object1,
b.object2,
b.object3,
case when (c.type = 'qrt' then c.object4 else b.object4) end object4, 
case when(c.type = 'qrt' then c.object5 else b.object5) end object5 
from A a
left join B b on b.a_id = a.id
left join c c on c.b_id = b.id

This query will return the data as follows:

id   object1    object2    object3    object4     object5
1    a1         b1         c1         d1          e1
1    a1         b1         c1         d2          e2
2    a2         b2         c2         d3          e3
2    a2         b2         c2         d4          d5

I want to map the result with the following POJO class

public class Example{

private Integer id;
private String object1;
private String object2;
private String object3;
List<InnerExample> innerExample;
}


public class InnerExample{
private String object4;
private String object5;
}

In UI data will be displayed in a table with pagination like below.

table, th, td {
  border: 1px solid black;
  border-collapse: collapse;
}

<table >
<tr>
<td rowspan = 2>id</td>
<td rowspan = 2>name</td>
<td rowspan = 2>object1</td>
<td rowspan = 2>object2</td>
<td rowspan = 2>object3</td>
<td>object4</td>
<td>object5</td>
</tr>
<tr>
<td>object4</td>
<td>object5</td>
</tr>
<tr>
<td rowspan = 2>id</td>
<td rowspan = 2>name</td>
<td rowspan = 2>object1</td>
<td rowspan = 2>object2</td>
<td rowspan = 2>object3</td>
<td>object4</td>
<td>object5</td>
</tr>
<tr>
<td>object4</td>
<td>object5</td>
</tr>
</table>

I am using Spring JPA and I didn't find an approach to directly map the result to the Example POJO.

Can anyone please help me...

Since your POJO's are not entities, I think you have to do the mapping manually, tough it is tedious. And requires 3 comparators. Even with projection, the same is true. — Aman, Nov 02 '20 at 15:57

Kaushik · Answer 1 · 2020-11-02T11:34:24.370

0

If you are using Spring boot JPA you can directly convert your query results into a class from any of the repository classes that you have written.

@Repository public interface BRepository extends JpaRepository<BEntity, Integer> {

@Query("SELECT new path.from.src.folder.Example(" + 
       "a.id, b.object1,b.object2, b.object3, " + 
       "new path.from.src.folder.Example.InnerExample(" +
       "case when(c.type='qrt' then c.object4 else b.object4) end object4,"+ 
       "case when(c.type='qrt' then c.object5 else b.object5) end object5" + 
       "))" + 
       "from AEntity as a " + 
       "left join BEntity as b on b.a_id = a.id " + 
       "left join c c on c.b_id = b.id ")
Example customQueryJoiningAandBandCtable();

}

Also, add the following constructors in the Example and InnerExample class.

public Example(Integer id, String object1, String object2, String object3, InnerExample innerExample) {
    this.id = id;
    this.object1 = object1;
    this.object2 = object2;
    this.object3 = object3;
    this.innerExample = innerExample;   }

public InnerExample(String object4, String object5) {
    this.object4 = object4;
    this.object5 = object5;   
}

Please keep in mind in here I have used List instead of InnerExample as I am quite unsure how the query is working and is giving list output. But you can modify accordingly.

edited Nov 02 '20 at 11:34

answered Nov 02 '20 at 11:14

Kaushik

108
5

I need List and I can't use JPA repository class. I have to do it with NativeSQLQuery and using EntityManager. If I have to use just an object I can do that but not sure about List. – Charvee Shah Nov 02 '20 at 11:33
Sorry, but I am still unable to understand the query. -------- case when (c.type = 'qrt' then c.object4 else b.object4) end object4, case when(c.type = 'qrt' then c.object5 else b.object5) end object5 -------------- This will just return InnerExample not a List. How is List coming into picture? The only thing I can think of is the whole query returning List instead of Example. – Kaushik Nov 02 '20 at 11:38
I edited a question with the SQL query result. Hope this will help to understand the question. – Charvee Shah Nov 02 '20 at 15:32
This is what I was talking about. If our query result is like this and then SQL will treat this as List it will not treat results with the same id in a single answer. Think of it this way, you can cast each row of the answer into a POJO. If you still need InnerExample as a list then either you need to change the query or modify results after the query is executed. – Kaushik Nov 02 '20 at 17:14
Yes, I agree, I have to do it manually but I was thinking if anyone has customized the @SqlResultSetMapping, so I can take help from them. – Charvee Shah Nov 03 '20 at 08:19

@SqlResultSetMapping binding inner class to targetclass

1 Answers1