CODEWITHSUNDEEP
javaspringhibernatehql
Rudy
Rudy

Reputation: 749

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?

Upvotes: 2

Views: 1894

Answers (2)

Shaan
Shaan

Reputation: 598

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);

Upvotes: 1

Dean Clark
Dean Clark

Reputation: 3868

HQL does not have regular expressions out of the box. You'd have to modify the Dialect for your specific database to add that functionality. (Oracle example)

If you're just trying use like with the % wildcard, then you can do the following:

String itemNm = "abc_xyz(YYYYMM[t-2]-YYYYMM[t-1])_[xyz]HU.xlsx";
Query q = session.createQuery("from Item where itemNm like :itemNm ";
q.setString("itemNm","%"+itemNm+"%");

Upvotes: 0

Related Questions