You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by John Zoetebier <jo...@transparent.co.nz> on 2003/12/13 11:09:52 UTC
[DbUtils] Enhancement request for ResultSetMetadata
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;
}
}
==>
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: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: [DbUtils] Enhancement request for ResultSetMetadata
Posted by John Zoetebier <jo...@transparent.co.nz>.
On Sat, 13 Dec 2003 08:12:49 -0800 (PST), David Graham
<gr...@yahoo.com> wrote:
> 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.
I have made ResultSetMetaDataHandler which implements ResultSetHandler.
Code and test class shown below.
It works fine.
However class TableMetaData from previous email does something different.
It derives the meta data of a table from the Connection.
Next the metadata are mapped to a list which consist of map entries with a
{ColumnProperty, PropertyValue}
This means you do not have to keep the connection open.
However class ResultSetMetaDataHandler propbably fits better in DbUtils.
Code of ResultSetMetaData:
==>
/*
* Created on Dec 14, 2003
*
*/
package db;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.ResultSetHandler;
/**
* @author John Zoetebier
*
*/
public class ResultSetMetaDataHandler implements ResultSetHandler {
/**
*
*/
public ResultSetMetaDataHandler() {
super();
}
/* (non-Javadoc)
* @see
org.apache.commons.dbutils.ResultSetHandler#handle(java.sql.ResultSet)
*/
public Object handle(ResultSet rs) throws SQLException {
try {
List columnList = new ArrayList();
Map columnPropertyMap = null;
String columnPropterty = null;
ResultSetMetaData metaData = rs.getMetaData();
for (int i = 1; i <= metaData.getColumnCount(); i++) {
columnPropertyMap = new HashMap(2);
columnPropertyMap.put("catalog_name", metaData.getCatalogName(i));
columnPropertyMap.put("column_class_name",
metaData.getColumnClassName(i));
columnPropertyMap.put("column_label", metaData.getColumnLabel(i));
columnPropertyMap.put("column_name", metaData.getColumnName(i));
columnPropertyMap.put("column_type_name",
metaData.getColumnTypeName(i));
columnPropertyMap.put("schema_name", metaData.getSchemaName(i));
columnPropertyMap.put("table_name", metaData.getTableName(i));
columnPropertyMap.put("column_display_size", new
Integer(metaData.getColumnDisplaySize(i)));
columnPropertyMap.put("column_type", new
Integer(metaData.getColumnType(i)));
columnPropertyMap.put("precision", new
Integer(metaData.getPrecision(i)));
columnPropertyMap.put("scale", new Integer(metaData.getScale(i)));
columnList.add(columnPropertyMap);
}
return columnList;
} catch (SQLException se) {
String msg = "SQL error: " + se.getMessage();
throw new SQLException(msg);
}
}
}
==>
Code of test class:
==>
/*
* Created on Dec 14, 2003
*
*/
package test;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import db.DataSourceHandler;
import db.ResultSetMetaDataHandler;
/**
* Test ResultSetMetaDataHandler
*
* @author John Zoetebier
*
*/
public class TestDbUtils4 {
private String[] columnProperties = {
"catalog_name",
"column_class_name",
"column_label",
"column_name",
"column_type_name",
"schema_name",
"table_name",
"column_display_size",
"column_type",
"precision",
"scale"
};
/**
*
*/
public TestDbUtils4() {
super();
}
public void go() {
DataSource dataSource= DataSourceHandler.getDataSource();
QueryRunner runner = new QueryRunner(dataSource);
ResultSetHandler rsh = new ResultSetMetaDataHandler();
String sql = "select * from Client";
List columnList = null;
try {
columnList = (List) runner.query(sql, rsh);
} catch (SQLException se) {
System.out.println(se.getMessage());
return;
}
Iterator iterator = columnList.iterator();
String fieldName = null;
Map columnMap = null;
while (iterator.hasNext()) {
columnMap = (Map) iterator.next();
System.out.println("column_name = " + columnMap.get("column_name"));
for (int i=0; i<columnProperties.length; i++) {
System.out.println(" " + columnProperties[i] + " = "
+ columnMap.get(columnProperties[i]));
}
}
System.out.println("==> Ready");
}
public static void main(String[] args) {
new TestDbUtils4().go();
}
}
==>
--
John Zoetebier
Web site: http://www.transparent.co.nz
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: [DbUtils] Enhancement request for ResultSetMetadata
Posted by David Graham <gr...@yahoo.com>.
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 <jo...@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