Spring Data JPA连接Amazon Redshift报错：列符号.id应用于字符类型问题

错误信息 #

com.amazon.support.exceptions.ErrorException: [Amazon](500310) Invalid operation: 
    column notation .id applied to type character, which is not a composite type;

调用GET localhost:8080/get时，Hibernate生成的SQL为：

select c1_0.id,c1_0.firstname,c1_0.lastname,c1_0.accountid from user c1_0

相关配置与代码 #

application.properties #

spring.jpa.show-sql=true

spring.datasource.url=jdbc:redshift://mydb.blah.region.redshift.amazonaws.com:5439/dev?currentSchema=myschema

spring.datasource.username=user
spring.datasource.password=pass

spring.datasource.dbcp2.validation-query=SELECT 1

Controller代码 #

@RestController
@RequestMapping("/")
public class ContactController {
        
    private RedshiftRepo repo;
    
    @Autowired
    public ContactController(RedshiftRepo repo) {
        this.repo = repo;
    }
    
    @GetMapping(value = "/get")
    public ResponseEntity<List<Contact>> getTest(){
        
        List<Contact> list = repo.findAll();        
        return new ResponseEntity<List<Contact>>(list, HttpStatus.OK);
    }

    @GetMapping(value = "/getByEmail")
    public ResponseEntity<List<Contact>> getByEmail(@RequestParam String email){
        
        List<Contact> list = repo.getContactByEmail(email);         
        return new ResponseEntity<List<Contact>>(list, HttpStatus.OK);
    }
}

RedshiftRepo代码 #

@Repository
public interface RedshiftRepo extends JpaRepository<Contact, Integer>{
    
    @Query("select id, firstname, lastname, accountid from Contact c where c.email = ?1")
    public Contact getContactByEmail(String email);
    
}

Entity代码 #

@Entity
@Table(name ="user")
public class Contact {

    @Id
    private String id;
    
    private String firstname;
    
    private String lastname;
    
    private String accountid;

}

pom.xml代码 #

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.0.1</version>
        <relativePath /> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.contactAPI</groupId>
    <artifactId>pocContactAPI</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>pocContactAPI</name>
    <description>Contact Getter</description>
    <properties>
        <java.version>11</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

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

        <dependency>
            <groupId>com.amazonaws</groupId>
            <artifactId>aws-java-sdk-redshift</artifactId>
            <version>1.11.999</version>
        </dependency>
        <dependency>
            <groupId>com.amazon.redshift</groupId>
            <artifactId>redshift-jdbc42-no-awssdk</artifactId>
            <version>1.2.41.1065</version>
        </dependency>
    </dependencies>

    <repositories>
        <repository>
            <id>redshift</id>
            <url>http://redshift-maven-repository.s3-website-us-east-1.amazonaws.com/release</url>
        </repository>
    </repositories>


    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

问题原因分析 #

1. 表名与Redshift保留关键字冲突：user是PostgreSQL（Redshift基于PostgreSQL）的内置保留关键字，Hibernate生成SQL时未对表名加引号，数据库会将user识别为内置类型而非表名，因此c1_0.id会被解析为对user类型的字段访问，触发报错。
2. JPA泛型ID类型不匹配：RedshiftRepo继承JpaRepository<Contact, Integer>，但Contact实体的id字段类型是String，二者不匹配，会导致ORM映射异常。
3. 字段映射缺失与返回类型不匹配：Contact实体未映射email字段，但Repo的查询语句中使用了c.email；同时getContactByEmail方法返回Contact，但Controller中用List<Contact>接收，类型不兼容。

解决方案 #

1. 转义保留关键字表名 #

修改Contact实体的@Table注解，给表名添加双引号转义：

@Entity
@Table(name = "\"user\"")
public class Contact {
    // ... 其他代码不变
}

修改后Hibernate生成的SQL会正确识别"user"为表名：

select c1_0.id,c1_0.firstname,c1_0.lastname,c1_0.accountid from "user" c1_0

2. 修正JPA泛型ID类型 #

修改RedshiftRepo的泛型参数，与实体id字段类型保持一致：

@Repository
public interface RedshiftRepo extends JpaRepository<Contact, String>{
    // ... 其他代码不变
}

3. 补充字段映射与修正返回类型 #

• 在Contact实体中添加email字段映射：

@Entity
@Table(name = "\"user\"")
public class Contact {

    @Id
    private String id;
    
    private String firstname;
    
    private String lastname;
    
    private String accountid;
    
    private String email; // 新增email字段映射
}

• 统一返回类型：若需返回多条数据，修改Repo方法为：

@Query("select c.id, c.firstname, c.lastname, c.accountid from Contact c where c.email = ?1")
public List<Contact> getContactByEmail(String email);

若仅返回单条数据，修改Controller的返回类型为ResponseEntity<Contact>。

4. 优化依赖（可选） #

Redshift JDBC驱动兼容PostgreSQL协议，可移除postgresql依赖避免潜在冲突：

<!-- 移除该依赖 -->
<!-- <dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
</dependency> -->

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