jdbc: Get the SQL Type Name from java.sql.Type code

Question

I have an array with Field Names and jdbc Type codes. (Those int codes that you can find in

http://download.oracle.com/javase/1.4.2/docs/api/constant-values.html#java.sql.Types.BIT

I use a level 4 Driver.

I can't figure out how to ask the driver for the corresponding SQL (DDL) Type names. It would be useful in jdbc and in native dialects.

I have
(CustomerId, 1) (CustomerName, -8)

and I want

(customerId, INT) (customerId, VARCHAR(200))

Where can I find functions that help me with that? I am using jdbc in jython via zxJDBC, so I can use all java and python DB API 2.0 functionality.

Answer 1

Java 8 and later: JDBCType & SQLType

With improvements in the API's, as of Java 8 and JDBC 4.2, we have JDBCType and SQLType, and in the same spirit as some of the other examples can be simply used as follows:

String typeName = JDBCType.valueOf(-5).getName();

But of course, why using the numeric types to begin with. Make a habit, and switch over from numeric's to the enum constants defined in JDBCType:

String typeName = JDBCType.BIGINT.getName();

et voilà!

However, that might not be enough to have something good enough for using in a DDL ... you might need to implement vendor specific translation. As an example, you might need to consider to translate VARCHAR to VARCHAR2 in the case of Oracle.

Answer 2

To specifically answer "Get the SQL Type Name from java.sql.Type code", if you are using a version of java that can do reflection, here is a small utility method that pretty much does the same thing:

public Map<Integer, String> getAllJdbcTypeNames() {

    Map<Integer, String> result = new HashMap<Integer, String>();

    for (Field field : Types.class.getFields()) {
        result.put((Integer)field.get(null), field.getName());
    }

    return result;
}

Add import java.lang.reflect.Field; to your import declarations. Once you have that in place, simply use it as follows:

...
Map<Integer, String> jdbcMappings = getAllJdbcTypeNames();

String typeName = jdbcMappings.get(-5); // now that will return BIGINT
...

Answer 3

public static String getSqlTypeName(int type) {
    switch (type) {
    case Types.BIT:
        return "BIT";
    case Types.TINYINT:
        return "TINYINT";
    case Types.SMALLINT:
        return "SMALLINT";
    case Types.INTEGER:
        return "INTEGER";
    case Types.BIGINT:
        return "BIGINT";
    case Types.FLOAT:
        return "FLOAT";
    case Types.REAL:
        return "REAL";
    case Types.DOUBLE:
        return "DOUBLE";
    case Types.NUMERIC:
        return "NUMERIC";
    case Types.DECIMAL:
        return "DECIMAL";
    case Types.CHAR:
        return "CHAR";
    case Types.VARCHAR:
        return "VARCHAR";
    case Types.LONGVARCHAR:
        return "LONGVARCHAR";
    case Types.DATE:
        return "DATE";
    case Types.TIME:
        return "TIME";
    case Types.TIMESTAMP:
        return "TIMESTAMP";
    case Types.BINARY:
        return "BINARY";
    case Types.VARBINARY:
        return "VARBINARY";
    case Types.LONGVARBINARY:
        return "LONGVARBINARY";
    case Types.NULL:
        return "NULL";
    case Types.OTHER:
        return "OTHER";
    case Types.JAVA_OBJECT:
        return "JAVA_OBJECT";
    case Types.DISTINCT:
        return "DISTINCT";
    case Types.STRUCT:
        return "STRUCT";
    case Types.ARRAY:
        return "ARRAY";
    case Types.BLOB:
        return "BLOB";
    case Types.CLOB:
        return "CLOB";
    case Types.REF:
        return "REF";
    case Types.DATALINK:
        return "DATALINK";
    case Types.BOOLEAN:
        return "BOOLEAN";
    case Types.ROWID:
        return "ROWID";
    case Types.NCHAR:
        return "NCHAR";
    case Types.NVARCHAR:
        return "NVARCHAR";
    case Types.LONGNVARCHAR:
        return "LONGNVARCHAR";
    case Types.NCLOB:
        return "NCLOB";
    case Types.SQLXML:
        return "SQLXML";
    }

    return "?";
}

Answer 4

You need the ResultSetMetaData object of your current ResultSet. You can get it with getMetaData(). Iterate over the columns and call foreach column the method

1. getColumnType(i)
2. getColumnClassName(i)
3. getColumnTypeName(i)

of your ResultSetMetaData.

i represents the column number (starting by 1).

Answer 5

Spring has a handy helper Enum called JdbcTypesEnum, but it is indeed quite strange, that this is not part of JDBC proper. However, instead of using

rs = connection.getMetaData().getColumns();
...
int dataType = rs.getInt("DATA_TYPE");

I would use

String typeName = rs.getString("TYPE_NAME");

when retrieving the column type. For example when inspecting a H2 database table with a special VARCHAR_IGNORECASE or UUID type:

                    dataType   vs. typeName
UUID:               -2=BINARY  vs. "UUID"
VARCHAR_IGNORECASE: 12=VARCHAR vs. "VARCHAR_IGNORECASE"

But note, that you cannot cover the type range of the string for all databases, in this case the int would be somewhat more handy (but it is after all not a closed Enum type).

Answer 6

The Apache DdlUtils Library has a convenience class for this: https://db.apache.org/ddlutils/api/org/apache/ddlutils/model/TypeMap.html

String typeName = TypeMap.getJdbcTypeName(typeCode)

The library helps you also with other DDL needs, but doesn't seem to get much attention lately.

Answer 7

I was also searching for SqlType and i found it in this sourcecode
http://www.docjar.com/html/api/java/sql/Types.java.html

Answer 8

You seem to be using some JDBC metadata methods you have not posted. I believe what you are seeing is the name of the column along with the JDBC type constant from which you can derive the column type. Have a look at the java.sql API to read more on how to get more meta data.