用JSP导出ORACLE的数据表DDL

下面的程序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"> </td> </tr>

<%

}

}

rs1.close();

st.close();

%>

</table>

<br>

<%

}

rs.close();

conn.close();

%>

</div>