Geek asks and answers

How to make a Hibernate request using like and%?

I have a sleep request, for example:

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 request like query.list()I got the 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".

Can I find out how I can solve this problem?

+5
source share
3 answers

this should work:

    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 + "%");

  ...
}
+12
source

It works for us ...

    whereCluase += " and lower(" + firstAttribute + ") like ?";
    queryParams.add("%" + value.toLowerCase() + "%");

Sorry to send a small snippet. But this should be enough to save you from trouble.

EDIT : from the comments lowerand toLowerCase()it is necessary to implement case sensitivity.

+3
source

MatchMode ( , ).

 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 , .

:

  • : '%' + + '%'
  • END: '%' +
  • START: value + '%'
  • EXACT: value
+2
source

More articles:


All Articles