分类： Java
2008-03-16 09:02:19
利用DatabaseMetaData得到数据库中所有表的信息,利用ResultSetMetaData获取表的结构,并和表中的数据一起输出到客户端.
1, 编写GetDBInfoServlet.java
|
package cn.apache.pl; import java.io.*; import java.sql.*; import java.util.ArrayList; import javax.servlet.*; import javax.servlet.http.*; public class GetDBInfoServlet extends HttpServlet { private String url; private String user; private String password; public void init() throws ServletException { ServletContext sc = getServletContext(); String driverClass = sc.getInitParameter("driverClass"); url = sc.getInitParameter("url"); user = sc.getInitParameter("user"); password = sc.getInitParameter("password"); try { Class.forName(driverClass); }catch(ClassNotFoundException ce) { throw new UnavailableException("数据库驱动加载失败"); } } public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException { Connection conn = null; Statement stmt = null; ResultSet rs = null; try { conn = DriverManager.getConnection(url, user, password); resp.setContentType("text/html; charset=gb2312"); PrintWriter out = resp.getWriter(); out.println(""); out.println(" out.println(""); //调用请求对象的getParameter()方法得到查询的表名 String tableName = req.getParameter("tableName"); //如果表名为空,则执行if块内的代码 if(null == tableName || tableName.equals("")) { //调用连接对象的getMetaData()方法得到DatabaseMetaData对象 DatabaseMetaData dbMeta = conn.getMetaData(); /* *调用DatabaseMetaData对象的getTables()得到,传递"TABLE"参数 *(代码中使用new String[]{"TABLE"})获取数据库中所有表的信息, *返回一个结果集对象 *还可以传递"VIEW",获取数据库中视图的信息 */ rs = dbMeta.getTables(null, null, null, new String[]{"TABLE"}); //输出一个表单 out.println(" //表单中有一个列表框 out.println(" while(rs.next()) { //调用getString("TABLE_NAME")得到表的名字 out.println(" out.println(rs.getString("TABLE_NAME")); out.println(""); } out.println(" "); out.println("提交\">"); out.println(""); } //如果用户提交了表名参数,则执行else块 else { stmt = conn.createStatement(); //查询表中所有字段,返回结果集 rs = stmt.executeQuery("select * from "+tableName); //调用ResultSet对象的getMetaData()方法ResultSetMetaData对象 ResultSetMetaData rsMeta = rs.getMetaData(); //调用ResultSetMetaData对象的getColumnCount()方法得到结果集中列的总数 int columnCount = rsMeta.getColumnCount(); //以表格形式输出表的结构 out.println(" out.println(" out.println(" //构造一个ArrayList集合对象,用于存储表中字段的名字 ArrayList for(int i=1; i<=columnCount; i++) { out.println(" //调用ResultSetMetaData对象的getColumnName()方法得到列的名字 String columnName = rsMeta.getColumnName(i); out.println(" //将列的名字保存到ArrayList集合中 al.add(columnName); /* *调用ResultSetMetaData对象的getColumnTypeName()方法得到列的 *数据库特定的类型名 和 列的最大字符宽度 */ out.println(" out.println(" } out.println(" "); //以表格形式输出表中的数据 out.println(" out.println(" out.println(" //通过for循环取出存储在ArrayList集合中的列名,作为表格的表头 for(int i=0; i { out.println(" } while(rs.next()) { out.println(" for(int i=1; i<=columnCount; i++) { out.println(" } out.println(" } out.println(" } out.println("
"); out.close(); }catch(SQLException se) { se.printStackTrace(); }finally { if(rs != null) { try { rs.close(); }catch(SQLException se) { se.printStackTrace(); } rs = null; } if(stmt != null) { try { stmt.close(); }catch(SQLException se) { se.printStackTrace(); } stmt = null; } if(conn != null) { try { conn.close(); }catch(SQLException se) { se.printStackTrace(); } conn = null; } } } } |
2,部署Servlet:
|
|
3,运行GetDBInfoServlet
启动Tomact服务器,在浏览器中打开:
选择account表后,点”提交”,打开
表的结构
|
字段名 |
字段类型 |
最大字符宽度 |
|
userid |
VARCHAR |
20 |
|
balance |
FLOAT |
6 |
表中的数据
|
userid |
balance |
|
彭磊 |
500.00 |
|
徐璐 |
1000.50 |
再选”bookinfo”, 点”提交”,打开
表的结构
|
字段名 |
字段类型 |
最大字符宽度 |
|
id |
INTEGER |
11 |
|
title |
VARCHAR |
100 |
|
author |
VARCHAR |
100 |
|
bookconcern |
VARCHAR |
200 |
|
publish_date |
DATE |
10 |
|
price |
FLOAT |
4 |
|
amount |
SMALLINT |
6 |
|
remark |
VARCHAR |
400 |
表中的数据
|
id |
title |
author |
bookconcern |
publish_date |
price |
amount |
remark |
|
1 |
JAVA从入门到精通 |
孙卫琴 |
电子工业出版社 |
2004-06-01 |
34.00 |
35 |
null |
|
2 |
JSP应用开发详解 |
刘小华 |
清华大学出版社 |
2005-10-03 |
56.00 |
20 |
null |
|
3 |
MySQL数据库编程 |
孙鑫 |
人民邮电出版社 |
2006-06-29 |
78.00 |
10 |
null |
|
4 |
JAVA 国际认证 |
SCJP |
清华大学出版社 |
2005-02-03 |
46.00 |
55 |
null |
4,测试:
mysql> desc account;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| userid | varchar(10) | NO | PRI | | |
| balance | float(6,2) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
2 rows in set
mysql> select * from account;
+--------+---------+
| userid | balance |
+--------+---------+
| 彭磊 | 500.00 |
| 徐璐 | 1000.50 |
+--------+---------+
2 rows in set
mysql> desc bookinfo;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | | |
| title | varchar(50) | NO | | | |
| author | varchar(50) | NO | | | |
| bookconcern | varchar(100) | NO | | | |
| publish_date | date | NO | | | |
| price | float(4,2) | NO | | | |
| amount | smallint(6) | YES | | NULL | |
| remark | varchar(200) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
8 rows in set
mysql> select * from bookinfo;
+----+------------------+--------+----------------+--------------+-------+--------+------
| id | title | author | bookconcern | publish_date | price | amount | remark
+----+------------------+--------+----------------+--------------+-------+--------+------
| 1 | JAVA从入门到精通 | 孙卫琴 | 电子工业出版社 | 2004-06-01 | 34.00 | 35 | NULL |
| 2 | JSP应用开发详解 | 刘小华 | 清华大学出版社 | 2005-10-03 | 56.00 | 20 | NULL |
| 3 | MySQL数据库编程 | 孙鑫 | 人民邮电出版社 | 2006-06-29 | 78.00 | 10 | NULL |
| 4 | JAVA 国际认证 | SCJP | 清华大学出版社 | 2005-02-03 | 46.00 | 55 | NULL|
+----+------------------+--------+----------------+--------------+-------+--------+------
4 rows in set
仙尘(QQ:584059034)于08年3月15日
