JPA - 按对象集合中的多个属性查找

Question

我有一个具有以下属性的事件对象：

class Event {

    String name;

    String location;

    LocalDateTime date;

    String description;

}

假设我从 Web API 获得了一个事件列表：

List<Events> events = getEvents(); // e.g. 5 events

现在我想检查我的数据库中已经有多少这些事件。

如果值的组合：名称、位置和日期也是唯一的，则事件是唯一的。

所以基本上我想要一个创建查询来做到这一点：

Optional<Event> getByNameAndLocationAndDate(String name, String location, LocalDate date);

但是对于一个查询中的项目列表。就像是：

Optional<Event> getByNameAndLocationAndDate(List<Events> events);

JPA可以吗？

Answer 1

没有内置的或特别漂亮的方法来做到这一点。但是您可以使用循环生成查询：public List<Event> getByNameAndLocationAndDate(List<Event> events) {&nbsp; &nbsp; if (events.isEmpty()) {&nbsp; &nbsp; &nbsp; &nbsp; return new ArrayList<>();&nbsp; &nbsp; }&nbsp; &nbsp; final StringBuilder queryBuilder = new StringBuilder("select e from Event e where ");&nbsp; &nbsp; int i = 0;&nbsp; &nbsp; for (final Event event : events) {&nbsp; &nbsp; &nbsp; &nbsp; if (i > 0) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; queryBuilder.append("or")&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; queryBuilder.append(" (e.name = :name" + i);&nbsp; &nbsp; &nbsp; &nbsp; queryBuilder.append(" and e.location = :location" + i);&nbsp; &nbsp; &nbsp; &nbsp; queryBuilder.append(" and e.date = :date" + i + ") ");&nbsp; &nbsp; &nbsp; &nbsp; i++;&nbsp; &nbsp; }&nbsp; &nbsp; final TypedQuery<Event> query = em.createQuery(queryBuilder.toString());&nbsp; &nbsp; int j = 0;&nbsp; &nbsp; for (final Event event : events) {&nbsp; &nbsp; &nbsp; &nbsp; query.setParameter("name" + j, event.getName());&nbsp; &nbsp; &nbsp; &nbsp; query.setParameter("location" + j, event.getLocation());&nbsp; &nbsp; &nbsp; &nbsp; query.setParameter("date" + j, event.getDate());&nbsp; &nbsp; }&nbsp; &nbsp; return query.getResultList();}就像我说的，不是很漂亮。使用标准 API 可能会更好。再说一次，除非您对执行速度有非常严格的要求，否则最好一次遍历列表检查一个事件。这将导致对数据库运行更多的查询，但也会产生更漂亮的代码。编辑：这是使用标准 API 的尝试，还没有使用它太多，所以只是通过谷歌搜索创建的，不能保证它按原样工作..public List<Event> getByNameAndLocationAndDate(List<Event> events) {&nbsp; &nbsp; if (events.isEmpty()) {&nbsp; &nbsp; &nbsp; &nbsp; return new ArrayList<>();&nbsp; &nbsp; }&nbsp; &nbsp; final CriteriaBuilder cb = em.getCriteriaBuilder();&nbsp; &nbsp; final CriteriaQuery<Event> query = cb.createQuery(Event.class);&nbsp; &nbsp; final Root<Event> root = query.from(Event.class);&nbsp; &nbsp; final List<Predicate> predicates = new ArrayList<>();&nbsp; &nbsp; final List<Predicate> predicates = events.stream().map(event -> {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return cb.and(cb.equal(root.get("name"), event.getName()),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cb.equal(root.get("location"), event.getLocation()),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cb.equal(root.get("date"), event.getDate()));&nbsp; &nbsp; }).collect(Collectors.toList());&nbsp; &nbsp; query.select(root).where(cb.or(predicates.toArray(new Predicate[]{})));&nbsp; &nbsp; return em.createQuery(query).getResultList();}

Answer 2

尝试List<Event>&nbsp;findByNameInAndLocationInAndDateIn(List<String>&nbsp;names,List<String>&nbsp;locations,List<Date>&nbsp;dates);但这将返回一个列表，而不是单个事件，如果您需要验证一个事件是否不在数据库中，唯一的方法是逐一搜索，您可以使用此函数来决定是否需要。我不确定这个功能是否如你所愿