databaseutil.java

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

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

        Connection connection = null;
        try {
            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 = FastMap.newInstance();
            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);
                }

                boolean foundCols = false;
                ResultSet rsCols = dbData.getColumns(null, lookupSchemaName, null, null);
                if (!rsCols.next()) {
                    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);
                    }
                    rsCols = dbData.getColumns(null, lookupSchemaName, "%", "%");
                    if (!rsCols.next()) {
                        // TODO: now what to do? I guess try one table name at a time...
                    } else {
                        foundCols = true;
                    }
                } else {
                    foundCols = true;
                }
                if (foundCols) {
                    do {
                        try {
                            ColumnCheckInfo ccInfo = new ColumnCheckInfo();

                            ccInfo.tableName = ColumnCheckInfo.fixupTableName(rsCols.getString("TABLE_NAME"), lookupSchemaName, needsUpperCase);
                            // 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");

                            Map tableColInfo = (Map) colInfo.get(ccInfo.tableName);
                            if (tableColInfo == null) {
                                tableColInfo = FastMap.newInstance();
                                colInfo.put(ccInfo.tableName, tableColInfo);
                            }
                            tableColInfo.put(ccInfo.columnName, 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;
                        }
                    } while (rsCols.next());
                }

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

                if (getPks) {
                    boolean foundPks = false;
                    ResultSet rsPks = dbData.getPrimaryKeys(null, lookupSchemaName, null);
                    if (!rsPks.next()) {
                        try {
                            rsPks.close();
                        } catch (SQLException sqle) {
                            String message = "Unable to close ResultSet for primary key list, continuing anyway... Error was:" + sqle.toString();
                            Debug.logError(message, module);
                            if (messages != null) messages.add(message);
                        }
                        rsPks = dbData.getPrimaryKeys(null, lookupSchemaName, "%");
                        if (!rsPks.next()) {
                            // TODO: now what to do? I guess try one table name at a time...
                        } else {
                            foundPks = true;
                        }
                    } else {
                        foundPks = true;
                    }
                    if (foundPks) {
                        do {
                            try {
                                String tableName = ColumnCheckInfo.fixupTableName(rsPks.getString("TABLE_NAME"), lookupSchemaName, needsUpperCase);
                                String columnName = rsPks.getString("COLUMN_NAME");
                                if (needsUpperCase && columnName != null) {
                                    columnName = columnName.toUpperCase();
                                }
                                Map tableColInfo = (Map) colInfo.get(tableName);
                                if (tableColInfo == null) {
                                    // not looking for info on this table
                                    continue;
                                }
                                ColumnCheckInfo ccInfo = (ColumnCheckInfo) tableColInfo.get(columnName);
                                if (ccInfo == null) {
                                    // this isn't good, what to do?
                                    Debug.logWarning("Got primary key information for a column that we didn't get column information for: tableName=[" + tableName + "], columnName=[" + columnName + "]", module);
                                    continue;
                                }

                                /*
                                KEY_SEQ short => sequence number within primary key
                                PK_NAME String => primary key name (may be null)
                                */
                                ccInfo.isPk = true;
                                ccInfo.pkSeq = rsPks.getShort("KEY_SEQ");
                                ccInfo.pkName = rsPks.getString("PK_NAME");
                            } catch (SQLException sqle) {
                                String message = "Error getting primary key info for column. Error was:" + sqle.toString();
                                Debug.logError(message, module);
                                if (messages != null) messages.add(message);
                                continue;
                            }
                        } while (rsPks.next());
                    }

                    try {
                        rsPks.close();
                    } catch (SQLException sqle) {
                        String message = "Unable to close ResultSet for primary key 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;
            }
            return colInfo;
        } finally {
            if (connection != null) {
                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);
                }
            }
        }
    }

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

        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(