0

I am trying to implement a repository query method to search an address by its house name. I want this search query to be case-insensitive.

I have the following search method defined in the Address repository. But this search is case sensitive.

 @Query(value = "select ad from #{#entityName} ad where ad.houseName LIKE :houseName%")
    public Page<LocalAddress> findByHouseName(@Param("houseName") String houseName, Pageable page);

How do I make this query case insensitive?

Charlie
  • 3,113
  • 3
  • 38
  • 60
  • Possible duplicate of [How can I search (case-insensitive) in a column using LIKE wildcard?](http://stackoverflow.com/questions/2876789/how-can-i-search-case-insensitive-in-a-column-using-like-wildcard) – jediz May 21 '17 at 07:14
  • @DeltaCharlie If my answer helped you please don't forget to accept it ) – Cepr0 May 22 '17 at 06:19

2 Answers2

2

To implement your task you don't need manually write a query, just use a Spring Data magic:

Page<LocalAddress> findByHouseNameIgnoreCaseContaining(String partOfHouseName, Pageable page);

This method will use case-insensitive like operator and fetch your data independently from DB.

Nevertheless, you can specify this explicitly in the JPQL query:

@Query("select a from LocalAddress a where upper(a.houseName) like concat('%', upper(?1), '%')")
Page<LocalAddress> getByName(String partOfHouseName, Pageable page)

More info here: JPQL Reference

Cepr0
  • 28,144
  • 8
  • 75
  • 101
  • I am already aware of this method. I was just trying to implement the same with SpEL. With findBy queries names get too long at times so there must be an alternative. – Charlie May 22 '17 at 02:55
0

Well, this is rather question of a database engine. SpEL only evaluates the expression and replaces the placeholder with a value. See question How can I search (case-insensitive) in a column using LIKE wildcard?

Community
  • 1
  • 1
jediz
  • 4,459
  • 5
  • 36
  • 41
  • So is it not possible to do a case-insensitive search using LIKE in SpEL? I dont want my query to be tied to any specific DB engine – Charlie May 21 '17 at 08:17
  • It sounds like you are trying to use the wrong tool to do the job. How would you expect Spring to implement the feature? I doubt it's intelligent enough to rewrite your query magically (it would have to support all database vendors). – jediz May 21 '17 at 08:48
  • I think I understand your point. Since syntax for case-insensitive LIKE is different based on the DB engine, it is not possible to generate a dynamic query using SpEL right? – Charlie May 22 '17 at 06:10
  • Right. It's not impossible, but it's not an easy thing in general. – jediz May 22 '17 at 08:03