There are a number of cases where it is useful to be able to obtain a description of a relational database table. The table description consists of a set of column names and types. For example, a table description for a bank balance might be:
ACCNUM NUMBER(20),
NAME VARCHAR(128),
BALANCE NUMBER(10)
In this example each line lists the column name, followed by the database type assigned for the column. This description includes comma separators, which allows the description to be included in an SQL command. For example, the creation of a temporary table or a new table, which additional fields.
The Java method below is a simply JDBC hack to return the table description. The function throws some imaginary exception (e.g., MyCustomException).
/**
Return a description of the table's column names and types, in the format for creating or deleting a table. For example
ID NUMBER(20),
NAME VARCHAR2(256),
SOMEVAL VARCHAR2(256)
The only way I've been able to figure out how to get the table description is from the ResultSet meta data. In this case I don't actually want the result set. So to avoid network traffic a "where" clause, which will always be false is used. As a result there will be no data in the result set, but at least under Oracle the meta data is available.
@param tableName The name of the table
@param conn A JDBC database connection
@return A string describing the table. The string will include embedded '\n'
characters (or character sequences) separating each type.
*/
public String getTableDesc( String tableName, Connection conn)
throws MyCustomException
{
String tableDesc = null;
final String sqlBase = "select * from ";
String sql = sqlBase + tableName + " where 1 = 2";
Statement stmt = null;
ResultSet rslt = null;
try {
stmt = conn.createStatement();
rslt = stmt.executeQuery( sql );
ResultSetMetaData meta = rslt.getMetaData();
int numCols = meta.getColumnCount();
StringBuffer strBuf = new StringBuffer();
for (int i = 1; i <= numCols; i++) {
String colName = meta.getColumnName( i );
strBuf.append( colName );
strBuf.append(" ");
String typeName = meta.getColumnTypeName( i );
strBuf.append( typeName );
int precision = meta.getPrecision( i );
if (precision > 0) {
strBuf.append('(');
strBuf.append( precision );
strBuf.append(')');
}
if (i < numCols) {
strBuf.append(',');
strBuf.append('\n');
}
} // for
tableDesc = strBuf.toString();
}
catch (SQLException e) {
throw new MyCustomException("getTableDesc: SQLException = " + e.getMessage() );
}
finally {
if (stmt != null) {
try { stmt.close(); } catch (SQLException ignore) {}
}
if (rslt != null) {
try { rslt.close(); } catch (SQLException ignore) {}
}
}
return tableDesc;
} // getTableDesc
Ian Kaplan, December, 2003
Updated:
back to Miscellaneous Java Related Topics