DATA JPA filter query with like and ignore case
im trying to implement a filter/search function based on attributes that can be null, i have this so far:
@Query("""
select a from Item a where
(?1 is null or a.name LIKE %?1%)
and (?2 is null or a.brand LIKE %?2%)
and (?3 is null or a.condition = ?3)
and (?4 is null or a.price = ?4)
and (?5 is null or a.size LIKE %?5%)
""")
Page<Item> findFilteredIgnoreCase(Pageable page, String name, String brand, Condition condition, BigDecimal price, String size);
i have tried using LIKE LOWER(CONCAT()) which works if i add it just for the first attribute but as soon as i add to the others i get the following error: function lower(bytea) doest not exist.
does anyone know how to fix this? or if there is a better way to implement a search function please let me know. Thanks
10 Replies
⌛ This post has been reserved for your question.
Hey @Victor! Please useTIP: Narrow down your issue to simple and precise questions to maximize the chance that others will reply in here./closeor theClose Postbutton above when your problem is solved. Please remember to follow the help guidelines. This post will be automatically marked as dormant after 300 minutes of inactivity.
You might want to use the Jakarta Criteria API
so instead of writing an SQL statement, you write Java code that specifies what should be executed (not based on SQL
Strings)i'll look into it, thanks 🤙
https://www.baeldung.com/spring-data-criteria-queries should have an introduction :)
Baeldung
Use Criteria Queries in a Spring Data Application | Baeldung
A quick tutorial to using JPA Criteria Queries using Spring Data JPA.
i was looking at that one lol
so to use it alongside jpa repository i need to create a implementation of the interface to write the custom queries?
You can create another method in your respository that accepts a specification
or predicate
one of them
and if your repository extends
JpaSpecificationExecutor, it should have that alreadyi was just extending the JpaRepository, to be honest its my first time hearing about the criteria queries and specification or predicate
imma need to take a look at it
interfaces can extend multiple interfaces so that shouldn't be an issue
the criteria API can be very useful for these things but it can also become annoying
im going to play around with it
thanks
If you are finished with your post, please close it.
If you are not, please ignore this message.
Note that you will not be able to send further messages here after this post have been closed but you will be able to create new posts.
💤 Post marked as dormant
This post has been inactive for over 300 minutes, thus, it has been archived.
If your question was not answered yet, feel free to re-open this post or create a new one.
In case your post is not getting any attention, you can try to use /help ping.
Warning: abusing this will result in moderative actions taken against you.