webthread.java

非常棒的java数据库

        String sql = session.getCommand(id);
        session.put("query", PageParser.escapeHtmlData(sql));
        return "query.jsp";
    }

    private int addColumns(DbTableOrView table, StringBuffer buff, int treeIndex, boolean showColumnTypes,
            StringBuffer columnsBuffer) throws SQLException {
        DbColumn[] columns = table.columns;
        for (int i = 0; columns != null && i < columns.length; i++) {
            DbColumn column = columns[i];
            if (columnsBuffer.length() > 0) {
                columnsBuffer.append(' ');
            }
            columnsBuffer.append(column.name);
            String col = StringUtils.urlEncode(PageParser.escapeJavaScript(column.name));
            buff.append("setNode(" + treeIndex + ", 1, 1, 'column', '" + PageParser.escapeJavaScript(column.name)
                    + "', 'javascript:ins(\\'" + col + "\\')');\n");
            treeIndex++;
            if (showColumnTypes) {
                buff.append("setNode(" + treeIndex + ", 2, 2, 'type', '" + PageParser.escapeJavaScript(column.dataType)
                        + "', null);\n");
                treeIndex++;
            }
        }
        return treeIndex;
    }

    private static class IndexInfo {
        String name;
        String type;
        String columns;
    }

    private int addIndexes(DatabaseMetaData meta, String table, String schema, StringBuffer buff, int treeIndex)
            throws SQLException {
        // index reading is very slow for oracle (2 seconds per index), so don't
        // do it
        ResultSet rs = meta.getIndexInfo(null, schema, table, false, false);
        HashMap indexMap = new HashMap();
        while (rs.next()) {
            String name = rs.getString("INDEX_NAME");
            IndexInfo info = (IndexInfo) indexMap.get(name);
            if (info == null) {
                int t = rs.getInt("TYPE");
                String type;
                if (t == DatabaseMetaData.tableIndexClustered) {
                    type = "";
                } else if (t == DatabaseMetaData.tableIndexHashed) {
                    type = " (${text.tree.hashed})";
                } else if (t == DatabaseMetaData.tableIndexOther) {
                    type = "";
                } else {
                    type = null;
                }
                if (name != null && type != null) {
                    info = new IndexInfo();
                    info.name = name;
                    type = (rs.getBoolean("NON_UNIQUE") ? "${text.tree.nonUnique}" : "${text.tree.unique}") + type;
                    info.type = type;
                    info.columns = rs.getString("COLUMN_NAME");
                    indexMap.put(name, info);
                }
            } else {
                info.columns += ", " + rs.getString("COLUMN_NAME");
            }
        }
        rs.close();
        if (indexMap.size() > 0) {
            buff.append("setNode(" + treeIndex + ", 1, 1, 'index_az', '${text.tree.indexes}', null);\n");
            treeIndex++;
            for (Iterator it = indexMap.values().iterator(); it.hasNext();) {
                IndexInfo info = (IndexInfo) it.next();
                buff.append("setNode(" + treeIndex + ", 2, 1, 'index', '" + PageParser.escapeJavaScript(info.name)
                        + "', null);\n");
                treeIndex++;
                buff.append("setNode(" + treeIndex + ", 3, 2, 'type', '" + info.type + "', null);\n");
                treeIndex++;
                buff.append("setNode(" + treeIndex + ", 3, 2, 'type', '" + PageParser.escapeJavaScript(info.columns)
                        + "', null);\n");
                treeIndex++;
            }
        }
        return treeIndex;
    }

    private int addTablesAndViews(DbSchema schema, boolean mainSchema, StringBuffer buff, int treeIndex)
            throws SQLException {
        if (schema == null) {
            return treeIndex;
        }
        Connection conn = session.getConnection();
        DatabaseMetaData meta = session.getMetaData();
        int level = mainSchema ? 0 : 1;
        String indentation = ", " + level + ", " + (level + 1) + ", ";
        String indentNode = ", " + (level + 1) + ", " + (level + 1) + ", ";
        DbTableOrView[] tables = schema.tables;
        if (tables == null) {
            return treeIndex;
        }
        boolean isOracle = schema.contents.isOracle;
        boolean notManyTables = tables.length < 100;
        for (int i = 0; i < tables.length; i++) {
            DbTableOrView table = tables[i];
            if (table.isView) {
                continue;
            }
            int tableId = treeIndex;
            String tab = table.quotedName;
            if (!mainSchema) {
                tab = schema.quotedName + "." + tab;
            }
            tab = StringUtils.urlEncode(PageParser.escapeJavaScript(tab));
            buff.append("setNode(" + treeIndex + indentation + " 'table', '" + PageParser.escapeJavaScript(table.name)
                    + "', 'javascript:ins(\\'" + tab + "\\',true)');\n");
            treeIndex++;
            if (mainSchema) {
                StringBuffer columnsBuffer = new StringBuffer();
                treeIndex = addColumns(table, buff, treeIndex, notManyTables, columnsBuffer);
                if (!isOracle && notManyTables) {
                    treeIndex = addIndexes(meta, table.name, schema.name, buff, treeIndex);
                }
                buff.append("addTable('" + PageParser.escapeJavaScript(table.name) + "', '"
                        + PageParser.escapeJavaScript(columnsBuffer.toString()) + "', " + tableId + ");\n");
            }
        }
        tables = schema.tables;
        for (int i = 0; i < tables.length; i++) {
            DbTableOrView view = tables[i];
            if (!view.isView) {
                continue;
            }
            int tableId = treeIndex;
            String tab = view.quotedName;
            if (!mainSchema) {
                tab = view.schema.quotedName + "." + tab;
            }
            tab = StringUtils.urlEncode(PageParser.escapeJavaScript(tab));
            buff.append("setNode(" + treeIndex + indentation + " 'view', '" + PageParser.escapeJavaScript(view.name)
                    + "', 'javascript:ins(\\'" + tab + "\\',true)');\n");
            treeIndex++;
            if (mainSchema) {
                StringBuffer columnsBuffer = new StringBuffer();
                treeIndex = addColumns(view, buff, treeIndex, notManyTables, columnsBuffer);
                if (schema.contents.isH2) {
                    PreparedStatement prep = null;
                    try {
                        prep = conn.prepareStatement("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=?");
                        prep.setString(1, view.name);
                        ResultSet rs = prep.executeQuery();
                        if (rs.next()) {
                            String sql = rs.getString("SQL");
                            buff.append("setNode(" + treeIndex + indentNode + " 'type', '"
                                    + PageParser.escapeJavaScript(sql) + "', null);\n");
                            treeIndex++;
                        }
                        rs.close();
                    } finally {
                        JdbcUtils.closeSilently(prep);
                    }
                }
                buff.append("addTable('" + PageParser.escapeJavaScript(view.name) + "', '"
                        + PageParser.escapeJavaScript(columnsBuffer.toString()) + "', " + tableId + ");\n");
            }
        }
        return treeIndex;
    }

    private String tables() {
        DbContents contents = session.getContents();
        boolean isH2 = false;
        try {
            contents.readContents(session.getMetaData());
            session.loadBnf();
            Connection conn = session.getConnection();
            DatabaseMetaData meta = session.getMetaData();
            isH2 = contents.isH2;

            StringBuffer buff = new StringBuffer();
            buff.append("setNode(0, 0, 0, 'database', '" + PageParser.escapeJavaScript((String) session.get("url"))
                    + "', null);\n");
            int treeIndex = 1;

            DbSchema defaultSchema = contents.defaultSchema;
            treeIndex = addTablesAndViews(defaultSchema, true, buff, treeIndex);
            DbSchema[] schemas = contents.schemas;
            for (int i = 0; i < schemas.length; i++) {
                DbSchema schema = schemas[i];
                if (schema == defaultSchema || schema == null) {
                    continue;
                }
                buff.append("setNode(" + treeIndex + ", 0, 1, 'folder', '" + PageParser.escapeJavaScript(schema.name)
                        + "', null);\n");
                treeIndex++;
                treeIndex = addTablesAndViews(schema, false, buff, treeIndex);
            }
            if (isH2) {
                Statement stat = null;
                try {
                    stat = conn.createStatement();
                    ResultSet rs = stat
                            .executeQuery("SELECT * FROM INFORMATION_SCHEMA.SEQUENCES ORDER BY SEQUENCE_NAME");
                    for (int i = 0; rs.next(); i++) {
                        if (i == 0) {
                            buff.append("setNode(" + treeIndex
                                    + ", 0, 1, 'sequences', '${text.tree.sequences}', null);\n");
                            treeIndex++;
                        }
                        String name = rs.getString("SEQUENCE_NAME");
                        String current = rs.getString("CURRENT_VALUE");
                        String increment = rs.getString("INCREMENT");
                        buff.append("setNode(" + treeIndex + ", 1, 1, 'sequence', '"
                                + PageParser.escapeJavaScript(name) + "', null);\n");
                        treeIndex++;
                        buff.append("setNode(" + treeIndex + ", 2, 2, 'type', '${text.tree.current}: "
                                + PageParser.escapeJavaScript(current) + "', null);\n");
                        treeIndex++;
                        if (!"1".equals(increment)) {
                            buff.append("setNode(" + treeIndex + ", 2, 2, 'type', '${text.tree.increment}: "
                                    + PageParser.escapeJavaScript(increment) + "', null);\n");
                            treeIndex++;
                        }
                    }
                    rs.close();
                    rs = stat.executeQuery("SELECT * FROM INFORMATION_SCHEMA.USERS ORDER BY NAME");
                    for (int i = 0; rs.next(); i++) {
                        if (i == 0) {
                            buff.append("setNode(" + treeIndex + ", 0, 1, 'users', '${text.tree.users}', null);\n");
                            treeIndex++;
                        }
                        String name = rs.getString("NAME");
                        String admin = rs.getString("ADMIN");
                        buff.append("setNode(" + treeIndex + ", 1, 1, 'user', '" + PageParser.escapeJavaScript(name)
                                + "', null);\n");
                        treeIndex++;
                        if (admin.equalsIgnoreCase("TRUE")) {
                            buff.append("setNode(" + treeIndex + ", 2, 2, 'type', '${text.tree.admin}', null);\n");
                            treeIndex++;
                        }
                    }
                    rs.close();
                } finally {
                    JdbcUtils.closeSilently(stat);
                }
            }
            String version = meta.getDatabaseProductName() + " " + meta.getDatabaseProductVersion();
            buff.append("setNode(" + treeIndex + ", 0, 0, 'info', '" + PageParser.escapeJavaScript(version)
                    + "', null);\n");
            buff.append("refreshQueryTables();");
            session.put("tree", buff.toString());
        } catch (Exception e) {
            session.put("tree", "");
            session.put("error", getStackTrace(0, e, isH2));
        }
        return "tables.jsp";
    }

    private String getStackTrace(int id, Throwable e, boolean isH2) {
        try {
            StringWriter writer = new StringWriter();
            e.printStackTrace(new PrintWriter(writer));
            String stackTrace = writer.toString();
            stackTrace = PageParser.escapeHtml(stackTrace);
            if (isH2) {
                stackTrace = linkToSource(stackTrace);
            }
            stackTrace = StringUtils.replaceAll(stackTrace, "\t", "&nbsp;&nbsp;&nbsp;&nbsp;");
            String message = PageParser.escapeHtml(e.getMessage());
            String error = "<a class=\"error\" href=\"#\" onclick=\"var x=document.getElementById('st" + id
                    + "').style;x.display=x.display==''?'none':'';\">" + message + "</a>";
            if (e instanceof SQLException) {
                SQLException se = (SQLException) e;
                error += " " + se.getSQLState() + "/" + se.getErrorCode();
                if (isH2) {
                    int code = se.getErrorCode();
                    error += " <a href=\"http://h2database.com/javadoc/org/h2/constant/ErrorCode.html#c" + code
                            + "\">(${text.a.help})</a>";
                }
            }
            error += "<span style=\"display: none;\" id=\"st" + id + "\"><br />" + stackTrace + "</span>";
            error = formatAsError(error);
            return error;
        } catch (OutOfMemoryError e2) {
            e.printStackTrace();
            return e.toString();
        }
    }