MySQL : JDBC call into ResultSetMetaData returns incorrect Column Name instead of the Alias in the SQL

Writing by on Saturday, 15 of December , 2012 at 5:11 pm

Yeah, this is a tricky one. Is ResultSetMetaData.getColumnName(column) not returning the aliased column?

The simplest way to fix it to use ResultSetMetaData.getColumnLabel(column) which is apparently the “JDBC compliant” way of getting it.

I found the answer on this thread: Bug #43684: JDBC Metadata ColumnName Name is incorrect if using field alias

So, let’s look at this example.

-- Extremely Simplified Top N style nested SQL with aliasing
-- say "Name" and "Score" are two columns in the table "Scores"
-- With this type of SQL, I want to show there's no way to get to them
SELECT t.column1 "Name Alias", t.column2 "Score Alias"  -- alias them back
    (SELECT name "column1", AVG(score) "column2"  -- temporary alias 
     FROM scores 
     GROUP BY name ORDER BY AVG(score) DESC) t

Now if you look at the following Java Code Snippet

ResultSet rs = stmt.executeQuery(sqlQueryShownAbove);
ResultSetMetaData rsmd = rs.getMetaData();
String firstColumnName = rsmd.getColumnName(1);   // returns "column1"
String firstColumnLabel = rsmd.getColumnLabel(1); // returns "Name Alias"

Well, it’s an API thing. The MySQL JDBC driver has ensured that you can get to the Original Column Name (getColumnName) as well as the Aliased Column Name (getColumnLabel) and has left it to you to select what you need. Just that – it’s not obvious. However in the above example, we’re not getting the Original Column Name at all, but that is because in this case a temporary table was created in the inner SQL which was aliasing the “real” original columns thereby making them the original columns of the temporary table.

In my case, the tricky part was that I was sending the ResultSet to a 3rd party library, which after decompiling seemed to use getColumnName(column). BOOM !!! hack hack hack away
Luckily I had a method in the 3rd party library to override the column labels (PHEW).

But if getColumnLabel not an option as it could have been in my case, you could also try to set ?useOldAliasMetadataBehavior=true in the JDBC connection URL as documented in the MySQL JDBC URL Format.

Here’s the source for com.mysql.jdbc.ResultSetMetaData. Look for the getColumnName(int column) and getColumnLabe(int column) implementations.

Personally, I would have expected or liked to see getOriginalColumnName to get the original column name, but that of course is another discussion.

Leave a comment

Category: Java,SQL DB

Search all JARs for a class

Writing by on Wednesday, 10 of October , 2012 at 9:48 pm

If you need to search for a class (say HelloWorld) in a lib/ folder with several other jars just to see if it exists, here’s a simple command to confirm that.

find ./lib/*.jar -exec jar -tf ‘{}’ \; | grep HelloWorld.class

For a more detailed listing, you can use my script shown below.


# Check usage
if [ $# -lt 2 ] ; then
    echo "Syntax: $0  <Class> <Folder>"
    echo "Example: $0 HelloWorld.class ./lib/a*.jar"
    exit 1


# Loop over files and print info for those that match
for f in $FILES
	LINES=`jar -tf $f | grep $PATTERN | wc -l`
	if [ $LINES -ge 1 ] ; then
		echo -e "\n\n**** Found match in:" $f " ****\n"	
		jar -tf $f | grep $PATTERN 

Leave a comment

Category: Java,Linux

Get the Schema/Metadata out of a SQL Query without executing it

Writing by on Thursday, 7 of June , 2012 at 5:06 pm

You can use PreparedStatement to compile the SQL and then get the ResultSetMetaData from it.

// Get the Connection
Connection conn = getConnection();

// Get the MetaData
PreparedStatement ps = conn.prepareStatement(sql);
ResultSetMetaData rsmd = ps.getMetaData();

// Get the alias & type
for (int i=0; i<rsmd.getColumnCount(); i++) {
   String alias = rsmd.getColumnName(i+1);
   String strType = rsmd.getColumnTypeName(i+1);
   int type = rsmd.getColumnType(i+1)

Leave a comment

Category: Java,SQL DB

Internationalization issues: How to set UTF-8 with MimeMessage for email in Java

Writing by on Tuesday, 31 of January , 2012 at 9:50 pm

Content and subject for Chinese, Japanese, Korean (CJK) and other language characters showing up garbled or with question marks ????

Gosh! I know – What a pain! But, there’s a solution! Simply set the encoding to UTF-8 and not ISO8859_1 or any other encoding format.

Found a good post from Isocra Consulting that solves this issue.

Properties props = new Properties();
// put in your SMTP host in here
props.put("", "localhost");
Session s = Session.getInstance(props, null);

MimeMessage message = new MimeMessage(s);
message.setFrom(new InternetAddress(from));
message.addRecipient(Message.RecipientType.TO, new InternetAddress(to));
message.setHeader("Content-Type", "text/plain; charset=UTF-8");
message.setSubject(subject, "UTF-8"); //IMPORTANT

// Either use setText() or setContent()
message.setText(body, "UTF-8"); //IMPORTANT 
// message.setContent(body, "text/plain; charset=UTF-8"); // ALTERNATIVELY USE THIS


Leave a comment

Category: Java

Escaping spaces in a Properties file in Java

Writing by on Monday, 5 of December , 2011 at 6:25 pm

Problem 1:

Can you escape keys or values in a properties file? Yes. Use backslash \ as the escape character.
So Hello World=foo should be written as Hello\ World=foo
Wikipedia has an excellent article

Problem 2:

You might most probably use the String replaceAll(String regex, String replacement) method. So how do you escape spaces?

// This Will NOT WORK !!
String escaped = “Hello World”.replaceAll(” “, “\\ “);

// This is the right way to do it, since we need to work with regex
String escaped = “Hello World”.replaceAll(“\\s”, “\\\\ “);

Leave a comment

Category: Java

Better Programmer

Writing by on Thursday, 10 of November , 2011 at 8:08 am

I just took the Java Test before going to bed and got a 96% on my certificate.

Pretty good questions on the test in my opinion.

Leave a comment

Category: Java

Shivdev Kalambi's Blog

Shivdev Kalambi is a Software Development Manager, previously a Principal Software Engineer at ArcSight/HP. With over 16 years' experience in software development, he's worked on several technologies and played different roles and contributed to all phases of projects. Non-tech activies include Ping-pong, Rock Climbing and Yoga at PG, Golf, Skiing, Swimming & a beer enthusiast.