JPA Error joining table and view

Question

I need to join a table and a view in a JPA query. The query won't compile because the view columns can't be identified.

Any suggestions are greatly appreciated.

Updated with parent entity and consistent naming

The query is:

select count(m.id) 
    from MultiSpeedMotor m, 
        MultiSpeedQuery q1  
    where m.id = q1.motorId 
        and q1.power = 10

The errors are:

The state field path 'q1.motorId' cannot be resolved to a valid type.
The state field path 'q1.power' cannot be resolved to a valid type.

I am working with a legacy database that has a denormalized table similar to this

Long motorId 
Long id  
Double hi_power 
Double lo_power

I have used a view with a union query to normalize this table into

Long motorId  
Long id 
Long hi 
Double power

To model the view of union query in JPA, I have used an @IdClass

public class MultiSpeedQueryId implements Serializable {
    private static final long serialVersionUID = -7996931190943239257L;

    private Long motorId;
    private Long id;
    private Long hi;
    ...
}

@Entity
@Table(name = "multi_speed_query")
@IdClass(MultiSpeedQueryId.class)
public class MultiSpeedQuery implements IMultiSpeedQuery {
    @Id
    @Column(name = "motor_id")
    private Long motorId;
    @Id
    private Long id;
    @Id
    private Long hi;

    private Double power;
    ...
}

The parent Entity is mapped as:

@Entity
@Table(name = "multi_speed_motor")
public class MultiSpeedMotor implements Serializable, IMultiSpeedMotor {
    private static final long serialVersionUID = 3019928176257499187L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    ...
}

I'm not sure, but maybe the following works: `... WHERE q1.motor = m ...` and replace `MultiSpeedQuery.motorId` with the actual `MultiSpeedMotor motor`. — jabu.10245, Nov 27 '15 at 13:58
I'm not sure what you are suggesting. The q1 and m are standard JPA alias. — Timothy Vogel, Nov 28 '15 at 11:39
According to your table def there while there is a parentId column, there is no motorId .. does this actually exist in the db ? If so the actual table definitions for both tables would be helpful — James Gawron, Nov 30 '15 at 22:08
I updated the original post to use the actual field name motorId in the explanation instead of parentId. I used parentId originally in the explanation to better indicate the role. — Timothy Vogel, Dec 01 '15 at 00:13

score 1 · Accepted Answer · answered Dec 05 '15 at 19:07

The query is correct as written.

You CAN join Entities with no pre-defined relationship by using the syntax.

where a.id = b.joinField

The issue was much simpler. I missed part of the JPA error log that was telling the real problem.

The abstract schema type 'MultiSpeedQuery' is unknown.

Once I added the Entity to the persistence.xml, the query, as originally written, worked perfectly.

JPA Error joining table and view

1 Answers1