Win a copy of Transfer Learning for Natural Language Processing (MEAP) this week in the Artificial Intelligence and Machine Learning forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Paul Clapham
  • Devaka Cooray
  • Bear Bibeault
Sheriffs:
  • Junilu Lacar
  • Knute Snortum
  • Liutauras Vilda
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Piet Souris
Bartenders:
  • salvin francis
  • Carey Brown
  • Frits Walraven

Example JPA - creating dynamic query - Spring Boot

 
Ranch Hand
Posts: 111
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a method controller which gonna return all advertisements that are being filtered by passed paremetrs.



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)



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

Service method:



Repo



SQL generated for this:

If I made



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

 
Ranch Hand
Posts: 1871
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 111
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Ranch Hand
Posts: 1871
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Try this :

I  think you don't need to build a query  since Spring Data JPA will build a query for you.
 
must Janik
Ranch Hand
Posts: 111
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Himai Minh wrote:Try this :

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
Ranch Hand
Posts: 1871
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:
 
must Janik
Ranch Hand
Posts: 111
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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:



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?
 
Rancher
Posts: 4544
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 111
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Rancher
Posts: 4544
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1871
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 111
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's why I wanted to use Example, but nevermind will check criteria out.
 
Himai Minh
Ranch Hand
Posts: 1871
12
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, Must,
I think you may need a CustomAdvertisementRepository interface and its implementation:






Your service or controller can do this:

In your pom, put java persistence dependency

 
must Janik
Ranch Hand
Posts: 111
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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






Your service or controller can do this:

In your pom, put java persistence dependency



yee, I have made with the same solution earlier. Thanks anyway, will be for next people!
 
Slime does not pay. Always keep your tiny ad dry.
Two software engineers solve most of the world's problems in one K&R sized book
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic