没有任何关联的Hibernate Criteria API连接实体 [英] Hibernate Criteria API join entities without any association

问题描述

我正在尝试编写与以下SQL查询等效的Hibernate Criteria API：

I'm trying to write a Hibernate Criteria API equivalent of the following SQL query:

select c.NAME         as carName,
   cc.COLOR_CODE      as colorCode,
   cc.COLOR           as color,
   c.DESCRIPTION      as desc,
   c.MANUFACTURE_YEAR as year
from CAR c
     LEFT JOIN CAR_COLOR CC on c.COLOR_CODE_ID = CC.ID
WHERE CC.COLOR_CODE = ?

这是我的 Car 实体

public class Car extends BaseEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String name;

    @Basic
    @Column(name = "MANUFACTURE_YEAR")
    private String year;

    @Basic
    @Column(name = "DESCRIPTION")
    private String desc;
    private Long colorCodeId;
    private String manufacturer;
}

这是我的 CarColor 实体

public class CarColor extends BaseEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String color;
    private String colorCode;
}

Car.colorCodeId 是引用 CarColor.id 的外键。尽管没有明显的实体级关系，并且我无法添加任何关系。

Car.colorCodeId is a foreign key which references CarColor.id. Although there is no apparent entity-level relationship and I cannot add any relationships.

我想在这两个表/实体之间进行左连接，并将结果映射到 CarSearch ？我该怎么做？非常感激。

I want to do a left join between these two tables/entities and map the result to CarSearch? How would I do this? Much appreciated.

public class CarSearch {
    private String carName;
    private String colorCode;
    private String color;
    private String desc;
    private String year;
}

推荐答案

可以使用笛卡尔积

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);
Root<Car> car = query.from(Car.class);
Root<CarColor> carColor = query.from(CarColor.class);

Predicate joinPredicate = cb.equal(car.get("colorCodeId"), carColor.get("id"));

query.multiselect(
    car.get("name"),
    carColor.get("colorCode"),
    carColor.get("color"),
    car.get("desc"),
    car.get("year")
).where(
    joinPredicate, 
    cb.equal(carColor.get("colorCode"), "yourColorCode")
);

要避免在查询结果中使用 Object [] 您应使用适当的构造函数创建 QueryResultDto 类，并使用这种方式

To avoid Object[] in query result you should create QueryResultDto class with appropriate constructor and use this way

CriteriaQuery<QueryResultDto> query = cb.createQuery(QueryResultDto.class);
//...
query.select(cb.construct(QueryResultDto.class, 
    car.get("name"),
    carColor.get("colorCode"),
    carColor.get("color"),
    car.get("desc"),
    car.get("year")
));

这篇关于没有任何关联的Hibernate Criteria API连接实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！