JPA @NamedQuery with two tables, possible?

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

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

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'"

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).

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;