>我创建了这两个实体来演示我的问题:
所有者实体.java:
@Entity
public class OwnerEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Size(min = 1)
@OneToMany(mappedBy = "ownerEntity", cascade = CascadeType.ALL)
private Set<ChildEntity> childEntities = new HashSet<>();
}
子实体.java:
@Entity
public class ChildEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
@NotNull
@ManyToOne(optional = false)
private OwnerEntity ownerEntity;
public ChildEntity() {
}
public Long getId() {
return id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public OwnerEntity getOwnerEntity() {
return ownerEntity;
}
public void setOwnerEntity(OwnerEntity ownerEntity) {
this.ownerEntity = ownerEntity;
}
}
我想编写一个查询,该查询将显示来自所有者实体和一个加入的子实体的信息。我创建了一个投影:
所有者实体投影.java:
public interface OwnerEntityProjection {
Long getId();
String getName();
}
我的 Jpa存储库:
public interface OwnerEntityRepository extends JpaRepository<OwnerEntity, Long> {
@Query(" SELECT " +
" ownerEntity.id AS id, " +
" childEntities.name AS name " +
" FROM OwnerEntity ownerEntity " +
" JOIN ownerEntity.childEntities childEntities ")
// There must be also WHERE clause, but for demonstration it is omitted
Slice<OwnerEntityProjection> findAllPaginated(Pageable pageRequest);
}
现在,当我运行这个简单的测试时:
@Test
public void findAllPaginatedTest() {
Pageable pageRequest = new PageRequest(0, 3, Sort.Direction.ASC, "name");
Slice<OwnerEntityProjection> OwnerEntityProjectionsPaginated =
ownerEntityRepository.findAllPaginated(pageRequest);
}
我收到以下错误:
org.hibernate.QueryException: could not resolve property: name of: com.example.domain.OwnerEntity
我还检查了日志中生成的 JQPL:
... order by ownerEntity.name asc
如您所见,Spring Data Jpa 从 FROM 子句附加了第一个实体别名。我发现如果我将PageRequest更改为以下内容:
new PageRequest(0, 3, Sort.Direction.ASC, "childEntities.name");
它可以正常工作,但我不想将排序属性传递给具有 JPQL 查询中某处别名的存储库。我想传递直接存在于存储库方法返回的投影中的属性。如果我像这样在 JPQL 查询中手动编写 ORDER BY:
... ORDER BY name ASC
然后,此查询也运行而没有任何错误,因为我可以从 ORDER BY 子句引用 SELECT 子句中的别名。 有没有办法告诉Spring Data Jpa在不附加FROM或JOIN子句别名的情况下执行排序?像这样:
new PageRequest(0, 3, Sort.Direction.ASC, "name") ===> ORDER BY name asc
这是 Spring 数据中的一个错误:没有正确检测到别名。这里也有报道。
在applySorting
方法的 QueryUtils 中,仅检测(外部)联接别名和恰好带有一对括号的函数别名。属性的简单别名当前不起作用。
一种解决方法是在构建PageRequest
时将JpaSort.unsafe
与括号中的别名一起使用,例如
PageRequest.of(0, 3, JpaSort.unsafe(Sort.Direction.ASC, "(name)"))
顾名思义,当根据用户输入动态排序时,这是不安全的,因此它应该仅用于硬编码排序。
在可分页控制器入口点的上下文中,我将 Dario Seidl 的答案改编为以下内容:
public static Pageable parenthesisEncapsulation(final Pageable pageable) {
Sort sort = Sort.by(Collections.emptyList());
for (final Sort.Order order : pageable.getSort()) {
if (order.getProperty().matches("^\(.*\)$")) {
sort = sort.and(JpaSort.unsafe(order.getDirection(), order.getProperty()));
} else {
sort = sort.and(Sort.by(order.getDirection(), order.getProperty()));
}
}
return PageRequest
.of(pageable.getPageNumber(), pageable.getPageSize(), sort);
}
我可以像这样给我的控制器排序指令:
/myroute?sort=(unsafesortalias),desc&sort=safesortfield,asc
String orderSort=page.getSort().toString();
Pageable page1 =
PageRequest.of(page.getPageNumber(),page.getPageSize(),
JpaSort.unsafe(Sort.Direction.valueOf
(orderSort.substring(page.getSort().toString().indexOf(':')+2,
orderSort.length())),
orderSort.substring(0,page.getSort().toString().indexOf(')')+1)));