企业的生产环境中，如果不允许直接操作数据表中的数据，则需要开发一个前台界面，在必要时实现对多个数据表中数据的增删改查， 此时就需要后端将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, "******");
}