commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Graham <grahamdavid1...@yahoo.com>
Subject Re: [DbUtils] Enhancement request for ResultSetMetadata
Date Sat, 13 Dec 2003 16:12:49 GMT
QueryRunner can't return the meta data because it closes the ResultSet and
PreparedStatement objects after running the query.  You can get access to
the meta data by implementing a ResultSetHandler and calling
rs.getMetaData() in the handle() method.

David

--- John Zoetebier <john.zoetebier@transparent.co.nz> wrote:
> It would be helpful if QueryRunner would return ResultSetMetadata after 
> running a query.
> 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;
> 	}
> 
=== message truncated ===


__________________________________
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org


Mime
View raw message