企業的生產環境中，如果不允許直接操作數據表中的數據，則需要開發一個前臺界面，在必要時實現對多個數據表中數據的增刪改查， 此時就需要后端將Oracle表字段及數據查詢返回前端動態展示……

一、Oracle特定元數據查詢

1. 使用JDBC獲取Oracle表字段信息

public List<String> getOracleTableColumns(String tableName) throws SQLException {List<String> columns = new ArrayList<>();try (Connection conn = dataSource.getConnection()) {// Oracle元數據查詢DatabaseMetaData metaData = conn.getMetaData();// Oracle注意: 表名需要大寫ResultSet rs = metaData.getColumns(null, conn.getSchema(), tableName.toUpperCase(), null);while (rs.next()) {columns.add(rs.getString("COLUMN_NAME"));}}return columns;
}

2. 獲取Oracle字段詳細信息（含數據類型）

public List<ColumnMeta> getOracleTableMetaData(String tableName) throws SQLException {List<ColumnMeta> columns = new ArrayList<>();try (Connection conn = dataSource.getConnection()) {DatabaseMetaData metaData = conn.getMetaData();ResultSet rs = metaData.getColumns(null, conn.getSchema(), tableName.toUpperCase(), null);while (rs.next()) {ColumnMeta meta = new ColumnMeta();meta.setColumnName(rs.getString("COLUMN_NAME"));meta.setDataType(rs.getString("TYPE_NAME"));meta.setColumnSize(rs.getInt("COLUMN_SIZE"));meta.setNullable("YES".equals(rs.getString("IS_NULLABLE")));meta.setRemarks(rs.getString("REMARKS")); // 列注釋columns.add(meta);}}return columns;
}@Data
class ColumnMeta {private String columnName;private String dataType;private int columnSize;private boolean nullable;private String remarks;
}

二、Oracle表數據查詢方案

1. 通用數據查詢DTO

@Data
public class OracleTableDataDTO {private String tableName;private List<ColumnMeta> columnMetas;private List<Map<String, Object>> rows;private Pagination pagination; // 分頁信息
}@Data
class Pagination {private int total;private int page;private int pageSize;
}

2. 使用JDBC查詢Oracle表數據

public OracleTableDataDTO getOracleTableData(String tableName, int page, int pageSize) {OracleTableDataDTO dto = new OracleTableDataDTO();dto.setTableName(tableName);try (Connection conn = dataSource.getConnection()) {// 1. 獲取列元數據List<ColumnMeta> columns = getOracleTableMetaData(tableName);dto.setColumnMetas(columns);// 2. 構建分頁SQL (Oracle分頁語法)String sql = "SELECT * FROM (SELECT a.*, ROWNUM rn FROM (" +"SELECT * FROM " + tableName + ") a WHERE ROWNUM <= ?) WHERE rn > ?";// 3. 計算分頁參數int start = (page - 1) * pageSize;int end = page * pageSize;// 4. 執行查詢List<Map<String, Object>> rows = new ArrayList<>();try (PreparedStatement stmt = conn.prepareStatement(sql)) {stmt.setInt(1, end);stmt.setInt(2, start);ResultSet rs = stmt.executeQuery();ResultSetMetaData rsMeta = rs.getMetaData();while (rs.next()) {Map<String, Object> row = new LinkedHashMap<>();for (int i = 1; i <= rsMeta.getColumnCount(); i++) {String colName = rsMeta.getColumnName(i);Object value = rs.getObject(i);// 處理Oracle特定類型if (value instanceof oracle.sql.TIMESTAMP) {value = ((oracle.sql.TIMESTAMP)value).timestampValue();} else if (value instanceof oracle.sql.CLOB) {value = ((oracle.sql.CLOB)value).getSubString(1, (int)((oracle.sql.CLOB)value).length());}row.put(colName, value);}rows.add(row);}}dto.setRows(rows);// 5. 獲取總記錄數int total = getOracleTableCount(conn, tableName);dto.setPagination(new Pagination(total, page, pageSize));} catch (SQLException e) {throw new RuntimeException("查詢Oracle表數據失敗", e);}return dto;
}private int getOracleTableCount(Connection conn, String tableName) throws SQLException {String sql = "SELECT COUNT(*) FROM " + tableName;try (PreparedStatement stmt = conn.prepareStatement(sql);ResultSet rs = stmt.executeQuery()) {return rs.next() ? rs.getInt(1) : 0;}
}

三、Spring Boot控制器實現
RESTful API接口

@RestController
@RequestMapping("/api/oracle")
public class OracleTableController {@Autowiredprivate OracleTableService tableService;@GetMapping("/{tableName}/metadata")public ResponseEntity<List<ColumnMeta>> getTableMetadata(@PathVariable String tableName) {try {return ResponseEntity.ok(tableService.getOracleTableMetaData(tableName));} catch (SQLException e) {return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build();}}@GetMapping("/{tableName}/data")public ResponseEntity<OracleTableDataDTO> getTableData(@PathVariable String tableName,@RequestParam(defaultValue = "1") int page,@RequestParam(defaultValue = "10") int pageSize) {// 表名安全檢查if (!isValidTableName(tableName)) {return ResponseEntity.badRequest().build();}return ResponseEntity.ok(tableService.getOracleTableData(tableName, page, pageSize));}private boolean isValidTableName(String tableName) {// 實現表名白名單驗證return tableName.matches("[A-Za-z0-9_]+");}
}

四、前端展示方案
Vue.js動態表格組件

<template><div><h2>{{ tableName }} 表數據</h2><!-- 字段元數據展示 --><div class="metadata" v-if="columnMetas.length"><h3>表結構</h3><table class="meta-table"><thead><tr><th>列名</th><th>類型</th><th>長度</th><th>可空</th><th>注釋</th></tr></thead><tbody><tr v-for="col in columnMetas" :key="col.columnName"><td>{{ col.columnName }}</td><td>{{ col.dataType }}</td><td>{{ col.columnSize }}</td><td>{{ col.nullable ? '是' : '否' }}</td><td>{{ col.remarks || '-' }}</td></tr></tbody></table></div><!-- 數據展示 --><div class="data-table"><h3>表數據 (共 {{ pagination.total }} 條)</h3><table><thead><tr><th v-for="col in columnMetas" :key="col.columnName">{{ col.columnName }}</th></tr></thead><tbody><tr v-for="(row, index) in rows" :key="index"><td v-for="col in columnMetas" :key="col.columnName">{{ formatValue(row[col.columnName]) }}</td></tr></tbody></table><!-- 分頁控件 --><div class="pagination"><button @click="prevPage" :disabled="pagination.page <= 1">上一頁</button><span>第 {{ pagination.page }} 頁 / 共 {{ totalPages }} 頁</span><button @click="nextPage" :disabled="pagination.page >= totalPages">下一頁</button></div></div></div>
</template><script>
export default {data() {return {tableName: '',columnMetas: [],rows: [],pagination: {total: 0,page: 1,pageSize: 10}};},computed: {totalPages() {return Math.ceil(this.pagination.total / this.pagination.pageSize);}},methods: {async loadTableData() {try {// 加載元數據const metaRes = await this.$axios.get(`/api/oracle/${this.tableName}/metadata`);this.columnMetas = metaRes.data;// 加載數據const dataRes = await this.$axios.get(`/api/oracle/${this.tableName}/data`, {params: {page: this.pagination.page,pageSize: this.pagination.pageSize}});const data = dataRes.data;this.rows = data.rows;this.pagination = data.pagination;} catch (error) {console.error('加載表數據失敗:', error);}},formatValue(value) {if (value === null || value === undefined) return 'NULL';if (value instanceof Object) return JSON.stringify(value);return value;},prevPage() {if (this.pagination.page > 1) {this.pagination.page--;this.loadTableData();}},nextPage() {if (this.pagination.page < this.totalPages) {this.pagination.page++;this.loadTableData();}}},mounted() {this.tableName = this.$route.params.tableName;this.loadTableData();}
};
</script><style scoped>
/* 添加適當的表格樣式 */
table {width: 100%;border-collapse: collapse;
}
th, td {border: 1px solid #ddd;padding: 8px;
}
th {background-color: #f2f2f2;
}
.meta-table {margin-bottom: 20px;
}
.pagination {margin-top: 15px;
}
</style>

五、Oracle特定處理注意事項
數據類型處理：

// 特殊處理Oracle類型
if (value instanceof oracle.sql.TIMESTAMP) {value = ((oracle.sql.TIMESTAMP)value).timestampValue();
}
if (value instanceof oracle.sql.BLOB) {value = "[BLOB]";
}
if (value instanceof oracle.sql.CLOB) {value = ((oracle.sql.CLOB)value).getSubString(1, 1000); // 限制CLOB讀取長度
}

表名大小寫問題：

// Oracle默認表名是大寫的
String normalizedTableName = tableName.toUpperCase();

性能優化：

// 使用Oracle的ROWID快速分頁
String sql = "SELECT * FROM " + tableName + " WHERE ROWID IN (SELECT RID FROM (" +"   SELECT ROWID AS RID, ROWNUM AS RN FROM " + tableName +"   WHERE ROWNUM <= ?) WHERE RN > ?)";

長文本處理：

// 對于CLOB字段，限制讀取長度
if ("CLOB".equals(columnType)) {stmt.setCharacterStream(i, reader, 1024); // 限制讀取1K字符
}

六、安全增強措施
SQL注入防護：

// 表名白名單驗證
private static final Set<String> ALLOWED_TABLES = Set.of("EMPLOYEES", "DEPARTMENTS");public void validateTableName(String tableName) {if (!ALLOWED_TABLES.contains(tableName.toUpperCase())) {throw new IllegalArgumentException("不允許訪問該表");}
}

敏感字段過濾：

// 過濾掉密碼等敏感字段
columns.removeIf(col -> SENSITIVE_COLUMNS.contains(col.toUpperCase()));

數據脫敏：

// 對敏感數據進行脫敏處理
if (columnName.toUpperCase().contains("PASSWORD")) {row.put(columnName, "******");
}