Spring JPA查询获取带计数条件的数据
Spring JPA查询获取带计数条件的数据
提问于 2018-11-03 21:19:21
我有一个Order实体，它有许多Review实体。Order有许多OrderItem实体，它也有许多Review实体。因此，用户可以输入特定订单的点评作为常见点评，也可以输入特定订单项目的点评作为点评。
现在我想列出有评论的订单和没有评论的订单。为此，我必须检查Order || OrderItem同时在审阅表中有评论。我认为使用Join子句执行count查询。请解决这个问题。
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<>();
}ReviewDO.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);
}回答 1
Stack Overflow用户
发布于 2018-11-03 22:48:04
要查找与订单或订单项目相关联的评论，您可以通过放置如下所示的ReviewRepository来查询ReviewDO对象：-
@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));
}
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/53131732
复制相关文章
相似问题
腾讯云开发者
