Example JPA - creating dynamic query - Spring Boot

[must Janik]

I have a method controller which gonna return all advertisements that are being filtered by passed paremetrs.

@GetMapping("/search/advertisements")
    public List<Advertisement> getSpecicShortAdvertisements(
            @RequestParam(value = "city", required = false) String city,
            @RequestParam(value = "category", required = false) AdvertisementCategory category) {
        return shortAdvertisementService.getSpecificShortAdvertisements(city,category);
    }

So for example it can be: http://localhost:8080/search/advertisements?city=WARSZAWA&category=ANIMALSCARE

but it can be also http://localhost:8080/search/advertisements?category=ANIMALSCARE If I do not send any parameters it shoudl return all advertisements.

It would be so hard to write query for every single parameter, so I've used Example, but do not know why it returns empty list all the time despite having data in DB.

It's being added for every run of application (initial data)

advertisementRepository.save(Advertisement.builder()
                .advertisementCategory(AdvertisementCategory.ANIMALSCARE)
                .dateTime(LocalDateTime.now())
                .description("LAAAAAAAAAAAAAAAAAAA")
                .featured(false)
                .photos(null)
                .title("TiTitle 2tle 1")
                .city("KRAKOW")
                .build());

        advertisementRepository.save(Advertisement.builder()
                .advertisementCategory(AdvertisementCategory.ANIMALSCARE)
                .dateTime(LocalDateTime.now())
                .description("LAAAAAAAAAAAAAAAAAAALA" +
                        "AALAAAAAAAAAAAAAAAAAAALAAAAAAAAAAAAAAAAAAALAAAAAAAAAAAAAAAAAAALAAAAA" +
                        "AAAAAAAAAAAAAALAAAAAAAAAAAAAAAAAAALAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA4AAAALAAAAAAAAAAAAAAAAAAA")
                .featured(false)
                .photos(null)
                .title("TTitle 2Title 2Title 2itle 2")
                .city("WARSZAWA")
                .build());

So as you can see if I send 'WARSZAWA' as an city it should return this advertisement, but it returns nothing.

Service method:

public List<Advertisement> getSpecificShortAdvertisements(String city, AdvertisementCategory category) {
        Advertisement example = Advertisement.builder()
                .advertisementCategory(category)
                .city(city)
                .build();

        return advertisementRepository.findAll(Example.of(example));
    }

Repo

@Repository
public interface AdvertisementRepository extends JpaRepository<Advertisement, Long> {

}

SQL generated for this:

select advertisem0_.advertisementid as advertis1_0_, advertisem0_.advertisement_category as advertis2_0_, advertisem0_.city as city3_0_, advertisem0_.date_time as date_tim4_0_, advertisem0_.description as descript5_0_, advertisem0_.featured as featured6_0_, advertisem0_.title as title7_0_ from advertisement advertisem0_ where advertisem0_.featured=? and advertisem0_.advertisementid=0 and advertisem0_.city=?

If I made

public List<Advertisement> getSpecificShortAdvertisements(String city, AdvertisementCategory category) {
        ExampleMatcher exampleMatcher = ExampleMatcher.matchingAny();

        Advertisement example = Advertisement.builder()
                .advertisementCategory(category)
                .city(city)
                .build();

        return advertisementRepository.findAll(Example.of(example, exampleMatcher));
    }

then it returns everything despite sending http://localhost:8080/search/advertisements?city=LALA which should not match anything

[Himai Minh]

Hi, Must,
In your getSpecificShortAdvertisements() , the city is not a request parameter.  So, even if you input city=LALA, it still returns you everything.
In your Advertisement builder, the city is null and it may return you everything?

[must Janik]

Himai Minh wrote:Hi, Must,
In your getSpecificShortAdvertisements() , the city is not a request parameter.  So, even if you input city=LALA, it still returns you everything.
In your Advertisement builder, the city is null and it may return you everything?

Hi Himai, thanks for the answer, but to be honest I do not understand.

To be clear, if I send the city "LALA" I want to get all advertisements with that city which I passed via controller to the service. If I pass city and category so for example: the city is LALA and category is HOUSEWORK I want to get all advertisements that contain category AND city not this or this. If nothing is passed so city and category is null it should return everything.

That's what I want to achieve. I searched via google and have found that this may be the best option for me. If you knwo some other options to create dynamic query you can tell.

[Himai Minh]

Try this :

public interface AdvertisementRepository extends JpaRepository<Advertisement, Long>{
  List<Advertisement> findByCityAndCategory(String city,  String category); 
}

I  think you don't need to build a query  since Spring Data JPA will build a query for you.

[must Janik]

Himai Minh wrote:Try this :

public interface AdvertisementRepository extends JpaRepository<Advertisement, Long>{
  List<Advertisement> findByCityAndCategory(String city,  String category); 
}

I  think you don't need to build a query  since Spring Data JPA will build a query for you.

