JPA, Spring, Spring Data

Using @ElementCollection in Spring JPA queries

In my domain model, I had a list of Strings to be stored. As it is a typical reason of using element collections, I decided to do so.

Using element collection in the domain model has the following advantages:

  • The domain model remains simple. No additional type,
  • The connected list is bounded to the “master” entity. You do not need to take care of the maintenance of the elements by defining cascade type
  • The data was static, so only read from database was needed, therefore creating a one to many connection seemed not necessary.
  • It is even possible to define an enumeration as type of the element collection. It gives the possibility to store only predefined values in the field.
My entity class looks like this:
public class SmartHomeSubscription implements Serializable {

@Column(name = “ID”, unique = true, nullable = false, length = 36)
private String id;

@Column(name = “NAME”, nullable = false, unique = true, length = 20)
private String name;

@ElementCollection(fetch = FetchType.EAGER)
@CollectionTable(name = “ADDON”, joinColumns = @JoinColumn(name = “SMART_HOME_SUBSCRIPTION_ID”))
@Column(name = “NAME”)
private Set<String> addonNames = new HashSet();

@ElementCollection(targetClass = AllowedFunction.class, fetch = FetchType.EAGER)
@CollectionTable(name = “ALLOWED_FUNCTION”, joinColumns = @JoinColumn(name = “SMART_HOME_SUBSCRIPTION_ID”))
@Column(name = “NAME”)
private Set<AllowedFunction> allowedFunctions = new HashSet();

So how it is possible to use these element collections in queries in Spring JPA repository?

As you can see, the items of the element collection with String and annotated type can be used simple as Strings in the query. Equal and IN operator can be used to them such as for normal String fields.

 @Query(value = "SELECT count(s) FROM SmartHomeSubscription s INNER JOIN s.allowedFunctions f INNER JOIN s.addonNames a WHERE f = :allowedFunction AND a IN :addonNames")
 public int getNumberOfAddonsValidForFunction(@Param("allowedFunction") AllowedFunction allowedFunction,
   @Param("addonNames") List<String> addonNames);

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s