Search code examples
javaspring-bootresultsetjdbctemplate

ResultSet Value of Aliased Column without Column Index

I'm running the following query using a jdbc template against a MySQL db:

SELECT d.*, a.mongo_id as yyyyy_profile_mongo_id,t.mongo_id as targeting_profile_mongo_id, CAST(xxxxx_type AS SIGNED) AS xxxxx_type_int
LEFT JOIN zzzzz t on t.id = d.targeting_profile_id
LEFT JOIN yyyyy a on a.id = d.yyyyy_profile_id
FROM xxxxx d

When attempting to get a string from a ResultSet using the aliased name "targeting_profile_mongo_id" I get a "Invalid Column Name" error

enter image description here

I can see in the debugger that even though I did "as targeting_profile_mongo_id" the columnName still shows as the original value of "mongo_id" and only the column label is changed.

enter image description here

Are there any work arounds here without having to loop through the metadata to find the column index?

Solution

  • I found a solution after some more digging! Looks like you can somehow get around this by putting a concat around the columns causing the issue.

    like this

    SELECT d.*, CONCAT(a.mongo_id,'') as yyyyy_profile_mongo_id,CONCAT(t.mongo_id,'') as targeting_profile_mongo_id, CAST(xxxxx_type AS SIGNED) AS xxxxx_type_int
LEFT JOIN zzzzz t on t.id = d.targeting_profile_id
LEFT JOIN yyyyy a on a.id = d.yyyyy_profile_id
FROM xxxxx d

    mysql column alias not working, have to create an empty concatenation to make it work