JPA:当在JPQL中使用构造函数表达式来包装DTO中的奇异实体时，绕过N+1

Question

首先，我知道上的类似问题(用JPA/Hibernate构造函数表达式防止"n+1选择"？(，但我的情况不同，其他问题没有足够的答案。

我注意到，当我使用Hibernate作为我的JPA提供程序执行JPQL查询时，并且我试图通过使用构造函数表达式将实体包装在DTO中，Hibernate似乎只选择实体的ID，这将导致N+1查询以获取其余属性。

休眠版本：5.4.21最终

查询：

select new com.example.DistanceResult(p, distance(:center, p.location)) from Place p where dwithin(:center, p.location, :radiusMeters) = true

查询日志：

select place1_.id as col_0_0_, st_distance(?, place0_.location) as col_1_0_ from places place0_ inner join places place1_ on (place0_.id=place1_.id) where st_dwithin(?, place0_.location, ?)=true
binding parameter [1] as [OTHER] - [POINT (10.90943 48.37102)]
binding parameter [2] as [OTHER] - [POINT (10.90943 48.37102)]
binding parameter [3] as [DOUBLE] - [2000.0]
select place0_.id as id1_0_0_, place0_.location as location2_0_0_, place0_.name as name3_0_0_ from places place0_ where place0_.id=?
binding parameter [1] as [BIGINT] - [3]
select place0_.id as id1_0_0_, place0_.location as location2_0_0_, place0_.name as name3_0_0_ from places place0_ where place0_.id=?
binding parameter [1] as [BIGINT] - [4]

正如你所看到的，我正试图做一个"；位于地理点周围的实体"；使用hibernate spatial的查询类型，但我认为这与问题无关。如何告诉hibernate选择整个实体，然后将其包装到DTO中？我已经尝试过进行获取自加入，但这没有帮助。

Java类：(使用Lombok(

@Value
public class DistanceResult {
Place place;
Double distanceMeters;
}

@NoArgsConstructor
@Getter
@Setter
@ToString
@Entity
@Table(name = "PLACES")
public class Place extends BaseEntity<Long> {
public Place(String name, Point location) {
this.name = name;
this.location = location;
}
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private Point location;
}

BaseEntity相当于弹簧数据jpa中的AbstractPersistable，没有ID定义。

Answer 1

我找到了一个解决方案，但不确定这是否是最好的方法。我没有在JPQL中使用构造函数表达式，而是使用了(特定于休眠的(ResultTransformer。

代码：

public List<DistanceResult> findInDistance(Point center, double radiusMeters) {
String jpql = "select p, distance(:center, p.location) from Place p where dwithin(:center, p.location, :radiusMeters) = true";
return (List<DistanceResult>) entityManager.createQuery(jpql)
.setParameter("center", center)
.setParameter("radiusMeters", radiusMeters)
.unwrap(Query.class)
.setResultTransformer(
(ListResultTransformer)
(tuple, aliases) -> new DistanceResult(
(Place) tuple[0],
((Number) tuple[1]).doubleValue()
)
).getResultList();
}

小心：ListResultTransformer是Vlad Mihalcea的休眠类型库的一部分。(另请参见：https://vladmihalcea.com/hibernate-resulttransformer/)

查询日志：

select place0_.id as col_0_0_, st_distance(?, place0_.location) as col_1_0_, place0_.id as id1_0_, place0_.location as location2_0_, place0_.name as name3_0_ from places place0_ where st_dwithin(?, place0_.location, ?)=true
binding parameter [1] as [OTHER] - [POINT (10.90943 48.37102)]
binding parameter [2] as [OTHER] - [POINT (10.90943 48.37102)]
binding parameter [3] as [DOUBLE] - [2000.0]