[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - Help on Named Query

[scout1lacno]

Hi all,

Im having problem upon using LIKE with '%' statements on NamedQuery.

anonymous wrote : Ordinary query using LIKE without '%'
  | 
  | SQL
  | select o.field1,o.field1 from TblMessages AS o WHERE o.CategoryID = '+CategoryID+' and o.date LIKE '+Date+' ORDER BY o.datReceived DESC ")
  |   | 
  | 
  | Named Query
  |   | 
  |   | @NamedQuery(name = "TblMessages.findMessage", query = "select o.field1,o.field1" +                
  |   |                 "from TblMessages AS o WHERE o.CategoryID = :CategoryID and " +
  |   |                 "and o.date LIKE :date ORDER BY o.datReceived DESC ")        
  |   | 
  |   | 	Query queryTest = em.createNamedQuery("TblMessages.findMessages");        
  |   |         queryTest.setParameter("CategoryID", CategoryID );
  |   |         queryTest.setParameter("date", date);                
  |   |         List employees = queryTest.getResultList();        
  |   |         return employees;

The abovde code would work. But

My question now is how to use the '%' on my named query.

anonymous wrote : SQL
  | select o.field1,o.field1 from TblMessages AS o WHERE o.CategoryID = '+CategoryID+' and o.date LIKE '+Date+%' ORDER BY o.datReceived DESC ")
  |   | 
  | 
  | Name Query
  | @NamedQuery(name = "TblMessages.findMessage", query = "select o.field1,o.field1" +                
  |   |                 "from TblMessages AS o WHERE o.CategoryID = :CategoryID and " +
  |   |                 "and o.date LIKE :date ORDER BY o.datReceived DESC ") 
  |   | 
  |   | Query queryTest = em.createNamedQuery("TblMessages.findMessages");        
  |   |         queryTest.setParameter("CategoryID", CategoryID );
  |   |         queryTest.setParameter("date", date);                
  |   |         List employees = queryTest.getResultList();        
  |   |         return employees;

I have read that you can set the parameter as:
queryTest.setParameter("string", "C%");
this would work on strings..

But what If I use dates or integers...
date = 2007/01/02
queryTest.setParameter("date", date+"%");

Doea anyone there have some suggestions?

Thanks in advance.

:)


View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4028656#4028656

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4028656