JPA findBy multiple items optionally

Question

I'm looking for a simple solution to an ugly problem. I am using Spring Data JPA and have 7 entities that are all related. I have a need to do a findByEntity1_NameAndEntity2_NameAndEntity3_NameAndEntity4_NameAndEntity5_NameAndEntity6_NameAndEntity7_Name

I need every permutation including and excluding each other those entities. I could build all 128 methods and use a big case statement to select which one to use, but that's horridly ugly. I feel like I'm missing the easy button on this one.

Would it be easier to implement with a native query? – Juan Jan 09 '19 at 00:58 — Juan, Jan 09 '19 at 00:58

Dovmo · Answer 1 · 2019-01-09T21:25:50.427

Query By Example method

I think the best option for you is to use the (imo, somewhat infrequently used) QueryByExample feature of Spring Data JPA. On researching this answer, I posted an answer somewhere else that needed the same response. I'd take a look at that to get an idea of how this solves your problem.

You'll first need to the QueryByExampleExecutor to your Repository. Secondly you'll just need to create your query like so (and hopefully you're using fluent builders for your entities!):

ExampleMatcher matcher = ExampleMatcher.matching().withIgnoreNullValues();
Example<MyObject> exampleQuery = Example.of(new MyObject()
    .withEntity1(new Entity1().withName("foo"), matcher);
repository.findAll(exampleQuery);

Would select all of the MyObject elements with Entity1 having name of foo.

ExampleMatcher matcher = ExampleMatcher.matching().withIgnoreNullValues();
Example<MyObject> exampleQuery = Example.of(new MyObject()
    .withEntity1(new Entity1().withName("foo"),
    .withEntity2(new Entity2().withName("bar"),
    .withEntity3(new Entity3().withName("baz"),
    .withEntity4(new Entity4().withName("foo"),
    .withEntity5(new Entity5().withName("bar"),
    .withEntity6(new Entity6().withName("baz"),
    .withEntity7(new Entity7().withName("foo")
), matcher);
repository.findAll(exampleQuery);

Would select all of the MyObject elements for Entity1 with name of foo, Entity2, with name of bar, etc.

JPA findBy multiple items optionally

1 Answers1