Spring Data Pageable JPA Filter Query with 3 of 4 Parts of Composite Key via a Query Method

Let’s say you are using Spring Data and you have a “Composite Key” (ie, a tuple or EmbeddedId). Generally, you may want to query by the whole composite key (ie, by ID), which is generally fairly straightforward with a JPA “query method” (still not sure that is the official name). Even so, one of the reasons you use tuples or composite keys is to group data (and maybe even group within a group). Running with that thought, let’s say you have the following composite key:

 @NoArgsConstructor
 @AllArgsConstructor
 @Data
 @Embeddable
 public class PlantScheduleCompositeKey implements Serializable {
     @Column(name = "state")
     private String state;
     @Column(name = "city")
     private String city;
     @Column(name = "zip")
     private String zip;
     @Column(name = "crop")
     private String crop;
 } 

Next, let’s use the composite key in this Entity:

 @Entity
 @Data
 @Table(name="plant_schedule")
 public class PlantSchedule implements Serializable {
 
     @EmbeddedId
     private PlantScheduleCompositeKey id;

     private boolean frostDates;
     private boolean moonDates;
     private String sowSeedsIndoorSchedule;
     private String transplantSeedlingsSchedule;
     private String directSowSchedule;
     private String source;
 } 

Now, if you are using a CrudRepository, or better yet a PagingAndSortingRepository, you’ll already get findById() out of the box. This will not help us if we want to search by only city, state, and zip (ie, without the crop name). So, we need to craft a custom query method, which will be turned into JPQL behind the scenes.

 @RepositoryRestResource
 public interface IPlantScheduleRepository extends PagingAndSortingRepository<PlantSchedule, PlantScheduleCompositeKey>{

     public List<PlantSchedule> findAllByIdCityContainingAndIdStateContainingAndIdZipContaining(String city, String state, String zip, Pageable pageable);
 } 

The key here is to understand that there is a hierarchy here with a filter that is repeated for the 3 out of the 4 parts of the composite key we want to search against. We can interpret the substring IdCityContaining as being the field id on the entity PlantSchedule, followed by the part of the composite key we want to do a contains comparison against. Therefore, we repeat this pattern twice more for state and zip. So, in summary, we have a query method that will return all PlantSchedules with a city, state, and zip matching the provided arguments.

Sample call:

Pageable pageable = PageRequest.of(0, 100);
         List<PlantSchedule> pageList = plantScheduleRepository.findAllByIdCityContainingAndIdStateContainingAndIdZipContaining(city, state, zip, pageable);
          

60 Comments:

  1. Hi, i think that i saw you visited my blog so i came to “return the favor”.I am attempting to find things to improve my site!I suppose its ok to use a few of your ideas!!

  2. Pingback: Google

  3. Pingback: free download for windows 10

  4. Pingback: free app for pc download

  5. Pingback: pc games for windows 10

  6. Pingback: download apk app for pc

  7. Pingback: Monkeys For Sale

  8. Pingback: Agence de communication

  9. Pingback: IT Services Somerset

  10. Pingback: Sig Sauer Handguns

  11. Pingback: Pinball Machines for Sale

  12. Pingback: Shipping containers For Sale

  13. Pingback: Macbook pro akku schnell leer meilen

  14. Pingback: FARMING EQUIPMENT FOR SALE IN ZIMBABWE

  15. Pingback: shipping containers for sale

  16. Pingback: aromatherapy shop

  17. Pingback: website

  18. Pingback: Buy Methadone 10mg online

  19. Pingback: GELATI STRAIN

  20. Pingback: mossberg 590 18.5

  21. Pingback: savage impulse rifle for sale

  22. Pingback: zip 22 for sale

  23. Pingback: Liquid pentobarbital for sale

  24. Pingback: mossberg 930 combo for sale

  25. Pingback: stiiizy battery alternative Archives

  26. Pingback: magic mushrooms

  27. Pingback: buy skittles exotic carts online

  28. Pingback: background check

  29. Pingback: beretta semi auto shotgun

  30. Pingback: canadian marble fox

  31. Pingback: smith and wesson arms

  32. Pingback: catnapper recliners

  33. Pingback: weed for sale in uk

  34. Pingback: 3080 ftw3 msrp

  35. Pingback: best dumbells

  36. Pingback: شركة تسويق

  37. Pingback: Ruger AR-556 Semi-Auto Rifle

  38. Pingback: G17 GEN5 MOS FS STANDARD | 9X19MM

  39. Pingback: RissMiner

  40. Pingback: Sig Sauer Official Gun Shop

  41. Pingback: vertigo

  42. Pingback: rhonda worthey

  43. Pingback: savage grow plus scam

  44. Pingback: Safari Holidays in Kenya

  45. Pingback: beretta guns for sale

  46. Pingback: buy raw garden carts online

  47. Pingback: sig sauer rifles

  48. Pingback: kopru altinda sikin benim

  49. Pingback: springfield saint edge for sale

  50. Pingback: ufo extracts cartridges

Leave a Reply

Your email address will not be published. Required fields are marked *