JDBC Metadata and Column Types

Today I’ve been testing my data caching module… it’s pretty straight foward, the relevant part for today’s ramblings is that it takes an SQL query, executes that query on some external database, and then creates a table in my database to mirror the resultset.

Making the database connection “some external database” was simple enough (I talk about that a little in my entry on class loaders…), and executing the query is easy. Once I’ve obtained the ResultSet, I simply get the ResultSetMetaData and iterate over the columns. The mistake I made was that I’d been calling metadata.getColumnTypeName( idx ) and getting the vendor-specific type name for that column. The fix was simple yet dissapointing, and I’m hoping one of you will have a more elegant solution —

The ResultSetMetaData class also provides a method called getColumnType() that takes the index of the column I want the information about. getColumnType, as opposed to getColumnTypeName(), returns the “JDBC Type” of the column, as an int as defined by the java.sql.Types class. The Types class contains fields that represent each of the generic SQL types (called JDBC Types in Java), however, as far as I could tell, there was no way to get string that represented that SQL Type… just the integer value of it’s field in Types. What good is that?

So anyway, to get around this little annoyance, I made myself a little helper class that looks something like the following:

public class SQLTypeMap
{
    private final static Map typemap;

    static
    {
        typemap = Collections.synchronizedMap( new HashMap() );

        Field[] fields = Types.class.getFields();
        for ( int i = 0; i < fields.length; i++ )
        {
            String fieldname = fields[i].getName();

            try
            {
                int iValue = fields[i].getInt( null );
                Integer value = new Integer( iValue );

                typemap.put( value, fieldname );
                typemap.put( fieldname, value );
            }
            catch ( IllegalAccessException e )
            {

            }
        }
    }

    public final static String getTypeName( int type ) { ... }
    public final static int getType( String name ) { ... }
}

Java provides amazing support for introspection, so I took advantage of that to make a hash map that contained entries mapping the integer value, to the name of the JDBC Type. That way, to get the name of the SQL type of a column in my result set, I can do the following:

int type = rsmetadata.getColumnType( idx );
String typename = SQLTypeMap( type );

It’s quick and easy, but feels to dirty to be the “right way”. Anyone have any better ideas, or know something I obviously don’t about where to find this information? If this is the best way, why??? It seems a little rediculous.