R2DBC仓库基础方法失效问题求助

使用Spring Boot 3.0.6、Java 17和PostgreSQL开发响应式Web应用时，调用cardRepository.findAll()等基础方法返回SQL语法错误：

ERROR c.a.a.c.h.ControllerExceptionHandler - executeMany; bad SQL grammar [SELECT card.* FROM card]

相关代码与配置如下：

数据库配置类 #

@Configuration
public class PostgresConfig extends AbstractR2dbcConfiguration {

    @Override
    @Bean
    public ConnectionFactory connectionFactory() {
        return new PostgresqlConnectionFactory(
                PostgresqlConnectionConfiguration.builder()
                        .host("localhost")
                        .port(5432)
                        .username("postgres")
                        .password("0000")
                        .database("databaseName")
                        .codecRegistrar(EnumCodec.builder()
                                .withEnum("card_status", CardStatus.class)
                                .withEnum("credit_card_order_status", CreditCardOrderStatus.class)
                                .withEnum("credit_status", CreditStatus.class)
                                .withEnum("payment_system", PaymentSystem.class)
                                .build())
                        .build());
    }
}

Repository接口 #

public interface CardRepository extends ReactiveCrudRepository<Card, UUID> {
}

配置文件（application.yml） #

liquibase:
    change-log: classpath:db.migration/db.changelog-master.xml
    database-change-log-lock-table: LIQ_DATABASECHANGELOGLOCK
    database-change-log-table: LIQ_DATABASECHANGELOG
    driver-class-name: org.postgresql.Driver
    password: ${PSQL_DATASOURCE_PASSWORD:0000}
    url: jdbc:postgresql://${PSQL_DATASOURCE_URL:localhost}:5432/databaseName
    user: ${PSQL_DATASOURCE_USERNAME:postgres}
r2dbc:
    password: 0000
    url: r2dbc:postgresql://${PSQL_DATASOURCE_URL:localhost}:5432/databaseName
    username: postgres

Maven依赖配置 #

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>3.0.6</version>
    <relativePath/><!-- lookup parent from repository -->
</parent>

...
    <dependency>
        <groupId>io.projectreactor</groupId>
        <artifactId>reactor-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-r2dbc</artifactId>
        <version>3.0.2</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-webflux</artifactId>
    </dependency>
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>r2dbc-postgresql</artifactId>
        <version>1.0.1.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.6.0</version>
    </dependency>

Controller方法 #

@GetMapping
public ResponseEntity<Flux<Card>> getCreditCards() {
    return ResponseEntity.ok(repo.findAll());
}

Card实体类 #

@Getter
@Setter
@ToString
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Table(name = "card")
public class Card {
    @Id
    @Column("id")
    private UUID id;

    @Column("card_number")
    private String cardNumber;

    @Column("holder_name")
    private String holderName;

    @Column("expiration_date")
    private LocalDate expirationDate;

    @Column("payment_system")
    private PaymentSystem paymentSystem;

    @Column("balance")
    private BigDecimal balance;

    @Column("status")
    private CardStatus status;

    @Column("transaction_limit")
    private BigDecimal transactionLimit;

    @Column("delivery_point")
    private String deliveryPoint;

    @Column("digital_wallet")
    private Boolean digitalWallet;

    @Column("virtual")
    private Boolean virtual;

    @Column("co_brand")
    private String coBrand;

    @Column("hashed_pin_code")
    private String hashedPinCode;

    @Column( "card_name")
    private String cardName;

    @Transient
    private CardProductOrder cardProductOrder;

    @Column("card_product_order_id")
    private UUID cardProductOrderId;
 
    @Transient
    private Account account;

    @Column("account_id")
    private UUID accountId;
}

核心原因及修复步骤 #

1. 表名与PostgreSQL关键字冲突
   card是PostgreSQL的保留关键字，直接作为表名会导致SQL解析失败。两种修复方式：

   • 重命名数据库表为非关键字（例如cards），同步修改实体类的@Table(name = "cards")
   • 转义表名：在实体类中使用@Table(name = "\"card\"")，强制PostgreSQL将其识别为表名而非关键字

2. R2DBC依赖版本不兼容
   当前r2dbc-postgresql:1.0.1.RELEASE适配Spring Boot 2.x，与Spring Boot 3.0.6不兼容。Spring Boot 3.x需要使用R2DBC PostgreSQL 1.1.x及以上版本：

   • 移除r2dbc-postgresql的手动版本配置，让Spring Boot父依赖自动管理兼容版本：

     <dependency>
         <groupId>org.postgresql</groupId>
         <artifactId>r2dbc-postgresql</artifactId>
     </dependency>
     

3. 自定义ConnectionFactory配置冲突
   继承AbstractR2dbcConfiguration会覆盖Spring Boot自动配置的ConnectionFactory，可能导致配置缺失。

   • 若无需自定义EnumCodec，直接删除PostgresConfig类，依赖application.yml中的R2DBC配置即可
   • 若需要保留EnumCodec，确保配置参数（主机、端口、数据库名等）与yml一致，且使用兼容版本的r2dbc-postgresql

4. 验证数据库表结构
   确认Liquibase迁移脚本已正确执行，card表存在且字段与实体类完全匹配：

   • 检查UUID类型字段是否对应数据库的uuid类型
   • 枚举类型的映射是否与数据库中的枚举定义一致

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