0

In my Spring Boot app, I use @Query as shown below on PostgreSQL and compare name value case-insensitive:

@Query(value = "SELECT DISTINCT e.id, e.name FROM Employee e " +
        "WHERE e.name ILIKE :name) ",
        nativeQuery = true)
List<Recipe> getEmployees(@Param("name") String name);

Although it seems to be ok for some time, it cannot return the same result randomly and it continues after rebuilding app and restarting. But it is working when executing the same query on DBeaver.

So, how should I compare equality of name field case insensitively in PostgreSQL?

Jack
  • 1
  • 21
  • 118
  • 236
  • 1
    Can the `name` parameter be null? Can we have a look at your `Recipe` object? Could you try again with this query : `SELECT DISTINCT e.id, e.name FROM Employee e WHERE LOWER(cast(e.name AS TEXT)) LIKE CONCAT('%', LOWER(cast(:name AS TEXT)),'%')` – Marc Bannout Dec 06 '22 at 09:20
  • 1
    @MarcBannout Thanks for your reply, it seems to work. Actually I let the user to pass null or empty string and convert them in the app before query. So, maybe it is good idea to use `lower()` method of PostgreSQL. But this approach also useful, many thanks. – Jack Dec 06 '22 at 09:25
  • 1
    Glad it worked, though, if the solution I provided worked. I suspect that the problem wasn't with `ILIKE`. You can always try to make it work with `ILIKE` if you prefer not to use the `LOWER()` function. Maybe you could try with `SELECT DISTINCT e.id, e.name FROM Employee e WHERE cast(e.name AS TEXT) ILIKE CONCAT('%', cast(:name AS TEXT),'%')` – Marc Bannout Dec 06 '22 at 09:48

1 Answers1

1

Your query seems to be wrong. There is twice the parameter :name used. it should be

@Query(value = "SELECT DISTINCT e.id, e.name FROM Employee e " +
    "WHERE name ILIKE :name) ",
    nativeQuery = true)
List<Recipe> getEmployees(@Param("name") String name);

Another way to implement case insensitive search is to use an interface method:

List<Recipe> findByNameIgnoreCase(String name);
Simon Martinelli
  • 34,053
  • 5
  • 48
  • 82
  • Thanks Senior, actually it is a copy mistake and it is correct (updated). So, how can I make comparison in my native PostgreSQL query? – Jack Dec 05 '22 at 17:37
  • By the way, I have seen your useful comments on SO for searching and would like to ask you the following issue --> – Jack Dec 05 '22 at 17:40
  • Actually I created a custom filter using Specifications before asking the question. But, although it works well in my Recipe entity, I cannot make it filter related entities e.g. Ingredient. I use Hibernate and actually I implemented all the necessary relationship between entities properly. When I retrieve a Recipe data, I can receive Ingredient data belonging to this Recipe, but I am not sure if I can filter data on Ingredient as well. – Jack Dec 05 '22 at 17:40
  • @Numichi Here is the approach that I followed, could you just have a look at and inform me if it is possible to filter Recipes having an ingredient with name? https://blog.piinalpin.com/2022/04/searching-and-filtering-using-jpa-specification/ – Jack Dec 05 '22 at 17:40
  • Any idea about these issues besides text comparison in PostgreSQL? – Jack Dec 05 '22 at 17:41
  • See my updated answer. And about the other issue, please post a new question. Don't ask in comments – Simon Martinelli Dec 05 '22 at 17:43
  • I will check. Here is my question. If you suggest me a way to filter based on related entities, I would be happy. You can post your example on that page (you can give your example based on the approach shared in my previous comment)). [How to create custom query using Spring Data JPA Specifications?](https://stackoverflow.com/questions/74680426/how-to-create-custom-query-using-spring-data-jpa-specifications/74683115?noredirect=1#comment131828439_74683115) – Jack Dec 05 '22 at 17:48
  • And thanks for your reply. But I need to make comparison based on specific field in the joined tables (I just simplified question and omitted some code). So, I really need to make this sorting on PostgreSQL side – Jack Dec 05 '22 at 17:49
  • So please ask what you really want to know – Simon Martinelli Dec 06 '22 at 07:28
  • Thanks a lot for your helps, voted up. I added the related entities to [this](https://stackoverflow.com/questions/74685356/dynamic-query-with-query-in-spring-data-jpa/74698090#74698090) page >>> – Jack Dec 06 '22 at 08:35
  • I have 3 related entities (Recipe, RecipeIngredient and Ingredient) and while retrieving data from Recipe using `recipeRepository.findAll()` method by using custom filter with Specifications, I need to filter result based on Ingredient Name and need to pass filter name as a key parameter (please see **Filter by Name and Sort by Release Date ASC** section on [this](https://blog.piinalpin.com/2022/04/searching-and-filtering-using-jpa-specification/) article that I followed) >>> – Jack Dec 06 '22 at 08:35
  • So, you may first have a look at the answer @M.Denium posted on [that](https://stackoverflow.com/questions/74685356/dynamic-query-with-query-in-spring-data-jpa/74698090#74698090) page and share your comments based on this answer. Thanks in advance... – Jack Dec 06 '22 at 08:35