JAVA根据表名获取Oracle表结构信息

响应实体封装

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;/*** @author CQY* @version 1.0* @date 2024/8/15 16:33**/
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class OracleTableInfo {private static final long serialVersionUID = -2767767791747125388L;/*** 序号*/private int columnId;/*** 表英文名称*/private String tableName;/*** 表注释*/private String tableComment;/*** 字段英文名*/private String columnName;/*** 字段中文名称*/private String columnComment;/*** 字段类型*/private String fieldType;/*** java对应字段类型*/private String columnClassName;/*** 主键*/private String primaryKey;/*** 空值验证*/private String allowNull;/*** 备注*/private String remarks;
}

JDBC工具类

import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.StrUtil;
import oracle.jdbc.OracleBfile;
import oracle.jdbc.OracleBlob;
import oracle.jdbc.OracleClob;
import oracle.jdbc.OracleNClob;import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;/*** @author CQY* @version 1.0* @date 2024/8/15 16:38**/
public class JDBCUtils {/*** ORACLE数据类型映射*/public static final Map<String, String> ORACLE_TYPE_MAP = new HashMap<>();static {ORACLE_TYPE_MAP.put("NVARCHAR2", String.class.getName());ORACLE_TYPE_MAP.put("VARCHAR2", String.class.getName());ORACLE_TYPE_MAP.put("CHAR", String.class.getName());ORACLE_TYPE_MAP.put("NCHAR", String.class.getName());ORACLE_TYPE_MAP.put("LONG", String.class.getName());ORACLE_TYPE_MAP.put("NUMBER", BigDecimal.class.getName());ORACLE_TYPE_MAP.put("FLOAT", BigDecimal.class.getName());ORACLE_TYPE_MAP.put("CLOB", OracleClob.class.getName());ORACLE_TYPE_MAP.put("NCLOB", OracleNClob.class.getName());ORACLE_TYPE_MAP.put("BLOB", OracleBlob.class.getName());ORACLE_TYPE_MAP.put("TIMESTAMP", Timestamp.class.getName());ORACLE_TYPE_MAP.put("DATE", Timestamp.class.getName());ORACLE_TYPE_MAP.put("RAW", byte[].class.getName());ORACLE_TYPE_MAP.put("LONG RAW", byte[].class.getName());ORACLE_TYPE_MAP.put("BFILE", OracleBfile.class.getName());}/*** 根据表名获取Oracle表结构信息** @param table    表名* @param url      数据库连接URL* @param user     数据库用户名* @param password 数据库密码* @return 返回表结构信息列表*/@SuppressWarnings("all")public static List<OracleTableInfo> getTableInfo(String table, String url, String user, String password) {List<OracleTableInfo> tableInfoList = new ArrayList<>();Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try {Class.forName("oracle.jabc.driver.OracleDriver");conn = DriverManager.getConnection(url, user, password);String sql = "SELECT T1.TABLE_NAME,\n" +"       NVL(T2.COMMENTS, '')                         AS TAB_COMMENTS,\n" +"       T1.COLUMN_NAME,\n" +"       T3.COMMENTS                                  AS COL_COMMENTS,\n" +"       T1.DATA_TYPE ,\n" +"       T1.NULLABLE,\n" +"       T4.CONSTRAINT_NAME,\n" +"       T1.COLUMN_ID\n" +"FROM USER_TAB_COLUMNS T1\n" +"         LEFT JOIN USER_TAB_COMMENTS T2 ON T1.TABLE_NAME = T2.TABLE_NAME AND T2.TABLE_TYPE = 'TABLE'\n" +"         LEFT JOIN USER_COL_COMMENTS T3 ON T1.TABLE_NAME = T3.TABLE_NAME AND T1.COLUMN_NAME = T3.COLUMN_NAME\n" +"         LEFT JOIN (SELECT CONS.CONSTRAINT_NAME, CONS.TABLE_NAME, COLUS.COLUMN_NAME\n" +"                    FROM USER_CONSTRAINTS CONS\n" +"                             INNER JOIN USER_CONS_COLUMNS COLUS ON CONS.CONSTRAINT_NAME = COLUS.CONSTRAINT_NAME\n" +"                    WHERE CONS.CONSTRAINT_TYPE = 'P') T4\n" +"                   ON T1.TABLE_NAME = T4.TABLE_NAME AND T1.COLUMN_NAME = T4.COLUMN_NAME\n" +"WHERE T1.TABLE_NAME = ? \n" +"ORDER BY T1.COLUMN_ID";ps = conn.prepareStatement(sql);ps.setString(1, table);rs = ps.executeQuery();while (rs.next()) {final String constraintName = StrUtil.nullToEmpty(rs.getString("CONSTRAINT_NAME"));String primaryKey = "No";if (StrUtil.isNotBlank(constraintName)) {primaryKey = StrUtil.format("Yes({})", constraintName);}final String tableName = StrUtil.nullToEmpty(rs.getString("TABLE_NAME")).toUpperCase();final String columnComment = StrUtil.nullToEmpty(rs.getString("COL_COMMENTS"));String nullable = StrUtil.nullToEmpty(rs.getString("NULLABLE"));String dataType = StrUtil.nullToEmpty(rs.getString("DATA_TYPE"));if (dataType.contains("TIMESTAMP")) {dataType = "TIMESTAMP";}final String columnclassName = MapUtil.getStr(ORACLE_TYPE_MAP, dataType, "");final OracleTableInfo tableInfo = OracleTableInfo.builder().columnId(rs.getInt("COLUMN_ID")).tableName(tableName).tableComment(StrUtil.nullToEmpty(rs.getString("TAB_COMMENTS"))).columnName(StrUtil.nullToEmpty(rs.getString("COLUMN_NAME"))).columnComment(columnComment).fieldType(dataType).columnClassName(columnclassName).primaryKey(primaryKey).allowNull(nullable).remarks("").build();tableInfoList.add(tableInfo);}} catch (ClassNotFoundException | SQLException e) {e.printStackTrace();} finally {try {if (rs != null) {rs.close();}if (ps != null) {ps.close();}if (conn != null) {conn.close();}} catch (SQLException e) {e.printStackTrace();}}return tableInfoList;}
}

