如何编写适配多数据库的Spring Data JPA大小写敏感列POJO

问题背景 #

产品需同时运行在MySQL、Oracle、SQLServer数据库上，通过Spring Data JPA访问customer表时遇到列名大小写敏感适配问题：

• 适配Oracle的POJO需在@Column的name属性中添加双引号（如"cxCifID"），才能匹配Oracle中大小写敏感的列名
• 但该写法在MySQL中会触发语法错误：

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"cxCifID" as cxcifid1_0_, customer0_."custEmail1" as custemai2_0_, customer0_."c' at line 1

• 适配MySQL的POJO（不带双引号）可正常运行，但无法兼容Oracle的大小写敏感列

解决方案 #

方案1：利用Hibernate全局配置+数据库方言（推荐） #

无需修改POJO（使用适配MySQL的版本即可），通过分环境配置Hibernate参数，自动适配不同数据库的大小写规则：

1. 统一POJO版本

import lombok.*;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Getter
@Setter
@ToString
@Table(name="customer")
public class Customer{
    @Id
    @Column(name="cxCifID")
    private String cxCifID;
    @Column(name="hostCustId")
    private String hostCustId;
    @Column(name="custMobile1")
    private String custMobile1;
    @Column(name="custEmail1")
    private String custEmail1;
    @Column(name="custName")
    private String custName;
    @Column(name="custMotherTongue")
    private String custMotherTongue;
}

2. 分数据库环境配置

在Spring Boot中通过多环境配置文件实现：

Oracle环境（application-oracle.yml）

spring:
  jpa:
    hibernate:
      ddl-auto: none
    properties:
      hibernate:
        dialect: org.hibernate.dialect.Oracle12cDialect
        globally_quoted_identifiers: true # 自动给所有标识符加双引号，适配Oracle大小写敏感

MySQL环境（application-mysql.yml）

spring:
  jpa:
    hibernate:
      ddl-auto: none
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQL8Dialect
        globally_quoted_identifiers: false # 关闭全局引号
  datasource:
    url: jdbc:mysql://localhost:3306/your_db?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&lower_case_table_names=1
    # lower_case_table_names=1 让MySQL不区分表/列名大小写（Windows默认值，Linux需在my.cnf中配置）

SQLServer环境（application-sqlserver.yml）

spring:
  jpa:
    hibernate:
      ddl-auto: none
    properties:
      hibernate:
        dialect: org.hibernate.dialect.SQLServer2012Dialect
        # SQLServer默认不区分大小写，无需全局引号配置

方案2：使用Spring属性占位符动态指定列名 #

通过Spring的属性占位符，在POJO中动态引用不同环境的列名配置：

1. 修改后的POJO

import lombok.*;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Getter
@Setter
@ToString
@Table(name="${customer.table.name}")
public class Customer{
    @Id
    @Column(name="${customer.column.cxCifID}")
    private String cxCifID;
    @Column(name="${customer.column.hostCustId}")
    private String hostCustId;
    @Column(name="${customer.column.custMobile1}")
    private String custMobile1;
    @Column(name="${customer.column.custEmail1}")
    private String custEmail1;
    @Column(name="${customer.column.custName}")
    private String custName;
    @Column(name="${customer.column.custMotherTongue}")
    private String custMotherTongue;
}

2. 分环境配置属性

Oracle环境（application-oracle.yml）

customer:
  table:
    name: "customer"
  column:
    cxCifID: "\"cxCifID\""
    hostCustId: "\"hostCustId\""
    custMobile1: "\"custMobile1\""
    custEmail1: "\"custEmail1\""
    custName: "\"custName\""
    custMotherTongue: "\"custMotherTongue\""

MySQL环境（application-mysql.yml）

customer:
  table:
    name: "customer"
  column:
    cxCifID: "cxCifID"
    hostCustId: "hostCustId"
    custMobile1: "custMobile1"
    custEmail1: "custEmail1"
    custName: "custName"
    custMotherTongue: "custMotherTongue"

SQLServer环境（application-sqlserver.yml）

customer:
  table:
    name: "customer"
  column:
    cxCifID: "cxCifID" # SQLServer支持带/不带引号的大小写列名，按需配置即可
    hostCustId: "hostCustId"
    custMobile1: "custMobile1"
    custEmail1: "custEmail1"
    custName: "custName"
    custMotherTongue: "custMotherTongue"

原理说明 #

• Oracle：默认区分标识符大小写，仅当列名被双引号包裹时才会保留定义时的大小写，否则自动转为大写
• MySQL：默认不区分标识符大小写（受lower_case_table_names参数控制），双引号会被视为字符串字面量，导致语法错误
• SQLServer：默认不区分标识符大小写，但支持双引号包裹的大小写敏感列名

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