databaseutil.java

国外的一套开源CRM

        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);

            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);
            }
            return null;
        }

        /*
         try {
         if (Debug.infoOn()) Debug.logInfo("Database Product Name is " + dbData.getDatabaseProductName(), module);
         if (Debug.infoOn()) Debug.logInfo("Database Product Version is " + dbData.getDatabaseProductVersion(), module);
         } catch (SQLException sqle) {
         Debug.logWarning("Unable to get Database name & version information", module);
         }
         try {
         if (Debug.infoOn()) Debug.logInfo("Database Driver Name is " + dbData.getDriverName(), module);
         if (Debug.infoOn()) Debug.logInfo("Database Driver Version is " + dbData.getDriverVersion(), module);
         } catch (SQLException sqle) {
         Debug.logWarning("Unable to get Driver name & version information", module);
         }
         */

        if (Debug.infoOn()) Debug.logInfo("Getting Foreign Key (Reference) Info From Database", module);

        Map refInfo = new HashMap();

        try {
            // ResultSet rsCols = dbData.getCrossReference(null, null, null, null, null, null);
            String lookupSchemaName = null;
            if (dbData.supportsSchemasInTableDefinitions()) {
                if (this.datasourceInfo.schemaName != null && this.datasourceInfo.schemaName.length() > 0) {
                    lookupSchemaName = this.datasourceInfo.schemaName;
                } else {
                    lookupSchemaName = dbData.getUserName();
                }
            }

            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);
            }

            ResultSet rsCols = dbData.getImportedKeys(null, lookupSchemaName, null);
            int totalFkRefs = 0;

            // Iterator tableNamesIter = tableNames.iterator();
            // while (tableNamesIter.hasNext()) {
            // String tableName = (String) tableNamesIter.next();
            // ResultSet rsCols = dbData.getImportedKeys(null, null, tableName);
            // Debug.logVerbose("Getting imported keys for table " + tableName, module);

            while (rsCols.next()) {
                try {
                    ReferenceCheckInfo rcInfo = new ReferenceCheckInfo();

                    rcInfo.pkTableName = rsCols.getString("PKTABLE_NAME");
                    if (needsUpperCase && rcInfo.pkTableName != null) {
                        rcInfo.pkTableName = rcInfo.pkTableName.toUpperCase();
                    }
                    rcInfo.pkColumnName = rsCols.getString("PKCOLUMN_NAME");
                    if (needsUpperCase && rcInfo.pkColumnName != null) {
                        rcInfo.pkColumnName = rcInfo.pkColumnName.toUpperCase();
                    }

                    rcInfo.fkTableName = rsCols.getString("FKTABLE_NAME");
                    if (needsUpperCase && rcInfo.fkTableName != null) {
                        rcInfo.fkTableName = rcInfo.fkTableName.toUpperCase();
                    }
                    // ignore the column info if the FK table name is not in the list we are concerned with
                    if (!tableNames.contains(rcInfo.fkTableName)) {
                        continue;
                    }
                    rcInfo.fkColumnName = rsCols.getString("FKCOLUMN_NAME");
                    if (needsUpperCase && rcInfo.fkColumnName != null) {
                        rcInfo.fkColumnName = rcInfo.fkColumnName.toUpperCase();
                    }
                    rcInfo.fkName = rsCols.getString("FK_NAME");
                    if (needsUpperCase && rcInfo.fkName != null) {
                        rcInfo.fkName = rcInfo.fkName.toUpperCase();
                    }

                    if (Debug.verboseOn()) Debug.logVerbose("Got: " + rcInfo.toString(), module);

                    Map tableRefInfo = (Map) refInfo.get(rcInfo.fkTableName);
                    if (tableRefInfo == null) {
                        tableRefInfo = new HashMap();
                        refInfo.put(rcInfo.fkTableName, tableRefInfo);
                        if (Debug.verboseOn()) Debug.logVerbose("Adding new Map for table: " + rcInfo.fkTableName, module);
                    }
                    if (!tableRefInfo.containsKey(rcInfo.fkName)) totalFkRefs++;
                    tableRefInfo.put(rcInfo.fkName, rcInfo);
                } catch (SQLException sqle) {
                    String message = "Error getting fk reference info for table. Error was:" + sqle.toString();
                    Debug.logError(message, module);
                    if (messages != null) messages.add(message);
                    continue;
                }
            }

            // if (Debug.infoOn()) Debug.logInfo("There are " + totalFkRefs + " in the database", module);
            try {
                rsCols.close();
            } catch (SQLException sqle) {
                String message = "Unable to close ResultSet for fk reference list, continuing anyway... Error was:" + sqle.toString();
                Debug.logError(message, module);
                if (messages != null) messages.add(message);
            }
            // }
            if (Debug.infoOn()) Debug.logInfo("There are " + totalFkRefs + " foreign key refs in the database", module);

        } catch (SQLException sqle) {
            String message = "Error getting fk reference meta data Error was:" + sqle.toString() + ". Not checking fk refs.";
            Debug.logError(message, module);
            if (messages != null) messages.add(message);
            refInfo = null;
        } finally {
            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 refInfo;
    }

    public Map getIndexInfo(Set tableNames, 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;
        }

        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);

            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);
            }
            return null;
        }

        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);
        }

        if (Debug.infoOn()) Debug.logInfo("Getting Index Info From Database", module);

        Map indexInfo = new HashMap();
        try {
            int totalIndices = 0;
            Iterator tableNamesIter = tableNames.iterator();
            while (tableNamesIter.hasNext()) {
                String curTableName = (String) tableNamesIter.next();

                String lookupSchemaName = null;
                if (dbData.supportsSchemasInTableDefinitions()) {
                    if (this.datasourceInfo.schemaName != null && this.datasourceInfo.schemaName.length() > 0) {
                        lookupSchemaName = this.datasourceInfo.schemaName;
                    } else {
                        lookupSchemaName = dbData.getUserName();
                    }
                }

                ResultSet rsCols = null;
                try {
                    // false for unique, we don't really use unique indexes
                    // true for approximate, don't really care if stats are up-to-date
                    rsCols = dbData.getIndexInfo(null, lookupSchemaName, curTableName, false, true);
                } catch (Exception e) {
                    Debug.logWarning(e, "Error getting index info for table: " + curTableName + " using lookupSchemaName " + lookupSchemaName, module);
                }

                while (rsCols != null && rsCols.next()) {
                    // NOTE: The code in this block may look funny, but it is designed so that the wrapping loop can be removed
                    try {
                        // skip all index info for statistics
                        if (rsCols.getShort("TYPE") == DatabaseMetaData.tableIndexStatistic) continue;

                        // HACK: for now skip all "unique" indexes since our foreign key indices are not unique, but the primary key ones are
                        if (!rsCols.getBoolean("NON_UNIQUE")) continue;

                        String tableName = rsCols.getString("TABLE_NAME");
                        if (needsUpperCase && tableName != null) {
                            tableName = tableName.toUpperCase();
                        }
                        if (!tableNames.contains(tableName)) continue;

                        String indexName = rsCols.getString("INDEX_NAME");
                        if (needsUpperCase && indexName != null) {
                            indexName = indexName.toUpperCase();
                        }