Query ElementCollection of Enum by using JPA Criteria API
Solution 1
Thanks siebz0r!
I was modifying your code a little bit since your code returns all Cars that has 1 or more security (and not all), i.e. returns all cars which has a securityList that contain at least a subset of the securityList.
Here is my code:
public List<Car> searchCars(String makePattern, Set<Security> requiredSecuirtySet) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Car> cq = cb.createQuery(Car.class);
Root<Car> car = cq.from(Car.class);
Predicate criteria = cb.conjunction();
for (Security security : carQueryData.getSecurityCriteria()) {
criteria = cb.and(criteria, car.get(Car_.securityList).in(security) );
}
// Add more predicates, for instance:
// for (Equipment equipment : carQueryData.getEquipmentsCriteria()) {
// criteria = cb.and(criteria, car.get(Car_.equipmentList).in(equipment) );
// }
Predicate makePredicate = cb.equal(car.get(Car_.make), makePattern);
cq.select(car).where(makePredicate, criteria);
return em.createQuery(cq).getResultList();
}
Best regards
Solution 2
You can use collections as parameters so maybe this will work:
TypedQuery<Car> q = em.createQuery("select c from Car c where c.make = :make and c.securityList in :secutiryList", Car.class);
q.setParameter("make", makePattern);
q.setParameter("securityList", requiredSecuirtySet);
return q.getResultList();
I haven't tested this so I'm not sure it will work. It is based on this question. I also haven't worked with the criteria API so I didn't know how to 'translate' it.
Here's a shot at the query with the criteria API:
public List<Car> searchCars(String makePattern,
Set<Security> requiredSecuirtySet)
{
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Car> query = builder.createQuery(Car.class);
Root<Car> car = query.from(Car.class);
query.select(car).where(
builder.equal(car.get("make"), makePattern),
car.get("securityList").in(requiredSecuirtySet));
return em.createQuery(query).getResultList();
}
Related videos on Youtube
kungcc
Updated on June 16, 2022Comments
-
kungcc almost 2 years
I'm working of a web application for a car dealer. I have a Car class with a field which contain a set of security enums.
public class Car { @Id @GeneratedValue private Long id; @NotNull(message = "{year}") @Min(value = 1950) @Max(value = 2020) @Column(nullable = false) private int year; @NotNull() @Column(nullable = false) private String make; @NotNull() @Column(nullable = false) private String model; @NotNull() @Min(value = 0) @Max(value = 1000000) @Column(nullable = false) private int kilometres; @Column(nullable = false) private int price; @NotNull() @Enumerated(EnumType.STRING) private Gearbox gearbox; @ElementCollection(fetch = FetchType.EAGER) @Enumerated(EnumType.STRING) @CollectionTable(name="SECURITY") @Column(name="TYPE") private Set<Security> securityList = new HashSet<Security>(); @NotNull() @Column(nullable = false) private String description; @OneToMany(cascade = { CascadeType.ALL }, fetch = FetchType.LAZY, orphanRemoval = true) private List<Picture> pictureList = new ArrayList<Picture>(); // Getters and setters + help methods..
The Security enum is like:
public enum Security { ABS("abs"), AIRBAG("airbag"), ANTISPIN("antispin"), CENTRAL_LOCKING("centralLocking"), REMOTE_ALARM("remoteAlarm"), FOUR_WHEEL("fourWheel"), PARKING_ASSISTANCE("parkingAssistance"), SERVICE_MANUAL("serviceManual"), STABILITY_CONTROL("stabilityControl"), XENON_LIGHT("xenonLight"); private String label; private Security(String label) { } public String getLabel() { return label; } }
In the web application, I will create a search page, where the users is able to define required Securitiy parts and a manufacturer pattern (make field in Car class) . For instance, a user might search for Cars which have a make pattern according to "Volkswagen" and Security with at least ABS and REMOTE_ALARM.
My problem is that I am not sure how to create the query using the criteria API. I guess it should start like:
public List<Car> searchCars(String makePattern, Set<Security> requiredSecuirtySet) { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Car> cq = cb.createQuery(Car.class); Root<Car> _car = cq.from(Car.class); // Give me some help here please =) return em.createQuery(cq).getResultList(); }
Can you please help me? I also have a meta model over the Car class.
Best regards and thanks in advance!
-
kungcc almost 12 yearsThanks, that is exaclty what I want, but with the criteria API.
-
siebz0r almost 12 years@kungcc I've made an attempt to do make the query with the criteria query API. Please see my updated answer.
-
kungcc almost 12 yearsMeta model was added in the snippet