importjava.io.IOException;importjava.io.PrintWriter;importjava.sql.Connection;importjava.sql.DatabaseMetaData;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.ResultSetMetaData;importjava.sql.SQLException;importjava.sql.Statement;importjava.util.ArrayList;importjava.util.List;importjavax.servlet.ServletException;importjavax.servlet.http.HttpServlet;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;public class AnalysisDatabaseStructureServlet extendsHttpServlet {private static final long serialVersionUID = 1L;
Connection conn= null;
Statement st= null;publicAnalysisDatabaseStructureServlet() {super();
}//获取conn
public void init() throwsServletException {try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn=java.sql.DriverManager.getConnection("jdbc:mysql://localhost:3306/base", "root", "root");
}catch(InstantiationException e) {
e.printStackTrace();
}catch(IllegalAccessException e) {
e.printStackTrace();
}catch(ClassNotFoundException e) {
e.printStackTrace();
}catch(SQLException e) {
e.printStackTrace();
}
}public voiddoGet(HttpServletRequest request, HttpServletResponse response)throwsServletException, IOException {//1、获取数据库所有表
StringBuffer sbTables = newStringBuffer();
List tables = new ArrayList();
sbTables.append("-------------- 数据库中有下列的表 ----------
");try{
DatabaseMetaData dbMetaData=conn.getMetaData();
ResultSet rs= dbMetaData.getTables(null, null, null,new String[] { "TABLE"});while (rs.next()) {// ///TABLE_TYPE/REMARKS
sbTables.append("表名:" + rs.getString("TABLE_NAME") + "
");
sbTables.append("表类型:" + rs.getString("TABLE_TYPE") + "
");
sbTables.append("表所属数据库:" + rs.getString("TABLE_CAT") + "
");
sbTables.append("表所属用户名:" + rs.getString("TABLE_SCHEM")+ "
");
sbTables.append("表备注:" + rs.getString("REMARKS") + "
");
sbTables.append("------------------------------
");
tables.add(rs.getString("TABLE_NAME"));
}
}catch(SQLException e) {
e.printStackTrace();
}//2、遍历数据库表,获取各表的字段等信息
StringBuffer sbCloumns = newStringBuffer();for(String tableName : tables) {
String sql= "select * from " +tableName;try{
PreparedStatement ps=conn.prepareStatement(sql);
ResultSet rs=ps.executeQuery();
ResultSetMetaData meta=rs.getMetaData();int columeCount =meta.getColumnCount();
sbCloumns.append("表 "+ tableName + "共有 "+columeCount+" 个字段。字段信息如下:
");for (int i = 1; i < columeCount + 1; i++) {
sbCloumns.append("字段名:"+meta.getColumnName(i)+"
");
sbCloumns.append("类型:"+meta.getColumnType(i)+"
");
sbCloumns.append("------------------------------
");
}
}catch(SQLException e) {
e.printStackTrace();
}
sbCloumns.append("------------------------------
");
}
response.setContentType("text/html");
PrintWriter out=response.getWriter();
out.println(""-//W3C//DTD HTML 4.01 Transitional//EN\">");
out.println("");
out.println("
A Servlet");out.println("
");out.println("" +sbTables.toString());
out.println("" +sbCloumns.toString());
out.println(" ");
out.println("");
out.flush();
out.close();
}public voiddoPost(HttpServletRequest request, HttpServletResponse response)throwsServletException, IOException {this.doGet(request, response);
}//释放conn
public voiddestroy() {super.destroy();if (conn != null) {try{
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
}