Hibernate: Schema-validation: missing table

Question

I try to read from an existing table in a MSSQL Server 2016 Database. If I validate (hbm2ddl.auto --> validate) I get an Exception "Schema-validation: missing table".

I compared the generated Hibernate SQL Code (if I switch from validate to update with the SQL Code which I can generate in MSSQL Server itself. It looks equal:

-- generated SQL from Hibernate 
create table Artikel (
       Artnr int not null,
        Artgruppe CHAR(5),
        Bezeichnung VARCHAR(30) not null,
        EPreis money,
        primary key (Artnr)
    )

-- generated SQL from MSSQL Server 2016
CREATE TABLE [dbo].[Artikel](
    [Artnr] [int] NOT NULL,
    [Bezeichnung] [varchar](30) NOT NULL,
    [EPreis] [money] NULL,
    [Artgruppe] [char](5) NULL,
PRIMARY KEY CLUSTERED 
(
    [Artnr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

The Java-Model is:

@Entity
@Table(name = "Artikel")
public class Article {
    @Id
    @Column(name = "Artnr", nullable=true)
    private int article_id;

    @Column(name = "Bezeichnung", columnDefinition="VARCHAR(30)", nullable=false)
    private String description;


    @Column(name = "EPreis", columnDefinition = "money", nullable=true)
    private BigDecimal price;

    @Column(name = "Artgruppe", columnDefinition="CHAR(5)")
    private String article_group;


    // getters and setters
    // ... not shown here
}

So what is the problem? Maybe it has something to do with the money type?

Thanks and regards

Vorashil Farzaliyev · Answer 1 · 2020-05-25T21:59:32.500

2 issues here: Case sensitivity in table/column names and Naming strategy.

Be careful with what the table name is in your error message, and pay attention to upper and lower case letters. When I faced this problem , my assumption was that table names were case insensitive. At least that's the case when I write an sql query (I was using MSSQL in my case) . But apparently, JPA does care about case sensitivity.

About the naming strategy, if you don't have any strategy specified, JPA is transforming table, column names with some upper case letters (e.g User_Detail) to all lower-case and underscore separated names (i.e dbo.user_detail). If you are providing you table and column names in annotations, and don't want JPA to transform them, then add these 2 lines to your application.properties:

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

You are right, it helped me to narrow issue search space. I added an answer for `CamelCaseToUnderscoresNamingStrategy` below. — gavenkoa, Jan 10 '22 at 21:57

score 3 · Answer 2 · answered Aug 14 '18 at 10:46

Finally I solved the problem. I replaced

@Table(name = "Artikel")
public class Article {

against

@Table(name = "Artikel", schema="dbo")
public class Article {

But dbo is the default schema for the user so this should work without schema="dbo". Maybe this is some kind of bug? I someone has more information please let me know...

score 2 · Answer 3 · answered Oct 24 '19 at 08:42

2

I added this in my properties and it's working:

spring.jpa.properties.hibernate.default_schema=dbo

answered Oct 24 '19 at 08:42

NikNik

2,191
2
15
34

score 0 · Answer 4 · answered Jan 10 '22 at 21:54

For those who use org.hibernate.boot.model.naming.CamelCaseToUnderscoresNamingStrategy as spring.jpa.hibernate.naming.physical-strategy: this implementation lowers the case of every identifier, even from @Table(name="")!

You can disable this behavior by:

public class CustomCamelCaseToUnderscoresNamingStrategy extends CamelCaseToUnderscoresNamingStrategy {
    @Override
    protected boolean isCaseInsensitive(JdbcEnvironment jdbcEnvironment) {
        return false;
    }
}

or even define own behavior:

public class CustomCamelCaseToUnderscoresNamingStrategy extends CamelCaseToUnderscoresNamingStrategy {

    private static final Pattern LOWER_RE = Pattern.compile("[a-z]");

    /**
     * Lowercase only if there is lowercase (like if entity name is "User" we want a table with name "user").
     * Do not change the name if it is all uppercase.
     */
    @Override
    protected Identifier getIdentifier(String name, boolean quoted, JdbcEnvironment jdbcEnvironment) {
        if (LOWER_RE.matcher(name).find()) {
            name = name.toLowerCase(Locale.ROOT);
        }
        return new Identifier( name, quoted );
    }
}

score -1 · Answer 5 · answered Jan 06 '21 at 18:50

-1

In case of SQLServer is recomended to map the user to the dedault database to avoid problems with the schema , check this:

https://stackoverflow.com/a/65601637/7159247

answered Jan 06 '21 at 18:50

Jhoan Mauricio

14
3

As this link to another thread, it is better as a comment and not as an answer – DanielM Jan 06 '21 at 19:53

Hibernate: Schema-validation: missing table

5 Answers5

Linked