databaseutil.java

Sequoia ERP是一个真正的企业级开源ERP解决方案。它提供的模块包括：电子商务应用(e-commerce), POS系统(point of sales),知识管理,存货与仓库管理

    public DatabaseMetaData getDatabaseMetaData(Connection connection, Collection messages) {
        if (connection == null) {
            try {
                connection = getConnection();
            } catch (SQLException sqle) {
                String message = "Unable to esablish a connection with the database... Error was:" + sqle.toString();
                Debug.logError(message, module);
                if (messages != null) messages.add(message);
                return null;
            } catch (GenericEntityException e) {
                String message = "Unable to esablish a connection with the database... Error was:" + e.toString();
                Debug.logError(message, module);
                if (messages != null) messages.add(message);
                return null;
            }
        }

        if (connection == null) {
            String message = "Unable to esablish a connection with the database, no additional information available.";
            Debug.logError(message, module);
            if (messages != null) messages.add(message);
            return null;
        }

        DatabaseMetaData dbData = null;
        try {
            dbData = connection.getMetaData();
        } catch (SQLException sqle) {
            String message = "Unable to get database meta data... Error was:" + sqle.toString();
            Debug.logError(message, module);
            if (messages != null) {
                messages.add(message);
            }
            return null;
        }

        if (dbData == null) {
            Debug.logWarning("Unable to get database meta data; method returned null", module);
        }

        return dbData;
    }

    public void printDbMiscData(DatabaseMetaData dbData, Connection con) {
        if (dbData == null) {
            return;
        }
        // Database Info
        if (Debug.infoOn()) {
            try {
                Debug.logInfo("Database Product Name is " + dbData.getDatabaseProductName(), module);
                Debug.logInfo("Database Product Version is " + dbData.getDatabaseProductVersion(), module);
            } catch (SQLException sqle) {
                Debug.logWarning("Unable to get Database name & version information", module);
            }
        }
        // JDBC Driver Info
        if (Debug.infoOn()) {
            try {
                Debug.logInfo("Database Driver Name is " + dbData.getDriverName(), module);
                Debug.logInfo("Database Driver Version is " + dbData.getDriverVersion(), module);
                Debug.logInfo("Database Driver JDBC Version is " + dbData.getJDBCMajorVersion() + "." + dbData.getJDBCMinorVersion(), module);
            } catch (SQLException sqle) {
                Debug.logWarning("Unable to get Driver name & version information", module);
            } catch (AbstractMethodError ame) {
                Debug.logWarning("Unable to get Driver JDBC Version", module);
            }
        }
        // Db/Driver support settings
        if (Debug.infoOn()) {
            try {
                Debug.logInfo("Database Setting/Support Information (those with a * should be true):", module);
                Debug.logInfo("- supports transactions    [" + dbData.supportsTransactions() + "]*", module);
                Debug.logInfo("- isolation None           [" + dbData.supportsTransactionIsolationLevel(Connection.TRANSACTION_NONE) + "]", module);
                Debug.logInfo("- isolation ReadCommitted  [" + dbData.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED) + "]", module);
                Debug.logInfo("- isolation ReadUncommitted[" + dbData.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED) + "]", module);
                Debug.logInfo("- isolation RepeatableRead [" + dbData.supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ) + "]", module);
                Debug.logInfo("- isolation Serializable   [" + dbData.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE) + "]", module);
                Debug.logInfo("- default fetchsize        [" + con.createStatement().getFetchSize() + "]", module);
                Debug.logInfo("- forward only type        [" + dbData.supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY) + "]", module);
                Debug.logInfo("- scroll sensitive type    [" + dbData.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE) + "]", module);
                Debug.logInfo("- scroll insensitive type  [" + dbData.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE) + "]", module);
                Debug.logInfo("- is case sensitive        [" + dbData.supportsMixedCaseIdentifiers() + "]", module);
                Debug.logInfo("- stores LowerCase         [" + dbData.storesLowerCaseIdentifiers() + "]", module);
                Debug.logInfo("- stores MixedCase         [" + dbData.storesMixedCaseIdentifiers() + "]", module);
                Debug.logInfo("- stores UpperCase         [" + dbData.storesUpperCaseIdentifiers() + "]", module);
                Debug.logInfo("- max table name length    [" + dbData.getMaxTableNameLength() + "]", module);
                Debug.logInfo("- max column name length   [" + dbData.getMaxColumnNameLength() + "]", module);
                Debug.logInfo("- max schema name length   [" + dbData.getMaxSchemaNameLength() + "]", module);
                Debug.logInfo("- concurrent connections   [" + dbData.getMaxConnections() + "]", module);
                Debug.logInfo("- concurrent statements    [" + dbData.getMaxStatements() + "]", module);
                Debug.logInfo("- ANSI SQL92 Entry         [" + dbData.supportsANSI92EntryLevelSQL() + "]", module);
                Debug.logInfo("- ANSI SQL92 Itermediate   [" + dbData.supportsANSI92IntermediateSQL() + "]", module);
                Debug.logInfo("- ANSI SQL92 Full          [" + dbData.supportsANSI92FullSQL() + "]", module);
                Debug.logInfo("- ODBC SQL Grammar Core    [" + dbData.supportsCoreSQLGrammar() + "]", module);
                Debug.logInfo("- ODBC SQL Grammar Extended[" + dbData.supportsExtendedSQLGrammar() + "]", module);
                Debug.logInfo("- ODBC SQL Grammar Minimum [" + dbData.supportsMinimumSQLGrammar() + "]", module);
                Debug.logInfo("- outer joins              [" + dbData.supportsOuterJoins() + "]*", module);
                Debug.logInfo("- limited outer joins      [" + dbData.supportsLimitedOuterJoins() + "]", module);
                Debug.logInfo("- full outer joins         [" + dbData.supportsFullOuterJoins() + "]", module);
                Debug.logInfo("- group by                 [" + dbData.supportsGroupBy() + "]*", module);
                Debug.logInfo("- group by not in select   [" + dbData.supportsGroupByUnrelated() + "]", module);
                Debug.logInfo("- column aliasing          [" + dbData.supportsColumnAliasing() + "]", module);
                Debug.logInfo("- order by not in select   [" + dbData.supportsOrderByUnrelated() + "]", module);
                // this doesn't work in HSQLDB, other databases? Debug.logInfo("- named parameters         [" + dbData.supportsNamedParameters() + "]", module);
                Debug.logInfo("- alter table add column   [" + dbData.supportsAlterTableWithAddColumn() + "]*", module);
                Debug.logInfo("- non-nullable column      [" + dbData.supportsNonNullableColumns() + "]*", module);
            } catch (Exception e) {
                Debug.logWarning(e, "Unable to get misc. support/setting information", module);
            }
        }
    }

    public TreeSet getTableNames(Collection messages) {
        Connection connection = null;

        try {
            connection = getConnection();
        } catch (SQLException sqle) {
            String message = "Unable to esablish a connection with the database... Error was:" + sqle.toString();
            Debug.logError(message, module);
            if (messages != null) messages.add(message);
            return null;
        } catch (GenericEntityException e) {
            String message = "Unable to esablish a connection with the database... Error was:" + e.toString();
            Debug.logError(message, module);
            if (messages != null) messages.add(message);
            return null;
        }

        if (connection == null) {
            String message = "Unable to esablish a connection with the database, no additional information available.";
            Debug.logError(message, module);
            if (messages != null) messages.add(message);
            return null;
        }

        DatabaseMetaData dbData = this.getDatabaseMetaData(connection, messages);
        if (dbData == null) {
            return null;
        }

        printDbMiscData(dbData, connection);
        if (Debug.infoOn()) Debug.logInfo("Getting Table Info From Database", module);

        // get ALL tables from this database
        TreeSet tableNames = new TreeSet();
        ResultSet tableSet = null;

        String lookupSchemaName = null;
        try {
            String[] types = {"TABLE", "VIEW", "ALIAS", "SYNONYM"};
            lookupSchemaName = getSchemaName(dbData);
            tableSet = dbData.getTables(null, lookupSchemaName, null, types);
            if (tableSet == null) {
                Debug.logWarning("getTables returned null set", module);
            }
        } catch (SQLException sqle) {
            String message = "Unable to get list of table information, let's try the create anyway... Error was:" + sqle.toString();
            Debug.logError(message, module);
            if (messages != null) messages.add(message);

            try {
                connection.close();
            } catch (SQLException sqle2) {
                String message2 = "Unable to close database connection, continuing anyway... Error was:" + sqle2.toString();
                Debug.logError(message2, module);
                if (messages != null) messages.add(message2);
            }
            // we are returning an empty set here because databases like SapDB throw an exception when there are no tables in the database
            return tableNames;
        }

        try {
            boolean needsUpperCase = false;
            try {
                needsUpperCase = dbData.storesLowerCaseIdentifiers() || dbData.storesMixedCaseIdentifiers();
            } catch (SQLException sqle) {
                String message = "Error getting identifier case information... Error was:" + sqle.toString();
                Debug.logError(message, module);
                if (messages != null) messages.add(message);
            }
            while (tableSet.next()) {
                try {
                    String tableName = tableSet.getString("TABLE_NAME");
                    // for those databases which do not return the schema name with the table name (pgsql 7.3)
                    boolean appendSchemaName = false;
                    if (tableName != null && lookupSchemaName != null && !tableName.startsWith(lookupSchemaName)) {
                        appendSchemaName = true;
                    }
                    if (needsUpperCase && tableName != null) {
                        tableName = tableName.toUpperCase();
                    }
                    if (appendSchemaName) {
                        tableName = lookupSchemaName + "." + tableName;
                    }

                    // NOTE: this may need a toUpperCase in some cases, keep an eye on it, okay for now just do a compare with equalsIgnoreCase
                    String tableType = tableSet.getString("TABLE_TYPE");
                    // only allow certain table types
                    if (tableType != null && !"TABLE".equalsIgnoreCase(tableType) && !"VIEW".equalsIgnoreCase(tableType) && !"ALIAS".equalsIgnoreCase(tableType) && !"SYNONYM".equalsIgnoreCase(tableType)) {
                        continue;
                    }

                    // String remarks = tableSet.getString("REMARKS");
                    tableNames.add(tableName);
                    // if (Debug.infoOn()) Debug.logInfo("Found table named [" + tableName + "] of type [" + tableType + "] with remarks: " + remarks, module);
                } catch (SQLException sqle) {
                    String message = "Error getting table information... Error was:" + sqle.toString();
                    Debug.logError(message, module);
                    if (messages != null) messages.add(message);
                    continue;
                }
            }
        } catch (SQLException sqle) {
            String message = "Error getting next table information... Error was:" + sqle.toString();
            Debug.logError(message, module);
            if (messages != null) messages.add(message);
        } finally {
            try {
                tableSet.close();
            } catch (SQLException sqle) {
                String message = "Unable to close ResultSet for table list, continuing anyway... Error was:" + sqle.toString();
                Debug.logError(message, module);
                if (messages != null) messages.add(message);
            }

            try {
                connection.close();
            } catch (SQLException sqle) {
                String message = "Unable to close database connection, continuing anyway... Error was:" + sqle.toString();
                Debug.logError(message, module);
                if (messages != null) messages.add(message);
            }
        }
        return tableNames;
    }