0

I am working with Spring Data JPA and I have the following entities: Country: {country_id, country_name} Region: {region_id, region_name, country_id}

There is a onetomany relationship from Country to Region.

I have a JpaRepository for Country and I have a method like the following:

@Query("SELECT new org.example.CountryDTO(a.countryId, a.countryName, b.regionName ) FROM Country AS a left join Region AS lpd on a. countryId = r. countryId  WHERE a. countryId = LOWER(:country) and r.regionName= LOWER(:region) ”)
List<CountryDTO> find(@Param("country") String country, @Param("region") String region);

The question is, is there any way to avoid having to specify an SQL query? Ideally I would like to do something like:

List<CountryDTO> findByCountryAndRegionName(@Param("country") String country, @Param("region") String region);

However, that seems a bit complicated because the region_name column comes from the Region table.

Has anybody done anything like that? Any elegant solution that you might propose?

Any help will be greatly appreciated!

user1532449
  • 322
  • 4
  • 14
  • Please reformat your question: https://stackoverflow.com/editing-help#syntax-highlighting – Cepr0 Jun 08 '18 at 21:00

3 Answers3

1

According to the documentation you can define constraints by traversing nested properties. See 2.4.3 Property Expressions

Assuming Country has a property regions and both Country and Region have name properties, you may write something like : List<Country> findByNameAndRegions_Name(String countryName, String regionName)

Sébastien Helbert
  • 2,185
  • 13
  • 22
  • Hi @sébastien-helbert Thank you for your suggestion. Question for you here, is it necessary to have one repository for Country and one for Region? I ask you this because since all the queries I will create are only related to Country at this moment I do not have a repository for Region. The other question would be, does this work without relationships? – user1532449 Jun 11 '18 at 15:08
  • If all queries are related to Country only you don't need a Region Repository. About relationships, as far as I know, it won't work without relationships because nested paths rely on relationship names / rely on properties names. – Sébastien Helbert Jun 13 '18 at 20:20
1

I think this should work:

interface CountryProjection {
  String getCountryId();
  String getCountryName()
  String getRegionName()
}

and then your query method can look like this:

List<CountryProjection> findByCountryNameAndRegionRegionNameAllIgnoreCase(String country,String region);

You must be using Spring Data JPA 1.10 a later for projection support in query methods.

Strelok
  • 50,229
  • 9
  • 102
  • 115
0

Looks like you can return a List<Object[]> if you use a native query, as suggested here.

Although I would prefer creating a separate DTO as this is a different projection.

Pankaj
  • 302
  • 4
  • 7