Application creates the database, but doesn't create the tables

Question

In my spring project, I am looking for a way to create the database which will be used by my application from java code to avoid the final user having to create it manually.

I have this service class where I want to implement this feature:

@Service
public class InstallService {

    public boolean create_database(String maquina, String usuario, String senha) {
        return false;
    }

    public boolean create_user(String usuario, String senha, String email) {
        return false;
    }
}

Searching in the web, I find this topic here in stackoverflow with a suggestion to do this for mysql:

creating a database in mysql from java

In my application, the database is created through one of steps in a wizard, where the user informs the machine where the DBMS is running, besides the username and password who will own the database.

Plus, I am using Hibernate, then the jdbc driver and db name already is stored in a file called persistence.properties, which has the following content:

jdbc.Classname=org.postgresql.Driver
jdbc.url=
jdbc.user=
jdbc.pass=
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
hibernate.show_sql=false
hibernate.hbm2ddl.auto=update

After the user informs the machine, username and password data, I want to save this data in this file too.

Can anyone point to the direction how to modify the example from the other topic to accomplish what I want?

ps.: the project is this:

https://github.com/klebermo/webapp_horario_livre

UPDATE

My current code (still doesn't create the database):

@Service
public class InstallService {

    @Autowired
    UsuarioHome usuario;

    @Autowired
    AutorizacaoHome autorizacao;

    public boolean create_database(String maquina, String usuario, String senha) {
        Configuration config = new Configuration();
        config.setProperty("jdbc.Classname", "org.postgresql.Driver");
        config.setProperty("jdbc.url", "jdbc:postgresql://"+maquina+"/horario?charSet=LATIN1");
        config.setProperty("jdbc.user", usuario);
        config.setProperty("jdbc.pass", senha);
        config.setProperty("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
        config.setProperty("hibernate.show_sql", "false");
        config.setProperty("hibernate.hbm2ddl.auto", "create");

        SchemaExport schema = new SchemaExport(config);
        schema.create(true, true);

        Properties properties = new Properties();
        properties.setProperty("jdbc.Classname", "org.postgresql.Driver");
        properties.setProperty("jdbc.url", "jdbc:postgresql://"+maquina+"/horario?charSet=LATIN1");
        properties.setProperty("jdbc.user", usuario);
        properties.setProperty("jdbc.pass", senha);
        properties.setProperty("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
        properties.setProperty("hibernate.show_sql", "false");
        properties.setProperty("hibernate.hbm2ddl.auto", "validate");

        try {
            File file = new File("classpath:database.properties");
            FileOutputStream fileOut = new FileOutputStream(file);
            properties.store(fileOut, "propriedades");
            fileOut.close();
        } catch(FileNotFoundException e) {
            e.printStackTrace();
        } catch(IOException e) {
            e.printStackTrace();
        }

        return autorizacao.persist(new Autorizacao("permissao_teste"));
    }

    public boolean create_user(String login, String senha, String pnome, String unome) {
        Usuario novo = new Usuario(login, senha, pnome, unome);
        novo.setAutorizacao(autorizacao.findALL());
        return usuario.persist(novo);
    }
}

UPDATE 2

My current code (creates the database, but doesn't create the tables):

@Service
public class InstallService {

    @Autowired
    private UsuarioHome usuario;

    @Autowired
    private AutorizacaoHome autorizacao;

    public boolean create_database(String maquina, String usuario, String senha) {
        try {
            Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("ClassNotFoundException");
        }
        try {
            String url = "jdbc:postgresql://"+maquina+"/postgres";
            System.out.println("url = "+url);
            System.out.println("usuario = "+usuario);
            System.out.println("senha = "+senha);
            Connection conn = DriverManager.getConnection(url,usuario,senha);
            Statement stmt = conn.createStatement();

            ResultSet rs = stmt.executeQuery("SELECT count(*) FROM pg_catalog.pg_database WHERE datname = 'horario';");
            rs.next();
            int counter  = rs.getInt(1);
            System.out.println("counter = "+counter);
            if(counter > 0) {
                System.out.println("calling_create_tables");
                create_tables(maquina, usuario, senha);
                rs.close();
                stmt.close();
                conn.close();
                return true;
            }

            int result = stmt.executeUpdate("CREATE DATABASE horario WITH OWNER "+usuario+";");
            System.out.println("result = "+result);
            if(result > 0) {
                System.out.println("calling_create_tables");
                create_tables(maquina, usuario, senha);
                rs.close();
                stmt.close();
                conn.close();
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("SQLException");
            return false;
        }
        System.out.println("retornando false");
        return false;
    }

    public void create_tables(String maquina, String usuario, String senha) {
        System.out.println("create_tables");
        create_properties(maquina, usuario, senha);

        Configuration config = new Configuration();
        Properties props = new Properties();
        FileInputStream fos;
        try {
            fos = new FileInputStream( "database.properties" );
            props.load(fos);
            fos.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        config.setProperties(props);

        try {
            String url = props.getProperty("jdbc.url");
            Connection conn = DriverManager.getConnection(url,usuario,senha);
            SchemaExport schema = new SchemaExport(config, conn);
            schema.create(true, true);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        insert_default_values();
    }

    public void insert_default_values() {
        System.out.println("insert_default_values");
        String [] autorizacoes = {"cad_evento", "lista_evento", "cad_horario", "lista_horario", "cad_usuario", "lista_usuario", "cad_campo", "cad_tipo", "cad_permissao"};
        for(int i=0; i<autorizacoes.length; i++) {
            autorizacao.persist(new Autorizacao(autorizacoes[i]));
        }
    }

    public void create_properties(String maquina, String usuario, String senha) {
        System.out.println("create_properties");
        Properties props = new Properties();

        props.setProperty("jdbc.Classname", "org.postgresql.Driver");
        props.setProperty("jdbc.url", "jdbc:postgresql://"+maquina+"/horario" );
        props.setProperty("jdbc.user", usuario );
        props.setProperty("jdbc.pass", senha );

        props.setProperty("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
        props.setProperty("hibernate.show_sql", "false");
        props.setProperty("hibernate.hbm2ddl.auto", "update");

        FileOutputStream fos;
        try {
            fos = new FileOutputStream( "database.properties" );
            props.store( fos, "propriedades" );
            fos.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public boolean create_user(String login, String senha, String pnome, String unome) {
        System.out.println("create_user");
        Usuario novo = new Usuario(login, senha, pnome, unome);

        if(usuario.persist(novo))
            novo.setAutorizacao(autorizacao.findALL());
        else
            return false;

        if(usuario.merge(novo) != null)
            return true;
        else
            return false;
    }
}

Answer 1

After you've set up your configuration but before you create your session, the following lines will create your database for you:

SchemaExport schema = new SchemaExport(config);
schema.create(true, true);

Alternatively, have the hibernate.hbm2ddl.auto property set to create-drop or create. The latter creates the database tables and the former drops them after you exit.

UPDATE

Your edit is not going to work -- see any hibernate tutorial for how to do this properly. However, here's a hack which may do what you wish:

   Connection conn1 = DriverManager.getConnection("jdbc:postgresql://"+maquina+"/template1??charSet=LATIN1", usario, senha);
   conn1.createStatement().execute("CREATE DATABASE horario");
   conn1.close(); 

You'd put these lines in after the properties are declared but before a connection is made.

UPDATE #2

You should set your properties in a separate configuration file. By default, hibernate looks for a file called hibernate.cfg.xml at the root of your classpath. It should look something like

<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
    <session-factory>
        <property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>
        <property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
        <property name="hibernate.connection.username">postgres</property>
        <property name="hibernate.connection.password">password</property>
        <property name="hibernate.connection.url">jdbc:postgresql://localhost:5432/hibernatedb</property>
        <property name="connection_pool_size">1</property>
        <property name="hbm2ddl.auto">create-drop</property>
        <property name="show_sql">true</property>
    </session-factory>
</hibernate-configuration>

To load this file, assuming it's at the default name and location:

import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.SessionFactory;
/**
* Hibernate Utility class with a convenient method to get Session Factory object.
*
* @author hd1
*/
public class HibernateUtil {
   private static final SessionFactory sessionFactory;

   static {
      try {
         // Create the SessionFactory from standard (hibernate.cfg.xml)
         // config file.
         sessionFactory = new AnnotationConfiguration().configure().buildSessionFactory();
      } catch (Throwable ex) {
         // Log the exception.
         System.err.println("Initial SessionFactory creation failed." + ex);
         throw new ExceptionInInitializerError(ex);
      }

    }

    public static SessionFactory getSessionFactory() {
       return sessionFactory;
    }
}

Hope that helps...

Answer 2

Ok, finally I acomplish my objective and now both database and tables are being created. The final code for the method create_tables() is:

public void create_tables(String maquina, String usuario, String senha) {
    System.out.println("create_tables");
    create_properties(maquina, usuario, senha);

    Configuration config = new Configuration();
    Properties props = new Properties();
    FileInputStream fos;
    try {
        fos = new FileInputStream( "database.properties" );
        props.load(fos);
        fos.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    config.setProperties(props);

    config.addAnnotatedClass(com.horariolivre.entity.Atributo.class);
    config.addAnnotatedClass(com.horariolivre.entity.ConfigHorarioLivre.class);
    config.addAnnotatedClass(com.horariolivre.entity.Evento.class);
    config.addAnnotatedClass(com.horariolivre.entity.HorarioLivre.class);
    config.addAnnotatedClass(com.horariolivre.entity.Key.class);
    config.addAnnotatedClass(com.horariolivre.entity.Tipo.class);
    config.addAnnotatedClass(com.horariolivre.entity.Value.class);
    config.addAnnotatedClass(com.horariolivre.entity.Autorizacao.class);
    config.addAnnotatedClass(com.horariolivre.entity.Usuario.class);

    try {
        String url = props.getProperty("jdbc.url");
        Connection conn = DriverManager.getConnection(url,usuario,senha);
        SchemaExport schema = new SchemaExport(config, conn);
        schema.create(true, true);
    } catch (SQLException e) {
        e.printStackTrace();
    }

    insert_default_values();
}