SELECT mc.contactid, ce.emailAddress as emailAddress
FROM merchant_contact mc
JOIN (
SELECT contactid, GROUP_CONCAT(DISTINCT emailaddress SEPARATOR ',') as emailAddress
FROM contact_email GROUP BY contactid
) ce ON ce.contactid = mc.contactid
It ran fine in dbVisualizer, but then when it ran as part of my Java app it returned values like
[B@2d7f2fae
[B@79135fd7
[B@66f95a5a
These looked something like pointer addresses, not the email values I was expecting.
After trying a few things that did not work, including adding group_concat as an sql function to my configuration (recommended here) I tried changing this:
query.addScalar("emailAddress", Hibernate.STRING);
to
query.addScalar("emailAddress");
to let Hibernate try to determine the type itself. Although this didn't fix it, I did get more information to work with, because it complained "No Dialect mapping for JDBC type: -4". Searching for this got me to this post on CodeRanch (which I find helpful from time to time) where the guy fixed his problem by CASTing it from an NVARCHAR to a VARCHAR. I tried a variation on this and it fixed my issue, so here is what I ended up doing:
SELECT mc.contactid, ce.emailAddress as emailAddress
FROM merchant_contact mc
JOIN (
SELECT contactid, CAST(GROUP_CONCAT(DISTINCT emailaddress SEPARATOR ',') AS char) as emailAddress
FROM contact_email GROUP BY contactid
) ce ON ce.contactid = mc.contactid
FROM merchant_contact mc
JOIN (
SELECT contactid, CAST(GROUP_CONCAT(DISTINCT emailaddress SEPARATOR ',') AS char) as emailAddress
FROM contact_email GROUP BY contactid
) ce ON ce.contactid = mc.contactid
Posted here in case this helps someone going forward.
3 comments:
Thank you very much for this solution. I have been searching for it - and the above solution worked for me. Adding function to Hibernate's run-time configuration does not help in this case - the change in above query is the only sufficient solution. Works perfectly for me :)
I love you.
Very good, thanks
Post a Comment