spring - Spring JPA 查询以获取具有计数条件的数据
问题描述
我有一个包含许多评论实体的订单实体。Order 有许多 OrderItem 实体,也有许多 Review 实体。因此,用户可以输入特定订单的评论作为普通评论或特定订单项目作为项目评论。
现在我想列出可用评论的订单和没有评论的订单。为此,我必须检查订单 || OrderItem 同时在评论表中有评论。我认为要执行带有 Join 子句的计数查询。请解决这个问题。
OrderDO.java
@Table(name = "ORDER")
public class OrderDO implements Serializable{
@Column(name = "ORDER_NUMBER", nullable = false)
private String orderNumber;
@OneToMany(cascade = CascadeType.ALL, mappedBy="order")
private Set<ReviewDO> reviews = new HashSet<>();
}
OrderItemDO.java
@Table(name = "ORDER_ITEM")
public class OrderItemDO implements Serializable{
@Column(name = "PRODUCT_NAME",nullable = false)
private String name;
@OneToMany(cascade = CascadeType.ALL, mappedBy="item")
private Set<ReviewDO> reviews = new HashSet<>();
}
回顾DO.java
@Table(name = "REVIEW")
public class ReviewDO implements Serializable {
@Column(name = "RATING")
private double rating;
@Column(name = "REVIEW_TEXT")
private String reviewText;
@ManyToOne()
@JoinColumn(name = "ITEM_ID", referencedColumnName="ID")
private OrderItemDO item;
@ManyToOne()
@JoinColumn(name = "ORDER_ID", referencedColumnName="ID")
private OrderDO order;
}
OrderRepository.java
@Repository
public interface OrderRepository extends CrudRepository<OrderDO, Long>{
@Query("select order from OrderDO order where order.orderNumber = ?1")
public Optional<OrderDO> findByOrderNumber(String orderNumber);
}
解决方案
要查找与订单或订单项目相关的评论,您可以ReviewDO
通过放置ReviewRepository
如下所示的方式查询对象:-
@Repository
public interface ReviewRepository extends CrudRepository<ReviewDO, Long>{
@Query(value = "SELECT r FROM ReviewDO r where r.item is not null or r.order is not null")
List<ReviewDO> findAllByOrdersOrOrderItems();
}
请检查下面给出的测试用例是否符合您的要求
导入静态 org.junit.Assert.assertEquals;
import java.util.List;
import java.util.stream.Collectors;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import com.raj.so.artefacts.OrderDO;
import com.raj.so.artefacts.OrderItemDO;
import com.raj.so.artefacts.OrderItemRepository;
import com.raj.so.artefacts.OrderRepository;
import com.raj.so.artefacts.ReviewDO;
import com.raj.so.artefacts.ReviewRepository;
@DataJpaTest
@RunWith(SpringRunner.class)
public class ReviewRepositoryTest {
@Autowired
private OrderRepository orderRepository;
@Autowired
private OrderItemRepository orderItemRepository;
@Autowired
private ReviewRepository reviewRepository;
@Test
public void testRepositoryFetch() {
// creating 2 orders
OrderDO orderDO1 = new OrderDO();
orderDO1.setOrderNumber("1");
orderRepository.save(orderDO1);
OrderDO orderDO2 = new OrderDO();
orderDO2.setOrderNumber("2");
orderRepository.save(orderDO2);
// creating an order item
OrderItemDO orderItemDO1 = new OrderItemDO();
orderItemDO1.setName("order item 1");
orderItemRepository.save(orderItemDO1);
// creating 2 reviews - one for order; other for item
ReviewDO reviewDO1 = new ReviewDO();
reviewDO1.setRating(5.0);
reviewDO1.setReviewText("rate five");
reviewDO1.setOrder(orderDO1);
reviewRepository.save(reviewDO1);
ReviewDO reviewDO2 = new ReviewDO();
reviewDO2.setRating(4.0);
reviewDO2.setReviewText("rate four");
reviewDO2.setItem(orderItemDO1);
reviewRepository.save(reviewDO2);
// query all reviews associated to orders or items
// returned review objects will have details about orders and items
List<ReviewDO> reviews = reviewRepository.findAllByOrdersOrOrderItems();
assertEquals(2, reviews.size());
List<String> orderNumbers = reviews.stream().map(item -> {
if (item.getOrder() != null) {
return item.getOrder().getOrderNumber();
} else {
return null;
}
})
.filter(item -> item != null)
.collect(Collectors.toList());
assertEquals("1", orderNumbers.get(0));
List<String> orderItemNames = reviews.stream().map(item -> {
if (item.getItem() != null) {
return item.getItem().getName();
} else {
return null;
}
})
.filter(item -> item != null)
.collect(Collectors.toList());
assertEquals("order item 1", orderItemNames.get(0));
}
}
推荐阅读
- typo3 - Typo3 损坏:前端/后端空白页,即使在恢复备份后也没有错误
- orbeon - 注销不适用于 DIGEST 身份验证
- python - 如何计算二维列表中相邻的元素数量
- html - 如何将标题文本放在另一个标题后面?(见图)
- python - 在元组列表的每个元组末尾添加列表元素
- python - 选择 NumPy 数组范围,包括最后一个元素
- javascript - 这是将多级深度对象数组转换为简洁对象数组的好方法吗?
- cython - 如何遍历其他结构中包含的对象并调用它们的方法(让 Cython 知道它们)?
- linux-kernel - 正式的 Linux 内核内存模型
- python - 502 Bad Gateway:注册的端点未能处理请求。: Python