invalid-file<%@ page import="oracle.jdbc.driver.*" %>
<%@ page import="javax.naming.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="java.sql.*" %>
<%
/*
response.setContentType("application/vnd.ms-excel; charset=euc-kr");
request.setCharacterEncoding("euc-kr");
response.setHeader("Cache-Control", "no-cache");
response.setHeader("Content-Disposition", "attachment;filename=table.xls");
response.setHeader("Content-Description", "JSP Generated Data");
*/
%>
<%!
public String isnull(String str) {
if (str == null) return " ";
return str;
}
%>
<html>
<head>
<title>테이블명세서</title>
<style type='text/css'>
td {font-size:12px; text-align:center;}
.header {font-weight:bold; background:#ddd}
</style>
</head>
<body>
<table border='1' cellspacing='0'>
<%
try {
Connection conDs = null;
Statement stmt = null;
Statement stmt2 = null;
ResultSet rs = null;
String query = new String();
String querysub = new String();
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = null;
conDs = DriverManager.getConnection("jdbc:oracle:thin:@000.000.000.:1521:aaaaa", "bbbbb", "ccccc");
stmt = conDs.createStatement();
stmt2 = conDs.createStatement();
query = " SELECT NVL (a.tname, ' ') tname, NVL (b.comments, ' ') comments, "
+ " NVL (b.owner, ' ') owner "
+ " FROM tab a, all_tab_comments b "
+ " WHERE a.tname = b.table_name "
+ " AND (b.table_name like 'T~_%' ESCAPE '~' or b.table_name like 'W~_%' ESCAPE '~') ";
System.out.println(query);
rs = stmt.executeQuery(query);
ResultSet rssub = null;
while (rs.next()) {
querysub = " SELECT cname, coltype, length, nulls, pk, defaultval, comments FROM ( "
+ " SELECT b.cname, a.comments, b.colno, b.coltype, b.width, b.scale, b.precision, "
+ " b.nulls, b.defaultval, "
+ " (CASE "
+ " WHEN b.coltype = 'NUMBER' "
+ " THEN b.scale + b.PRECISION "
+ " ELSE width "
+ " END "
+ " ) length, "
+ " (CASE "
+ " WHEN b.colno IN ( "
+ " SELECT s2.POSITION "
+ " FROM all_constraints s1, all_cons_columns s2 "
+ " WHERE s1.table_name = b.tname "
+ " AND s1.constraint_type = 'P' "
+ " AND s1.table_name = s2.table_name "
+ " AND s1.constraint_name = s2.constraint_name) "
+ " THEN 'PK' "
+ " ELSE ' ' "
+ " END "
+ " ) pk "
+ " FROM SYS.all_col_comments a, col b "
+ " WHERE a.table_name = b.tname "
+ " AND a.column_name = b.cname "
+ " AND a.table_name = '"+rs.getString(1)+"' )";
rssub = stmt2.executeQuery(querysub);
int intCnt = 0;
while(rssub.next()) {
if(intCnt == 0 || intCnt%25 == 0) {
if(intCnt > 0) {
%>
<tr height="40">
<td colspan="8"></td>
</tr>
<%}%>
<tr>
<td colspan="2">테이블 정보</td>
<td colspan="6"><%=isnull(rs.getString("tname"))%></td>
</tr>
<tr>
<td colspan="2">테이블 설명</td>
<td colspan="6"><%=isnull(rs.getString("comments"))%></td>
</tr>
<tr>
<td class='header'>번호 </td>
<td class='header'>컬럼명</td>
<td class='header'>데이터 타입</td>
<td class='header'>Length</td>
<td class='header'>널</td>
<td class='header'>키</td>
<td class='header'>기본값</td>
<td class='header'>설명</td>
</tr>
<% }
intCnt = intCnt + 1;
%>
<tr>
<td><%=intCnt%></td>
<td><%=isnull(rssub.getString(1))%></td>
<td><%=isnull(rssub.getString(2))%></td>
<td><%=isnull(rssub.getString(3))%></td>
<td><%=isnull(rssub.getString(4))%></td>
<td><%=isnull(rssub.getString(5))%></td>
<td><%=isnull(rssub.getString(6))%></td>
<td><%=isnull(rssub.getString(7))%></td>
</tr>
<% }
rssub.close();
%>
<tr height="40">
<td colspan="8"></td>
</tr>
<%
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (stmt != null) try {stmt.close();} catch(Exception e) {}
if (conDs != null) try {conDs.close();} catch(Exception e) {}
}
%>
</table>
</body>
</html>
'Project' 카테고리의 다른 글
| cvs 사용자 추가 (0) | 2011.03.10 |
|---|---|
| 프로젝트 진행 사전 정보 (1) | 2011.01.10 |
| erwin script 모음 (3) | 2010.11.19 |
| java coding conventions (1) | 2010.04.09 |
| 프로젝트 관리용 FileCheck (3) | 2010.04.07 |