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 T1
         LEFT 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_NAME
         LEFT JOIN (SELECT CONS.CONSTRAINT_NAME, CONS.TABLE_NAME, COLUS.COLUMN_NAME
                    FROM USER_CONSTRAINTS CONS
                             INNER JOIN USER_CONS_COLUMNS COLUS ON CONS.CONSTRAINT_NAME = COLUS.CONSTRAINT_NAME
                    WHERE CONS.CONSTRAINT_TYPE = 'P') T4
                   ON 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 T1
         LEFT 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 = ?