当 JPA 原生查询遇上 PostgreSQL：一场 null 参数引发的"捉鬼"实录

缘起：一个看似普通的查询

最近在做一个 GIS 相关的项目，需要从 vector_data 表中根据三个可选条件进行组合查询：

dataId（Long，精确匹配）
keyword（String，全文检索）
propFilterStr（String，JSONB 字段包含匹配）

同时支持分页和按创建时间倒序排序。很典型的多条件动态查询，在 Spring Data JPA 中，我习惯性地写了这样一个 @Query：

1
2
3
4
5
6
7


@Query(value = "SELECT * FROM vector_data v " +
       "WHERE v.data_id = :dataId " +
       "AND v.keywords @@ to_tsquery(:keyword) " +
       "AND v.fields @> cast(:propFilterStr as jsonb) " +
       "ORDER BY v.create_time DESC",
       nativeQuery = true)
Page<VectorDataEntity> searchVectorData(...);

结果一跑，只要任何一个参数为 null，就报错：org.postgresql.util.PSQLException: 无法转换 bytea 到 bigint。

第一回合：为什么 null 会炸？

原生查询中，Hibernate 会把 :dataId 等参数原样传给 PreparedStatement。当参数为 null 时，JDBC 无法确定其 SQL 类型，PostgreSQL 的 JDBC 驱动默认将其映射为 bytea。接着执行 cast(bytea as bigint) 或 to_tsquery(bytea) 时，自然就类型转换失败。

核心矛盾：SQL 语法解析发生在参数绑定之前，即使条件逻辑上可以短路，但 cast 表达式本身已经被数据库"看到"并尝试解析了。

第二回合：SpEL 的假希望

Spring Data JPA 提供了 SpEL 可以在 @Query 中访问方法参数。我尝试用 SpEL 来短路：

1

WHERE (:#{#dataId == null} OR v.data_id = cast(:dataId as bigint))

然而运行后发现，SpEL 只是决定了是否拼接这个条件片段，但 cast(:dataId as bigint) 这个表达式仍然会被解析和编译。当 dataId 为 null 时，参数绑定后仍然是一个 bytea，转换依然失败。

我尝试了 cast(cast(:dataId as text) as bigint)——bytea 转 text 再转 bigint，null 转空文本再转 bigint居然不报错了！但仍然无法优雅地处理"参数为 null 时不加条件"的需求。

第三回合：Specification 规范方案

拥抱 JPA Criteria API，写了一个 Specification 动态拼接条件：

1
2
3


public static Specification<VectorDataEntity> dataIdEquals(Long dataId) {
    return (root, query, cb) -> dataId == null ? null : cb.equal(root.get("dataId"), dataId);
}

这样确实做到了"null 参数不生成条件"。但是全文检索和 JSONB 操作又成了新坑：@@ 需要 ts_match_vq 函数，@> 需要 jsonb_contains 函数，可读性极差。处理 geography 字段的 ST_DWithin 等操作更是噩梦。

第四回合：JdbcTemplate 救场？

动态拼接 SQL，用 NamedParameterJdbcTemplate 绑定参数，null 参数根本不会出现在 SQL 中，类型转换问题彻底消失。

但是看到 geog 和 geom 字段时傻了——JdbcTemplate 默认只能映射基础类型，PostGIS 的 geometry 和 geography 需要注册自定义 ResultSetExtractor，将 PGgeometry 转换为 JTS Geometry 类型。这工作量几乎等于重写整个 ORM 层。

而且项目里已经有大量基于 Hibernate 的 PostGIS 函数调用，换成 JdbcTemplate 意味着全部重写。权衡后放弃。

第五回合：回到原点，优化原生 @Query

折腾了一圈，发现最初的原生 @Query 离正确只差两步：

用 SpEL 短路条件——确保参数为 null 时，整个条件被替换为 true
双重 cast 让 null 安全通过类型转换

最终可用的代码：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


@Query(value = "SELECT * FROM vector_data v " +
        "WHERE (:#{#dataId == null} OR v.data_id = cast(cast(:dataId as text) as bigint)) " +
        "AND (:#{#keyword == null} OR v.keywords @@ plainto_tsquery(cast(:keyword as text))) " +
        "AND (:#{#propFilterStr == null} OR v.fields @> cast(:propFilterStr as jsonb)) " +
        "ORDER BY v.create_time DESC",
        nativeQuery = true)
Page<VectorDataEntity> searchVectorData(@Param("dataId") Long dataId,
                                        @Param("keyword") String keyword,
                                        @Param("propFilterStr") String propFilterStr,
                                        Pageable pageable);

改进：用 plainto_tsquery 代替 to_tsquery，避免用户输入特殊字符导致语法错误。

复盘

方案	优点	痛点
原生 `@Query` 无处理	简单直接	null 参数导致 bytea 转换错误
加 SpEL 短路	条件可选	无法阻止 cast 表达式解析
双重 cast	null 安全	需要手动处理每个参数
Specification	类型安全、可组合	PostgreSQL 特有操作符支持差，地理字段噩梦
JdbcTemplate	完全控制 SQL	地理类型映射代价极高

结论：对于混合了全文检索、JSONB、地理数据的复杂查询，原生 @Query + SpEL 条件短路 + 双重 cast 是生产环境最务实的选择。

给后来者的建议

不要迷信 JPA 能搞定一切。PostgreSQL 的强大扩展往往需要"稍微降级"到原生 SQL。
理解 null 参数在 native query 中的本质：它被当作 bytea，必须显式转换。
SpEL 只能控制条件的生成，不能控制表达式的解析。
对于简单的动态查询，Specification 足够好；但一旦涉及数据库特有操作符，果断切回原生查询。
PostGIS 的 geometry/geography 字段是 JPA 的舒适区，千万别用 JdbcTemplate 自找麻烦。

技术的优雅和现实的复杂度之间，往往需要找一个平衡点。