如何在查询中使用HQL检查对是否存在? [英] How to check if a pair exists using HQL in query?

问题描述

我有一个不同的ID及其名称的列表.对于每个id[0]，我们都有需要匹配的name[0].

I have a list of different ids and their names. For every id[0] we have name[0] that needs to be matched.

• ID列表，l{1,2,3,4};
• 名称列表，n{a,b,c,d};

• list of ids, l{1,2,3,4};
• list of names, n{a,b,c,d};

现在假设如果我想同时获得上述两种组合的精确匹配，那么HQL中是否有任何方法可以得到结果?

Now suppose if I want to get an exact match for both above combination, is there any way in HQL to get the result?

我正在寻找替代查询的方法:

I am looking to find a replacement for a query like:

select any_column 
from table_name 
where (id[0]=1 and name[0]=a) or (id[1]=2 and name[1]=b and so on...);

HQL查询应如下所示:

The HQL query should be something like below:

select any_column 
from table_name 
where (id,name) IN {(id[0],name[0]), (id[1], name[1]),...};

有什么建议吗?

推荐答案

我不是hql/sql专家，但是我能想到的一种方法是，在您的hql中，将ID和名称与一个空格(或其他空格)连接特殊字符)，然后使用in子句.像这样:

I am not hql/sql guy, however one way I can think of is, in your hql, you concatenate the id and name with a space (or other special char) then with in sub-clause. something like:

select * from table where concat(id, ' ', name) in (:pairList)

pairList参数是一个Java集合，您应该在hql查询调用之前进行准备，该调用具有元素id[x] + " " + name[x].

The pairList parameter is a java collection, you should prepare before the hql query call, which has element id[x] + " " + name[x].

我认为这应该可行.

如果您正在使用休眠方式，则另一种可能的解决方案是，在table实体上使用休眠方式的@Formular批注，以创建一个计算列，例如:

If you are using hibernate, another possible solution is, make use of the hibernate's @Formular annotation on your table entity, to create a calculated column, such as:

@Formula(value = " concat(id, ' ', name) ")
private String idNamePair;

然后您可以在hql中将其用作普通字段.就像... from table where idNamePair in (:paramList)

Then you can in hql use it as a normal field. like ... from table where idNamePair in (:paramList)

这篇关于如何在查询中使用HQL检查对是否存在?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！