用jsp导出mysql库表的结构说明

我又来csdn 发表论文了 :)

有的时候，需要写验收文档，可能没有sqlyog,或者navicat一类的工具，来导出项目中的mysql库结构说明，那么其实字写一个jsp，也能达到很好效果。

当然用sqlYog的“创建数据库架构的HTML”，效果也很好。

 

<%@ page contentType="text/html; charset=utf8" %>

<%
	
	response.setHeader("Cache-Control","no-store");
		
	response.setHeader("Pragrma","no-cache");

	response.setDateHeader("Expires",0);

try
{


    Class.forName("com.mysql.jdbc.Driver");
	String url="jdbc:mysql://test.youcomapny.com:3309/dbxxx?useUnicode=true&characterEncoding=utf-8&characterSetResults=UTF-8&zeroDateTimeBehavior=convertToNull";
	String user="mysqluser";
	String password="23232";

	Connection conn1= DriverManager.getConnection(url,user,password);

	Statement stmt=conn1.createStatement();
    
    ResultSet rs = stmt.executeQuery("show table");

    while(rs.next())
    {
        //*****************************
                String tableName = rs.getString(1);
                String sql2 = "SHOW FULL COLUMNS FROM  " + tableName;
                Statement stmt2=conn1.createStatement();
                ResultSet rs2 = stmt2.executeQuery(sql2);
                ResultSetMetaData rsmd2 = rs2.getMetaData();
                int numberOfColumns2 = rsmd2.getColumnCount();
                

                out.println("<br>表名称:" + tableName + "<table border=\"1\">");
                out.println("<tr bgcolor=green>");

                for(int i=1;i<=numberOfColumns2;i++){
                    String columnNameEnglish = rsmd2.getColumnName(i);
                    String columnNameChinese = "";
                    if( columnNameEnglish.equals("COLUMN_NAME")){
                        columnNameChinese = "列名";
                        	// 列类型	字符集	是否为空	列的键值	列的缺省值	附加说明	可执行的权限	列的用途
                            //COLUMN_NAME	COLUMN_TYPE	COLLATION_NAME	IS_NULLABLE	COLUMN_KEY	COLUMN_DEFAULT	EXTRA	PRIVILEGES	COLUMN_COMMENT
                    }else if(columnNameEnglish.equals("COLUMN_TYPE")){
                        columnNameChinese = "列类型";
                    }else if(columnNameEnglish.equals("COLLATION_NAME")){
                        columnNameChinese = "字符集";
                    }else if(columnNameEnglish.equals("IS_NULLABLE")){
                        columnNameChinese = "是否为空";
                    }else if(columnNameEnglish.equals("COLUMN_KEY")){
                        columnNameChinese = "列的键";
                    }else if(columnNameEnglish.equals("COLUMN_DEFAULT")){
                        columnNameChinese = "列的缺省值";
                    }else if(columnNameEnglish.equals("EXTRA")){
                        columnNameChinese = "附加说明";
                    }else if(columnNameEnglish.equals("PRIVILEGES")){
                        columnNameChinese = "可执行的权限";
                    }else if(columnNameEnglish.equals("COLUMN_COMMENT")){
                        columnNameChinese = "列的用途";
                    }
                    out.println("<td style=\"color:white\">" + columnNameChinese  + "</td>");
                }

                out.println("</tr>");

                while(rs2.next())
                {
                    out.println("<tr>");
                    for(int i=1;i<=numberOfColumns2;i++){
                        out.println("<td>" +  rs2.getString(i) + "</td>");
                    }
                    out.println("</tr>");
                }
                out.println("</table>");
        
                rs2.close();
        //*****************************
    }    


    rs.close();
    stmt.close();
    conn1.close();
}
catch(Exception e)
{
    out.println("error when query"+ e.getMessage());
    e.printStackTrace();
    System.out.println("************ONE MORE TIME");
}
%>