Postgresql JPA Hibernate create Database

Question

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)

swinkler · Accepted Answer · 2015-05-08 14:56:15Z

2

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

answered May 8, 2015 at 14:56

swinkler

1,71110 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Santo Over a year ago

I already managed to make this work but under MySQL, and now that i switched to Postgresql it doesn't work anymore, and i'm working with JPA Hibernate under Springboot, so there is no JDBC.

swinkler Over a year ago

The code mentioned is tested locally, so it should work. As I said JPA/Hibernate is a framework assuming the DB is in place, but not the API to create new databases and users. JDBC is part of the JDK so you can use it anyhow. Spring also has jdbc parts to be used - docs.spring.io/spring-boot/docs/current/reference/html/…

user330315 Over a year ago

@Zouazou: you need to turn autocommit on before you can run a create database statement.

Neil Stockton · Accepted Answer · 2015-05-10 08:22:52Z

2

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.

answered May 10, 2015 at 8:22

Neil Stockton

11.6k3 gold badges36 silver badges29 bronze badges

Collectives™ on Stack Overflow

Postgresql JPA Hibernate create Database

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related