响应实体封装
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
@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;
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;
public class JDBCUtils {
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());
}
@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 = ?