like with int columns

mopont · **Joined:** Thu Jan 18, 2007 4:45 pm **Posts:** 10

How can I make a query with like in hibernate for a integer column in the database. I tried but it gives me a class cast exception (java.lang.String cannot be cast to java.lang.Integer)

the objective is: when I search for the number 45 the result is:
45
450
1245
124598
How can I do it?
can I do it with the criteria API or I have to use HQL????

thatmikewilliams · **Posted:** Tue Aug 21, 2007 12:45 pm

Its pretty easy with HQL:

Code:

session.createQuery("from Entity where str(intPropertyName) like :val")
.setParameter("val", "%45%")
.list();

Can't see a way to apply an SQL function with the criteria API directly. Looks like you have to write your own custom Criterion:

Code:

session.createCriteria(Entity.class)
.add(new IntegerLikeExpression("intPropertyName", 45))
.list();

Here's a specific one covering your case:

Code:

public class IntegerLikeExpression implements Criterion {
   private String propertyName;
   private String likeValue;
   
   public IntegerLikeExpression(String propertyName, int likeValue) {
      this.propertyName = propertyName;
      this.likeValue = "%"+String.valueOf(likeValue)+"%";
   }
   
   public TypedValue[] getTypedValues(Criteria criteria,
         CriteriaQuery criteriaQuery) throws HibernateException {
      return new TypedValue[] { new TypedValue(Hibernate.STRING, likeValue, EntityMode.POJO) };
   }
   
   public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery)
         throws HibernateException {
      SQLFunction toStringFunc = criteriaQuery.getFactory().getSqlFunctionRegistry().findSQLFunction("str");
      List<String> funcArgs = new ArrayList<String>();
      funcArgs.add(propertyName);
      return toStringFunc.render(funcArgs, criteriaQuery.getFactory()) +" like ?";
   }
}

mopont · **Joined:** Thu Jan 18, 2007 4:45 pm **Posts:** 10

Yes, with HQL it's easy, but i think that the Criteria form is better (even with more code). Thanks for the nice example... in my development I was using only the API functions... it's time to develop my own functions

thanks!