用JSP导出ORACLE的数据表DDL

这几天忙着整理搞ISO的文档，也不知道自己维护的数据库手册是否齐全，算了干脆写个程序全给导出来算了；）。下面的程序expddl.jsp可导出当前连接数据库的用户下的所有数据表的DDL.

EXPDDL.JSP:

<%@ page contentType="text/html; charset=gb2312" language="java" %>
<%@ page import = "java.sql.*" %>
<%@ page import = "oracle.jdbc.OracleDatabaseMetaData" %>
<%@ page import = "oracle.jdbc.OracleResultSetMetaData" %>
<%@ page import = "javax.sql.*,javax.naming.Context" %>
<%@ page import = "javax.naming.InitialContext" %>
<div align="center">
<%
 DataSource ds = null;
 
 Context ic = new InitialContext();
 ds = (DataSource) ic.lookup("java:comp/env/jdbc/mydb");
 
 Connection conn = ds.getConnection();
 
 OracleDatabaseMetaData odmd = (OracleDatabaseMetaData) conn.getMetaData();
 String us = odmd.getUserName();
 String[] types = new String[1];
    types[0]  = "TABLE";
 ResultSet rs = odmd.getTables(null,us,"%",types);
 while(rs.next()) {
   //取表名
   String tname = rs.getString(3);
 %>
   <div align="center">表名：<%= tname%></div>
   <table width="483" height="50" border="1"  cellpadding="2" cellspacing="0">
     <tr>
       <td><div align="center">序号</div></td>
       <td><div align="center">标识</div></td>
       <td><div align="center">类型/精度</div></td>
          <td><div align="center">标识说明</div></td>
     </tr>
 <%
   Statement st = conn.createStatement();
   ResultSet rs1 = st.executeQuery("select * from " + tname);
   OracleResultSetMetaData ormd = (OracleResultSetMetaData) rs1.getMetaData();
   //取列总数
   int colcnt = ormd.getColumnCount();
   //列序号
      int num = 1;
   for(int j=0;j<colcnt;j++) {
  String s = ormd.getColumnTypeName(j+1).toLowerCase();
  //只处理varchar2 char date number类型(根据需要添加相应的处理)
  if(s.equals("varchar2") || s.equals("char") || s.equals("date") || s.equals("number")) {
    if(s.equals("varchar2") || s.equals("char"))
      s = s + "(" + ormd.getColumnDisplaySize(j+1) + ")";
    if(s.equals("number"))
      s = s + "(" + ormd.getPrecision(j+1) + "," + ormd.getScale(j+1) + ")";
  %>
   <tr>
      <td width="40"><div align="center"><%= num++ %></div></td>
   <td width="98"><%=ormd.getColumnName(j+1)%></td><td width="144"><%= s %></td>
   <td width="173">&nbsp;</td> </tr>
 <%
     }
   }
   rs1.close();
   st.close();
 %>
  </table>
  <br>
  <%
 }
 rs.close();
 conn.close();
  %>
</div>

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18