import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

public class TestDemo {
	public static Connection getConnection() {
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://数据库IP地址:3306/数据库名称";
			String user = "数据库用户名";
			String pass = "数据库用户密码";
			conn = DriverManager.getConnection(url, user, pass);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	public static void main(String[] args) {
		Connection conn = getConnection();
		String sql = "select * from AccessType";
		PreparedStatement stmt;
		try {
			stmt = conn.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery(sql);
			ResultSetMetaData data = rs.getMetaData();
			for (int i = 1; i <= data.getColumnCount(); i++) {
				// 获得所有列的数目及实际列数
				int columnCount = data.getColumnCount();
				// 获得指定列的列名
				String columnName = data.getColumnName(i);
				// 获得指定列的列值
				int columnType = data.getColumnType(i);
				// 获得指定列的数据类型名
				String columnTypeName = data.getColumnTypeName(i);
				// 所在的Catalog名字
				String catalogName = data.getCatalogName(i);
				// 对应数据类型的类
				String columnClassName = data.getColumnClassName(i);
				// 在数据库中类型的最大字符个数
				int columnDisplaySize = data.getColumnDisplaySize(i);
				// 默认的列的标题
				String columnLabel = data.getColumnLabel(i);
				// 获得列的模式
				String schemaName = data.getSchemaName(i);
				// 某列类型的精确度(类型的长度)
				int precision = data.getPrecision(i);
				// 小数点后的位数
				int scale = data.getScale(i);
				// 获取某列对应的表名
				String tableName = data.getTableName(i);
				// 是否自动递增
				boolean isAutoInctement = data.isAutoIncrement(i);
				// 在数据库中是否为货币型
				boolean isCurrency = data.isCurrency(i);
				// 是否为空
				int isNullable = data.isNullable(i);
				// 是否为只读
				boolean isReadOnly = data.isReadOnly(i);
				// 能否出现在where中
				boolean isSearchable = data.isSearchable(i);
				System.out.println(columnCount);
				System.out.println("获得列" + i + "的字段名称:" + columnName);
				System.out.println("获得列" + i + "的类型,返回SqlType中的编号:" + columnType);
				System.out.println("获得列" + i + "的数据类型名:" + columnTypeName);
				System.out.println("获得列" + i + "所在的Catalog名字:" + catalogName);
				System.out.println("获得列" + i + "对应数据类型的类:" + columnClassName);
				System.out.println("获得列" + i + "在数据库中类型的最大字符个数:" + columnDisplaySize);
				System.out.println("获得列" + i + "的默认的列的标题:" + columnLabel);
				System.out.println("获得列" + i + "的模式:" + schemaName);
				System.out.println("获得列" + i + "类型的精确度(类型的长度):" + precision);
				System.out.println("获得列" + i + "小数点后的位数:" + scale);
				System.out.println("获得列" + i + "对应的表名:" + tableName);
				System.out.println("获得列" + i + "是否自动递增:" + isAutoInctement);
				System.out.println("获得列" + i + "在数据库中是否为货币型:" + isCurrency);
				System.out.println("获得列" + i + "是否为空:" + isNullable);
				System.out.println("获得列" + i + "是否为只读:" + isReadOnly);
				System.out.println("获得列" + i + "能否出现在where中:" + isSearchable);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
 
 

 
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
/***
 *  2014-07-21日：
 *  oracle数据库， 表结构查询 ，字段信息查询，字段注释查询 
 *  表字段查询 all_tab_columns
 *  表字段注释查询 all_col_comments
 * 
 */
public class OracleTable {
    private static final String DRIVER_CLASS = "oracle.jdbc.driver.OracleDriver";
    private static final String DATABASE_URL = "jdbc:oracle:thin:@192.168.1.10:1521:orcl";
    private static final String DATABASE_USER = "dev";
    private static final String DATABASE_PASSWORD = "dev";
    private static Connection con = null;
     
    public static Connection getConnection() {
        try {
            Class.forName(DRIVER_CLASS);
            con=DriverManager.getConnection(DATABASE_URL,DATABASE_USER,DATABASE_PASSWORD);
            return con;
        } catch (Exception ex) {
            System.out.println("2:"+ex.getMessage());
        }
        return con;
    }
     
    /***
     * 打印test
     * @throws SQLException
     */
    public static void sysoutStrTablePdmCloumns(String Table,String Owner) throws SQLException{
        getConnection();
         
        List<HashMap<String,String>> columns = new ArrayList<HashMap<String,String>>();
         
        try{
            Statement stmt = con.createStatement();
             
            String sql=
             "select "+
             "         comments as \"Name\","+
             "         a.column_name \"Code\","+
             "         a.DATA_TYPE as \"DataType\","+
             "         b.comments as \"Comment\","+
             "         decode(c.column_name,null,'FALSE','TRUE') as \"Primary\","+
             "         decode(a.NULLABLE,'N','TRUE','Y','FALSE','') as \"Mandatory\","+
             "         '' \"sequence\""+
             "   from "+
             "       all_tab_columns a, "+
             "       all_col_comments b,"+
             "       ("+
             "        select a.constraint_name, a.column_name"+
             "          from user_cons_columns a, user_constraints b"+
             "         where a.constraint_name = b.constraint_name"+
             "               and b.constraint_type = 'P'"+
             "               and a.table_name = '"+Table+"'"+
             "       ) c"+
             "   where "+
             "     a.Table_Name=b.table_Name "+
             "     and a.column_name=b.column_name"+
             "     and a.Table_Name='"+Table+"'"+
             "     and a.owner=b.owner "+
             "     and a.owner='"+Owner+"'"+
             "     and a.COLUMN_NAME = c.column_name(+)" +
             "  order by a.COLUMN_ID";
            System.out.println(sql);
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()){
                HashMap<String,String> map = new HashMap<String,String>();
                map.put("Name", rs.getString("Name"));
                map.put("Code", rs.getString("Code"));
                map.put("DataType", rs.getString("DataType"));
                map.put("Comment", rs.getString("Comment"));
                map.put("Primary", rs.getString("Primary"));
                map.put("Mandatory", rs.getString("Mandatory"));
                columns.add(map);
            }
        }
        catch (SQLException e){
            e.printStackTrace();
        }finally{
            con.close();
        }
    }
    public static void main(String[] args) throws SQLException{
         
        sysoutStrTablePdmCloumns("CT_INFO_CONTRACT_BORROW","DEV");
         
         
    }
     
}
 
 
 
 

SELECT DISTINCT
     * 
 FROM
     (
 SELECT
     comments AS "Name",
     a.column_name "Code",
     a.DATA_TYPE AS "DataType",
     b.comments AS "Comment",
     DECODE( c.column_name, NULL, 'FALSE', 'TRUE' ) AS "Primary",
     DECODE( a.NULLABLE, 'N', 'TRUE', 'Y', 'FALSE', '' ) AS "Mandatory" 
 FROM
     all_tab_columns a,
     all_col_comments b,
     (
 SELECT
     a.constraint_name,
     a.column_name 
 FROM
     user_cons_columns a,
     user_constraints 
 WHERE
     a.constraint_name = b.constraint_name 
     AND b.constraint_type = 'P' 
     AND a.table_name = upper( 'sys_config' ) 
     ) c 
 WHERE
     a.Table_Name = b.table_Name 
     AND a.column_name = b.column_name 
     AND a.Table_Name = upper( 'sys_config' ) 
     AND a.COLUMN_NAME = c.column_name ( + ) 
 ORDER BY
     a.COLUMN_ID 
     )

Oracle 查询表字段和注释
 
声明：Oracle 中查询字段注释和字段时，表名都必须大写
 
---查询表的名称，字段信息以及字段注释

select 
      ut.COLUMN_NAME,--字段名称
      uc.comments,--字段注释
      ut.DATA_TYPE,--字典类型
      ut.DATA_LENGTH,--字典长度
      ut.NULLABLE--是否为空
from user_tab_columns  ut
inner JOIN user_col_comments uc
on ut.TABLE_NAME  = uc.table_name and ut.COLUMN_NAME = uc.column_name
where ut.Table_Name='RC_METADATA' 
order by ut.column_name
-------------执行结果
![这里写图片描述](https://img-blog.csdn.net/20170713170154882?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQveWFsaTE5OTA1MTU=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)

--获取表字段：

select * 
from user_tab_columns 
where Table_Name='RC_METADATA' 
order by column_name

--获取表注释：

select * 
from user_tab_comments 
where Table_Name='RC_METADATA'
order by Table_Name

--获取字段注释：

select * 
from user_col_comments 
where Table_Name='RC_METADATA'
order by column_name


/* 获取表：*/

select table_name from user_tables; --当前用户的表      

select table_name from all_tables; --所有用户的表  

select table_name from dba_tables;--包括系统表

select table_name from dba_tables where owner='SS_MONITOR'

/* 
user_tables：

table_name,tablespace_name,last_analyzed等

dba_tables：

ower,table_name,tablespace_name,last_analyzed等

all_tables：

ower,table_name,tablespace_name,last_analyzed等

all_objects：

ower,object_name,subobject_name,object_id,created,last_ddl_time,timestamp,status等 
*/

/*  获取表字段：*/

select * from user_tab_columns where Table_Name='RC_METADATA';

select * from all_tab_columns where Table_Name='RC_METADATA';

select * from dba_tab_columns where Table_Name='RC_METADATA';

/* user_tab_columns：

table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等

all_tab_columns ：

ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等

dba_tab_columns：

ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等 
*/

/*  获取表注释：*/

select * from user_tab_comments

/* 
user_tab_comments：table_name,table_type,comments

相应的还有dba_tab_comments，all_tab_comments，这两个比user_tab_comments多了ower列。 
*/

/* 获取字段注释：*/

select * from user_col_comments

/*

user_col_comments：table_name,column_name,comments

相应的还有dba_col_comments，all_col_comments，这两个比user_col_comments多了ower列。 
*/