在 Hibernate 3.2.2 的本机 SQL 查询中使用 IN 子句

发布于 2024-10-30 14:56:22 字数 1953 浏览 4 评论 0原文

以类似于此处发现的问题的方式：在本机中使用 IN 子句sql查询;我试图通过 Hibernate 中的本机 SQL 查询来使用 IN() 子句。虽然另一个问题的作者能够使用 JPA，但我不能。另外，我一直坚持使用 3.2.2 版本。

似乎 Hibernate 本身不支持 IN() ，因为它在应用查询参数时尝试将我的 ID 列表（长原语数组）转换为二进制形式：query. setParameter("publisherGroups", [1243536264532,1243536264533,1243536264535]);

来自 Hibernate：

SELECT sum(C2CReportedConversion) 作为 c2CConversion， sum(C2CReportedRevenue) 作为 c2CRevenue， sum(I2CReportedConversion) 作为 i2CConversion， sum(I2CReportedRevenue) 作为 i2CRevenue， sum(Clicks) 作为点击次数， sum(Impressions) 作为印象数， sum(Requests) 作为请求， sum(Views) 作为视图，合并（名称，显示名称）从 UiTemplateReportingCache 加入广告单元使用 (AdUnitId) 在哪里出版商 ID = ? AND PublisherGroupId IN ( ？）且日期 >= ? 和日期 <= ? 分组依据合并（名称， DisplayName)

来自 mysql 日志：

SELECT sum(C2CReportedConversion) as c2CConversion, sum(C2CReportedRevenue) as c2CRevenue, sum(I2CReportedConversion) as i2CConversion, sum(I2CReportedRevenue) as i2CRevenue, sum(Clicks) as clicks, sum(Impressions) 作为展示次数，sum(Requests) 作为请求，sum(Views) 作为视图，coalesce(Name, DisplayName) FROM UiTemplateReportingCache JOIN AdUnit USING (AdUnitId) WHERE PublisherId = 1239660230591 AND PublisherGroupId IN (_binary'��\0ur\ 0[Jx ��u�\0\0xp\0\0\0 \0\0!��T\0\0!��U\0\0!��W\0\0!��m\0\0!��n\0\0! ��t\0\0!��{\0\0!��|\0\0!��}\0\0!��~\0\0#��\0\ 0$|��S') AND 日期 >= '2011-03-17 00:00:00' AND 日期 <= '2011-03-18 23:59:59' GROUP BY coalesce(Name, DisplayName)

请注意启动 IN() 值的 _binary 部分。使这项工作成功的秘诀是什么？我使用的 Hibernate 版本可以做到这一点吗？如果没有，我还有什么选择？

预先感谢，

卡尔

原文

In a fashion similar to the question found here: Using IN clause in a native sql query; I am attempting to make use of an IN() clause by way of a native SQL query in Hibernate. While the author in the other question was able to use JPA, I am not. In addition, I am stuck with version 3.2.2.

It seems as though Hibernate doesn't support IN() natively because it is attempting to convert my list (array of long primitives) of IDs into binary form when applying the query parameters: query.setParameter("publisherGroups", [1243536264532,1243536264533,1243536264535]);

From Hibernate:

SELECT sum(C2CReportedConversion) as c2CConversion, sum(C2CReportedRevenue) as c2CRevenue, sum(I2CReportedConversion) as i2CConversion, sum(I2CReportedRevenue) as i2CRevenue, sum(Clicks) as clicks, sum(Impressions) as impressions, sum(Requests) as requests, sum(Views) as views, coalesce(Name, DisplayName) FROM UiTemplateReportingCache JOIN AdUnit USING (AdUnitId) WHERE PublisherId = ? AND PublisherGroupId IN ( ? ) AND Date >= ? AND Date <= ? GROUP BY coalesce(Name, DisplayName)

From the mysql logs:

SELECT sum(C2CReportedConversion) as c2CConversion, sum(C2CReportedRevenue) as c2CRevenue, sum(I2CReportedConversion) as i2CConversion, sum(I2CReportedRevenue) as i2CRevenue, sum(Clicks) as clicks, sum(Impressions) as impressions, sum(Requests) as requests, sum(Views) as views, coalesce(Name, DisplayName) FROM UiTemplateReportingCache JOIN AdUnit USING (AdUnitId) WHERE PublisherId = 1239660230591 AND PublisherGroupId IN (_binary'��\0ur\0[Jx ��u�\0\0xp\0\0\0 \0\0!��T\0\0!��U\0\0!��W\0\0!��m\0\0!��n\0\0!��t\0\0!��{\0\0!��|\0\0!��}\0\0!��~\0\0#��\0\0$|��S') AND Date >= '2011-03-17 00:00:00' AND Date <= '2011-03-18 23:59:59' GROUP BY coalesce(Name, DisplayName)

Notice the _binary part that starts the IN() value. What's the trick to making this work? Will the version of Hibernate I'm using even do this? If not, what alternatives do I have?

Thanks in advance,

Carl

分享到QQ

分享到微博