wildcard "%" (in like) not working in placeholders

gcorro · **Joined:** Fri Aug 17, 2007 5:45 am **Posts:** 6

Hibernate version: 1.3.1

Name and version of the database you are using: Oracle 10i

Hello,

I have the next callbak DAO method:

Code:

    public Vmenstrans[] findByCodnt(
            final String startdate, 
            final String enddate, 
            final String codInt) 
    throws VmenstransDaoException {
        Collection collection = (Collection) this.hibernateTemplate.execute(
                new HibernateCallback() {
                    public Object doInHibernate(Session session){
                        Query query = session.createQuery(" from Vmenstrans " +
                                " where timestamp >= to_date(?, 'dd/mm/yy HH24:MI') " +
                                " and timestamp <= to_date(?, 'dd/mm/yy HH24:MI')  " + 
                                " and upper(codint) like ? "   +
                        " order by msgid desc ");
                        query.setCacheable(true);                        
                        query.setString(0, startdate);
                        query.setString(1, enddate);
                        query.setString(2, codInt);  
                        return query.list();                        
                    }
                }); 
        Vmenstrans ret[] = convertToDTO( (List) collection );
        return ret;            
    }

The problem comes in the like ? part of the sentence, when the placeholder replaces the variable I'm not getting the expected effect of having something like: "like %cat%"

I have tried adding;
" and upper(codint) like %?% "
or
" and upper(codint) like \'%?%\' "

but it does not work.

How can I add the wildcard % to my placeholder query.

Thank you!

nathanmoon · **Posted:** Wed Aug 22, 2007 11:22 am

this is how I have done it in the past:

Code:

... like '%' || ? || '%'

gcorro · **Joined:** Fri Aug 17, 2007 5:45 am **Posts:** 6

Thanks. that solved the problem

linuxerwang · **Joined:** Fri Sep 16, 2005 3:08 am **Posts:** 15

The best way to do it is like this:

" and upper(codint) like ? "

and:

query.setParameter(n, '%' + codint.toUpperCase() + '%');

Because it gives more flexibility and control of the pattern.