CODEWITHSUNDEEP
javahibernatejpah2
gordon
gordon

Reputation: 834

JPA select entity and use subquery to assign value in entity all within a single query

I'm having some trouble with a JPA query. I should tell you now, I'm not an expert in JPA at all.

I have two classes, Line and LineData.

The relationship between Line and LineData is that a Line can potentially have 100s of LineData objects associated to it. The reason I haven't explicitly joined them is because most of the time when I retrieve a Line entity, I don't need the LineData (and seeing as there could be 100s, it would be a waste to load them all and then not use them).

However, what I would like to be able to do, when retrieving a Line entity, is get the maximum LineData.value1 value as well and return that as a part of the Line entity, as Line.maxVal1 . I've tried doing this with the following query:

SELECT l, l.maxVal1 = (SELECT MAX(d.value1) FROM LineData d WHERE d.name = l.name)
FROM Line l
WHERE l.name = example123
ORDER BY l.name ASC

But Line.maxVal1 is coming back as null. I have tried the subquery on its own and it does return an actual value. Is it possibly to do what I'm trying here? If so, how?

Here are simplified versions of my classes:

public class Line {
    @Column(nullable = false, unique = true)
    public String name;

    @Column
    public boolean active;

    @Transient
    public Integer maxVal1;
}

public class LineData {
    @Column(nullable = false)
    public String name;
    @Column
    public Integer value1;
    @Column
    public Integer value2;
    @Column
    public Integer value3;
    ...
    @Column
    public Integer activeN;
}

EDIT Here's an example of the what the database values would look like:

Line
name       | active
======================
example123 | true
example234 | false
example345 | true

LineData
name       | value1 | value2 | ... | valueN
============================================
example123 | 1      | 2      |     | 1
example123 | 3      | 2      |     | 1
example345 | 1      | 2      |     | 1
example123 | 14     | 2      |     | 1
example123 | 2      | 2      |     | 1
example123 | 2      | 2      |     | 1
example123 | 3      | 2      |     | 1

And the result of the query returning a Line object, with the values:

name       | active | maxVal1
==============================
example123 | true   | 14

Upvotes: 0

Views: 3960

Answers (1)

drkunibar
drkunibar

Reputation: 1337

If you use hibernate you can try @Formula

public class Line {
    @Column(nullable = false, unique = true)
    public String name;

    @Column
    public boolean active;

    @Formula("(SELECT MAX(d.value1) FROM LineData d WHERE d.name = name)")
    public Integer maxVal1;
}

The @Formula contains native SQL - no HQL

Upvotes: 1

Related Questions