You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@commons.apache.org by st...@apache.org on 2002/10/21 13:41:55 UTC
cvs commit: jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/io MetadataReader.java
stevencaswell 2002/10/21 04:41:55
Added: sql/src/java/org/apache/commons/sql/io MetadataReader.java
Log:
added; provides functionality to create the Database bean model from a JDBC DatabaseMetadata
first cut only deals with tables and columns and needs some cleanup
Revision Changes Path
1.1 jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/io/MetadataReader.java
Index: MetadataReader.java
===================================================================
/*
* MetadataReader.java
*
* Created on October 15, 2002, 8:51 AM
*/
package org.apache.commons.sql.io;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Hashtable;
import java.util.List;
import java.util.Vector;
import org.apache.commons.sql.model.Column;
import org.apache.commons.sql.model.Database;
import org.apache.commons.sql.model.Table;
import org.apache.commons.sql.model.TypeMap;
import java.util.Collection;
/**
* Reads a database's metadata and creates a fully populated Database bean.
*
* @author Steven Caswell
* @version $Id: MetadataReader.java,v 1.1 2002/10/21 11:41:55 stevencaswell Exp $
*/
public class MetadataReader
{
// --- Protected member variables ---
/** Hashtable of columns that have primary keys. */
protected Hashtable primaryKeys;
/** Hashtable to track what table a column belongs to. */
protected Hashtable columnTableMap;
/** DB schema to use. */
protected String dbSchema;
// --- Constructors ---
/**
* Constructs a new instance of
* <code>MetadataReader</code>.
*/
public MetadataReader()
{
}
// --- Public member operations
public Database parse(String driver, String url, String user, String password) throws Exception
{
// Load the driver
Class.forName(driver);
// Connect to the data source
Connection connection = DriverManager.getConnection(url, user, password);
// Get the metadata
DatabaseMetaData dbMetaData = connection.getMetaData();
Database database = new Database();
// Get database tables
List tables = getTables(dbMetaData);
System.out.println("table list size: " + tables.size());
// Build a database-wide column -> table map.
columnTableMap = new Hashtable();
int tablesSize = tables.size();
/*
System.out.println("Building column/table map...");
for (int i = 0; i < tablesSize; i++)
{
// List table = (List) tableList.get(i);
Table table = (Table) tables.get(i);
// String curTable = (String) table.get(0);
String curTableName = table.getName();
List columns = getColumns(dbMetaData, curTableName);
for (int j = 0; j < columns.size(); j++)
{
// List col = (List) columns.get(j);
// String name = (String) col.get(0);
Column column = (Column) columns.get(j);
String name = column.getName();
columnTableMap.put(name, curTableName);
}
}
*/
for (int i = 0; i < tablesSize; i++)
{
// Add Table.
// List tbl = (List) tableList.get(i);
// String curTable = (String) tbl.get(0);
// String tableType = (String) tbl.get(1);
// String curTable = (String) tableList.get(i);
// dbMap.addTable(curTable);
Table table = (Table) tables.get(i);
// Add Columns.
// TableMap tblMap = dbMap.getTable(curTable);
List columns = getColumns(dbMetaData, table.getName());
table.addColumns(columns);
// List primKeys = getPrimaryKeys(dbMetaData, curTable);
// Collection forgnKeys = getForeignKeys(dbMetaData, curTable);
database.addTable(table);
}
return database;
}
/**
* Get all the table names in the current database that are not
* system tables.
*
* @param dbMeta JDBC database metadata.
* @return The list of all the tables in a database.
* @exception SQLException
*/
public List getTables(DatabaseMetaData dbMeta)
throws SQLException
{
String tablePattern = null; // temporary
System.out.println("Getting table list...");
List tables = new Vector();
ResultSet tableSet = null;
// these are the entity types we want from the database
// String[] types = {"TABLE", "VIEW"};
String[] types = {"TABLE", "VIEW"};
try
{
tableSet = dbMeta.getTables(null, "SGL_EXIST_XCM", tablePattern, types);
while (tableSet.next())
{
String catalog = tableSet.getString(1);
String schema = tableSet.getString(2);
String name = tableSet.getString(3);
String type = tableSet.getString(4);
String remarks = tableSet.getString(5);
Table table = new Table();
table.setCatalog(catalog);
table.setSchema(schema);
table.setName(name);
table.setType(type);
table.setRemarks(remarks);
/*
List table = new Vector(5);
table.add(catalog);
table.add(schema);
table.add(name);
table.add(type);
table.add(remarks);
*/
tables.add(table);
}
}
finally
{
if (tableSet != null)
{
tableSet.close();
}
}
return tables;
}
/**
* Retrieves all the column names and types for a given table from
* JDBC metadata. It returns a vector of vectors. Each element
* of the returned vector is a vector with:
*
* element 0 => a String object for the column name.
* element 1 => an Integer object for the column type.
* element 2 => size of the column.
* element 3 => null type.
*
* @param dbMeta JDBC metadata.
* @param tableName Table from which to retrieve column
* information.
* @return The list of columns in <code>tableName</code>.
*/
public List getColumns(DatabaseMetaData dbMeta, String tableName)
throws SQLException
{
List columns = new Vector();
ResultSet columnSet = null;
try
{
columnSet = dbMeta.getColumns(null, dbSchema, tableName, null);
while (columnSet.next())
{
String name = columnSet.getString(4);
Integer sqlType = new Integer(columnSet.getInt(5));
String typeName = columnSet.getString(6);
int size = columnSet.getInt(7);
String scale = columnSet.getString(9);
String precisionRadix = columnSet.getString(10);
int nullable = columnSet.getInt(11);
String remarks = columnSet.getString(12);
String defValue = columnSet.getString(13);
String charOctetLength = columnSet.getString(16);
String ordinalPosition = columnSet.getString(17);
String isNullable = columnSet.getString(18);
Column column = new Column();
column.setName(name);
column.setType(TypeMap.getSQLTypeString(sqlType));
if(TypeMap.isTextType(TypeMap.getSQLTypeString(sqlType)))
{
column.setIsTextType(new Boolean(true));
}
column.setSize(size);
if(scale != null)
{
column.setScale(new Integer(scale));
}
if(precisionRadix != null)
{
column.setPrecisionRadix(Integer.getInteger(precisionRadix));
}
column.setNullable((nullable == 1) ? true : false);
column.setDefaultValue(defValue);
if(charOctetLength != null)
{
column.setCharOctetLength(Integer.getInteger(charOctetLength));
}
if(ordinalPosition != null)
{
column.setOrdinalPosition(Integer.getInteger(ordinalPosition));
}
column.setIsNullable(isNullable);
/*
List col = new Vector(5);
col.add(name);
col.add(sqlType);
col.add(size);
col.add(nullType);
col.add(defValue);
col.add(scale);
col.add(isNullable);
columns.add(col);
*/
columns.add(column);
}
}
finally
{
if (columnSet != null)
{
columnSet.close();
}
}
return columns;
}
/**
* Retrieves a list of the columns composing the primary key for a given
* table.
*
* @param dbMeta JDBC metadata.
* @param tableName Table from which to retrieve PK information.
* @return A list of the primary key parts for <code>tableName</code>.
*/
public List getPrimaryKeys(DatabaseMetaData dbMeta, String tableName)
throws SQLException
{
List pk = new Vector();
ResultSet parts = null;
try
{
parts = dbMeta.getPrimaryKeys(null, dbSchema, tableName);
while (parts.next())
{
pk.add(parts.getString(4));
}
}
finally
{
if (parts != null)
{
parts.close();
}
}
return pk;
}
/**
* Retrieves a list of foreign key columns for a given table.
*
* @param dbMeta JDBC metadata.
* @param tableName Table from which to retrieve FK information.
* @return A list of foreign keys in <code>tableName</code>.
*/
public Collection getForeignKeys(DatabaseMetaData dbMeta, String tableName)
throws SQLException
{
Hashtable fks = new Hashtable();
ResultSet foreignKeys = null;
try
{
foreignKeys = dbMeta.getImportedKeys(null, dbSchema, tableName);
while (foreignKeys.next())
{
String fkName = foreignKeys.getString(12);
// if FK has no name - make it up (use tablename instead)
if (fkName == null)
{
fkName = foreignKeys.getString(3);
}
Object[] fk = (Object[])fks.get(fkName);
List refs;
if (fk == null)
{
fk = new Object[2];
fk[0] = foreignKeys.getString(3); //referenced table name
refs = new Vector();
fk[1] = refs;
fks.put(fkName, fk);
}
else
{
refs = (Vector)fk[1];
}
String[] ref = new String[2];
ref[0] = foreignKeys.getString(8); //local column
ref[1] = foreignKeys.getString(4); //foreign column
refs.add(ref);
}
}
finally
{
if (foreignKeys != null)
{
foreignKeys.close();
}
}
return fks.values();
}
}
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>