当将纯SQL发送到PostgreSQL时,如下所示:
CREATE TABLE things (
id BIGINT PRIMARY KEY NOT NULL,
foo BIGINT NOT NULL,
bar BIGINT NOT NULL
);
INSERT INTO things VALUES (9900, 1, 2);
INSERT INTO things VALUES (9901, 3, 4);
INSERT INTO things VALUES (9902, 1, 4);
SELECT * FROM things WHERE foo IN (1, 2);
SELECT * FROM things WHERE (foo, bar) IN (VALUES (1, 2), (3, 4));
它按预期工作。
但是,当尝试使用 Spring Boot 做同样的事情时,它会失败。这是我的最小示例(Kotlin(:
实体:
import org.springframework.data.jpa.domain.AbstractPersistable
import javax.persistence.Entity
import javax.persistence.Table
@Entity
@Table(name = "things")
class Thing(
val foo: Long,
val bar: Long
) : AbstractPersistable<Long>()
存储库:
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.Query
interface ThingRepository : JpaRepository<Thing, Long> {
@Query(nativeQuery = true, value = "SELECT * FROM things WHERE foo IN (1, 2);")
fun selectNativeByFoo(): Iterable<Thing>
@Query(nativeQuery = true, value = "SELECT * FROM things WHERE (foo, bar) IN (VALUES (1, 2), (3, 4));")
fun selectNativeByFooAndBar(): Iterable<Thing>
}
调用selectNativeByFoo
工作正常,但调用selectNativeByFooAndBar
失败,并出现以下异常:
org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [SELECT * FROM things WHERE (foo, bar) IN (VALUES (1, 2), (3, 4));]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement
Caused by: org.h2.jdbc.JdbcSQLException: Invalid parameter count for "VALUES", expected count: "1"; SQL statement:
SELECT * FROM things WHERE (foo, bar) IN (VALUES (1, 2), (3, 4)); [7001-197]
我做错了什么?
JB Nizet的这个评论,我才能够解决它。
使用的语法
SELECT * FROM things WHERE (foo, bar) IN (VALUES (1, 2), (3, 4));
只有真正的 PostgreSQL 支持,而不受我用于测试的 H2 支持,即使使用
spring.datasource.url: jdbc:h2:mem:db;MODE=PostgreSQL
将其更改为
SELECT * FROM things WHERE (foo, bar) IN ((1, 2), (3, 4));
使其在真正的 Postgres 和 H2 中都可以在 Postgres 模式下工作。
另一种选择是切换到embedded-database-spring-test
并使用它的嵌入式 Postgres 进行测试。然后原始语法(使用 VALUES
(也可以工作。