Thursday, December 22, 2011

Getting db column size using JDBC

Looking at ResultSetMetaData api below was my first approach to the problem.

    private static String getColDbType(Connection conn, String table, String col) throws Exception {
String query = " select * from " + table + " where 2='9999'";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
ResultSetMetaData rsmd = rs.getMetaData();
try {
for (int i = 1; i < rsmd.getColumnCount() + 1; i++) {
String columnName = rsmd.getColumnName(i);
if (columnName.equals(col)) {
String s = rsmd.getColumnTypeName(i);
if (s.equalsIgnoreCase("varchar")) {
s += "(" + rsmd.getPrecision(i) + ")";
}
return s;
}
}
return null;
} finally {
rs.close();
stmt.close();
}
}



However the column sizes were not accurate; googling helped me understand that ResultSetMetaData is in the context of the result set returned by the query and it need not reflect the actual table details. So below is the 2nd approach that finally worked. Tried this with MySQL 5.x db instance

    private static String getColDbType(Connection connection, String table, String col) throws Exception {
DatabaseMetaData metadata = connection.getMetaData();
int i = url.lastIndexOf("/") + 1;
String schema = url.substring(i);
ResultSet resultSet = metadata.getColumns(connection.getCatalog(), schema.trim(), table, col);
while (resultSet.next()) {
String name = resultSet.getString("COLUMN_NAME");
String type = resultSet.getString("TYPE_NAME");
int size = resultSet.getInt("COLUMN_SIZE");
if (type.equalsIgnoreCase("varchar")) {
return type + "(" + size + ")";
}
return type;
}
return null;
}

No comments:

Post a Comment