java hql in,Hibernate HQL：将对值作为IN子句中的参数传递

最新推荐文章于 2021-02-24 01:34:35 发布

转载最新推荐文章于 2021-02-24 01:34:35 发布 · 319 阅读

标签

#java hql in

收录于

在Hibernate中使用HQL查询时，遇到一个挑战：如何将不同类型的参数（如字符串ID和日期）作为一对值传递给IN子句。尝试了Map、对象拼接和二维数组，但未成功。一种可能的解决方案是通过字符串连接ID和日期（如'id.date'），并在查询中使用集合传递这些连接后的字符串。如果性能是关注点，可以考虑在数据库中创建一个函数索引来实现相同的功能。

I am facing a problem: how to pass parameters to HQL for pair values in query with IN clause like -

select id, name from ABC where (id, reg_date) in ('x', 'y'). And the parameters are of different data types string (id) and reg_date (date).

Now, in HQL I can write:

hql = "select id, Name from ABC where (id, date) in (:listparam1)"

hibernateTemplate.findbynamedparam(hql, "listparam1", values).

Problem: how to pass a list of id and date pairs in HQL? Both parameters are of different data types: one is String and the other is Timestamp. I tried Map and concatenation of both values in Object, 2 dimensional array, but nothing is working.

解决方案

select a.id, a.name from ABC a where a.id || '.' || a.date in (:id_date)

....and you need to pass as parameter "id_date" collection of string of concatenated id and name. If performance is an obstacle, you can create function index on your SQL database(s) that do exactly the same job (of course if your database(s) supports function indexes)