Search code examples
javaspringhibernatehql

Fetching data with Special Characters from HQL

I have a search functionality that is implemented on my current project, I am using HQL for the repo method - Everything is okay except searching for special characters for some reason HQL is not escaping it.

 @Query("SELECT f FROM Item f WHERE f.id=:id AND f.itemNm LIKE %:itemNm %")
 Item findByItemNm(@Param("itemNm ") String itemNm, @Param("id") Long id);

The String search I am sending is

abc_xyz(YYYYMM[t-2]-YYYYMM[t-1])_[xyz]HU.xlsx

Debugging Steps

Before sending to the Repo - I did replace all the special characters with the following -newsearchString is the one thatt is being passed to the repo

String newsearchString = searchString.replaceAll("(?=[]\\[+&|!(){}^\"~*?:\\\\_])  ", "/");

I have also tried it without replacing the special characters- It is not being returned

How do you escape characters in HQL?

Solution

  • Here is the solution for your problem...It should work for your need.

    1) Find and replace all your escape characte, and replaced by "\\" Example: [hi]_mysearch should be replaced like \\[hi\\]_mysearch

    2) Change your repo query like below

     @Query("SELECT f FROM Item f WHERE f.id=:id AND f.itemNm LIKE %:itemNm % escape '\\'")
 Item findByItemNm(@Param("itemNm ") String itemNm, @Param("id") Long id);