【Java/JDBC】利用Metadata获取Oracle表字段的注释

前篇：

代码：

package com.hy.lab.colcomment;

import java.sql.*;
import java.util.Properties;

public class Test {
    /**
     * 取注释的连接有其特殊性
     *
     * @return
     */
    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
            Properties pro = new Properties();
            pro.setProperty("user", "luna");
            pro.setProperty("password", "1234");
            pro.put("remarksReporting", "true");// 注意这里不可或缺

            conn = DriverManager.getConnection(url, pro);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static void main(String[] args) {
        try (Connection conn = getConnection()) {
            DatabaseMetaData dbmd = conn.getMetaData();
            ResultSet rs = dbmd.getColumns(null, "%", "emp524".toUpperCase(), "%");

            while (rs.next()) {
                String colName = rs.getString("COLUMN_NAME");
                String colRemarks = rs.getString("REMARKS");
                String pattern = "Column name:%s & remarks:%s";
                System.out.println(String.format(pattern, colName, colRemarks));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

输出：

Column name:ID & remarks:ID
Column name:NAME & remarks:姓名
Column name:AGE & remarks:年龄

建表充值添加注释的SQL：

create table emp524(
    id number(12),
    name nvarchar2(20),
    age number(3),
    primary key(id)
);


COMMENT ON TABLE emp524 IS '雇员表';
COMMENT ON COLUMN emp524.id IS 'ID';
COMMENT ON COLUMN emp524.name IS '姓名';
COMMENT ON COLUMN emp524.age IS '年龄';

insert into emp524(id,name) values ('1','andy');

END