CODEWITHSUNDEEP
javasqlhibernatehql
user2319262
user2319262

Reputation: 91

How to search in multiple columns using one like operator in HQL (hibernate sql)

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

Upvotes: 1

Views: 3437

Answers (2)

Buurman
Buurman

Reputation: 1984

A complicated variant such as the regexp_like variant proposed by @YCF_L likely includes additional performance overhead, if only because the DBMS has trouble optimizing such a query to the same degree as one using 'familiar' operators.

I would go for a solution where you reuse a parameter.

You state,

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)

I don't understand why you can't just capture one of the parameters and reuse it. YCL_F gives a very nice example of one in his update, but as long as you're using JPA, why not go for the full JPA solution? This has added benefits in that it is typesafe, refactorsafe and can be (better) cached than a native SQL query.

For example,

CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
CriteriaQuery<C> jpaquery = cb.createQuery(C.class);
Root<C> root = jpaquery.from(C.class);

jpaquery.where(cb.and(cb.equal(root.get(C_.clientId), parameter1),cb.or(cb.like(root.get(C_.vehicleReg1), parameter2), cb.like(root.get(C_.vehicleReg2), parameter2)));
jpaQuery.select(...);

return getEntityManager().createQuery(jpaQuery).getResultList();

Upvotes: 1

Youcef LAIDANI
Youcef LAIDANI

Reputation: 59978

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");

Upvotes: 3

Related Questions