Generating sql tables by means of Spring automatically using predefined scrypt file

Question

There is a functionality for embedded databases (like H2) which provides us with a possibility to automatically generate sql tables using a predefined scrypt file.

Bean example:

@Bean
    public DataSource dataSource() {
        EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
        return builder
                .setType(EmbeddedDatabaseType.H2)
                .addScript("embeddedDB.sql")
                .build();
    }

Scrypt file example:

CREATE TABLE Users
(
  id                INT(11)      NOT NULL AUTO_INCREMENT,
  username          VARCHAR(50)  NOT NULL,
  email             VARCHAR(255) NOT NULL,
  password          VARCHAR(255) NOT NULL,
  first_name        VARCHAR(50)  NOT NULL,
  last_name         VARCHAR(50)  NOT NULL,
  photo             BLOB,
  registration_date DATE         NOT NULL,
  role              VARCHAR(50)  NOT NULL,
  PRIMARY KEY (id)
);

Some time ago a saw on the web a similar solution to configure a bean that automatically generates SQL tables (when spring context starts) in case of MySQL data source. That solution didn't include JPA/Hibernate. Unfortunately, I can't find this example anymore... So, my question is how to implement automatical SQL tables generation by Spring in case of MySQL data source?

Answer 1

Property file in spring boot

        datasource.ranking.hibernate.hbm2ddl.method=create

Create will create new tables update will leave tables. The tables will be created based on your class model Then set the entity manager properties in your configuration class

  @Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory() throws NamingException {
    LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();
    factoryBean.setDataSource(dataSource());
    factoryBean.setPackagesToScan(new String[] { "com.package" });
    factoryBean.setJpaVendorAdapter(jpaVendorAdapter());
    factoryBean.setJpaProperties(jpaProperties());
    return factoryBean;

}

private Properties jpaProperties() {
        Properties properties = new Properties();
        properties.put("hibernate.dialect", environment.getRequiredProperty("datasource.ranking.hibernate.dialect"));
    properties.put("hibernate.hbm2ddl.auto", environment.getRequiredProperty("datasource.ranking.hibernate.hbm2ddl.method"));
    properties.put("hibernate.show_sql", environment.getRequiredProperty("datasource.ranking.hibernate.show_sql"));
    properties.put("hibernate.format_sql", environment.getRequiredProperty("datasource.ranking.hibernate.format_sql"));
   /* if(StringUtils.isNotEmpty(environment.getRequiredProperty("datasource.ranking.defaultSchema"))){
        properties.put("hibernate.default_schema", environment.getRequiredProperty("datasource.ranking.defaultSchema"));
    }*/
    return properties;

}