在 Hibernate 3.2.2 的本机 SQL 查询中使用 IN 子句
以类似于此处发现的问题的方式：在本机中使用 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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
回答了我自己的问题，并且应该在发布之前进行 RTFM 处理。 “技巧”是使用
query.setParameterList()而不是query.setParameter()。Answered my own question and should have RTFM'd before posting. The "trick" is to use
query.setParameterList()as opposed toquery.setParameter().