Hibernate执行原生PostgreSQL查询报列索引越界异常求助

我来帮你搞定这个问题！你遇到的The column index is out of range: 1, number of columns: 0.错误，本质是Hibernate在处理PostgreSQL的DO匿名PL/pgSQL块时，没办法正确识别和绑定参数导致的。

先复盘下你的场景：你想用@Modifying+@Transactional+原生Query实现“不存在则插入”的逻辑，但执行时触发了参数绑定异常。

你的代码片段 #

@Modifying 
@Transactional 
@Query(value = "DO $$ " + 
               " BEGIN " + 
               " IF NOT EXISTS ( " + 
               " SELECT * FROM subscriptions AS s " + 
               " WHERE s.client_id = :clientId AND " + 
               " s.status = :status AND " + 
               " s.messenger = :messenger AND " + 
               " s.batch = :batch AND " + 
               " quote_nullable(s.subscription_config ->> 'countryId') = quote_nullable( :countryId ) AND" + 
               " quote_nullable(s.subscription_config ->> 'cityId') = quote_nullable( :cityId ) AND " + 
               " quote_nullable(s.subscription_config ->> 'professionName') = quote_nullable( :professionName ) AND " + 
               " quote_nullable(s.subscription_config ->> 'salaryFrom') = quote_nullable( :salaryFrom ) " + 
               " ) " + 
               " THEN " + 
               " INSERT INTO subscriptions (client_id, status, messenger, batch, subscription_config) VALUES ( :clientId, :status, :messenger, :batch, to_jsonb(:subscriptionConfig)); " + 
               " END IF; " + 
               " RETURN; " + 
               "END; " + 
               "$$" ,nativeQuery = true) 
void saveSubscription( 
    @Param("clientId") Long clientId, 
    @Param("status")String status, 
    @Param("messenger")String messenger, 
    @Param("batch")Integer batch, 
    @Param("subscriptionConfig") SubscriptionConfig subscriptionConfig, 
    @Param("countryId") Long countryId, 
    @Param("cityId") Long cityId, 
    @Param("professionName")String professionName, 
    @Param("salaryFrom") Integer salaryFrom
);

异常核心堆栈 #

Caused by: org.postgresql.util.PSQLException: 列索引超出范围：1，列数：0。
	at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:65)
	at org.postgresql.core.v3.SimpleParameterList.setBinaryParameter(SimpleParameterList.java:132)
	at org.postgresql.jdbc.PgPreparedStatement.bindBytes(PgPreparedStatement.java:983)
	at org.postgresql.jdbc.PgPreparedStatement.setLong(PgPreparedStatement.java:279)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setLong(HikariProxyPreparedStatement.java)
	at org.hibernate.type.descriptor.sql.BigIntTypeDescriptor$1.doBind(BigIntTypeDescriptor.java:46)
	at org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:74)
	at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:276)
	at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:271)
	at org.hibernate.loader.custom.sql.NamedParamBinder.bind(NamedParamBinder.java:34)
	at org.hibernate.engine.query.spi.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:102)
	... 44 common frames omitted

问题根源 #

PostgreSQL的DO语句是匿名无返回值的PL/pgSQL块，JDBC驱动和Hibernate对它的参数绑定支持很差。Hibernate会把这个DO块当成一个没有参数的查询，但你实际传入了多个绑定参数，驱动在尝试绑定第一个参数时发现语句里没有对应的占位符位置，就抛出了“列索引超出范围”的错误。

解决方案 #

给你两个靠谱的方案，优先推荐方案一，更符合PostgreSQL的最佳实践：

方案一：改用PostgreSQL原生UPSERT（INSERT ... ON CONFLICT）

这是实现“不存在则插入”最简洁高效的方式，不需要写复杂的PL/pgSQL块。

1. 先给subscriptions表添加生成列，把JSONB字段里的查询条件提取出来（这样才能建唯一约束）：

ALTER TABLE subscriptions 
ADD COLUMN country_id BIGINT GENERATED ALWAYS AS ((subscription_config ->> 'countryId')::BIGINT) STORED;

