CODEWITHSUNDEEP
javaspringhibernatepostgresqljpa
Santo
Santo

Reputation: 182

Postgresql JPA Hibernate create Database

I'm having trouble with Postgresql with JPA Hibernate.

My code : 

@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();
}

I'm having the following Error.

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

If i delete the Transaction Annotation i get the following error :

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)

Upvotes: 0

Views: 1821

Answers (2)

Neil Stockton
Neil Stockton

Reputation: 11531

JPA 2.1 allows you to create the datastore tables etc (and optionally schema too depending on JPA implementation and datastore) when creating the EntityManagerFactory.

Upvotes: 2

swinkler
swinkler

Reputation: 1701

The JPA EntityManager is not the object to create new databases or users. see documentation what for entity manager is used. If you want to create a new database from java you can do this with simple JDBC - here some example code:

// 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\'");
// ...

Upvotes: 2

Related Questions