How do I use LIKE %
in Hibernate. I want to use a SQL with LIKE %
in my hbm.xml
file. I have 2 queries which I am consolidating to 1.
The query looks like this:
select * from PAY_GROUP_VW where CASE_SID=? AND CASE_TLE like %?%
I also tried %?%
. I have used like clause without %
and it works. But %
does not work.
Wrapped exception:
org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:630)
at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:424)
at org.springframework.orm.hibernate3.HibernateTemplate.executeFind(HibernateTemplate.java:343)
This is how I pass the parameters:
q.setParameter(0, csId);
q.setParameter(1, csTle);
return q.list();
Tried like %
|| ?
and got the following exception:
org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:630)
at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:424)
at org.springframework.orm.hibernate3.HibernateTemplate.executeFind(HibernateTemplate.java:343)
Maybe the user_id is not a char/varchar? You have to convert the user_id with str() to character data first!
Example:
SELECT *
FROM abc.def_vw a
WHERE user_id = ?
AND str(user_id) LIKE '%' || ?