I use ResultSetMetadata to dynamically build update and insert queries.
At the moment I use the following class: ==> /* * Created on Dec 3, 2003 * */ package db;
import java.sql.*; import java.util.ArrayList; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.logging.Logger;
import util.Constants;
/**
*
* @author johnz
*
*/
public final class TableMetaData { // Static variables
// private static String catalog = "";
// private static String schemaPattern = "APP";
/**
* Map with a list with meta data for each table
* Each entry in list is a Map with column properties
*/
private static Map tableMetaDataMap = new HashMap();private static final String[] columnPropertyNames = {
"TABLE_CAT", // String => table catalog (may be null)
"TABLE_SCHEM", // String => table schema (may be null)
"TABLE_NAME", // String => table name
"COLUMN_NAME", // String => column name
"DATA_TYPE", // int => SQL type from java.sql.Types
"TYPE_NAME", // String => Data source dependent type name, for a UDT the type name is fully qualified
"COLUMN_SIZE", // int => column size. For char or date types this is the maximum number of characters, for numeric or decimal types this is precision.
"BUFFER_LENGTH", // is not used.
"DECIMAL_DIGITS", // int => the number of fractional digits
"NUM_PREC_RADIX", // int => Radix (typically either 10 or 2)
"NULLABLE", // int => is NULL allowed.
// "columnNoNulls", - might not allow NULL values
// "columnNullable", - definitely allows NULL values
// "columnNullableUnknown", - nullability unknown
"REMARKS", // String => comment describing column (may be null)
"COLUMN_DEF", // String => default value (may be null)
"SQL_DATA_TYPE", // int => unused
"SQL_DATETIME_SUB", // int => unused
"CHAR_OCTET_LENGTH", // int => for char types the maximum number of bytes in the column
"ORDINAL_POSITION", // int => index of column in table (starting at 1)
"IS_NULLABLE", // String => "NO" means column definitely does not allow NULL values; "YES" means the column might allow NULL values. An empty string means nobody knows.
// "SCOPE_CATLOG", // String => catalog of table that is the scope of a reference attribute (null if DATA_TYPE isn't REF)
// "SCOPE_SCHEMA", // String => schema of table that is the scope of a reference attribute (null if the DATA_TYPE isn't REF)
// "SCOPE_TABLE", // String => table name that this the scope of a reference attribure (null if the DATA_TYPE isn't REF)
// "SOURCE_DATA_TYPE" // short => source type of a distinct type or user-generated Ref type, SQL type from java.sql.Types (null if DATA_TYPE isn't DISTINCT or user-generated REF)
};
/**
*
*/
public TableMetaData() {
super();
}
/**
* Return list with meta data for selected table
* Each entry in list is a map with column properties
*
* @see JavaDoc 1.4 java.sql.DatabaseMetaData.getColumns for name of the column properties
* @param tableName
* @return List of <code>Map</code> objects. Each map has entries with {ColumnProperty, PropertyValue}
* @throws SQLException
*/
public static List getTableMetaData(String tableName)
throws SQLException {
if (tableMetaDataMap.get(tableName) != null) {
// MetaData have been chached
return (List) tableMetaDataMap.get(tableName);
}
Logger log = Logger.getLogger("db");
Connection conn = null;
ResultSet rset = null;
Statement stmt = null;
DatabaseMetaData metaData = null;
String msg = null;
try {
conn = DriverManager.getConnection(Constants.JDBC_URL);
metaData = conn.getMetaData();
rset = metaData.getColumns("", "APP", tableName, "%");
List tableList = new ArrayList();
Map columnPropertyMap;
int i = 0;
String columnPropterty = null;
while (rset.next()) {
// Create new map
columnPropertyMap = new HashMap(columnPropertyNames.length);
for (i = 0; i < columnPropertyNames.length; i++) {
columnPropterty = columnPropertyNames[i];
columnPropertyMap.put(columnPropterty, rset.getObject(columnPropterty));
}
tableList.add(columnPropertyMap);
}
tableMetaDataMap.put(tableName, tableList);
return tableList;
} catch (SQLException se) {
msg = "SQL error: " + se.getMessage();
log.warning(msg);
throw new SQLException(msg);
} finally {
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
}
/**
* Convenience method to get array of column properties for selected table
* For name of column properties:
* @see JavaDoc 1.4 java.sql.DatabaseMetaData.getColumns for name of the column properties
* @param tableName
* @param propertyName Name of property. For example to get all column names pass COLUMN_NAME
* For list of all column properties see method getColumnProperties
* @return List which enumerates the selected column property over all columns
* @throws SQLException
*/
public static List getTableColumnProperties(String tableName, String columnPropertyName)
throws SQLException {
List columnPropertyList = new ArrayList();
List list = getTableMetaData(tableName);
Map columnMap = null;
Iterator iterator = list.iterator();
int i = 0;
Object obj = null;
while (iterator.hasNext()) {
columnMap = (Map) iterator.next();
columnPropertyList.add(columnMap.get(columnPropertyName));
}
return columnPropertyList;
}
/**
* Get array of all valid column properties
*
* @see JavaDoc 1.4 java.sql.DatabaseMetaData.getColumns for name of the column properties
* Some properties not valid before SDK 1.4 are skipped
* @return Array of column properties
*/
public static String[] getColumnPropertyNames() {
return columnPropertyNames;
}
}
==>
This class works fine, but means additional IO which can be avoided if the meta data are returned
by QueryRunner in a metod similar to above.
Is this something that fits into DbUtils ?
-- John Zoetebier Web site: http://www.transparent.co.nz
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
