Oracle Spatial 為關係型數據庫引入原生空間數據類型 MDSYS.SDO_GEOMETRY,支援點、線、面、集合等幾何對象及空間索引。隨著 JDBC 驅動更新 (適用 8.0+),新版驅動大幅簡化了空間對象的 Java 端構建與持久化流程。本文提供完整的 Maven 依賴配置、實體類設計、DAO 封裝與批量插入最佳實踐。
<dependencies>
<!-- Oracle JDBC 驅動 (19c/21c 建議使用 ojdbc10/11) -->
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc11</artifactId>
<version>23.4.0.0</version>
</dependency>
<!-- Spatial/Java API (含 JGeometry) -->
<dependency>
<groupId>com.oracle.database.spatial</groupId>
<artifactId>sdoapi</artifactId>
<version>23.4.0.0</version>
</dependency>
<!-- 幾何運算擴展 (可選) -->
<dependency>
<groupId>com.oracle.database.spatial</groupId>
<artifactId>sdoutil</artifactId>
<version>23.4.0.0</version>
</dependency>
</dependencies>
-- 建立空間表
CREATE TABLE pois (
id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR2(256) NOT NULL,
geom MDSYS.SDO_GEOMETRY NOT NULL,
category VARCHAR2(50),
created_at TIMESTAMP DEFAULT SYSTIMESTAMP
);
-- 插入空間元數據 (SRID 8307 = WGS84 經緯度)
INSERT INTO user_sdo_geom_metadata (table_name, column_name, diminfo, srid)
VALUES ('POIS', 'GEOM',
SDO_DIM_ARRAY(
SDO_DIM_ELEMENT('X', -180, 180, 0.5), -- 經度
SDO_DIM_ELEMENT('Y', -90, 90, 0.5) -- 緯度
), 8307);
-- 建立空間索引 (R-Tree)
CREATE INDEX pois_geom_idx ON pois(geom)
INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2
PARAMETERS ('LAYER_GTYPE=POINT SDO_INDX_DIMS=2');
import oracle.spatial.geometry.JGeometry;
import oracle.sql.STRUCT;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class Poi {
private Long id;
private String name;
private JGeometry geom; // 核心空間對象
private String category;
// Getter/Setter/Builder 省略
}
public class PoiDao {
private final DataSource dataSource;
public PoiDao(DataSource dataSource) { this.dataSource = dataSource; }
/** 插入單條 POI (點幾何) */
public Long insertPoint(Poi poi) throws SQLException {
String sql = "INSERT INTO pois (name, geom, category) VALUES (?, ?, ?)";
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql, new String[]{"id"})) {
// 構建 JGeometry 點 (X=經度, Y=緯度, SRID=8307)
JGeometry jGeom = new JGeometry(
poi.getGeom().getPoint()[0], // X
poi.getGeom().getPoint()[1], // Y
8307 // SRID
);
// 轉換為 Oracle STRUCT (Oracle 內部表示)
STRUCT struct = JGeometry.store(jGeom, conn);
ps.setString(1, poi.getName());
ps.setObject(2, struct); // 關鍵:setObject 綁定 STRUCT
ps.setString(3, poi.getCategory());
ps.executeUpdate();
// 獲取自動生成的 ID
try (ResultSet rs = ps.getGeneratedKeys()) {
if (rs.next()) return rs.getLong(1);
}
throw new SQLException("Failed to retrieve generated ID");
}
}
/** 批量插入 (使用 FORALL 等效的批量綁定) */
public int[] batchInsert(List pois) throws SQLException {
String sql = "INSERT INTO pois (name, geom, category) VALUES (?, ?, ?)";
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
conn.setAutoCommit(false);
for (Poi poi : pois) {
JGeometry jGeom = new JGeometry(
poi.getGeom().getPoint()[0],
poi.getGeom().getPoint()[1],
8307
);
STRUCT struct = JGeometry.store(jGeom, conn);
ps.setString(1, poi.getName());
ps.setObject(2, struct);
ps.setString(3, poi.getCategory());
ps.addBatch();
}
int[] results = ps.executeBatch();
conn.commit();
return results;
}
}
/** 空間範圍查詢示例:矩形窗口查詢 */
public List queryByWindow(double minX, double minY, double maxX, double maxY) throws SQLException {
String sql = """
SELECT id, name, geom, category
FROM pois
WHERE SDO_INSIDE(geom, SDO_GEOMETRY(2003, 8307, NULL,
SDO_ELEM_INFO_ARRAY(1,1003,3),
SDO_ORDINATE_ARRAY(?, ?, ?, ?)
)) = 'TRUE'
""";
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setDouble(1, minX);
ps.setDouble(2, minY);
ps.setDouble(3, maxX);
ps.setDouble(4, maxY);
List result = new ArrayList<>();
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
Poi poi = new Poi();
poi.setId(rs.getLong("id"));
poi.setName(rs.getString("name"));
poi.setCategory(rs.getString("category"));
// 從 STRUCT 還原 JGeometry
STRUCT struct = (STRUCT) rs.getObject("geom");
poi.setGeom(JGeometry.load(struct));
result.add(poi);
}
}
return result;
}
}
}
| 技術點 | 舊驅動痛點 | 新驅動 (10g+/19c+) 改進 |
|---|---|---|
| STRUCT 創建 | 手動構造 STRUCT 描述符、屬性數組 | JGeometry.store(jGeom, conn) 一鍵轉換 |
| 幾何類型支援 | 僅支援基本點/線/面 | 完整支援 3D、LRS、曲線、多重幾何、語義網絡 |
| 字符集處理 | NLS_LANG 環境變量敏感 | 驅動內部統一 UTF-16/UTF-8,無需客戶端配置 |
| 批量操作 | 需手動拼接 SQL 或使用舊式 OraclePreparedStatement | 標準 JDBC addBatch() 完美支援 STRUCT |
| 連接池兼容 | HikariCP/Druid 易發生 STRUCT 反序列化錯誤 | 實現 oracle.jdbc.OracleConnection 標準接口,池化無感知 |
USER_SDO_GEOM_METADATA 是否正確註冊 SRID、維度資訊。JGeometry.load(struct) 而非強制轉型。