CODEWITHSUNDEEP
javahibernatejpaspring-data-jpa
Alexandros
Alexandros

Reputation: 733

Query entity containing a property of type ArrayList<String>containing a specific set of strings using spring data jpa

It is, I think, a stupid question but I am stuck for days on this. I have an entity with a property to be a List of strings as below:

   public class DummyEntity {
        @Id
        @GeneratedValue
        private Long id;
        private String name;
        @ElementCollection
        private List<String> items;
    }

I am using Spring Data JPA and wish to query this entity and fetch from database entities that have an exact set of items in that array list. This means if I have in my database:

DummyEntity (1, "name1", ["first"])
DummyEntity (1, "name1", ["first", "second"])
DummyEntity (1, "name1", ["first", , "second", "third"])

I need a query where I pass i.e. ["first", "second"] (string order is irrelevant) and I get back only DummyEntity (1, "name1", ["first", "second"]). I know that I can do it with java, but I would hate it as work around.

I made for the purpose of the question: github demo project where I have a minimal project with a failing test which I need to succeed. What I tried is the following:

Page<DummyEntity> findAllByItemsIn(Pageable pageable,List<String> items);
Page<DummyEntity> findAllByItemsContains(Pageable pageable,List<String> items);
Page<DummyEntity> findAllByItemsEquals(Pageable pageable,List<String> items);
Page<DummyEntity> findAllByItems(Pageable pageable,List<String> items);

Upvotes: 1

Views: 1727

Answers (2)

Mladen Savić
Mladen Savić

Reputation: 472

Here is another way of doing this.

DummyEntity findOne(Example<DummyEntity> example); //repository

And in your service you can call it with following:

DummyEntity entity = new DummyEntity(List.of("first","second");
DummyEntity fromDb = repository.findOne(Example.of(entity));

Upvotes: 0

Kavithakaran Kanapathippillai
Kavithakaran Kanapathippillai

Reputation: 8203

    @Query("Select d from DummyEntity d left join d.items i where i in :items " +
            "group by d having count(i) = :itemsSize")
    Page<DummyEntity> findAllByItems(List<String> items, 
                                     long itemsSize, 
                                     Pageable pageable);

Upvotes: 2

Related Questions