package com.daing.gzb;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import com.daing.gzb.util.JdbcUtil;
public class DBInfoExport extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
doPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
resp.setContentType("text/html; charset=gbk");
String dataName = JdbcUtil.prop.getProperty("dataName");
//System.out.println("dataName" + dataName);
Connection conn = null;
ResultSet rs = null;
List tableList = new ArrayList();
List columnList = new ArrayList();
try {
conn = JdbcUtil.getConnection();
DatabaseMetaData metaData = conn.getMetaData();
rs = metaData.getCatalogs();
while (rs.next()) {
tableList.add(rs.getString("TABLE_CAT"));
}
String catalog = "";
for(int i=0;i<tableList.size();i++){
//System.out.println("dataName" + catalog);
catalog = (String)tableList.get(i);
if((dataName!=null) && !dataName.equalsIgnoreCase(catalog))continue;
rs = metaData.getTables(catalog, "%", "%", new String[]{"TABLE"});
while (rs.next()) {
columnList.add(rs.getString("TABLE_NAME"));
}
System.out.println("数据库名："+catalog);
System.out.println("数据表名：");
String tableName = "";
PrintWriter out = resp.getWriter();
out.println("<html>");
out.println("<style type=\"text/css\"><![CDATA[");
out.println("table.dataintable {font-family:Arial, Helvetica, sans-serif;margin-top:10px;border-collapse:collapse;border:1px solid #888;width:100%; } table.dataintable pre { width:auto; margin:0; padding:0; border:0; background-color:transparent; } table.dataintable th { vertical-align:baseline; padding:5px 15px 5px 5px; background-color:#ccc; border:1px solid #888; text-align:left; } table.dataintable td { vertical-align:text-top; padding:5px 15px 5px 5px; background-color:#efefef; border:1px solid #aaa; } table.dataintable p {margin:0 0 1px 0;} ");
out.println("]]></style>");
out.println("<body>");
out.println("<h1>数据库名："+ catalog +"</h1>" );
for(int j=0;j<columnList.size();j++){
tableName = (String) columnList.get(j);
out.println("<h3>表名："+ tableName +"</h3>" );
System.out.println("\n======================\n" +tableName + "\n======================\n");
getTableInfo(metaData , tableName, conn ,resp ,out);
}
out.println("</body>");
out.println("</html>");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if (rs != null)
rs.close();
if (conn != null)
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
private static void getTableInfo(DatabaseMetaData metaData, String tableName,
Connection con, HttpServletResponse resp, PrintWriter out) throws IOException {
Statement stmt = null;
String pkName = "";
//主键
try {
ResultSet pkSet = metaData.getPrimaryKeys("", "", tableName);
while(pkSet.next()) {
pkName = pkSet.getString("COLUMN_NAME");
System.out.println("主键字段名称>>>>>>>" + pkSet.getString("COLUMN_NAME"));
System.out.println("主键字段列号>>>>>>>" + pkSet.getString("KEY_SEQ"));
}
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
try {
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs=stmt.executeQuery("select * from " + tableName);
ResultSetMetaData rsmd = rs.getMetaData();
//ResultSet expKeys = metaData.getExportedKeys(columnType, columnType, columnType);
/*
<table border="1">
<tr>
<th>Month</th>
<th>Savings</th>
</tr>
<tr>
<td>January</td>
<td>$100</td>
</tr>
</table>
*/
out.println("<table class=\"dataintable\"");
out.println("</tr>");
out.println("<th>");
out.println("字段描述");
out.println("</th>");
out.println("<th>");
out.println("字段名");
out.println("</th>");
out.println("<th>");
out.println("数据类型");
out.println("</th>");
out.println("<th>");
out.println("是否可为空");
out.println("</th>");
out.println("<th>");
out.println("自增");
out.println("</th>");
out.println("<th>");
out.println("主键");
out.println("</th>");
out.println("</tr>");
System.out.println("字段名\t 数据类型 \t 是否为空 \t是否自增");
for(int i=1;i<=rsmd.getColumnCount();i++)
{
String clmname=rsmd.getColumnName(i);
String clmtype=rsmd.getColumnTypeName(i);
int clmsize=rsmd.getColumnDisplaySize(i);
int nullable=rsmd.isNullable(i);
Boolean increase= rsmd.isAutoIncrement(i);
String strNullable =(nullable==0) ? "Null" : "Not Null";
out.println("</tr>");
out.println("<td>");
ResultSet columnSet = metaData.getColumns("", "", tableName, clmname);
while(columnSet.next()) {
System.out.println("字段注释>>>>>>>" + columnSet.getString("REMARKS"));
out.println(columnSet.getString("REMARKS"));
}
out.println("</td>");
out.println("<td>");
out.println(clmname);
out.println("</td>");
out.println("<td>");
out.println(clmtype + "(" + clmsize + ")");
out.println("</td>");
out.println("<td>");
if(nullable==1)
out.println("是");
out.println("</td>");
out.println("<td>");
if(increase)
out.println("自增");
out.println("</td>");
out.println("<td>");
if(pkName.equalsIgnoreCase(clmname))
out.println("是");
out.println("</td>");
out.println("</tr>");
System.out.println(clmname+"\t"+clmtype+"("+clmsize+")\t"+strNullable+"\t"+
increase);
}
out.println("</table>");
//out.println("<h1>tableName:"+ tableName +"</h1>" );
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// TODO Auto-generated method stub
}
}
java 读取表结构 mysql
5星 · 超过95%的资源 需积分: 48 90 浏览量
2010-09-19
13:20:33
上传
评论 3
收藏 496KB RAR 举报 
DBInfoExport.rar （15个子文件） 
DBInfoExport src com daing gzb 
LinkTest.java 3KB DBInfoExport.java 7KB util JdbcUtil.java 2KB dbinfo.prop 136B .project 1KB WebRoot META-INF MANIFEST.MF 39B WEB-INF web.xml 639B classes com daing gzb DBInfoExport.class 7KB LinkTest.class 5KB util JdbcUtil.class 2KB dbinfo.prop 136B lib index.jsp 912B public 
mysql-connector-java-5.0.5-bin.jar 501KB .mymetadata 309B .classpath 461B
- 1
- 2
- 3
- 4
前往页