CODEWITHSUNDEEP
javahibernatejakarta-eejpa
Tomas
Tomas

Reputation: 1262

JPA lookups with arrays

I have an unidirectional relationship. Here i have Employee and Andress entities. In Employee entity i have the following code:

@OneToOne(cascade=CascadeType.ALL)
@JoinColumn(name = "HOME_ADDRESS")
private Address homeAddress;

I have an array of Adress objects and want to write a lookup that would return an array of Customer objects mapped to those adresses.

select e from Employee e where e.homeAddress.id IN '?'

I don't know what to do with the '?' part. Is the only option to loop over the address array, add id's to a string and pass it as a parameter to the query above, or is there a way to pass the array to the query and expect the same result?

Upvotes: 0

Views: 210

Answers (2)

Troy Young
Troy Young

Reputation: 191

2 Solutions:

  1. HQL
String hql="select e from Employee e where e.homeAddress.id IN (:addresses)";  
Query query = getSession().createQuery(hql);  
query.setParameterList("addresses", your_list_address_collection);
  1. Criteria
Criteria criteria = session.createCriteria(Employee.class);
criteria.add(Restrictions.in("addresses", your_list_address_collection));

Upvotes: 1

JB Nizet
JB Nizet

Reputation: 692043

No, you don't pass that as a String, but as a collection of IDs. And your query is invalid. It should be:

String jpql = "select e from Employee e where e.homeAddress.id IN :addresses";
Set<Long> addressIds = Arrays.stream(addresses)
                             .map(Address::getId)
                             .collect(Collectors.toSet());
return em.createQuery(jpql, Employee.class)
         .setParameter("addresses", addressIds)
         .getResultList();

This uses Java 8 to transform the array of addresses into a set of IDs, but you can of course use a goold old for loop.

Upvotes: 1

Related Questions