英文:
order by a field in a filtered OneToMany relation using JPA Specification ( CriteriaQuery CriteriaBuilder )
问题
What I want to achieve : 在应用条件后，对 @OneToMany 关系中的 Book 实体列表进行排序，具体排序属性是 order。
@Entity
public class Book {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "bookId")
private Long id;
@OneToMany(mappedBy = "entity", cascade = CascadeType.ALL, orphanRemoval = true, fetch = FetchType.EAGER)
Set<RowOrder> orders;
}
@Entity
public class RowOrder {
@EmbeddedId
private RowOrderId id;
@ManyToOne
@MapsId("entityId")
@JoinColumn(name = "entity_id")
private Book entity;
@ManyToOne
@MapsId("userId")
@JoinColumn(name = "user_id")
private User user;
@Column(name = "entity_order")
private Long order;
}
@Entity
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "userId")
private Long id;
}
我想要按照 order.userId == aUser.id 来对 books 进行排序。
我使用 jpa.domain.Specification 进行过滤，我希望在这个过滤条件下应用排序。
@AllArgsConstructor
public class BookSpecification implements Specification<Book> {
...
@Override
public Predicate toPredicate(Root<Task> root, CriteriaQuery<?> criteriaQuery,
CriteriaBuilder criteriaBuilder) {
...
aUser ; // 这是我希望应用于关系的用户实体
criteriaQuery.orderBy(criteriaBuilder.asc(
root.join("orders", JoinType.LEFT) // <--- 如何在这里筛选，WHERE row_order.user_id = aUser.id
.get("order"))
);
...
}
- 我该如何实现这个目标？
- 在使用
CriteriaBuilder时，我能否编写一些原生 SQL？如果可以，我如何编写我的情况下的 OrderBy 语句，使用原生 SQL？
英文:
What I want to achieve : order a list of Book entities on a @OneToMany relation, on a specific attribute after applying a condition.
@Entity
public class Book{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "bookId")
private Long id;
@OneToMany(mappedBy = "entity", cascade = CascadeType.ALL, orphanRemoval = true, fetch = FetchType.EAGER)
Set<RowOrder> orders;
}
@Entity
public class RowOrder {
@EmbeddedId
private RowOrderId id;
@ManyToOne
@MapsId("entityId")
@JoinColumn(name = "entity_id")
private Book entity;
@ManyToOne
@MapsId("userId")
@JoinColumn(name = "user_id")
private User user;
@Column(name = "entity_order")
private Long order;
}
@Entity
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "userId")
private Long id;
}
I want to order books by book.orders.order in which order.userId == aUser.id.
I am using jpa.domain.Specification to filter in which I want to apply this order.
@AllArgsConstructor
public class BookSpecification implements Specification<Book> {
...
@Override
public Predicate toPredicate(Root<Task> root, CriteriaQuery<?> criteriaQuery,
CriteriaBuilder criteriaBuilder) {
...
aUser ; // this the user entity in which i want to apply on the relation
criteriaQuery.orderBy(criteriaBuilder.asc(
root.join("orders", JoinType.LEFT) // <--- how to filter this with, WHERE row_order.user_id = aUser.id
.get("order"))
);
...
}
- how can I achieve this ?
- can I write some native sql when using
CriteriaBuilder? if so how can I write the OrderBy cause in my case with a natvie sql ?
答案1
得分: 0
这是我们的解决方法：
Join<Book, RowOrder> books_orders_join = root.join("orders", JoinType.LEFT);
Join<Join<Book, RowOrder>, User> books_orders_users_join = books_orders_join
.join("user", JoinType.LEFT);
predicates
.add(
criteriaBuilder
.equal(books_orders_users_join.get("id"), aUser.getId()));
criteriaQuery.orderBy(criteriaBuilder.asc(books_orders_join.get("order")));
英文:
this how we solved it:
Join<Book, RowOrder> books_orders_join = root.join("orders", JoinType.LEFT);
Join<Join<Book, RowOrder>, User> books_orders_users_join = books_orders_join
.join("user", JoinType.LEFT);
predicates
.add(
criteriaBuilder
.equal(books_orders_users_join.get("id"), aUser.getId()));
criteriaQuery.orderBy(criteriaBuilder.asc(books_orders_join.get("order")));
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论