java - 创建通过检查相关列表中的项目来选择的查询

Question

我有 Shop 实体:

@Entity
@Table(name = "shop")
public class Shop {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private Long id;

@OneToMany(cascade = CascadeType.ALL)
@JoinColumn(name="shop_id")
private List<OpenDay> openDays = new ArrayList<>();
}

和OpenDay实体:

@Entity
@Table(name = "open_day")
public class OpenDay {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

private LocalDate date;
}

我需要一个查询来选择所有具有 OpenDay 且 date 设置为特定日期的 Shop，比如说今天和明天，我选择今天和明天营业的商店。我怎样才能实现这个目标？Criteria API 是首选，因此我将能够将它与 spring-data-jpa Specification 一起使用。谢谢。

Answer 1

据我了解 open_day 表包含 shop_id 列。因此，我建议您将 Shop 字段添加到 OpenDay 实体。

@Entity
@Table(name = "open_day")
public class OpenDay {          
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private Long id;

   private LocalDate date;

   @ManyToOne
   @JoinColumn(name="shop_id")
   private Shop shop;
}

然后

EntityManager entityManager;

public List<Shop> getShopsByOpenDates(List<LocalDate> dates) {
   CriteriaBuilder builder = entityManager.getCriteriaBuilder();
   CriteriaQuery<Shop> query = builder.createQuery(Shop.class);
   Root<OpenDay> openDay = query.from(OpenDay.class);

   Predicate predicate = openDay.get("date").in(dates);

   query.select(openDay.get("shop")).distinct(true).where(predicate);

   return entityManager.createQuery(query).getResultList();
}