1

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

Youcef LAIDANI
  • 55,661
  • 15
  • 90
  • 140
user2319262
  • 91
  • 5
  • 15

2 Answers2

3

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");
Youcef LAIDANI
  • 55,661
  • 15
  • 90
  • 140
  • Thanks a lot for your suggestion! That regex worked and got past the createQuery exception I had. Quick question, is the REGEXP_LIKE replacing the param with % sign before and after the text? I'm asking this because i'm already passing the % sign in my "?" parameter. – user2319262 Mar 13 '17 at 12:01
  • @user2319262 yes it replace the % in the start and in the end, here is a link you can understand more how you can use REGEX_LIKE http://www.sqlsnippets.com/en/topic-11741.html, `select target from targets where target LIKE '%bc%' ;` can replaced by `select target from targets where REGEXP_LIKE( target, 'bc' ) ;` hope this can help you – Youcef LAIDANI Mar 13 '17 at 12:25
1

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();
Buurman
  • 1,914
  • 17
  • 26