【Java】在一个前台界面中动态展示多个数据表的字段及数据

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