Java tutorial
/** * Copyright (c) 2004-2011 Wang Jinbao(Julian Wong), http://www.ralasafe.com * Licensed under the MIT license: http://www.opensource.org/licenses/mit-license.php */ package org.ralasafe.db; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Collection; import java.util.HashMap; import java.util.Iterator; import java.util.LinkedList; import java.util.List; import java.util.Map; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.ralasafe.util.DBUtil; import org.ralasafe.util.StringUtil; /** * Reflect schema's table and view infos. * The table or view is omited which name starts with '$'. * We think it's a system table. * * @author back * */ public class DBView { private static final String[] tableType = new String[] { "TABLE" }; private static final String[] viewType = new String[] { "VIEW" }; private static final Log log = LogFactory.getLog(DBView.class); public static String getDefaultSchema(String dsName) { Connection conn = DBPower.getConnection(dsName); try { return DBUtil.getDefaultSchema(conn); } catch (SQLException e) { log.error("", e); return ""; } finally { DBUtil.close(conn); } } public static TableView getTable(String dsName, String tableName) { Connection conn = null; try { conn = DBPower.getConnection(dsName); return getTable(conn, dsName, null, tableName); } catch (SQLException e) { log.error("Failed to get definition of " + "table/view '" + tableName, e); throw new DBLevelException(e); } finally { DBUtil.close(conn); } } public static TableView getTable(String dsName, String schema, String tableName) { Connection conn = null; try { conn = DBPower.getConnection(dsName); return getTable(conn, dsName, schema, tableName); } catch (SQLException e) { log.error("Failed to get definition of " + "table/view '" + schema + "." + tableName, e); throw new DBLevelException(e); } finally { DBUtil.close(conn); } } private static TableView getTable(Connection conn, String dsName, String schema, String tableName) throws SQLException { String mySchema = ""; if (!StringUtil.isEmpty(schema)) { mySchema = schema + "."; } if (log.isDebugEnabled()) { log.debug("Get table/view definition: dsName=" + dsName + ", table/view Name=" + mySchema + tableName); } Statement stmt = null; ResultSet rs = null; ResultSet primaryKeys = null; try { stmt = conn.createStatement(); rs = stmt.executeQuery("select * from " + mySchema + tableName + " where 1=2"); ResultSetMetaData metaData = rs.getMetaData(); TableView table = new TableView(); table.setSchema(schema); table.setName(tableName); DatabaseMetaData metaData2 = conn.getMetaData(); String databaseProductName = DBUtil.getDatabaseProductName(conn); if (databaseProductName == DBUtil.MYSQL) { primaryKeys = metaData2.getPrimaryKeys(schema, null, tableName); } else { primaryKeys = metaData2.getPrimaryKeys(null, null, tableName); } Map pkColumnViewMap = new HashMap(); while (primaryKeys.next()) { pkColumnViewMap.put(primaryKeys.getString("COLUMN_NAME"), null); } List columnList = new ArrayList(metaData.getColumnCount()); for (int i = 1, columnCount = metaData.getColumnCount(); i <= columnCount; i++) { ColumnView column = new ColumnView(); String columnName = metaData.getColumnName(i); column.setName(columnName); String sqlType = metaData.getColumnTypeName(i); if (sqlType.equalsIgnoreCase("blob") || sqlType.equalsIgnoreCase("clob") || sqlType.equalsIgnoreCase("text")) { // DO NOTHING } else { int precision = metaData.getPrecision(i); int scale = metaData.getScale(i); if (precision != 0) { if (scale == 0) { sqlType = sqlType + "(" + precision + ")"; } else { sqlType = sqlType + "(" + precision + "," + scale + ")"; } } } column.setSqlType(sqlType); columnList.add(column); // it's a primary key? if (pkColumnViewMap.containsKey(columnName)) { pkColumnViewMap.put(columnName, column); } } table.setColumnViews(columnList); // sometimes, oracle jdbc driver returns pk info is redundance, // actually the column does exist at all. Clear them. clearInvalidPK(pkColumnViewMap); if (pkColumnViewMap.size() > 0) { table.setPkColumnViews(pkColumnViewMap.values()); } return table; } finally { DBUtil.close(primaryKeys); DBUtil.close(rs); DBUtil.close(stmt); } } private static void clearInvalidPK(Map pkColumnViewMap) { Iterator itr = pkColumnViewMap.entrySet().iterator(); while (itr.hasNext()) { Map.Entry entry = (Map.Entry) itr.next(); if (entry.getValue() == null) { itr.remove(); } } } public static String[] getSchemas(String dsName) { DataSource ds = DBPower.getDataSource(dsName); if (ds.isShowAllSchemas()) { return getAllSchemasFromDB(dsName); } else { return ds.getSchemas(); } } public static String[] getAllSchemasFromDB(String dsName) { Connection conn = null; ResultSet rs = null; try { conn = DBPower.getConnection(dsName); DatabaseMetaData metaData = conn.getMetaData(); String databaseProductName = DBUtil.getDatabaseProductName(conn); if (databaseProductName == DBUtil.MYSQL || databaseProductName == DBUtil.SQLSERVER) { rs = metaData.getCatalogs(); } else { rs = metaData.getSchemas(); } List result = new LinkedList(); while (rs.next()) { String name = rs.getString(1); result.add(name); } String[] names = new String[result.size()]; Iterator itr = result.iterator(); for (int i = 0; i < names.length; i++) { names[i] = (String) itr.next(); } return names; } catch (SQLException e) { log.error("", e); throw new DBLevelException(e); } finally { DBUtil.close(rs); DBUtil.close(conn); } } public static String[] getTableNames(String dsName, String schema) { return getObjectNames(dsName, schema, tableType); } private static String[] getObjectNames(String dsName, String schema, String[] objectTypes) { Connection conn = null; ResultSet tables = null; try { conn = DBPower.getConnection(dsName); DatabaseMetaData metaData = conn.getMetaData(); String databaseProductName = DBUtil.getDatabaseProductName(conn); if (databaseProductName == DBUtil.MYSQL || databaseProductName == DBUtil.SQLSERVER) { tables = metaData.getTables(schema, null, null, objectTypes); } else if (databaseProductName == DBUtil.ORACLE) { tables = metaData.getTables(null, null, null, objectTypes); } else { tables = metaData.getTables(null, schema, null, objectTypes); } List result = new LinkedList(); while (tables.next()) { String tableName = tables.getString("TABLE_NAME"); if (databaseProductName == DBUtil.ORACLE) { String s = tables.getString("TABLE_SCHEM"); if (s.equalsIgnoreCase(schema)) { result.add(tableName); } } else if (databaseProductName == DBUtil.SQLSERVER) { String s = tables.getString("TABLE_SCHEM"); tableName = s + "." + tableName; result.add(tableName); } else { result.add(tableName); } } String[] names = new String[result.size()]; Iterator itr = result.iterator(); for (int i = 0; i < names.length; i++) { names[i] = (String) itr.next(); } return names; } catch (SQLException e) { log.error("", e); throw new DBLevelException(e); } finally { DBUtil.close(tables); DBUtil.close(conn); } } public static Collection getTables(String dsName, String schema) { String[] tableNames = getTableNames(dsName, schema); return getTables(dsName, schema, tableNames); } public static Collection getTables(String dsName, String schema, String[] tableNames) { Connection conn = DBPower.getConnection(dsName); try { List tables = new ArrayList(); for (int i = 0; i < tableNames.length; i++) { // omit system tables like $sys_ if (tableNames[i].indexOf("$") >= 0) { continue; } try { TableView table = getTable(conn, dsName, schema, tableNames[i]); tables.add(table); } catch (SQLException e) { log.error( "Failed to get definition in schema '" + schema + "'." + "table/view:" + tableNames[i], e); } } return tables; } finally { DBUtil.close(conn); } } public static String[] getViewNames(String dsName, String schema) { return getObjectNames(dsName, schema, viewType); } public static Collection getViewTables(String dsName, String schema, String[] viewNames) { return getTables(dsName, schema, viewNames); } public static Collection getViewTables(String dsName, String schema) { String[] viewNames = getViewNames(dsName, schema); return getTables(dsName, schema, viewNames); } }