时间戳

首页 > 解决方案 > 来自 Java 的 PostgreSQL 查询中的可选日期参数不起作用

java - 来自 Java 的 PostgreSQL 查询中的可选日期参数不起作用

问题描述

我们正在将一些服务从 Oracle 迁移到 PostgreSQL，并且我们有基于 JDBC 的自定义数据库访问层。几乎我们所有的用例都可以正常工作，但带有可选参数 ( where field = :parameter or :parameter is null) 的查询却不行。

Oracle 中的原始查询是：

select field
from table
where (reference = ?)
  and (date >= ? or ? is null)

它有效，我们在 MySQL 中进行了测试，它也有效。但是，使用 PostgreSQL，我们会收到以下错误： org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $3

我们直接使用 JDBC PostgreSQL 库进行测试：

private static void testQuery() {
   try {
      String ref = "dummy";
      String sql = "select field From table where (reference = ?)";
      sql += " and (date >= ? or ? is null) ";
      PreparedStatement ps = getConnection().prepareStatement(sql);
      ps.setString(1, ref);
      ps.setNull(2, Types.NULL);
      ps.setNull(3, Types.NULL);
      ResultSet rs = ps.executeQuery();
      while (rs.next()) {
         System.out.print("Column 1 returned: ");
         System.out.println(rs.getString(1));
      }
      rs.close();
      ps.close();
   } catch (Exception ex) {
      ex.printStackTrace();
   }
}

我们确实使用该coalesce函数进行了测试，它可以工作，但我们的想法是让纯 SQL 查询可以在任何数据库中工作。

我们使用的是 PostgreSQL 12.4，依赖项是：

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.16</version>
</dependency>

我们可以让它工作指定

ps.setNull(2, Types.NULL);
ps.setNull(3, Types.VARCHAR);

在VARCHAR将参数与null. 这对我们没有多大帮助，因为从查询的角度来看它是相同的参数，所以我们不能根据条件is null是否在我们的数据库访问层中使用来放置两种类型。

到目前为止，只要参数为空，我们就VARCHAR默认使用 Oracle 和 MySQL 访问，但是 PostgreSQL 为第二个参数（日期）给出以下错误：

org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp without time zone >= character varying

有任何想法吗？

编辑：如果我没有弄错并且没有误读这个线程，除了（我认为丑陋的）选项之外没有真正的解决方案来指定查询中可空参数的类型：select * from my_table where ?::timestamp。似乎即使是 JPA 也有同样的问题，并且必须进行类型转换。

标签： javasqlpostgresql

解决方案

正如Andreas在他的第二条评论中指出的那样，问题在于 PostgreSQL 的 JDBC 实现比其他的更“精炼”，并且想要明确知道哪个类型是参数，即使传递的值是null. 这不是 Oracle 或 MySQL 等其他实现的情况，Varchar当值为null.

除了更改所有要使用的查询之外，我们不得不选择其他选项COALESCE。

如果有人感兴趣，这是一个解释问题的旧线程，以及最近的讨论。

推荐阅读