ALTER TABLE subscriptions 
ADD COLUMN city_id BIGINT GENERATED ALWAYS AS ((subscription_config ->> 'cityId')::BIGINT) STORED;

ALTER TABLE subscriptions 
ADD COLUMN profession_name VARCHAR GENERATED ALWAYS AS (subscription_config ->> 'professionName') STORED;

ALTER TABLE subscriptions 
ADD COLUMN salary_from INTEGER GENERATED ALWAYS AS ((subscription_config ->> 'salaryFrom')::INTEGER) STORED;

2. 创建唯一约束，覆盖你的查询条件：

ALTER TABLE subscriptions 
ADD CONSTRAINT unique_subscription 
UNIQUE (client_id, status, messenger, batch, country_id, city_id, profession_name, salary_from);

3. 修改你的JPA查询，用ON CONFLICT DO NOTHING实现逻辑：

@Modifying
@Transactional
@Query(value = "INSERT INTO subscriptions (client_id, status, messenger, batch, subscription_config) " +
               "VALUES (:clientId, :status, :messenger, :batch, to_jsonb(:subscriptionConfig)) " +
               "ON CONFLICT (client_id, status, messenger, batch, country_id, city_id, profession_name, salary_from) DO NOTHING",
       nativeQuery = true)
void saveSubscription(
    @Param("clientId") Long clientId,
    @Param("status") String status,
    @Param("messenger") String messenger,
    @Param("batch") Integer batch,
    @Param("subscriptionConfig") SubscriptionConfig subscriptionConfig
);

这样你还能删掉原来多余的countryId、cityId等参数，因为生成列会自动从subscriptionConfig里提取值，代码更简洁。

方案二：把PL/pgSQL逻辑封装成函数，调用函数实现参数绑定

如果不想改表结构，就把原来的DO块改成一个可调用的PostgreSQL函数：

1. 创建函数：

CREATE OR REPLACE FUNCTION save_subscription(
    p_client_id BIGINT,
    p_status VARCHAR,
    p_messenger VARCHAR,
    p_batch INTEGER,
    p_subscription_config JSONB,
    p_country_id BIGINT,
    p_city_id BIGINT,
    p_profession_name VARCHAR,
    p_salary_from INTEGER
) RETURNS VOID AS $$
BEGIN
    IF NOT EXISTS (
        SELECT * FROM subscriptions AS s
        WHERE s.client_id = p_client_id AND
              s.status = p_status AND
              s.messenger = p_messenger AND
              s.batch = p_batch AND
              quote_nullable(s.subscription_config ->> 'countryId') = quote_nullable(p_country_id) AND
              quote_nullable(s.subscription_config ->> 'cityId') = quote_nullable(p_city_id) AND
              quote_nullable(s.subscription_config ->> 'professionName') = quote_nullable(p_profession_name) AND
              quote_nullable(s.subscription_config ->> 'salaryFrom') = quote_nullable(p_salary_from)
    ) THEN
        INSERT INTO subscriptions (client_id, status, messenger, batch, subscription_config)
        VALUES (p_client_id, p_status, p_messenger, p_batch, p_subscription_config);
    END IF;
END;
$$ LANGUAGE plpgsql;

2. 修改JPA查询为调用这个函数：

@Modifying
@Transactional
@Query(value = "SELECT save_subscription(:clientId, :status, :messenger, :batch, to_jsonb(:subscriptionConfig), :countryId, :cityId, :professionName, :salaryFrom)",
       nativeQuery = true)
void saveSubscription(
    @Param("clientId") Long clientId,
    @Param("status") String status,
    @Param("messenger") String messenger,
    @Param("batch") Integer batch,
    @Param("subscriptionConfig") SubscriptionConfig subscriptionConfig,
    @Param("countryId") Long countryId,
    @Param("cityId") Long cityId,
    @Param("professionName") String professionName,
    @Param("salaryFrom") Integer salaryFrom
);

Hibernate对函数调用的参数绑定支持很好，这样就能避免原来的DO块参数绑定问题。

内容的提问来源于stack exchange，提问作者Дмитрий Литвин