Why Hibernate schema-validation fail to validate schema

Question

The same question has been asked number of times but none of them has solution to my problem.

I have created a hibernate + H2 + Sprinvg mvc project. I am using java based configuration. I have the following beans related to Datasource, SessionFactory and TransactionManager

@Configuration
@ComponentScan(basePackages="org.testpackage")
@EnableWebMvc
@EnableTransactionManagement

public class MyConfiguration extends WebMvcConfigurationSupport {


@Bean(initMethod="start",destroyMethod="stop")
public org.h2.tools.Server h2WebConsonleServer () throws SQLException {
  return org.h2.tools.Server.createWebServer("-web","-webAllowOthers","- 
  webDaemon","-webPort", "8082");
}

@Bean
public DataSource getDataSource() {     
    return new EmbeddedDatabaseBuilder()
            .generateUniqueName(false)
            .setName("mytestdb")
            .setType(EmbeddedDatabaseType.H2)
            .addDefaultScripts()
            .setScriptEncoding("UTF-8")
            .ignoreFailedDrops(true)
            .build();
}

@Bean
public LocalSessionFactoryBean sessionFactory() {
    final LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
    sessionFactory.setDataSource(getDataSource());
    sessionFactory.setHibernateProperties(hibernateProperties());
    sessionFactory.setPackagesToScan(new String[] {"org.testpackage.model"});

    return sessionFactory;
}

@Bean
@Autowired
public HibernateTransactionManager transactionManager(final SessionFactory sessionFactory) {
    final HibernateTransactionManager txManager = new HibernateTransactionManager();
    txManager.setSessionFactory(sessionFactory);

    return txManager;
}

final Properties hibernateProperties() {
    final Properties hibernateProperties = new Properties();
    hibernateProperties.setProperty("hibernate.hbm2ddl.auto", "validate");
    hibernateProperties.setProperty("hibernate.show_sql", "true");

    return hibernateProperties;
}
  //Some more beans  
}

I have the following Entity class

@Entity
@Table(name = "MYTESTDB.TEST_TABLE")
public class User{

@Id
@GeneratedValue
@Column(name = "id")
private int id;

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

@Column(name = "email", unique = true)
private String email;


public User(int id, String name, String email) {
    super();
    this.id = id;
    this.name = name;
    this.email = email;
}

public User() {
}
//Getters and Setters
}

in the DataSource bean I am using addDefaultScripts() and I have 2 sql scripts which create Schema in H2 and insert some predefined value. Which are as follows.

//schema.sql Script
CREATE SCHEMA `MYTESTDB` ;
Drop TABLE IF EXISTS MYTESTDBDB.TEST_TABLE;
CREATE TABLE MYTESTDB.TEST_TABLE ( 
   ID INT NOT NULL PRIMARY KEY, 
   NAME VARCHAR(50) NOT NULL, 
   EMAIL VARCHAR(20) NOT NULL, 
   );
CREATE UNIQUE INDEX ON MYTESTDB.TEST_TABLE (EMAIL)

//data.sql Script
INSERT INTO MYTESTDB.TEST_TABLE(id, name, email) 
VALUES ('1', 'Tom', '[email protected]');

If I use hibernate hbm2ddl.auto property value "create" everything works fine, hibernate drops the table and recreate it. I have verified it from the web browser. But if I use "validate" property I get the following error exception

Error creating bean with name 'sessionFactory' defined in org.testPackage.configuration.MYConfiguration: Invocation of init method    failed; nested exception is org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: missing table [MYTESTDB.TEST_TABLE]

Can anyone please help me to find out the problem?

What if you use @Table(schema = "MYTESTDB", name = "TEST_TABLE")? — Slaw

Daim Daim · Accepted Answer · 2018-11-26T12:18:20

Its working now. With the help of @Slaw I am able to use "Validate" property. Use @Table(schema = "MYTESTDB", name = "TEST_TABLE") instead of @Table(name = "MYTESTDB.TEST_TABLE"). But I had to change a bit more in user entity class. Instead of @GeneratedValue annotation I added @GeneratedValue(strategy = GenerationType.IDENTITY). Now everything is working fine.

Thanks @Slaw and @Mykhailo for your valuable time.

Why Hibernate schema-validation fail to validate schema

1 Answers