通用的Oracle数据库当前连接用户下的所有表结构、视图、索引 JSP展示-白红宇

通用的Oracle数据库当前连接用户下的所有表结构、视图、索引 JSP展示

阅读量：6935 次

发布时间：2019-06-27

本文共 3291 字，大约阅读时间需要 10 分钟。

<%@ page contentType="text/html; charset=GBK" %><%@ page language="java" import="java.sql.*"%><%@ page import="java.util.*" %><%@ page import="java.text.*"%><%@ page import="java.util.Date"%><%Connection con = null;try{ request.setCharacterEncoding("GBK");  String dbUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";     String theUser = "orcl";     String thePw = "orcl";       Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();     con = DriverManager.getConnection(dbUrl, theUser, thePw);      String querySql = ""; Statement cn = con.createStatement();  ResultSet rs = null; String DBUserName = ""; rs = cn.executeQuery("select user from dual"); if(rs.next()){  DBUserName = rs.getString(1); } rs.close(); %> 
     
     Oracle数据库当前连接用户下的所有表结构、视图、索引	 
    
      
     
     
<% for(int i=0;i
 <%  }%> 
                        一、系统表结构         
                    
         
<% int tableNum = 0; int tableSuffix = 0; querySql = "select count(*) from all_tab_comments t where t.owner='" + DBUserName + "' and t.table_type='TABLE'"; rs = cn.executeQuery(querySql); rs.next(); tableNum = rs.getInt(1); rs.close(); String tableName[] = new String[tableNum]; String comments[] = new String[tableNum]; querySql = "select t.*, rownum from all_tab_comments t where t.owner='" + DBUserName + "' and t.table_type='TABLE' order by table_name"; rs = cn.executeQuery(querySql); while(rs.next()){ tableName[tableSuffix] = rs.getString("TABLE_NAME"); comments[tableSuffix] = rs.getString("COMMENTS");%> 
<% tableSuffix++; } rs.close();%> 
                                   序号              表名称              注解             
                        <%=rs.getString("rownum")%>              <%=tableName[tableSuffix]%>              <%=comments[tableSuffix]%>             
          
         
          
                    二、用户表结构         
                 <%=i+1%>、<%=tableName[i]%>（<%=comments[i]%>）         
                    
         
<% querySql = "select t.column_name, t.COMMENTS, t1.data_type, t1.data_length, t1.nullable, rownum from all_col_comments t, all_tab_cols t1 where t.column_name = t1.column_name and t.table_name = t1.table_name and t1.owner='" + DBUserName + "' and t.owner = t1.owner and t.TABLE_NAME = '" + tableName[i] + "' order by t1.COLUMN_ID "; rs = cn.executeQuery(querySql); while(rs.next()){%> 
<% } rs.close();%> 
                                   序号              字段名称              字段类型(宽度)              是否为空              注解              　              　             
                        <%=rs.getString("rownum")%>              <%=rs.getString("column_name")%>              <%=rs.getString("data_type")%>(<%=rs.getString("data_length")%>)              <%=rs.getString("nullable")%>              <%=rs.getString("COMMENTS")%>              FALSE              FALSE             
          
         
            
                 三、视图         
                    
         
<% querySql = "select t.*, rownum from all_tab_comments t where t.owner='" + DBUserName + "' and t.table_type='VIEW' order by rownum desc "; rs = cn.executeQuery(querySql); while(rs.next()){ %> 
<% } rs.close();%> 
                                   序号              视图名称              注解             
                        <%=rs.getString("rownum")%>              <%=rs.getString("TABLE_NAME")%>              <%=rs.getString("COMMENTS")%>             
          
         
          
                    四、索引         
                    
         
<% querySql = "select t.*, rownum from all_indexes t where t.table_owner='" + DBUserName + "' order by rownum desc "; rs = cn.executeQuery(querySql); while(rs.next()){ %> 
<% } rs.close();%> 
                                   序号              索引名称              表名称             
                        <%=rs.getString("rownum")%>              <%=rs.getString("INDEX_NAME")%>              <%=rs.getString("TABLE_NAME")%>             
          
         
          
      
     
    <%}catch(Exception e){ out.print(e.getMessage());}finally{ con.close();}%>