How to do Hibernate query using like and %?

user_111398 0
Viewed 25953

I have a Hibernate SQL query like this:

public void funcA(String str) {
  StringBuilder sql = new StringBuilder();
  sql.append("select fieldA from tableA where fieldB like '%:searchKey%'");

  ...

  session.createSQLQuery(sql.toString())
  .addScalar("fieldA", StandardBasicTypes.STRING)
  .setParameter("searchKey", str);

  ...
}

when I make a query like this query.list(), I got following error:

[WARNING ] SQL Error: -7, SQLState: 42601[ERROR   ] The character "%" following "fieldB like" is not valid.
[ERROR   ] An error occurred during implicit system action type "2".  Information returned for the error includes SQLCODE "-7", SQLSTATE "42601" and message tokens "%|fieldB like".

May I know how could I solve this problem?

hibernate sql java
3 Answers

In the same example, i have used MatchMode (class of hibernate, to deal with like predicate restrictions).

 public void funcA(String str) {
  StringBuilder sql = new StringBuilder();
  sql.append("select fieldA from tableA where fieldB like :searchKey");

  ...
  session.createSQLQuery(sql.toString())
  .addScalar("fieldA", StandardBasicTypes.STRING)
  .setParameter("searchKey", MatchMode.ANYWHERE.toMatchString(str));

  ...
}

MatchMode.ANYWHERE.toMatchString(str) : toMatchString method converts the value string to enum from which it is called.

Options:

  • ANYWHERE: '%' + value + '%'
  • END: '%' + value
  • START: value + '%'
  • EXACT: value
user_56348220
Related