metadatavalues.java

eq跨平台查询工具源码 eq跨平台查询工具源码

     *
     * @param the table's name
     * @return a <code>Vector</code> of <code>ColumnIndexData</code> objects
     */
    public Vector<ColumnIndex> getTableIndexes(String catalog, 
                                  String schema, String table) throws DataSourceException {

        ResultSet rs = null;
        
        try {
            
            ensureConnection();
            DatabaseMetaData dmd = connection.getMetaData();
            
            rs = dmd.getIndexInfo(catalog, schema, table, false, true);

            Vector v = new Vector();
            while (rs.next()) {
                String name = rs.getString(6);

                if (MiscUtils.isNull(name)) {
                    continue;
                }

                ColumnIndex cid = new ColumnIndex();
                cid.setNonUnique(rs.getBoolean(4));
                cid.setIndexName(name);
                cid.setIndexedColumn(rs.getString(9));
                v.add(cid);                
            } 
            
            return v;            
        } 
        catch (SQLException e) {
            throw new DataSourceException(e);
        }
        finally {
            releaseResources(rs);
        } 
        
    }

    /**
     * Returns the table column meta data as a result set.
     *
     * @param the table name
     * @param the schema name
     * @param the table name
     */
    public ResultSet getTableMetaData(String catalog, 
                                      String schema, 
                                      String name) throws DataSourceException {
        try {
            ensureConnection();            
            DatabaseMetaData dmd = connection.getMetaData();
            return dmd.getColumns(catalog, schema, name, null);
        }
        catch (SQLException e) {
            throw new DataSourceException(e);
        }
        finally {
            // TODO: release ????
        }
    }
    
    /** <p>Retrieves complete and detailed meta data for all columns
     *  within the specified table and schema.
     *  <p>The meta data will include data type, size and all
     *  primary and foreign keys for the specified table. The results
     *  of this method are specifically displayed within the Database
     *  Browser feature for each selected table from the browser's
     *  tree structure.
     *
     *  @param the table name
     *  @param the schema name
     *  @return the column meta data as a <code>ColumnData</code> array
     */
    public ColumnData[] getColumnMetaData(String tableName, 
                                          String schemaName) throws DataSourceException {
        return getColumnMetaData(null, schemaName, tableName);
    }
    
    public ColumnData[] getColumnMetaData(String catalog, 
                                          String schema, 
                                          String name) throws DataSourceException {

        ResultSet rs = null;
        try {
            ensureConnection();            
            DatabaseMetaData dmd = connection.getMetaData();
            
            // -----------------------------------------
            // retrieve the primary keys for this table
            // -----------------------------------------
            
            ResultSet keys = dmd.getPrimaryKeys(catalog, schema, name);
            ArrayList _primaryKeys = new ArrayList();
            String pKey = null;
            String pKeyName = null;
            
            while (keys.next()) {
                ColumnConstraint cc = new ColumnConstraint();
                cc.setRefSchema(keys.getString(2));
                cc.setTable(keys.getString(3));
                cc.setColumn(keys.getString(4));
                cc.setName(keys.getString(6));
                cc.setType(ColumnConstraint.PRIMARY_KEY);
                _primaryKeys.add(cc);
                
                pKey = keys.getString(4);
                pKeyName = keys.getString(6);
            } 
            
            keys.close();
            
            int v_size = _primaryKeys.size();
            ColumnConstraint[] primaryKeys = new ColumnConstraint[v_size];
            
            for (int i = 0; i < v_size; i++) {
                primaryKeys[i] = (ColumnConstraint)_primaryKeys.get(i);
            } 
            
            // -----------------------------------------
            // retrieve the foreign keys of this table
            // -----------------------------------------
            
            keys = dmd.getImportedKeys(catalog, schema, name);
            
            // put the foreign key details in a temporary collection
            ArrayList _foreignKeys = new ArrayList();
            while (keys.next()) {
                ColumnConstraint cc = new ColumnConstraint();
                cc.setTable(name);
                cc.setRefSchema(keys.getString(2));
                cc.setRefTable(keys.getString(3));
                cc.setRefColumn(keys.getString(4));
                cc.setColumn(keys.getString(8));
                cc.setName(keys.getString(12));
                cc.setType(ColumnConstraint.FOREIGN_KEY);
                _foreignKeys.add(cc);
            } 
            
            v_size = _foreignKeys.size();
            ColumnConstraint[] foreignKeys = new ColumnConstraint[v_size];
            
            for (int i = 0; i < v_size; i++) {
                foreignKeys[i] = (ColumnConstraint)_foreignKeys.get(i);
            } 
            
            keys.close();
            
            // The primary key count
            int primaryKeyCount = 0;
            // The foreign key count
            int foreignKeyCount = 0;
            // The current column name
            String columnName = null;
            // The current key's column name
            String columnNameForKey = null;
            // to store the result set
            ArrayList _columns = new ArrayList();
            
            //Log.debug("catalog: " + catalog + " schema: " + schema);
            
            // retrieve the column data
            rs = dmd.getColumns(catalog, schema, name, null);
            
            while (rs.next()) {
                
                columnName = rs.getString(4);
                
                ColumnData cd = new ColumnData();
                cd.setCatalog(catalog);
                cd.setSchema(schema);
                cd.setColumnName(columnName);
                cd.setSQLType(rs.getShort(5));
                cd.setColumnType(rs.getString(6));
                cd.setColumnSize(rs.getInt(7));
                cd.setColumnScale(rs.getInt(9));
                cd.setColumnRequired(rs.getInt(11));
                cd.setDefaultValue(rs.getString(13));
                cd.setTableName(name);
                
                // check if all primary keys have been identified
                if (primaryKeyCount < primaryKeys.length) {
                    
                    // determine if the current column is a primary key
                    for (int j = 0; j < primaryKeys.length; j++) {
                        columnNameForKey = primaryKeys[j].getColumn();
                        
                        if (columnNameForKey.compareTo(columnName) == 0) {
                            cd.addConstraint(primaryKeys[j]);
                            cd.setPrimaryKey(true);
                            primaryKeyCount++;
                            break;
                        } 
                        
                    } 
                    
                } 
                
                // check if all foreign keys have been identified
                if (foreignKeyCount < foreignKeys.length) {
                    
                    // determine if the current column is a foreign key
                    for (int j = 0; j < foreignKeys.length; j++) {
                        columnNameForKey = foreignKeys[j].getColumn();
                        
                        if (columnNameForKey.compareTo(columnName) == 0) {
                            cd.addConstraint(foreignKeys[j]);
                            cd.setForeignKey(true);
                            foreignKeyCount++;
                            break;
                        } 
                        
                    } 
                    
                } 
                
                columnName = null;
                columnNameForKey = null;
                cd.setNamesToUpper();
                _columns.add(cd);
                
            } 
            
            v_size = _columns.size();
            ColumnData[] columnDataArray = new ColumnData[v_size];
            
            for (int i = 0; i < v_size; i++) {
                columnDataArray[i] = (ColumnData)_columns.get(i);
            } 
            
            return columnDataArray;
   
        }
        catch (SQLException e) {
            throw new DataSourceException(e);
        }
        finally {
            releaseResources(rs);
        } 
        
    }
    
    /** 
     * Retrieves the database product name from
     * the connection's meta data.
     *
     * @return the database product name
     */
    public String getDatabaseProductName() throws DataSourceException {
        try {
            ensureConnection();
            DatabaseMetaData dmd = connection.getMetaData();
            return dmd.getDatabaseProductName();            
        } 
        catch (SQLException e) {
            throw new DataSourceException(e);
        }
        finally {
            releaseResources();
        } 
    }

    /** 
     * Retrieves the database product version from
     * the connection's meta data.
     *
     * @return the database product version
     */
    public String getDatabaseProductVersion() throws DataSourceException {
        try {
            ensureConnection();
            DatabaseMetaData dmd = connection.getMetaData();
            return dmd.getDatabaseProductVersion();
        } 
        catch (SQLException e) {
            throw new DataSourceException(e);
        }
        finally {
            releaseResources();
        } 
        
    }

    /** 
     * Retrieves the database product version from
     * the connection's meta data.
     *
     * @return the database product version
     */
    public String getDatabaseProductNameVersion() throws DataSourceException {
        try {
            ensureConnection();
            DatabaseMetaData dmd = connection.getMetaData();
            return dmd.getDatabaseProductName() + " " + 
                    dmd.getDatabaseProductVersion();
        } 
        catch (SQLException e) {
            throw new DataSourceException(e);
        }
        finally {
            releaseResources();
        } 
        
    }

    public DatabaseProcedure[] getProcedures(String schema, 
                                             String[] names) throws DataSourceException {
        return getProcedures(null, schema, names);
    }

    public DatabaseProcedure[] getProcedures(String catalog, 
                                             String schema, 
                                             String[] names) throws DataSourceException {
        ResultSet rs = null;
        try {
            ensureConnection();
            DatabaseMetaData dmd = connection.getMetaData();
            
            List<DatabaseProcedure> list = new ArrayList<DatabaseProcedure>(names.length);
            for (int i = 0; i < names.length; i++) {                
                rs = dmd.getProcedureColumns(catalog, schema, names[i], null);

                DatabaseProcedure proc = new DatabaseProcedure(schema, names[i]);
                while (rs.next()) {
                    proc.addParameter(rs.getString(4),
                                      rs.getInt(5),
                                      rs.getInt(6),
                                      rs.getString(7),
                                      rs.getInt(8));
                } 
                list.add(proc);
                rs.close();
            }
            
            return (DatabaseProcedure[])
                        list.toArray(new DatabaseProcedure[names.length]);
        }
        catch (SQLException e) {
            throw new DataSourceException(e);
        }
        finally {
            releaseResources(rs);
        }
        
    }
    
    public DatabaseProcedure getProcedureColumns(String schema, String name) 
        throws DataSourceException {
        return getProcedureColumns(null, schema, name);
    }
    
    public DatabaseProcedure getProcedureColumns(String catalog, 
                                                 String schema, 
                                                 String name) 
                                                 throws DataSourceException {
        ResultSet rs = null;
/*
        if (schema == null) {
            schema = getSchemaName();
        }
*/

        /*
        Log.debug("catalog: " + catalog + 
                " schema: " + schema + " name: " + name);
        */

        try {
            ensureConnection();
            DatabaseMetaData dmd = connection.getMetaData();
            rs = dmd.getProcedureColumns(catalog, schema, name, null);
            DatabaseProcedure dbproc = new DatabaseProcedure(schema, name);

            while (rs.next()) {
                dbproc.addParameter(rs.getString(4),
                                    rs.getInt(5),
                                    rs.getInt(6),
                                    rs.getString(7),
                                    rs.getInt(8));
            } 
            return dbproc;
            
        }
        catch (SQLException e) {
            throw new DataSourceException(e);
        }
        finally {
            releaseResources(rs);
        }        
    }

    /**
     * Retrieves the data in its entirety from the specified table 
     * using <code>SELECT * FROM table_name</code>.
     *
     * @param schema - the schema name (may be null)
     * @param table - the table name
     * @return the table data
     */
    public ResultSet getTableData(String schema, String table) 
        throws DataSourceException {
        ResultSet rs = null;
        Statement stmnt = null;
        try {
            ensureConnection();
            StringBuffer sb = new StringBuffer();
            sb.append("SELECT * FROM ");
            
            if (!MiscUtils.isNull(schema)) {
                sb.append(schema);
                sb.append(".");
            }
            sb.append(table);
            
            stmnt = connection.createStatement();
            return stmnt.executeQuery(sb.toString());
        }
        catch (SQLException e) {
            throw new DataSourceException(e);
        }
    }
    
    /**
     * Retrieves the table data row count for the specified table.
     *
     * @param schema - the schema name (may be null)
     * @param table - the table name
     * @return the data row count
     */
    public int getTableDataRowCount(String schema, String table) 
        throws DataSourceException {
        ResultSet rs = null;
        Statement stmnt = null;
        try {
            ensureConnection();
            StringBuffer sb = new StringBuffer();
            sb.append("SELECT COUNT(*) FROM ");
            
            if (!MiscUtils.isNull(schema)) {