How to create a stored Postgresql database from a Java source

Question

Searching the internet for a solution to create a database on a Postgresql server for my Spring project, I found the following topic on Stackoverflow:

https://stackoverflow.com/questions/18389124/simulate-create-database-if-not-exists-for-postgresql

where there is the following Stored Process for this task:

DO
$do$
BEGIN

IF EXISTS (SELECT 1 FROM pg_database WHERE datname = 'mydb') THEN
   RAISE NOTICE 'Database already exists'; 
ELSE
   PERFORM dblink_exec('dbname=' || current_database() -- current db
                      , $$CREATE DATABASE mydb$$);
END IF;

END
$do$

I want to be able to run this code from my Java code, without using any database command (I am already creating the database tables via Hibernate, but I need to create a database first through pgAdmin3, step I want to avoid).

The ideal for me would be to call this code through the following Service class:

@Service
public class InstallService {

    private String query = "";

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

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

Someone knows how to do it?

Answer 1

You have to consult the postgresql system tables to know if the database already exists. If it does not exist, you make a new call. Ex. (using SQL);

String sqlIfExist = "SELECT count(*) = 1 FROM pg_database WHERE datname = :nomeDB";
Query q = session.createSQLQuery(sqlIfExist);
q.setString("nomeDB", "mydb"); // troque mydb pelo seu database
boolean existe = (Boolean) q.uniqueResult();
if (!existe) {
    String sqlCreateDB = "CREATE DATABASE :nomeDB";
    Query qc = session.createSQLQuery(sqlIfExist);
    qc.setString("nomeDB", "mydb"); // troque mydb pelo seu database
    qc.executeUpdate();
}

Remembering that your Postgresql user must have permission to create the database.

Answer 2

I got the desired result with the following code:

@Service
public class InstallService {

    private String property_file = "database.properties";

    @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");
                rs.close();
                stmt.close();
                conn.close();
                create_tables(maquina, usuario, senha);
                return true;
            }

            stmt.executeUpdate("CREATE DATABASE horario WITH OWNER "+usuario);
            rs = stmt.executeQuery("SELECT count(*) FROM pg_catalog.pg_database WHERE datname = 'horario'");
            rs.next();
            int result = rs.getInt(1);
            System.out.println("result = "+result);
            if(result > 0) {
                System.out.println("calling_create_tables");
                rs.close();
                stmt.close();
                conn.close();
                create_tables(maquina, usuario, senha);
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("SQLException");
            return 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( this.property_file );
            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();
    }

    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( this.property_file );
            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;
    }
}