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
FROM 
    (SELECT name "column1", AVG(score) "column2"  -- temporary alias 
     FROM scores 
     GROUP BY name ORDER BY AVG(score) DESC) t
LIMIT 3

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.

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 findClass.sh script shown below.

#!/bin/sh

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

PATTERN=$1
shift
FILES=$@

# Loop over files and print info for those that match
for f in $FILES
do
	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 
	fi
done

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

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("mail.smtp.host", "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

Transport.send(message);

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”, “\\\\ “);

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

Pretty good questions on the test in my opinion.