Thursday, May 17, 2012

MySQL function GROUP_CONCAT and CAST

I have a table of emails that may have multiple entries for a contact and I wanted to join all the emails together before joining the emails table to the contacts in my select. My query looked something like this:

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

Posted here in case this helps someone going forward.

3 comments:

tourist said...

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 :)

B said...

I love you.

FkJ said...

Very good, thanks