即使数据库不存在，也可以使用Hibernate即时创建Postgres数据库 [英] Create Postgres database on the fly using Hibernate even if the DB doesn't exist

问题描述

使用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; 
 $ b @Configuration 
 @PropertySource（file：C：/springconfig/qpmlib.properties）
 @ComponentScan（basePackages = {test.postgressql}）
 @EnableJpaRepositories（basePackages = {test.postgressql}）
 @EnableTransactionManagement 
公共抽象类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））; 
返回dmds; 
 
 
 private static String getNewDBName（）{
 returnH2DBTest; 
 
 
 $ Be $ b $ 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（）; 
退货工厂; 
 
 $ b $Be 
 public PlatformTransactionManager transactionManager（）{
 JpaTransactionManager txm = new JpaTransactionManager（
 entityManagerFactory（）。getObject（））; 
返回txm; 
 
 $ b $Bean 
 public JpaVendorAdapter jpaAdapter（）{
 HibernateJpaVendorAdapter adapter = new HibernateJpaVendorAdapter（）; 
 adapter.setDatabase（Database.H2）; 
 adapter.setGenerateDdl（true）; 
 adapter.setShowSql（true）; 
返回适配器; 
 
 
 $Be 
 public HibernateExceptionTranslator exceptionTranslator（）{
 return new HibernateExceptionTranslator（）; 
 
 
公共属性jpaProperties（）{
属性properties = new Properties（）; 
 properties.put（Environment.SHOW_SQL，true）; 
 properties.put（Environment.HBM2DDL_AUTO，create）; 
 properties.put（Environment.DIALECT，org.hibernate.dialect.H2Dialect）; 
返回属性; 
} 
 
} 
  
使用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; 
 $ b @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））; 
返回dmds; 
} 
 
 private static String getNewDBName（）{
 returnPostgresDBTest; 
 
 
 $ Be $ b $ 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（）; 
退货工厂; 
 
 $ b $Be 
 public PlatformTransactionManager transactionManager（）{
 JpaTransactionManager txm = new JpaTransactionManager（
 entityManagerFactory（）。getObject（））; 
返回txm; 
 
 $ b $Bean 
 public JpaVendorAdapter jpaAdapter（）{
 HibernateJpaVendorAdapter adapter = new HibernateJpaVendorAdapter（）; 
 adapter.setDatabase（Database.POSTGRESQL）; 
 adapter.setGenerateDdl（true）; 
 adapter.setShowSql（true）; 
返回适配器; 
 
 
 $Be 
 public HibernateExceptionTranslator exceptionTranslator（）{
 return new HibernateExceptionTranslator（）; 
 
 
公共属性jpaProperties（）{
属性properties = new Properties（）; 
 properties.put（Environment.SHOW_SQL，true）; 
 properties.put（Environment.HBM2DDL_AUTO，create）; 
 properties.put（Environment.DIALECT，org.hibernate.dialect.PostgreSQL9Dialect）; 
返回属性; 
} 
} 
  
 
 
解决方案
   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屋！