Distinct list of embedded entities
Posted by Mike Haller
on Saturday, August 9. 2008
at 06:39
in Java
With the Java Persistence API (JPA), you can create embeddable entities, which will be persisted within the table of the container object instead of a relation. Suppose you've got a Customer class and an embeddable Address class like this:
package example;
@Entity
public class Customer {
private String name;
@Embedded
private Address address;
}
package example;
@Embeddable
public class Address {
private String zip;
private String city;
}
You will eventually end up with the following table:
CUSTOMER NAME, ZIP, CITY
My first naive approach querying the embeddable entity to get a list of all cities did not work:
SELECT DISTINCT c.city FROM Customer c
The solution is not really straight forward and intuitive, but still rather easy.
You need to add a constructor with all the fields and use the NEW operator of JPQL:
SELECT DISTINCT NEW example.Address(c.zip,c.city) FROM Customer c
