Getting the JDBC column types

Question

I need to ask about my column jdbc type in a table, today I am looping throw my columns and then asking about its type, see my code below :

public int getColumnDataTypeFromDB(String columnName) {
    int datatype = 0;

    ResultSet columns = null;
    try {
        String schema =
            getMetaDataCatalogName() != null
                ? getMetaDataCatalogName()
                : getMetaDataSchemaName();

        if (TableManagerFactory.isCatalogBasedDatabase()) {
            columns =
                getMetaData().getColumns(
                    schema,
                    null,
                    tableName,
                    columnName);
        } else {
            columns =
                getMetaData().getColumns(
                    null,
                    schema,
                    tableName,
                    columnName);
        }
        //          columns =
        //              getMetaData().getColumns(getMetaDataCatalogName(), getMetaDataSchemaName(), tableName, columnName);
        if (columns.next()) {
            datatype = columns.getInt("DATA_TYPE");
        }
    } catch (SQLException ex) {
        Log.error(
            this,
            "Error while getting columns information: " + ex.getMessage(),
            ex);
        //return false;
    } catch (DDLCreationException ex) {
        Log.error(this, ex.getMessage());
    } finally {
        try {
            if (columns != null) {
                columns.close();
            }
        } catch (SQLException ex) {
        Log.error(this, ex.getLocalizedMessage());
        }
    }

    return datatype;
}

Can I get all the columns metadata in such a table at the same time ? if yes, how can I do it ??

Answer 1

Use jdbc ResultSetMetaData class to get the detail information of the table columns.

 ResultSet res=stmt.executeQuery("select * from tableName where 1<0");
 ResultSetMetaData rsmd=res.getMetaData();
 rsmd.getColumnType(1);
 rsmd.getColumnLabel(1);
 rsmd.getColumnDisplaySize(1);

Answer 2

The parameters to DatabaseMetaData.getColumns(..) are supposed to be a LIKE-pattern. So if you want to get all columns from a table, you simply need to pass in "%" to the last parameter, columnNamePattern:

getMetaData().getColumns(null, schema, tableName, "%");

Some drivers (also) allow null here, but not all drivers do that (the JDBC specification and API documentation is not entirely clear whether that is allowed or not for this parameter)