您的位置: 首页  >  技术问答  >  即使数据库不存在，也可以使用Hibernate动态创建PostgreSQL数据库

即使数据库不存在，也可以使用Hibernate动态创建PostgreSQL数据库

分类: 技术问答 • 2022-04-27 14:05:53
问题描述：

使用H2，

Environment.HBM2DDL_AUTO, "create"

如果数据库尚不存在则创建数据库。

creates the database if it does not exist yet.

但是，在Postgres中，不存在的数据库没有被创建，因此抛出了一个例外DB不存在的异常。有没有办法配置Postgres按需创建一个不存在的数据库？

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:

使用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;
    }

}

使用Postgres失败

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


答

hbmddl 工具只能为现有架构创建表，并且无法为您创建架构。在运行该工具之前，数据库必须存在。这是因为数据库必须由管理员创建，并且应该分配所有者。

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不支持从连接URL动态创建数据库。您可以在应用程序启动时使用管理帐户和默认PostgreSQL数据库添加 InitializingBean ，并在应用程序数据库不存在时发出CREATE DATABASE。或者，只需使用 Flyway 即可。

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 PostgreSQL database and issue a CREATE DATABASE if the application DB doesn't exist. Or, just use Flyway for that.

相关推荐