#StackBounty: #java #spring #spring-data-jpa #spring-data Querying an entity by values in an ElementCollections using Spring Data JpaRe…

Bounty: 50

Is it possible to query entities in a JpaRepository based on values stored in an attached @ElementCollection using query methods?

Setup

My Spring Boot 2 / Spring 5 application has an entity (Artwork) to which arbitrary metadata can be attached.
The metadata is implemented with a simple key value map using @ElementCollection and @CollectionTable, since the metadata is just plaintext key/value pairs and does not exists outside the scope of it’s entity.

The entity looks like this:

@Entity
@Table(name = "artwork")
public class Artwork implements Serializable {
    @Id
    @GeneratedValue(strategy = IDENTITY)
    private Long id;

    // more propperties 

    @ElementCollection
    @MapKeyColumn(name = "name")
    @Column(name = "value")
    @CollectionTable(name = "artwork_metadata", joinColumns = @JoinColumn(name = "artwork_id"))
    private Map<String, String> metadata = new HashMap<>();

    // more code
}

Problem

I have two use cases, where I need to query the entity based on information stored in the metadata map.

  • One use case is finding all Artworks with licenses (having metadata.name = license, no matter what is in the value column)
  • The other is finding all entities of one specific artist (having an exact key/value pair of metadata.name = artist and metadata.value = someName)

I have tried using the underscore notation for manual property path description; but my datatype is a map, not really an object with fields name / value.

public interface ArtworkRepository extends JpaRepository<Artwork, Long> {
    List<Artwork> findAllByReleaseDateAfter(Instant after);

    // Not working
    List<Artwork> findAllByMetadata_NameAndMetadata_value 

Querying in general seems to be possible. But most of the answers I found on StackOverflow are about searching in Lists, not in maps.

So my question is

How do I query entities in a JpaRepository based on values stored in an attached @ElementCollection using query methods? Or do I need to convert this rlationship at the object level using JPA’s one-to-many mapping.

Update

  • Since we have several tens of thousands of recordsin the artist table I want to filter at the database level and not within the application.
  • Querying by metadata will become common, so introducing helper flags like hasLicense or moving the artist to the primary entity is not an option.

Thanks.


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.