Spring Boot+PostgreSQL登录报错：column u1_0.accounttype不存在

使用Spring Boot 3.1.1、Java、PostgreSQL和Flyway开发API，初始版本通过Spring Security实现了包含ADMIN、USER、CUSTOMER三种角色的User实体，认证、授权及CRUD功能正常。为给Customer角色添加更多属性，创建了继承自User的Customer类并完成数据库迁移，当前可正常注册用户，但使用原有User（ADMIN/USER角色）登录时触发以下错误：

SQL Error: 0, SQLState: 42703
ERROR: column u1_0.accounttype does not exist
Posição: 62

注意：accounttype仅定义在Customer类中，User类无该属性。

User类 #

package com.example.auth.domain.user;

import jakarta.persistence.*;
import lombok.AllArgsConstructor;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.authority.SimpleGrantedAuthority;
import org.springframework.security.core.userdetails.UserDetails;

import java.util.Collection;
import java.util.List;

@Table(name = "users")
@Entity(name = "users")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(of = "id")
public class User implements UserDetails {
    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private String id;
    private String login;
    private String password;
    private UserRole role;

    public User(String login, String password, UserRole role){
        this.login = login;
        this.password = password;
        this.role = role;
    }

    @Override
    public Collection<? extends GrantedAuthority> getAuthorities() {
        if(this.role == UserRole.ADMIN){
            return List.of(new SimpleGrantedAuthority("ROLE_ADMIN"), new SimpleGrantedAuthority("ROLE_USER"), new SimpleGrantedAuthority("ROLE_CUSTOMER"));
        } 
        else if(this.role == UserRole.USER){
            return List.of(new SimpleGrantedAuthority("ROLE_USER"), new SimpleGrantedAuthority("ROLE_CUSTOMER"));
        }
        else{
            return List.of(new SimpleGrantedAuthority("ROLE_CUSTOMER"));
        }
    }

    @Override
    public String getUsername() {
        return login;
    }

    @Override
    public boolean isAccountNonExpired() {
        return true;
    }

    @Override
    public boolean isAccountNonLocked() {
        return true;
    }

    @Override
    public boolean isCredentialsNonExpired() {
        return true;
    }

    @Override
    public boolean isEnabled() {
        return true;
    }
}

Customer类 #

package com.example.auth.domain.costumer;

import com.example.auth.domain.user.User;
import com.example.auth.domain.user.UserRole;

import jakarta.persistence.Entity;
import jakarta.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@Entity(name = "customers")
@Table(name="customers")
@AllArgsConstructor
@Getter
@Setter
@NoArgsConstructor
public class Customer extends User{

    private String name;
    private String email;
    private String phone;
    private Integer relationshipTime;
    private Integer age;
    private CustomerContactRole contactorigin;
    private CustomerAccountTypeRole accounttype;

    public Customer(String login, String password, String name, String email, String phone,
            Integer relationshipTime, Integer age, CustomerContactRole contactOrigin,
            CustomerAccountTypeRole accountType) {
        super(login, password, UserRole.CUSTOMER);
        this.name = name;
        this.email = email;
        this.phone = phone;
        this.relationshipTime = relationshipTime;
        this.age = age;
        this.contactorigin = contactOrigin;
        this.accounttype = accountType;
    }

}

数据库迁移脚本V1__create-product-user-customer-table.sql #

CREATE TABLE product (
    id TEXT PRIMARY KEY UNIQUE NOT NULL,
    name TEXT NOT NULL,
    price INTEGER NOT NULL
);
CREATE TABLE users (
    id TEXT PRIMARY KEY UNIQUE NOT NULL,
    login TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    role TEXT NOT NULL,
    dtype TEXT NULL
);

CREATE TABLE customers (
    name TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    phone TEXT NOT NULL,
    relationshiptime INTEGER NOT NULL,
    age INTEGER NOT NULL,
    contactorigin TEXT NOT NULL,
    accounttype TEXT NOT NULL
) inherits (users);

登录接口 #

@PostMapping("/login")
public ResponseEntity<String> login(@RequestBody @Valid AuthenticationDTO data){
    var usernamePassword = new UsernamePasswordAuthenticationToken(data.login(), data.password());
    // 引发错误的代码行
    var auth = this.authenticationManager.authenticate(usernamePassword);

    String token = tokenService.generateToken((User) auth.getPrincipal());

    return ResponseEntity.status(HttpStatus.OK).body("Login finished! " + new LoginResponseDTO(token));
}

错误日志 #

2023-07-27T13:02:17.441-03:00  WARN 8700 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42703
2023-07-27T13:02:17.441-03:00 ERROR 8700 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: column u1_0.accounttype does not exist
  Posição: 62

问题根源 #

JPA默认采用**单表继承（Single Table）**策略，当查询User实体时会自动进行多态查询，包含所有子类（如Customer）的字段。但你的数据库使用了PostgreSQL的表继承机制，accounttype仅存在于customers表，users表无此字段，导致查询原有User时触发列不存在错误。

修复步骤 #

1. 调整JPA继承策略为联合继承（Joined）

在User类上添加继承策略和鉴别器配置，明确区分父类与子类实体：

@Table(name = "users")
@Entity(name = "users")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(of = "id")
@Inheritance(strategy = InheritanceType.JOINED) // 添加此行
@DiscriminatorColumn(name = "dtype", discriminatorType = DiscriminatorType.STRING) // 添加此行
public class User implements UserDetails {
    // 原有代码不变
}

在Customer类上添加鉴别器值，标记该实体对应的dtype：

@Entity(name = "customers")
@Table(name="customers")
@AllArgsConstructor
@Getter
@Setter
@NoArgsConstructor
@DiscriminatorValue("CUSTOMER") // 添加此行
public class Customer extends User{
    // 原有代码不变
}

2. 修改数据库迁移脚本，适配联合继承结构

PostgreSQL的表继承与JPA联合继承不兼容，需将customers表改为独立表，通过外键关联users表：

CREATE TABLE product (
    id TEXT PRIMARY KEY UNIQUE NOT NULL,
    name TEXT NOT NULL,
    price INTEGER NOT NULL
);
CREATE TABLE users (
    id TEXT PRIMARY KEY UNIQUE NOT NULL,
    login TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    role TEXT NOT NULL,
    dtype TEXT NOT NULL
);

-- 移除inherits(users)，添加外键关联users.id
CREATE TABLE customers (
    id TEXT PRIMARY KEY REFERENCES users(id),
    name TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    phone TEXT NOT NULL,
    relationshiptime INTEGER NOT NULL,
    age INTEGER NOT NULL,
    contactorigin TEXT NOT NULL,
    accounttype TEXT NOT NULL
);

3. 验证UserDetailsService查询逻辑

确保登录时加载用户的逻辑仅查询对应实体类型，避免不必要的多态查询。例如在UserDetailsService实现类中：

@Override
public UserDetails loadUserByUsername(String login) throws UsernameNotFoundException {
    // 使用JPA查询时指定User类，避免自动加载子类字段
    return userRepository.findByLogin(login)
            .orElseThrow(() -> new UsernameNotFoundException("用户不存在"));
}

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