How to perform a Native query on a table which is not present as a class?

Question

I have two Tables in my Application - Resident and Vehicle, which look like this:

@Entity
@Table(name = "resident")
public class Resident {

    @Id                     // PKey
    private String id;

    private Vehicle primaryVehicle;

    @ManyToMany
    private List<Vehicle> vehicles; 

    ...
}   

@Entity
@Table(name = "vehicle")
public class Vehicle {

    @Id                     // PKey
    private String id;

    private String model;

    ...
}   

This has caused 3 tables created in my Postgres database:

resident
vehicle
resident_vehicles

So this extra table resident_vehicles has been created automatically, there is no class for that table in my codebase.

I have a complex query which requires me to call this table using native query. A simplified query for example would be:

select vehicle_id from vehicle_currencies where vehicle_id = '<some-id>'

Please note that this query is a simplified version of what I want to do. My SQL queries are more complex, and most importantly dynamically generated at runtime. Using jpa repository methods are very difficult with the dynamic behaviour I have.

I am using Java's Native query for my purpose, like this:

Query query = entityManager.createQuery("select vehicle_id from vehicle_currencies where vehicle_id = '<some-id>'

List result = query.getResultList();
...

However, this is causing the error:

org.hibernate.hql.internal.ast.QuerySyntaxException: resident_vehicles is not mapped
    at org.hibernate.hql.internal.ast.util.SessionFactoryHelper.requireClassPersister(SessionFactoryHelper.java:170) ~[hibernate-core-5.6.14.Final.jar:5.6.14.Final]
    at org.hibernate.hql.internal.ast.tree.FromElementFactory.addFromElement(FromElementFactory.java:91) ~[hibernate-core-5.6.14.Final.jar:5.6.14.Final]
    at org.hibernate.hql.internal.ast.tree.FromClause.addFromElement(FromClause.java:77) ~[hibernate-core-5.6.14.Final.jar:5.6.14.Final]
    at org.hibernate.hql.internal.ast.HqlSqlWalker.createFromElement(HqlSqlWalker.java:334) ~[hibernate-core-5.6.14.Final.jar:5.6.14.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3782) ~[hibernate-core-5.6.14.Final.jar:5.6.14.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:3671) ~[hibernate-core-5.6.14.Final.jar:5.6.14.Final]
...

How do I solve this error?

Answer 1

The issue is with this line

Query query = entityManager.createQuery("select vehicle_id from vehicle_currencies where vehicle_id = '<some-id>');

You are trying to run a native query and since you don't have any mapping for the intermediate table this is the only way. But when we want to run a native query we should use the createNativeQuery method inside the EntityManager.

The createQuery method is used for HQL queries require mappings for all the specified tables and that's why you got this issue

org.hibernate.hql.internal.ast.QuerySyntaxException: resident_vehicles is not mapped

Links you can refer for detailed explanations

1. JPA's EntityManager createQuery() vs createNamedQuery() vs createNativeQuery()
2. Why do we need to create native query?
3. use of entityManager.createNativeQuery(query,foo.class)