CODEWITHSUNDEEP
javaspringjpaspring-data-jpaspring-data
Krzysztof Majewski
Krzysztof Majewski

Reputation: 2534

JPA - find by multiple attributes in collections of objects

I have an event object with following attributes:

class Event {
    String name;
    String location;
    LocalDateTime date;
    String description;
}

Lets say I get from web API a list of events:

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

And now I want to check how many of these events I already have in my DB.

Event is unique if combination of values: name, location and date is also unique.

So basically I want to a create query to do this:

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

but for a list of item in just one query. Something like:

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

Is it possible with JPA?

Upvotes: 1

Views: 3802

Answers (2)

Yonnyce
Yonnyce

Reputation: 16

try

List<Event> findByNameInAndLocationInAndDateIn(List<String> names,List<String> locations,List<Date> dates);

but this returns a list, not a single event, if you need verify if one event is not in database, the only way to do this is search one by one, you can use this function for decide if needs that.

I'm not sure if this function behaves as you wish

Upvotes: 0

Tobb
Tobb

Reputation: 12205

There is no built-in or specially pretty way of doing this. But you could generate a query by using a loop:

public List<Event> getByNameAndLocationAndDate(List<Event> events) {
    if (events.isEmpty()) {
        return new ArrayList<>();
    }
    final StringBuilder queryBuilder = new StringBuilder("select e from Event e where ");
    int i = 0;
    for (final Event event : events) {
        if (i > 0) {
            queryBuilder.append("or")
        } 
        queryBuilder.append(" (e.name = :name" + i);
        queryBuilder.append(" and e.location = :location" + i);
        queryBuilder.append(" and e.date = :date" + i + ") ");
        i++;
    }
    final TypedQuery<Event> query = em.createQuery(queryBuilder.toString());

    int j = 0;
    for (final Event event : events) {
        query.setParameter("name" + j, event.getName());
        query.setParameter("location" + j, event.getLocation());
        query.setParameter("date" + j, event.getDate());
    }
    return query.getResultList();
}

Like I said, not very pretty. Might be better with criteria API. Then again, unless you have very strict requirements for execution speed, you might be better off looping through the list checking one event at the time. It will result in the more queries run against the database, but also much prettier code.

Edit: Here is attempt using criteria API, haven't used it much so created just by googling, no guarantee it works as it is..

public List<Event> getByNameAndLocationAndDate(List<Event> events) {
    if (events.isEmpty()) {
        return new ArrayList<>();
    }
    final CriteriaBuilder cb = em.getCriteriaBuilder();
    final CriteriaQuery<Event> query = cb.createQuery(Event.class);
    final Root<Event> root = query.from(Event.class);

    final List<Predicate> predicates = new ArrayList<>();

    final List<Predicate> predicates = events.stream().map(event -> {
                return cb.and(cb.equal(root.get("name"), event.getName()),
                              cb.equal(root.get("location"), event.getLocation()),
                              cb.equal(root.get("date"), event.getDate()));
    }).collect(Collectors.toList());

    query.select(root).where(cb.or(predicates.toArray(new Predicate[]{})));

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

Upvotes: 1

Related Questions