Using IN clause in a native sql query with Hibernate 3.2.2
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
Answer
Answered my own question and should have RTFM'd before posting. The "trick" is to use query.setParameterList() as opposed to query.setParameter().