Schema-validation: missing column [id] in table

Question

In my spring boot application i am trying to map a sql-server table to entity.

@Entity(name="Employee")
@Table(name = "`Personels`")
public class Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="`Id`")
    private Long id;

    @Column(name="`Name`")
    private String name;

    @Column(name="`Surname`")
    private String surname;
...
}

The columns in the table are starting with uppercase and thats why i use @Column annotation for this, but in the error message it says that hibernate couldn't find the column "id"(not Id).

Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: missing column [id] in table [personels]
    at org.hibernate.tool.schema.internal.SchemaValidatorImpl.validateTable(SchemaValidatorImpl.java:85) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]

Here is my application.properties file

spring.datasource.url=jdbc:sqlserver://xxx.xxx.xx.xx;databaseName=xxx
spring.datasource.username=xxx
spring.datasource.password=xxx
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect
spring.jpa.hibernate.ddl-auto = validate
spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyHbmImpl
spring.jpa.hibernate.naming.physical-strategy=org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy

And here is the pom:

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.6.RELEASE</version>
        <relativePath /> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <start-class>com.xxx.yyy.Application</start-class>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
        </dependency>

    </dependencies>

Spring+Hibernate has some naming factory that ignores what you specify in annotations like `@Column`; any normal JPA provider should simply take any column name specified like that and not change it, but not with what you're using. This breaks the JPA spec, so you have to delve around to find out what this setting is. There are many posts on Stackoverflow about it. — Neil Stockton, Aug 13 '17 at 12:18
I would have a look at spring.jpa.hibernate.naming.physical-strategy and spring.jpa.hibernate.naming.implicit-strategy. Why are you using `` in column annotation? — kimy82, Aug 13 '17 at 15:37
Try not to validate for first time but to create. Be careful though since you will lose your data stored in your db. Have you created your db already with lower case first letters? — Ioannis Mitrolios, Nov 28 '17 at 11:48
I have a similar situation, where database is created with wrong naming convection. I used @Column(name="currentDate") = but hibernate looks for current_date in the database. If I do not use validate as a option and keep it auto - it fails when I try to read the data — Abdeali Chandanwala, May 02 '18 at 05:48

score 0 · Answer 1 · answered Sep 09 '18 at 15:14

0

solve this problem by changing the line "hibernate.hbm2ddl.auto:validate" in my configuration.yaml file from "validate" to "create" and now it looks like this :

hibernate.hbm2ddl.auto:create

p.s : if you plan on keeping input objects after running one time you have to change it back to "validate".

this will help: Hibernate hbm2ddl.auto possible values and what they do?

answered Sep 09 '18 at 15:14

narcis dpr

939
2
12
32

this may create a new column in database that we don't need and we wont be awaire of that – Seyed Ali Roshan Nov 23 '18 at 21:04
Or remove #spring.jpa.hibernate.ddl-auto=validate – BeHappy May 27 '21 at 06:46

score -2 · Answer 2 · answered Nov 28 '17 at 07:15

try

@Entity(name="Employee")
@Table(name = "`personels`")
public class Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="`id`")
    private Long id;

    @Column(name="`name`")
    private String name;

    @Column(name="`surname`")
    private String surname;
}

All column names are lowercase.

Schema-validation: missing column [id] in table

2 Answers2