How to search in multiple columns using a similar operator in HQL (hibernate sql)

advertisements

Hope someone can help me with this HQL query.

I'm using:

Query query = session.createQuery(sql);

where the sql is:

select distinct c.id from EstateConsumer as c where c.clientId = ? and  (c.vehicleReg1 or c.vehicleReg2) like ?

but is getting the following exception:

org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: or near line 1, column 121

So how can you use the "OR" syntax by using one "like"?

The following however works:

select distinct c.id from EstateConsumer as c where c.clientId = ? and  c.vehicleReg1 like ? or c.vehicleReg2 like ?

but I don't want to use multiple "like"'s


You can solve your problem with REGEX so for example :

REGEXP_LIKE('Hello world', 'Hello', 'mars') = 1

So you can replace your query :

select ... where c.clientId = ? and  c.vehicleReg1 like ? or c.vehicleReg2 like ?

by using this query here :

SELECT ... WHERE c.clientId = ? and REGEXP_LIKE(?, c.vehicleReg1, c.vehicleReg2) = 1
-- -------------------------------------^^

This mean if your value ? is like c.vehicleReg1 or c.vehicleReg2 return 1 else the matching is wrong


Note

@mm759, because of the way the program is coded and structured... it passes only 2 parameters (one for clientId and one for the multiple vehicleReg columns)

You can use the same parameter in multiple positions in your query like this :

q = getEntityManager().createNamedQuery(
"select ... where c.clientId = :par1 and  c.vehicleReg1 like :par2 or c.vehicleReg2 like :par2");
//-------------------------------^^----------------------------^^--------------------------^^

q.setParamettre("par1", "value1");
q.setParamettre("par2", "value2");