Re: [DbUtils] Enhancement request for ResultSetMetadata

[John Zoetebier]

On Sat, 13 Dec 2003 08:12:49 -0800 (PST), David Graham 
<[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]