Is it good? I mean it will search only by city if I pass city? What if I will have like 10 arguments to pass (10 filters) I will have really huge name method. I'm thinking about this.

[Himai Minh]

If you only want to search by city, then create another method called findByCity.
If you have a very complicated query you can create something like this:

@Query("Select * from Advertisement ads where ads.city = ?1 and ads.category = ?2)
List<Advertisement> findAdvertisements(@Param("city") city, @Param("category") category);

[must Janik]

Himai Minh wrote:If you only want to search by city, then create another method called findByCity.
If you have a very complicated query you can create something like this:

@Query("Select * from Advertisement ads where ads.city = ?1 and ads.category = ?2)
List<Advertisement> findAdvertisements(@Param("city") city, @Param("category") category);

Folk, you dont understand me. These parameters are OPTIONAL. What if city will be null and I want to search only by category, what if category will be null and I want to search only by city, what if I want to search by both arguemnts. What if I will have 3,4,5 arguments to pass whcih some fo them can be null as I do not pass any value?

That's why I want to use dynamic query to query by only these values which are not nul... Do you understnad?

[Dave Tolls]

Define a method for your repository which takes all the searchable parameters.
You'll then need to add an implementation, inside which use a CriteriaBuilder, adding criteria for each parameter that is supplied.

Something along those lines.

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.custom-implementations
https://www.baeldung.com/spring-data-jpa-query (section 9.2)

[must Janik]

Dave Tolls wrote:Define a method for your repository which takes all the searchable parameters.
You'll then need to add an implementation, inside which use a CriteriaBuilder, adding criteria for each parameter that is supplied.

Something along those lines.

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.custom-implementations
https://www.baeldung.com/spring-data-jpa-query (section 9.2)

Ye, I've heared about that but I thought that it's much longer solution than that I had. I cannot use Example right here?

[Dave Tolls]

It's not a longer solution.
It's a solution.

You have a number of optional parameters.
You either provide a method for searching using each combination, which would be a lot of methods, or you provide a single one that builds a query based on what parameters have been supplied (the solution I mention).

[Himai Minh]

Hi, Must,
Image you are writing SQL queries to search advertisement by city, search by category, or search by id or search by date ......
And image you are writing SQL queries to search advertisement by city and category, search by category and date, search by author and date ......
You will need to write tons of queries.
You either write a lot of methods or build criteria.
Reference : https://www.objectdb.com/java/jpa/query/criteria

[must Janik]

That's why I wanted to use Example, but nevermind will check criteria out.

[Himai Minh]

Hi, Must,
I think you may need a CustomAdvertisementRepository interface and its implementation:

 public interface CustomAdvertisementRepository {
   List<Advertisement> findByCityAndCategory (String city, String category);
}

public CustomAdvertisementRepositoryImpl implements CustomAdvertisementRepository{
   @Autowired
    private EntityManager entityManager;

   public List<Advertisement> findByCityAndCategory(String city, String category){
         CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
         CriteriaQuery<List> criteria = criteriaBuilder.createQuery(List.class);
         Root<Advertisement> root = criteria.from(Advertisement.class);
         criteria.where(criteriaBuilder.and( criteriaBuilder.equals( root.get("city") , city), criteria.equal(root.get("category") , category ))
      );
   return entityManager.createQuery(criteria).getResultList();
    } 
}

public interface CustomAdvertisementDao extends JpaRepository<Advertisement, Integer> , CustomAdvertisementRepository{
}

Your service or controller can do this:

@Autowired
private CustomAdvertisementDao adDao;

... 

In your pom, put java persistence dependency

 
<dependency>
    <groupId>javax.persistence</groupId>
    <artifactId>javax.persistence-api</artifactId>
</dependency>

[must Janik]

Himai Minh wrote:Hi, Must,
I think you may need a CustomAdvertisementRepository interface and its implementation:

 public interface CustomAdvertisementRepository {
   List<Advertisement> findByCityAndCategory (String city, String category);
}

public CustomAdvertisementRepositoryImpl implements CustomAdvertisementRepository{
   @Autowired
    private EntityManager entityManager;

   public List<Advertisement> findByCityAndCategory(String city, String category){
         CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
         CriteriaQuery<List> criteria = criteriaBuilder.createQuery(List.class);
         Root<Advertisement> root = criteria.from(Advertisement.class);
         criteria.where(criteriaBuilder.and( criteriaBuilder.equals( root.get("city") , city), criteria.equal(root.get("category") , category ))
      );
   return entityManager.createQuery(criteria).getResultList();
    } 
}

public interface CustomAdvertisementDao extends JpaRepository<Advertisement, Integer> , CustomAdvertisementRepository{
}

Your service or controller can do this:

@Autowired
private CustomAdvertisementDao adDao;

... 

In your pom, put java persistence dependency

 
<dependency>
    <groupId>javax.persistence</groupId>
    <artifactId>javax.persistence-api</artifactId>
</dependency>

yee, I have made with the same solution earlier. Thanks anyway, will be for next people!