current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 0 down vote favorite

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)
share|improve this question
up vote 1 down vote accepted

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\'");
// ...
share|improve this answer
    
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. – Zouazou May 8 '15 at 15:00
1  
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/… – swinkler May 8 '15 at 15:04
    
@Zouazou: you need to turn autocommit on before you can run a create database statement. – a_horse_with_no_name May 10 '15 at 8:29
up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.