Spring Boot 3升级后插入UserPreferences时出现SQL语法错误

问题现象 #

升级Spring Boot到3.x版本后，保存/更新UserPreferences实体时出现异常：

• 编辑现有用户ID时保存正常，仅插入新数据时出错
• 错误提示找不到u1_0.UserId列，但实体配置和数据库列名均为User_Id
• 错误发生在新增数据阶段的SELECT查询中，具体报错如下：

org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [select null,u1_0.First_Name,ou1_0.Last_Name from user u1_0 where u1_0.User_Id=?] [Unknown column 'u1_0.UserId' in 'field list'] [n/a]; SQL [n/a]
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:277) ~[spring-orm-6.1.14.jar:6.1.14]
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:241) ~[spring-orm-6.1.14.jar:6.1.14]
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:550) ~[spring-orm-6.1.14.jar:6.1.14]
at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61) ~[spring-tx-6.1.14.jar:6.1.14]

Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'u1_0.UserId' in 'field list'
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:121) ~[mysql-connector-j-8.0.33.jar:8.0.33]
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-j-8.0.33.jar:8.0.33]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916) ~[mysql-connector-j-8.0.33.jar:8.0.33]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:972) ~[mysql-connector-j-8.0.33.jar:8.0.33]
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-5.1.0.jar:na]
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-5.1.0.jar:na]
at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:246) ~[hibernate-core-6.5.3.Final.jar:6.5.3.Final]
... 226 common frames omitted

使用版本 #

• mysql server: 8.0.39
• mysql connector: 8.0.31
• jakarta.persistence-api-3.1.0.jar
• spring-data-jpa-3.3.5.jar
• hibernate-core-6.5.3.Final.jar
• hibernate-commons-annotations-6.0.6.Final.jar

相关代码 #

服务类 #

@Service
public class UserPreferencesImpl implements UserService {
    @Autowired
    UserPreferencesRepo userPreferenceRepo;

    @Override
    public StatusResponse addOrUpdateUserPreferences(){
        UserPreferences npe = new UserPreferences();
        User user = new User ();
        user.setUserId("sampele-user-id");
        ne.setUser(user);
        userPreferenceRepo.save(ne);
    }
}

仓库类 #

public interface UserPreferencesRepo extends JpaRepository<UserPreferences, String> {
    
}

UserPreferences实体 #

@AllArgsConstructor
@NoArgsConstructor
@Data
@Entity
@Table(name = "user_preferences")
public class UserPreferences implements Serializable {
    private static final long serialVersionUID = 1L;
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "up_id", unique = true, nullable = false)
    private String upId;
    
    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "User_Id") 
    private User user;
}

User实体 #

@AllArgsConstructor
@NoArgsConstructor
@Getter
@Setter
@Entity
@Table(name = "user")
public class User implements Serializable {
    private static final long serialVersionUID = 1L;
    
    @Id
    @Column(name = "User_Id", unique = true, nullable = false, length = 64)
    private String userId;
    
    @Column(name = "First_Name", length = 128)
    private String firstName;
    
    @Column(name = "Last_Name", length = 128)
    private String lastName;
}

解决方案 #

原因分析 #

问题源于Hibernate 6.x（Spring Boot 3默认依赖）的物理命名策略变更：Hibernate 6默认使用CamelCaseToUnderscoresNamingStrategy，会自动将驼峰属性名转换为下划线分隔列名，但在关联未持久化的User实体时，未正确识别@Column指定的User_Id列名，反而使用了属性名userId生成查询语句。另外代码中存在笔误（ne应为npe），但不影响核心问题。

修复方案 #

方案1：强制使用指定列名的命名策略

在配置文件中设置Hibernate严格遵循实体注解指定的列名：
application.properties

spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

application.yml

spring:
  jpa:
    hibernate:
      naming:
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

方案2：显式指定关联列引用

修改UserPreferences的关联注解，明确指定关联的主键列：

@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "User_Id", referencedColumnName = "User_Id") 
private User user;

方案3：先持久化关联的User实体

保存UserPreferences前先保存User，避免Hibernate生成错误查询：

@Override
public StatusResponse addOrUpdateUserPreferences(){
    UserPreferences npe = new UserPreferences();
    User user = new User ();
    user.setUserId("sample-user-id");
    // 先保存User实体
    userRepo.save(user);
    npe.setUser(user);
    userPreferenceRepo.save(npe);
}

额外注意事项 #

• 修正代码笔误：ne改为npe，sampele改为sample，避免空指针异常
• 检查Linux环境下MySQL大小写敏感配置，确保实体列名与数据库列名大小写完全一致

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