如何在HQL中使用一个类似的运算符在多列中搜索（hibernate sql） [英] How to search in multiple columns using one like operator in HQL (hibernate sql)

问题描述

我正在使用：

 查询查询= session.createQuery（sql）; 
  

其中的sql为：

 从EstateConsumer中选择不同的c.id作为c，其中c.clientId =？和（c.vehicleReg1或c.vehicleReg2）像？ 
  

但会产生以下异常：

 org.hibernate.hql.ast.QuerySyntaxException：意外的AST节点：或者接近第1行，第121列
  

那么如何使用一个like来使用OR语法呢？

>

 从EstateConsumer中选择不同的c.id作为c，其中c.clientId =？和c.vehicleReg1像？或c.vehicleReg2像？ 
  

但我不想使用多个like的

$ b $您可以使用 REGEX 解决您的问题，例如：

<$> p $ p> REGEXP_LIKE（'Hello world'，'Hello'，'mars'）= 1


所以你可以替换你的查询：

  select ... where c.clientId =？和c.vehicleReg1像？或c.vehicleReg2像？ 
  

在这里使用此查询：

  SELECT ... WHERE c.clientId =？和REGEXP_LIKE（？，c.vehicleReg1，c.vehicleReg2）= 1 
  -  ----------------------------- -------- ^^ 
  

这意味着如果您的值 ？就像 c.vehicleReg1 或 c.vehicleReg2 return 1 其他匹配错误

---

注意

@ mm759，因为程序的编码和结构方式......它
仅传递2个参数（一个用于clientId和一个对于多个
vehicleReg列）

您可以在查询中的多个位置使用相同的参数，如下所示：

  q = getEntityManager（）。createNamedQuery（
select ... where c.clientId =：par1 and c.vehicleReg1如：par2或c.vehicleReg2，如：par2）; 
 // ------------------------------- ^^ ----------- ----------------- ^^ -------------------------- ^^ 
 
 q.setParamettre（par1，value1）; 
 q.setParamettre（par2，value2）; 
  

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

这篇关于如何在HQL中使用一个类似的运算符在多列中搜索（hibernate sql）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！