在PostgreSQL中,我有这个表:
如果
CREATE TABLE public.my_table
(
id integer NOT NULL,
...
我想执行这个查询:显示给定ID的行。如果ID为空,则显示所有行。
我尝试使用以下方法:
public interface MyRepository extends JpaRepository<MyTable, Integer> {
@Query(value = "SELECT * FROM my_table WHERE (?1 IS NULL OR id = ?1)", nativeQuery = true)
List<MyTable> findAll(Integer id);
如果
id != null
,那么一切正常。但是如果 id == null
,我就会收到错误信息。org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:261) ~[spring-orm-4.3.13.RELEASE.jar:4.3.13.RELEASE]
...
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
...
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = bytea
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440) ~[postgresql-42.2.5.jar:42.2.5]
...
很明显短路评估不起作用,null
被转换为bytea
。
为了解决这个问题,我已将查询值更改为
SELECT * FROM my_table WHERE (?1 IS NULL OR id = (CAST (CAST(?1 AS character varying) AS integer)))
但这不太好,因为int被转换为字符串再转换为int。你是否有更好的解决方法,例如更好的强制类型转换或SQL查询?