Map database type to concrete Java class

Background

Map a column data type to its corresponding Java class.

Problem

A query returns meta information from a database:

SELECT
  rb.object_schema,
  rb.object_name,
  rb.column_name
FROM
  dictionary.resource_bundle rb

For example, this query returns (the self-referential):

dictionary, resource_bundle, column_name

Where ‘dictionary’ is the schema name, ‘resource_bundle’ is the object_name, and ‘column_name’ is the column_name.

It would be great to do something like:

SELECT
  rb.object_schema,
  rb.object_name,
  rb.column_name,
  rb.column_type
FROM
  dictionary.resource_bundle rb

And have this query return:

dictionary, resource_bundle, column_name, varchar

Then use JDBC to discover that varchar is mapped to java.lang.String.

Questions

1. In PostgreSQL, how do you determine what type is used to store the data, given a schema name, object name (guaranteed to be table or view), and column name?
2. In a database-neutral fashion (leveraging JDBC), how do you determine the mapping a database uses for a given data type?

Answer

Solution

The answer is more complicated than using the getMetaData method because there is no direct mapping from the integer types returned by the getMetaData method and the full class name. This solution requires two pieces of code:

• Implementing a method to obtain the java.sql.Types constant integer value.
• Creating a method to translate that value to a class name.

Java Type Method

The following method retrieves the meta information:

  public String getJavaType( String schema, String object, String column )
    throws Exception {
    String fullName = schema + '.' + object + '.' + column;
    DatabaseMetaData metaData = getConnection().getMetaData();
    ResultSet columnMeta = metaData.getColumns( null, schema, object, column );
    String javaType = null;

    if( columnMeta.first() ) {
      int dataType = columnMeta.getInt( "DATA_TYPE" );
      javaType = SQLTypeMap.convert( dataType );
    }
    else {
      throw new Exception( "Unknown database column " + fullName + '.' );
    }

    return javaType;
  }

Static Conversion Method

The constant integer values must be translated to a class name. This can be accomplished as follows:

import java.sql.Types;

/**
 * Converts database types to Java class types.
 */
public class SQLTypeMap {
    /**
     * Translates a data type from an integer (java.sql.Types value) to a string
     * that represents the corresponding class.
     * 
     * @param type
     *            The java.sql.Types value to convert to its corresponding class.
     * @return The class that corresponds to the given java.sql.Types
     *         value, or Object.class if the type has no known mapping.
     */
    public static Class<?> toClass(int type) {
        Class<?> result = Object.class;

        switch (type) {
            case Types.CHAR:
            case Types.VARCHAR:
            case Types.LONGVARCHAR:
                result = String.class;
                break;

            case Types.NUMERIC:
            case Types.DECIMAL:
                result = java.math.BigDecimal.class;
                break;

            case Types.BIT:
                result = Boolean.class;
                break;

            case Types.TINYINT:
                result = Byte.class;
                break;

            case Types.SMALLINT:
                result = Short.class;
                break;

            case Types.INTEGER:
                result = Integer.class;
                break;

            case Types.BIGINT:
                result = Long.class;
                break;

            case Types.REAL:
            case Types.FLOAT:
                result = Float.class;
                break;

            case Types.DOUBLE:
                result = Double.class;
                break;

            case Types.BINARY:
            case Types.VARBINARY:
            case Types.LONGVARBINARY:
                result = Byte[].class;
                break;

            case Types.DATE:
                result = java.sql.Date.class;
                break;

            case Types.TIME:
                result = java.sql.Time.class;
                break;

            case Types.TIMESTAMP:
                result = java.sql.Timestamp.class;
                break;
        }

        return result;
    }
}

Note that different databases can have different variations on the mapping.