databaseutil.java

国外的一套开源CRM

                if (this.datasourceInfo.schemaName != null && this.datasourceInfo.schemaName.length() > 0) {
                    lookupSchemaName = this.datasourceInfo.schemaName;
                } else {
                    lookupSchemaName = dbData.getUserName();
                }
            }
            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;
    }

    public Map getColumnInfo(Set tableNames, Collection messages) {
        // if there are no tableNames, don't even try to get the columns
        if (tableNames.size() == 0) {
            return new HashMap();
        }

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

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

        Map colInfo = new HashMap();
        String lookupSchemaName = null;
        try {
            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.getColumns(null, lookupSchemaName, null, null);
            while (rsCols.next()) {
                try {
                    ColumnCheckInfo ccInfo = new ColumnCheckInfo();

                    ccInfo.tableName = rsCols.getString("TABLE_NAME");
                    // for those databases which do not return the schema name with the table name (pgsql 7.3)
                    boolean appendSchemaName = false;
                    if (ccInfo.tableName != null && lookupSchemaName != null && !ccInfo.tableName.startsWith(lookupSchemaName)) {
                        appendSchemaName = true;
                    }
                    if (needsUpperCase && ccInfo.tableName != null) {
                        ccInfo.tableName = ccInfo.tableName.toUpperCase();
                    }
                    if (appendSchemaName) {
                        ccInfo.tableName = lookupSchemaName + "." + ccInfo.tableName;
                    }
                    // ignore the column info if the table name is not in the list we are concerned with
                    if (!tableNames.contains(ccInfo.tableName)) {
                        continue;
                    }

                    ccInfo.columnName = rsCols.getString("COLUMN_NAME");
                    if (needsUpperCase && ccInfo.columnName != null) {
                        ccInfo.columnName = ccInfo.columnName.toUpperCase();
                    }
                    // NOTE: this may need a toUpperCase in some cases, keep an eye on it
                    ccInfo.typeName = rsCols.getString("TYPE_NAME");
                    ccInfo.columnSize = rsCols.getInt("COLUMN_SIZE");
                    ccInfo.decimalDigits = rsCols.getInt("DECIMAL_DIGITS");
                    // NOTE: this may need a toUpperCase in some cases, keep an eye on it
                    ccInfo.isNullable = rsCols.getString("IS_NULLABLE");

                    List tableColInfo = (List) colInfo.get(ccInfo.tableName);
                    if (tableColInfo == null) {
                        tableColInfo = new ArrayList();
                        colInfo.put(ccInfo.tableName, tableColInfo);
                    }
                    tableColInfo.add(ccInfo);
                } catch (SQLException sqle) {
                    String message = "Error getting column info for column. Error was:" + sqle.toString();
                    Debug.logError(message, module);
                    if (messages != null) messages.add(message);
                    continue;
                }
            }

            try {
                rsCols.close();
            } catch (SQLException sqle) {
                String message = "Unable to close ResultSet for column list, continuing anyway... Error was:" + sqle.toString();
                Debug.logError(message, module);
                if (messages != null) messages.add(message);
            }
        } catch (SQLException sqle) {
            String message = "Error getting column meta data for Error was:" + sqle.toString() + ". Not checking columns.";
            Debug.logError(message, module);
            if (messages != null) messages.add(message);
            // we are returning an empty set in this case because databases like SapDB throw an exception when there are no tables in the database
            // colInfo = 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 colInfo;
    }

    public Map getReferenceInfo(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;
        }