【JDBC】使用properties连Oracle数据库，使用DatabaseMetaData获取字段的注释

简单的打铁代码如下：

package com.hy.propertyConn;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.apache.log4j.Logger;

import com.hy.DBParam;

class DbParam{
    public final static String Driver = "oracle.jdbc.driver.OracleDriver";
    public final static String DbUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    public final static String User = "ufo";
    public final static String Pswd = "1234";
}

class KeyValue implements Comparable<KeyValue>{
    String key;
    Object value;
    
    @Override
    public int compareTo(KeyValue another) {
        return this.key.compareTo(another.key);
    }
}
public class Fetcher {
    private static Logger log = Logger.getLogger(Fetcher.class);
    
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        
        try{
            Class.forName(DBParam.Driver).newInstance();
            Properties pro = new Properties();
            //pro.setProperty("initialSize", "10");
            //pro.setProperty("maxActive", "12");
            pro.setProperty("user", DBParam.User);// 这里不是username或是usr！
            pro.setProperty("password", DBParam.Pswd);// 这里不是pswd
            pro.put("remarksReporting","true");// 这一句才能让rs.getString("REMARKS")起作用
            
            conn = DriverManager.getConnection(DBParam.DbUrl, pro);
            
            stmt = conn.createStatement();
            
            String sql="select * from testtb17 where id=114 ";
            
            ResultSet rs = stmt.executeQuery(sql);
            ResultSetMetaData rsMetadata = rs.getMetaData();
            int count = rsMetadata.getColumnCount();
            Map<String,String> nameCmtMap=getColumnCommentMap(conn,"testtb17".toUpperCase());
            
            while (rs.next()) {
                List<KeyValue> kvList=new ArrayList<KeyValue>();
                
                for (int i=0; i<count; i++) {
                    int idx=i+1;
                    
                    KeyValue kv=new KeyValue();
                    kv.key = rsMetadata.getColumnLabel(idx);// 列名
                    kv.value=rs.getString(kv.key);
                    kvList.add(kv);
                }
                
                Collections.sort(kvList);
                
                System.out.println();
                StringBuilder sb=new StringBuilder();
                int idx=0;
                for(KeyValue kv:kvList) {
                    idx++;
                    sb.append(fixSizeStr("#"+idx,4)+fixSizeStr(kv.key,15)+fixSizeStr(kv.value.toString(),20)+fixSizeStr(nameCmtMap.get(kv.key),15)+"\n");
                }
                
                System.out.println(sb.toString());
            }
        } catch (Exception e) {
            System.out.print(e.getMessage());
            e.printStackTrace();
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                log.error("Can't close stmt/conn because of " + e.getMessage());
            }
        }
    }
    
    /**
     * 取某表的字段及注释信息
     * @param conn
     * @param table
     * @return
     * @throws SQLException
     */
    private static Map<String,String> getColumnCommentMap(Connection conn,String table) throws SQLException {
        Map<String,String> map=new LinkedHashMap<String,String>();
        
        DatabaseMetaData dbmd = conn.getMetaData();
        
        ResultSet rs = dbmd.getColumns(null,"%",table.toUpperCase(),"%");// 注意要让表名大写

        while(rs.next()) {
            //System.out.println(rs.getString("COLUMN_NAME")+":"+ rs.getString("REMARKS")); 
            map.put(rs.getString("COLUMN_NAME"), rs.getString("REMARKS"));
        }
        
        return map;
    }
    
    /**
     * get a fix-length string,if less add space （对中文有误）
     * @param str
     * @param size
     * @return
     */
    private static String fixSizeStr(String str, int size) {
        return String.format("%-"+size+"s", str);
    }
}

输出：

#1  AGE            0                   年龄             
#2  CREATEDTIME    2019-06-16 10:21:46 创建时间           
#3  ID             114                 唯一标识           
#4  NAME           0                   名字             

这段代码有时能起到一点作用。

--END-- 2019年12月5日20:58:05

参考网文:https://www.cnblogs.com/discuss/articles/1866940.html