如果它不存在使用Hibernate，即时创建Postgres数据库 [英] Create Postgres database on the fly, if it doesn't exists using Hibernate

问题描述

使用H2，

  Environment.HBM2DDL_AUTO，create
  
 
 
 
但是，在Postgres中，非现有的数据库不会被创建，因此抛出一个例如DB不存在的异常。有没有办法配置Postgres按需创建一个非现有的数据库？ 
 
 
 
以下配置文件可用于重现问题：
 
 
 
使用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环境环境
 
 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（）{
 returnH2DBTest; 
} 
 
 @Bean 
 public LocalContainerEntityManagerFactoryBean entityManagerFactory（）{
 LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean（）; 
 factory.setDataSource（digitalSource（））; 
 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属性jpaProperties（）{
属性properties = new Properties（）; 
 properties.put（Environment.SHOW_SQL，true）; 
 properties.put（Environment.HBM2DDL_AUTO，create）; 
 properties.put（Environment.DIALECT，org.hibernate.dialect.H2Dialect）; 
 return property; 
} 
 
} 
  
使用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环境环境
 
 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（）{
 returnPostgresDBTest; 
} 
 
 @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属性jpaProperties（）{
属性properties = new Properties（）; 
 properties.put（Environment.SHOW_SQL，true）; 
 properties.put（Environment.HBM2DDL_AUTO，create）; 
 properties.put（Environment.DIALECT，org.hibernate.dialect.PostgreSQL9Dialect）; 
 return property; 
} 
} 
  
 
 
解决方案
   hbmddl 工具只能为现有模式创建表，并且不能为您创建模式。在运行该工具之前，数据库必须存在。这是因为数据库必须由管理员创建，并且应该分配所有者。
 
 
 
因为在大多数应用程序中，应用程序只能访问具有限制权限的数据库角色，没有必要这样的功能。
 
 
 
 PostgreSQL不支持从连接URL即时创建数据库。您可以在应用程序启动时使用管理帐户和默认Postgres数据库连接到数据库服务器，添加 InitializingBean ，如果应用程序数据库不存在，则发出CREATE DATABASE。 / p> 
Using H2, 
Environment.HBM2DDL_AUTO, "create"
creates the database if it does not exist yet.

However, in Postgres, the non existing DB is not created and thus an exception which says something like "DB does not exist" is thrown. 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;
    }

}
Fails using 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;
    }
}

解决方案
The hbmddl tool can only create tables for an existing schema and it can't create a schema for you. The database must exist prior to running the tool. That's because a database must be created by an administrator and it should get an owner assigned.

Because in most applications, the application can only access a database role with restrictive privileges, there's no need for such a feature.

PostgreSQL doesn't support creating the database on-the-fly, from the connection URL. You can add an InitializingBean at application startup connecting to the database server using the administration account and the default Postgres database and issue a CREATE DATABASE if the application DB doesn't exist.

                        
这篇关于如果它不存在使用Hibernate，即时创建Postgres数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！