获取数据库用户表格信息

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.