提取数据库中表的数据字典

1、下载ojdbc6-11.2.0.1.jar
2、下载poi-3.2-FINAL.jar
3、测试代码如下：

public class DataDictionary {

    public static Connection getConnection() {

        Connection connection = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:orcl", "scott", "tiger");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void main(String[] args) throws SQLException {

        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = null;
            Connection connection = getConnection();
            Statement stmt = connection.createStatement();
            StringBuffer strbuf = new StringBuffer();
            strbuf.append("SELECT A.*,B.comments");
            strbuf.append(" FROM all_tab_columns A,DBA_COL_COMMENTS B");
            strbuf.append(" WHERE A.owner=B.owner");
            strbuf.append(" AND A.table_name=B.table_name");
            strbuf.append(" AND A.COLUMN_NAME=B.COLUMN_NAME");
            strbuf.append(" AND A.owner='SCOTT'");
            strbuf.append(" ORDER BY A.TABLE_NAME, A.COLUMN_ID");
            ResultSet rs = stmt.executeQuery(strbuf.toString());

            String tb = "";
            int k = 0;
            while (rs.next()) {
                if (!tb.equals(rs.getString("TABLE_NAME"))) {
                    sheet = wb.createSheet(rs.getString("TABLE_NAME"));
                    HSSFRow row = sheet.createRow(0);
                    setCellGBKValue(row.createCell(0), "COLUMN_NAME");
                    setCellGBKValue(row.createCell(1), "DATA_TYPE");
                    setCellGBKValue(row.createCell(2), "NULLABLE");
                    setCellGBKValue(row.createCell(3), "COMMENTS");
                    k = 1;
                } else {
                    k++;
                }
                tb = rs.getString("TABLE_NAME");
                HSSFRow row = sheet.createRow(k);
                setCellGBKValue(row.createCell(0), rs.getString("COLUMN_NAME"));
                String str = rs.getString("DATA_TYPE");
                StringBuilder sb = new StringBuilder();
                sb.append(str);
                if ("NUMBER".equals(str)) {
                    String precision = rs.getString("DATA_PRECISION");
                    if (precision != null) {
                        sb.append("(" + precision);
                    }
                    String scale = rs.getString("DATA_SCALE");
                    if (scale != null && !"0".equals(scale)) {
                        sb.append(", " + rs.getString("DATA_SCALE"));
                    }
                    if (precision != null) {
                        sb.append(")");
                    }
                }
                if ("VARCHAR2".endsWith(str)) {
                    sb.append("(" + rs.getString("DATA_LENGTH") + ")");
                }

                setCellGBKValue(row.createCell(1), sb.toString());
                setCellGBKValue(row.createCell(2), rs.getString("NULLABLE"));
                setCellGBKValue(row.createCell(3), rs.getString("COMMENTS"));
                sheet.autoSizeColumn((short) 0);
                sheet.autoSizeColumn((short) 1);
                sheet.autoSizeColumn((short) 2);
                sheet.autoSizeColumn((short) 3);
            }

            FileOutputStream fileOut = new FileOutputStream("D:\\数据字典.xls");
            wb.write(fileOut);
            fileOut.close();
            rs.close();
            stmt.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void setCellGBKValue(HSSFCell cell, String value) {
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        HSSFRichTextString hssfRichTextString = new HSSFRichTextString(value);
        cell.setCellValue(hssfRichTextString);
    }
}