java - Postgresql JPA Hibernate 创建数据库

Question

我在使用 Postgresql 和 JPA Hibernate 时遇到问题。

我的代码:

@Transactional
public void createDatabase(User user) {
    Query q = em.createNativeQuery("CREATE USER \"" + user.getEmail()
            + "\" WITH PASSWORD '" + user.getPasswordHash() + "' ;");
    q.executeUpdate();
    Query q1 = em.createNativeQuery("CREATE DATABASE " + user.getDb() + ";");
    q1.executeUpdate();
    Query q2 = em.createNativeQuery("GRANT ALL PRIVILEGES ON " + user.getDb()
            + " TO '" + user.getEmail() + "';");
    q2.executeUpdate();
}

我遇到以下错误。

Hibernate: CREATE USER "test" WITH PASSWORD 'test' ;
Hibernate: CREATE DATABASE test;
2015-05-08 15:15:49.531  WARN 1952 --- [nio-8080-exec-6] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 25001
2015-05-08 15:15:49.531 ERROR 1952 --- [nio-8080-exec-6] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERREUR: CREATE DATABASE cannot be created in a transaction bloc
2015-05-08 15:15:49.545 ERROR 1952 --- [nio-8080-exec-6] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute statement] with root cause

org.postgresql.util.PSQLException: ERREUR: CREATE DATABASE cannot be created in a transaction bloc

如果我删除事务注释，我会收到以下错误:

javax.persistence.TransactionRequiredException: Executing an update/delete query
    at org.hibernate.jpa.spi.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:71)
    at com.esisa.pfe.business.DefaultUserService.createDatabase(DefaultUserService.java:56)
    at com.esisa.pfe.controllers.ClientController.addAbonnement(ClientController.java:69)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

Answer 1

JPA EntityManager不是创建新数据库或用户的对象。请参阅文档实体管理器的用途。如果您想从 java 创建一个新数据库，您可以使用简单的 JDBC 来完成此操作 - 这里有一些示例代码:

// without db name
public static final String HOST = "jdbc:postgresql://localhost:5432/";

Statement stmt = conn.createStatement();
stmt.executeUpdate("CREATE DATABASE JavaDB");
stmt.executeUpdate("CREATE USER java_user WITH PASSWORD  \'java\'");
// ...