oracle数据库取得列的常用信息

来源：互联网发布：java对象数组编辑：程序博客网时间：2024/04/28 02:14

在开发中，需要取得Oracle数据中某张表的字段定义信息，如列名，数据类型，是否主键，是否可以为空，注释信息等，经过调查有以下两种实现方式：

1.直接用系统视图实现：

SELECT
  COLS.COLUMN_NAME,
  COLS.DATA_TYPE TYPE_NAME,
  COLS.DATA_LENGTH COLUMN_SIZE,
  COLS.NULLABLE,
  CMT.COMMENTS COMMENTS,
  PK.CONSTRAINT_TYPE ISPK
  FROM USER_TAB_COLS COLS
  INNER JOIN USER_COL_COMMENTS CMT
  ON CMT.TABLE_NAME=COLS.TABLE_NAME AND CMT.COLUMN_NAME=COLS.COLUMN_NAME
  LEFT JOIN
  ( SELECT
  CONSTRAINT_TYPE,USER_CONS_COLUMNS.TABLE_NAME,USER_CONS_COLUMNS.COLUMN_NAME
  FROM USER_CONS_COLUMNS
  LEFT JOIN USER_CONSTRAINTS ON USER_CONS_COLUMNS.CONSTRAINT_NAME =
  USER_CONSTRAINTS.CONSTRAINT_NAME
  WHERE CONSTRAINT_TYPE='P') PK
  ON PK.TABLE_NAME = COLS.TABLE_NAME AND PK.COLUMN_NAME = COLS.COLUMN_NAME
  WHERE COLS.TABLE_NAME = #value#

2.用JDBC的MetaData实现：

private DatabaseMetaData metaData = null; public ResultSet getColumnsDefines(String tableName) throws SQLException { if (this.metaData == null) { this.metaData = this.getDataSource().getConnection().getMetaData(); } return this.metaData.getColumns(null, this.metaData.getUserName(), tableName.toUpperCase(), null); } public ResultSet getPKs(String tableName) throws SQLException { if (this.metaData == null) { this.metaData = this.getDataSource().getConnection().getMetaData(); } return this.metaData.getPrimaryKeys(null, null, tableName.toUpperCase()); } private List<ColumnDefine> getDefaultColumns(String tableName) throws SQLException { List<ColumnDefine> newDefinesList = new ArrayList<ColumnDefine>(); ColumnDefine define = null; Set<String> pks = getDefaultPKs(tableName); ResultSet result = this.functionDao.getColumnsDefines(tableName); if (result != null) { while (result.next()) { define = new ColumnDefine(); define.setField(result.getString("COLUMN_NAME")); define.setType(getTypeName(result.getString("TYPE_NAME"), result .getInt("COLUMN_SIZE")));define.setIsPK(pks.contains(define.getField()) ? 1 : 0);... newDefinesList.add(define); } result.close(); } return newDefinesList; } protected Set<String> getDefaultPKs(String tableName) throws SQLException { Set<String> result = new HashSet<String>(); if (tableName != null) { ResultSet rs = this.functionDao.getPKs(tableName); if (rs != null) { while (rs.next()) { result.add(rs.getString("COLUMN_NAME")); } rs.close(); } } return result; }