javaspring-boothibernatejpa

java.sql.SQLSyntaxErrorException: Unknown column 'u1_0.UserId' in field list' after springboot upgrade to 3

After spring boot update to version 3, getting the error which was working before while trying to save add/update UserPreferences entity to MySQl DB. Usually, userid in the query is identified as 'u1_0.User_Id', but as per the error in DB the connector is checking for the column 'u1_0.UserId' which is strange. And the column name and entity name match too.

Update: The save query is Woking fine while editing an existing userid. The error happens only for an insert. While inspecting the save() function, for adding a new row, the error is happening for the SELECT query as mentioned below

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

Versions used

Service Class

@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);
    }
}

Repo class

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

UserPreferences Entity

@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 Entity

@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;
}

enter image description here

Solution

  • Adding hibernate.physical_naming_strategy as CamelCaseToUnderscoresNamingStrategy (which was introduced in Hibernate 5.6.0.Final)worked for me without any changes in entity/db. The same implementation was working fine without any custom naming strategy prior Hibernate version5.6.0.

    As I am using Entity manager factory for handling multiple databases, modified the code adding the physical naming strategy as hibernate property to the exisiting logic.

    @Primary
    @Bean(name = "sampleEntityManagerFactory")
    LocalContainerEntityManagerFactoryBean sampleEntityManagerFactory(EntityManagerFactoryBuilder builder,
                                                                            @Qualifier("sampleDataSource") DataSource dataSource) {
        Map<String, Object> properties = new HashMap<>();
        properties.put("hibernate.physical_naming_strategy", CamelCaseToUnderscoresNamingStrategy.class.getName());
        return builder
                .dataSource(dataSource)
                .packages("sample.package")
                .properties(properties)
                .persistenceUnit("samplePU")
                .build();
    }

    Another reccomented solution is to add config in application.yml (I didnt try though)

    spring:
  jpa:
    properties:
      hibernate.physical_naming_strategy: org.hibernate.boot.model.naming.CamelCaseToUnderscoresNamingStrategy