Oracle 查询SQL

SELECT T1.TABLE_NAME,NVL(T2.COMMENTS, '')  AS TAB_COMMENTS,T1.COLUMN_NAME,T3.COMMENTS AS COL_COMMENTS,T1.DATA_TYPE,T1.NULLABLE,T4.CONSTRAINT_NAME,T1.COLUMN_ID
FROM USER_TAB_COLUMNS T1LEFT JOIN USER_TAB_COMMENTS T2 ON T1.TABLE_NAME = T2.TABLE_NAME AND T2.TABLE_TYPE = 'TABLE'LEFT JOIN USER_COL_COMMENTS T3 ON T1.TABLE_NAME = T3.TABLE_NAME AND T1.COLUMN_NAME = T3.COLUMN_NAMELEFT JOIN (SELECT CONS.CONSTRAINT_NAME, CONS.TABLE_NAME, COLUS.COLUMN_NAMEFROM USER_CONSTRAINTS CONSINNER JOIN USER_CONS_COLUMNS COLUS ON CONS.CONSTRAINT_NAME = COLUS.CONSTRAINT_NAMEWHERE CONS.CONSTRAINT_TYPE = 'P') T4ON T1.TABLE_NAME = T4.TABLE_NAME AND T1.COLUMN_NAME = T4.COLUMN_NAME
WHERE T1.TABLE_NAME = ?
ORDER BY T1.COLUMN_ID

MySQL实现SQL

-- 表结构
SELECT T1.TABLE_NAME,                   -- 表名T2.TABLE_COMMENT,                -- 表的注释T1.COLUMN_NAME,                  -- 列名T1.COLUMN_COMMENT,               -- 列的注释T1.DATA_TYPE,                    -- 数据类型T1.IS_NULLABLE,                  -- 是否允许为空T1.ORDINAL_POSITION AS COLUMN_ID -- 列的顺序位置
FROM INFORMATION_SCHEMA.COLUMNS T1LEFT JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA = T2.TABLE_SCHEMA AND T1.TABLE_NAME = T2.TABLE_NAME
WHERE T1.TABLE_NAME = ? -- 过滤出指定的表
ORDER BY T1.ORDINAL_POSITION  -- 按列的顺序位置排序-- 主外键查询
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
where TABLE_NAME = ?
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
where TABLE_NAME = ?