如何对无对应实体类的自动生成表执行原生SQL查询？

应用中有两个实体类Resident和Vehicle，代码如下：

@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;

    ...
}   

这在PostgreSQL数据库中生成了3张表：

resident
vehicle
resident_vehicles

其中resident_vehicles是Hibernate自动生成的多对多中间表，代码库中无对应实体类。需对该表执行复杂且运行时动态生成的原生SQL查询，简化示例如下：

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

尝试使用如下Java代码查询：

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

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

但出现错误：

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

错误原因 #

entityManager.createQuery()默认执行HQL（Hibernate查询语言），HQL面向实体类，仅能识别有对应实体映射的表。而resident_vehicles是自动生成的中间表，无对应实体类，因此Hibernate无法解析该表名。

方法1：使用createNativeQuery()执行原生SQL #

直接调用createNativeQuery()方法执行原生SQL，该方法支持直接操作数据库表：

// 创建原生SQL查询
Query query = entityManager.createNativeQuery("select vehicle_id from resident_vehicles where vehicle_id = :vehicleId");
// 参数绑定避免SQL注入
query.setParameter("vehicleId", "<some-id>");
// 获取查询结果
List result = query.getResultList();

注意：必须用参数绑定（:vehicleId + setParameter()），禁止直接将ID拼入SQL字符串，防止SQL注入攻击。

方法2：（可选）为中间表创建实体类 #

若需频繁操作该中间表，可手动创建对应实体类，之后即可用HQL查询：

@Entity
@Table(name = "resident_vehicles")
public class ResidentVehicle {
    // 复合主键，需同时标注两个字段为@Id
    @Id
    @Column(name = "resident_id")
    private String residentId;
    
    @Id
    @Column(name = "vehicle_id")
    private String vehicleId;

    // 构造器、getter、setter方法
    public ResidentVehicle() {}
    
    public ResidentVehicle(String residentId, String vehicleId) {
        this.residentId = residentId;
        this.vehicleId = vehicleId;
    }
    
    // getter和setter省略
}

创建实体类后，即可用HQL查询：

Query query = entityManager.createQuery("select rv.vehicleId from ResidentVehicle rv where rv.vehicleId = :vehicleId");
query.setParameter("vehicleId", "<some-id>");
List result = query.getResultList();

内容的提问来源于stack exchange，提问作者BlazinglyFast