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
Hibernate provide option to execute native SQL queries through the use of SQLQuery object. Hibernate SQL Query is very handy when we have to execute database vendor specific queries that are not supported by Hibernate API. For example query hints or the CONNECT keyword in Oracle Database.
You can use native SQL to express database queries if you want to utilize database-specific features such as query hints or the CONNECT keyword in Oracle. Hibernate 3. x allows you to specify handwritten SQL, including stored procedures, for all create, update, delete, and load operations.
We can use @Query annotation to specify a query within a repository. Following is an example. In this example, we are using native query, and set an attribute nativeQuery=true in Query annotation to mark the query as native. We've added custom methods in Repository in JPA Custom Query chapter.
Answered my own question and should have RTFM'd before posting. The "trick" is to use query.setParameterList()
as opposed to query.setParameter()
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With