Distinct list of embedded entities

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

• Comment (1)
• Trackbacks (0)