Create Postgres database on the fly using Hibernate even if the database doesn't exist

Using H2,

Environment.HBM2DDL_AUTO, "create"

      

        
        
        
      

    

creates a database if it doesn't already exist.

However, Postgres does not create a database that does not exist, and thus throws an exception that says something like "the database does not exist". Is there a way to configure Postgres to create a non-existing database on demand?

The following configuration files can be used to reproduce the problem:

Works fine using H2:

package test.postgressql;

import java.util.Properties;

import javax.sql.DataSource;

import org.hibernate.cfg.Environment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.hibernate4.HibernateExceptionTranslator;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.Database;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@PropertySource("file:C:/springconfig/qpmlib.properties")
@ComponentScan(basePackages = {"test.postgressql"})
@EnableJpaRepositories(basePackages = { "test.postgressql" })
@EnableTransactionManagement
public abstract class H2DBConfig {

    @Autowired
    org.springframework.core.env.Environment env;

    public static final String DB_NAME = getNewDBName();

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dmds = new DriverManagerDataSource();
        dmds.setDriverClassName("org.h2.Driver");
        dmds.setUrl("jdbc:h2:tcp://localhost/~/" + DB_NAME );
        dmds.setUsername(env.getProperty("h2user"));
        dmds.setPassword(env.getProperty("h2pw"));
        return dmds;
    }

    private static String getNewDBName() {
        return "H2DBTest";
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
        LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
        factory.setDataSource(dataSource());
        factory.setPersistenceUnitName(DB_NAME);
        factory.setPackagesToScan("test.postgressql");
        factory.setJpaVendorAdapter(jpaAdapter());
        factory.setJpaProperties(jpaProperties());
        factory.afterPropertiesSet();
        return factory;
    }

    @Bean
    public PlatformTransactionManager transactionManager() {
        JpaTransactionManager txm = new JpaTransactionManager(
                entityManagerFactory().getObject());
        return txm;
    }

    @Bean
    public JpaVendorAdapter jpaAdapter() {
        HibernateJpaVendorAdapter adapter = new HibernateJpaVendorAdapter();
        adapter.setDatabase(Database.H2);
        adapter.setGenerateDdl(true);
        adapter.setShowSql(true);
        return adapter;
    }

    @Bean
    public HibernateExceptionTranslator exceptionTranslator() {
        return new HibernateExceptionTranslator();
    }

    public Properties jpaProperties() {
        Properties properties = new Properties();
        properties.put(Environment.SHOW_SQL, "true");
        properties.put(Environment.HBM2DDL_AUTO, "create");
        properties.put(Environment.DIALECT,"org.hibernate.dialect.H2Dialect");
        return properties;
    }

}

      

        
        
        
      

    

Failed to use Postgres

package test.postgressql;

import java.util.Properties;

import javax.sql.DataSource;

import org.hibernate.cfg.Environment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.hibernate4.HibernateExceptionTranslator;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.Database;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@PropertySource("file:C:/springconfig/qpmlib.properties")
@ComponentScan(basePackages = {"test.postgressql"})
@EnableJpaRepositories(basePackages = { "test.postgressql" })
@EnableTransactionManagement
public abstract class PGDBConfig {

    @Autowired
    org.springframework.core.env.Environment env;

    public static final String DB_NAME = getNewDBName();

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dmds = new DriverManagerDataSource();
        dmds.setDriverClassName("org.postgresql.Driver");
        dmds.setUrl("jdbc:postgresql://localhost:5432/" + DB_NAME);
        dmds.setUsername(env.getProperty("postgresuser"));
        dmds.setPassword(env.getProperty("postgrespw"));
        return dmds;
    }

    private static String getNewDBName() {
        return "PostgresDBTest";
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
        LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
        factory.setDataSource(dataSource());
        factory.setPersistenceUnitName(DB_NAME);
        factory.setPackagesToScan("test.postgressql");
        factory.setJpaVendorAdapter(jpaAdapter());
        factory.setJpaProperties(jpaProperties());
        factory.afterPropertiesSet();
        return factory;
    }

    @Bean
    public PlatformTransactionManager transactionManager() {
        JpaTransactionManager txm = new JpaTransactionManager(
                entityManagerFactory().getObject());
        return txm;
    }

    @Bean
    public JpaVendorAdapter jpaAdapter() {
        HibernateJpaVendorAdapter adapter = new HibernateJpaVendorAdapter();
        adapter.setDatabase(Database.POSTGRESQL);
        adapter.setGenerateDdl(true);
        adapter.setShowSql(true);
        return adapter;
    }

    @Bean
    public HibernateExceptionTranslator exceptionTranslator() {
        return new HibernateExceptionTranslator();
    }

    public Properties jpaProperties() {
        Properties properties = new Properties();
        properties.put(Environment.SHOW_SQL, "true");
        properties.put(Environment.HBM2DDL_AUTO, "create");
        properties.put(Environment.DIALECT,"org.hibernate.dialect.PostgreSQL9Dialect");
        return properties;
    }
}