Spring ReactiveCrudRepository自定义@Query在PostgreSQL报错排查

问题描述 #

使用Spring ReactiveCrudRepository时，自定义@Query在MariaDB后端的单元测试中完全正常，但切换到PostgreSQL时抛出PostgresqlBadGrammarException。其他自动生成的Repository查询（如findById、findByStockName）可正常执行，且两个数据库的结构一致。

相关代码与配置 #

实体类Stock.java #

@Data
@NoArgsConstructor
@AllArgsConstructor
@Table(schema = "experiment_schema", name = "stock_names")
public class Stock {

  public Stock(String stockName) {
    this.stockName = stockName;
  }

  @Id
  Integer id;

  @Length(min = 3, max = 3)
  @Pattern(regexp = "^([A-Z]){3}$")
  @Column(value = "stock_namee")
  String stockName;
}

仓库接口StockRepository #

import net.sytes.csongi.springreactivetest.entities.Stock;
import org.springframework.data.r2dbc.repository.Query;
import org.springframework.data.repository.reactive.ReactiveCrudRepository;
import org.springframework.stereotype.Repository;
import reactor.core.publisher.Flux;

@Repository
interface StockRepository extends ReactiveCrudRepository<Stock,Integer> {

// this query fails using PostgreSQL.
// also tried @Query(value = "SELECT * from stock_names where stock_namee=:$1") with same result
  @Query(value = "SELECT * from stock_names  where stock_namee=:s")
  Flux<Stock> myCustomSearchFor(String s);
}

服务类StockService #

import jakarta.validation.ConstraintViolation;
import jakarta.validation.ConstraintViolationException;
import jakarta.validation.Validator;
import net.sytes.csongi.springreactivetest.entities.Stock;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

import java.util.Set;

@Service
public class StockService {

  @Autowired
  private StockRepository repository;
  @Autowired
  private Validator validator;

  public Mono<Stock> save(Stock stockToSave) {
    Set<ConstraintViolation<Stock>> constraintValidations = validator.validate(stockToSave);
    if (constraintValidations.isEmpty())
      return repository.save(stockToSave);
    throw new ConstraintViolationException("The following errors occured: ", constraintValidations);
  }

  public Mono<Void> deleteAll() {
    return repository.deleteAll();
  }

  public Flux<Stock> findStockByName(String stockName) {
    return repository.myCustomSearchFor(stockName);
  }
}

测试代码 #

import jakarta.validation.ConstraintViolationException;
import net.sytes.csongi.springreactivetest.entities.Stock;
import net.sytes.csongi.springreactivetest.repositories.StockService;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import reactor.core.publisher.Mono;

import java.util.List;

import static org.junit.jupiter.api.Assertions.*;

@SpringBootTest
public class StockServiceTest {

  private static final String VALID_STOCK_NAME = "EDE";

  @Autowired
  private StockService stockService;
  private Stock validStock;


  @BeforeEach
  public void setup() {
    validStock = new Stock(VALID_STOCK_NAME);
    stockService.deleteAll().block();
  }
// ...
  @Test
  @DisplayName("Find by stock name should work")
  public void findByStockNameShouldWork() {
    stockService.save(validStock).block();
    List<Stock> loaded = stockService.findStockByName(VALID_STOCK_NAME).collectList().block();
    assertEquals(1,loaded.size());
  }
}

application.yml配置 #

spring:
 main:
  web-application-type: reactive
 r2dbc:
  url: r2dbc:postgresql://localhost:4444/product_db 
#  url: r2dbc:mariadb://localhost:7777/experiment_schema -> succeeds
  password: dev_xxx
  username: dev_xxx
 data:
  r2dbc:
   repositories:
    enabled: true
logging:
 level:
  root: info

错误信息 #

executeMany; bad SQL grammar [SELECT * from stock_names  where stock_namee=$1]
org.springframework.r2dbc.BadSqlGrammarException: executeMany; bad SQL grammar [SELECT * from stock_names  where stock_namee=$1]
    ...
Caused by: io.r2dbc.postgresql.ExceptionFactory$PostgresqlBadGrammarException: [42P01] relation "stock_names" does not exist
    ...

Gradle依赖 #

dependencies {
    compileOnly 'org.projectlombok:lombok'
    annotationProcessor 'org.projectlombok:lombok'
    implementation 'org.springframework.boot:spring-boot-starter-data-r2dbc'
    implementation 'org.springframework.boot:spring-boot-starter-validation'
    implementation 'org.springframework.boot:spring-boot-starter-webflux'
    runtimeOnly 'org.postgresql:r2dbc-postgresql'
    testRuntimeOnly 'org.mariadb:r2dbc-mariadb:1.1.3'
    annotationProcessor 'org.springframework.boot:spring-boot-configuration-processor'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
    testImplementation 'io.projectreactor:reactor-test'
}

原因分析 #

核心问题在于PostgreSQL的Schema机制与MariaDB的差异：

• 实体类上通过@Table(schema = "experiment_schema")指定了表所在的Schema
• Spring Data R2DBC自动生成的查询（如findById、findByStockName）会自动带上Schema前缀，所以能正常找到表
• 但自定义@Query是直接写的原生SQL，没有指定Schema前缀，而PostgreSQL连接URL中指定的是product_db数据库，默认不会自动切换到experiment_schema，因此找不到stock_names表

解决思路 #

• 在自定义SQL中显式指定Schema
  修改@Query语句，加上Schema前缀：

  @Query(value = "SELECT * from experiment_schema.stock_names where stock_namee=:s")
  Flux<Stock> myCustomSearchFor(String s);
  

• 配置PostgreSQL默认Schema
  两种配置方式二选一：

  1. 修改连接URL，添加默认Schema参数：

     spring:
       r2dbc:
         url: r2dbc:postgresql://localhost:4444/product_db?currentSchema=experiment_schema
     

  2. 添加R2DBC属性配置默认Schema：

     spring:
       r2dbc:
         properties:
           current-schema: experiment_schema
     

  配置后所有SQL都会默认使用指定Schema，自定义SQL无需修改。

• 验证PostgreSQL的Schema与权限
  确认product_db数据库中存在experiment_schema，且stock_names表确实在该Schema下；同时确保连接用户dev_xxx拥有访问该Schema的权限。

• 检查表名大小写问题
  PostgreSQL默认会将未加引号的标识符转为小写，如果实际表名是大写（如STOCK_NAMES），需要在SQL中给表名加双引号：

  @Query(value = "SELECT * from experiment_schema.\"STOCK_NAMES\" where stock_namee=:s")
  

内容的提问来源于stack exchange，提问作者Csongi77