carlsz carlsz - 4 months ago 23
SQL Question

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().