JPA @NamedQuery with two tables, possible?

Question

I'm having a kind of dummy problem, I need to make a @NamedQuery with a join with other table, some simple thing.

But in all my @NamedQuery I'm only dealing with my mapped Object/Table.

For example in my Object/Table Mapped cars:

@NamedQuery(name = Cars.GET_AVAILABLE_CARS, 
    query = "select c.id from Cars c where c.status = (select d.status from CarStatus d where d.color=red)")

I'm trying to use: @SecondaryTables but no success for now.

One other thing that is working is give all things from other table as a parameter, but I don't think this will be good in performance.

Like:

@NamedQuery(name = Cars.GET_AVAILABLE_CARS, query = 
     "select c.id from Cars c where c.status = :" + CarStatusParam)

Any tips?

Thanks in advance

Unless you can map your object to the second table using either inheritance, @SecondaryTable or a mapping (@OneToOne,...) then you will have to use a @NamedNativeQuery. In order to help you further I would need to know more about the table structure and also what JPA provider you are using (as an example EclipseLink have extensions that could accomplish this and it allows you to use a stored procedures (@NamedStoredProcedureQuery)). — Lars Tackmann, Dec 23 '09 at 09:00
What's the problem with the query? How are Cars and CarSatus defined and mapped? `d.color=red` is invalid. `d.color = 'red'` might be correct. — JB Nizet, Jul 26 '13 at 06:47

score 2 · Answer 1 · answered Dec 22 '09 at 14:12

2

A named query can use everything that an API query can use, so can clearly do subqueries. Which implementation of JPA ?

answered Dec 22 '09 at 14:12

DataNucleus

15,497
3
32
37

score 1 · Answer 2 · answered Aug 26 '12 at 04:20

I guess that you have something like this:

@Entity
public class Cars{
    private String status;
    //... something else
}

@Entity
public class CarStatus{
    private String status;
    private String color;
    //... something else
}

if so, change this

@Entity
public class Cars{
    private CarStatus status;  //<--THIS!!
    //... something else
}

@Entity
public class CarStatus{
    private String color;
    //... something else
}

and then update your NamedQuery to this:

query ="select c.id from Cars c where c.status.color = 'red'"

If I am wrong and the tables should not be related that way, then you should change your query tu use a join instead of a subquery. Something like this:

query = "select c.id from Cars c join CarStatus d where c.status = d.status and d.color = 'red'"

score 1 · Answer 3 · edited Oct 22 '15 at 17:38

1

What you are trying to do is not a join. It is a subselect. And in terms of performance it is as (in)efficient as getting the param beforehand.

If you insist, however, to use the subselect, the JPA Query Language support it. As it supports joins. Take a look here (at ~~7.11~~ 8.11 . Subqueries).

edited Oct 22 '15 at 17:38

Coder-guy

414
6
16

answered Dec 23 '09 at 17:24

Bozho

588,226
146
1,060
1,140

score -1 · Answer 4 · answered Dec 13 '18 at 00:25

The answer is yes it's possible. You need to make sure your columns define which table to look in. See the below code, you named query should work after that addition.

@Column(table = "SECONDARY_TABLE", name = "EXAMPLE_COLUMN_NAME") private String example;

JPA @NamedQuery with two tables, possible?

4 Answers4