获取数据库用户表格信息
package com.augurit.codebulder.builder.bpo.imp;
import com.augurit.codebulder.util.db.imp.ConnectionManagerImpl;
import java.sql.Connection;
import java.util.*;
import java.sql.*;
import com.augurit.codebulder.builder.bean.Field;
import com.augurit.codebulder.builder.bpo.inf.InfGetDBData;
public class ImpGetDBData extends ConnectionManagerImpl implements InfGetDBData {
/**
* 获得用户表集合
* @return List 用户表集合
* @throws Exception
*/
public List getTableList() throws Exception {
List list = new ArrayList();
Connection connection = null;
PreparedStatement prestat = null;
try {
connection = this.getConnection();
String sql = "select table_name from sys.user_tables t";
prestat = connection.prepareStatement(sql);
ResultSet rs = prestat.executeQuery();
while (rs.next()) {
Map map = new HashMap();
String tablename = rs.getString("TABLE_NAME");
if (tablename.indexOf("==") != -1 ||
tablename.indexOf("$0") != -1) {
continue;
}
map.put("name", tablename);
map.put("comment", this.getTableComment(connection, tablename));
map.put("comment", "");
list.add(map);
// System.out.println(rs.getString("TABLE_NAME"));
}
rs.close();
prestat.close();
} catch (Exception ex) {
throw new Exception(ex.getMessage());
} finally {
if (connection != null) {
connection.close();
connection = null;
}
}
return list;
}
/**
* 获得字段信息集合
* @return List
* @throws Exception
*/
public List getFieldList(String tablename) throws Exception {
int showNum = 5;
List list = null;
Connection connection = null;
PreparedStatement prestat = null;
try {
connection = this.getConnection();
Map mappk = this.getTablePK(connection, tablename);
Map mapcomment = this.getFieldComment(connection, tablename);
String sql = "select * from " + tablename;
prestat = connection.prepareStatement(sql);
ResultSet rs = prestat.executeQuery();
ResultSetMetaData ms = rs.getMetaData();
int count = ms.getColumnCount();
list = new ArrayList(count);
for (int i = 1; i < count + 1; i++) {
Field field = new Field();
field.setFieldName(ms.getColumnName(i).toLowerCase());
field.setFieldType(ms.getColumnType(i));
field.setTypeName(ms.getColumnTypeName(i));
boolean key = false;
if (mappk.get(ms.getColumnName(i)) != null) {
key = true;
}
field.setKey(key);
boolean empty = false;
int temp = ms.isNullable(i);
if (temp == ResultSetMetaData.columnNullable) {
empty = true;
}
field.setEmpty(empty);
field.setSize(ms.getColumnDisplaySize(i));
field.setPrecision(ms.getPrecision(i));
field.setScale(ms.getScale(i));
field.setComment((String) mapcomment.get(ms.getColumnName(i)));
if (i <= showNum) {
field.setIsView(true);
} else {
field.setIsView(false);
}
list.add(i - 1, field);
// System.out.println(field);
}
rs.close();
prestat.close();
} catch (SQLException ex) {
throw new Exception(ex.getMessage());
} finally {
if (connection != null) {
connection.close();
connection = null;
}
}
return list;
}
/**
* 获得表主键集合
* @param connection Connection
* @param tablename String
* @return Map
* @throws Exception
*/
private Map getTablePK(Connection connection, String tablename) throws
Exception {
Map map = new HashMap();
try {
DatabaseMetaData dbMeta = connection.getMetaData();
ResultSet pkRSet = dbMeta.getPrimaryKeys(null, null,
tablename.toUpperCase());
while (pkRSet.next()) {
map.put(pkRSet.getObject(4).toString(), "PK");
}
pkRSet.close();
} catch (Exception ex) {
throw new Exception(ex.getMessage());
}
return map;
}
/**
* 获得指定表的描述信息集合
* @param connection Connection
* @param tablename String
* @return String
* @throws Exception
*/
private String getTableComment(Connection connection, String tablename) throws
Exception {
PreparedStatement prestat = null;
String tablecomment = "";
try {
String sql =
"select comments from sys.user_tab_comments t where table_name = '" +
tablename.toUpperCase() + "'";
prestat = connection.prepareStatement(sql);
ResultSet rs = prestat.executeQuery();
rs.next();
tablecomment = rs.getString("comments");
tablecomment = (tablecomment == null) ? "" : tablecomment;
rs.close();
prestat.close();
} catch (SQLException ex) {
throw new Exception(ex.getMessage());
}
return tablecomment;
}
/**
* 获得表字段的描述信息集合
* @param connection Connection
* @param tablename String
* @return Map
* @throws Exception
*/
private Map getFieldComment(Connection connection, String tablename) throws
Exception {
Map map = new HashMap();
PreparedStatement prestat = null;
try {
String sql =
"select column_name,comments from sys.user_col_comments t where table_name = '" +
tablename.toUpperCase() + "'";
prestat = connection.prepareStatement(sql);
ResultSet rs = prestat.executeQuery();
while (rs.next()) {
map.put(rs.getString("column_name"), rs.getString("comments"));
}
rs.close();
prestat.close();
} catch (SQLException ex) {
throw new Exception(ex.getMessage());
}
return map;
}
public static void main(String[] args) {
ImpGetDBData test = new ImpGetDBData();
try {
// test.getFieldList("person");
// test.getTableList();
System.out.println(java.sql.Types.NUMERIC);
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
1 楼 chenm_bj 2008-06-04 高手,能有适用多种数据库的方法吗? 如获得表字段的描述信息集合getFieldComment目前只适用于Oracle.
