1

Let's say I have these tables:

TABLE A

col_a |
-----
a1
....

TABLE B

col_b | col_c | amount
---------------------
a1    | b1    | 2.2
a1    | b2    | 4.5

TABLE C

col_d
----
b1 
b2
....

All these tables have associated models within Java.

Now what I would like is the following:

col_a | b2 |  ....
------------
a1    | 4.5 |(from tableB.amount)
.....

The query I have now is this; 

SELECT a.col_a,
  SUM(CASE WHEN c.col_d = 'b1' THEN b.amount end) AS 'Kcal',
  SUM(CASE WHEN c.col_d = 'b2' THEN b.amount end) AS 'Kjoule',
  .... etc
    FROM tableA a
    LEFT JOIN tableB b ON b.col_b = a.col_a
    LEFT JOIN tableC c ON b.col_c = c.col_d
      GROUP BY a.col_a;

which results in something like this:

col_a | Kcal | Kjoule
---------------------
a1    | amount | amount

This ResulSet is exactly what I need, but I can't map this to my models in Java. How can I use this ResultSet to model it in Java, or should I use a different approach?

Mees Kluivers
  • 520
  • 2
  • 6
  • 26
  • Hi Mees refer to below link may be helpful. [https://stackoverflow.com/questions/13012584/jpa-how-to-convert-a-native-query-result-set-to-pojo-class-collection](https://stackoverflow.com/questions/13012584/jpa-how-to-convert-a-native-query-result-set-to-pojo-class-collection) – Md Shareef Oct 10 '17 at 09:37
  • @MdShareef I'm not using JPA for this project. – Mees Kluivers Oct 10 '17 at 09:48
  • You have to do mapping from ResultSet to JAVA object by using getXXX() functions in ResultSet – Tuco Oct 10 '17 at 10:05
  • @Tuco How could I keep the relations they have in the above result if I do that? – Mees Kluivers Oct 10 '17 at 10:10

1 Answers1

0

You can create another entity (model) with your fields, something like:

public class Result{
  private String col;
  private BigDecimal kcal;
  private BigDecimal kjoule;
  ...
  getters and setters
}
NikNik
  • 2,191
  • 2
  • 15
  • 34