How call a stored procedure from Postgresql in the Java code

Question

Searching the web for a solution to create a database for my spring project when it doesn't exist, I found this topic here in stackoverflow:

Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?

with this stored procedure to acomplish that:

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 run this procedure from my Java code. My initial idea was include this code as a String atribute in this 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;
    }
}

But I just find out this can't be done. Anyone have any sugestion of how to do that inside this class?

khampson · Accepted Answer · 2014-05-08T23:57:27.187

2

I would recommend calling the stored procedure via whatever method you're currently using to connect to Postgres from Spring. (i.e. Spring JDBC: http://docs.spring.io/spring/docs/3.0.x/spring-framework-reference/html/jdbc.html; or perhaps MyBatis, etc.)

You could define the stored procedure in, say, the template database, and then connect to it via Spring JDBC or whatever method you're employing, with a query of the form:

SELECT stored_proc_name(dbname)

(You would need to have the stored procedure take the DB name as an argument, also.)

Edit: In response to the comment below, inquiring if this can be done without adding anything to a database, the answer is yes:

You could connect to the template1 DB, run the SELECT query against pg_catalog to see if the DB exists (you'll get an empty set back if it doesn't), and if it doesn't exist, run another query on the connection to the template1 db to create the DB.

Basically, it'd be deconstructing the stored procedure into its constituent parts and calling them directly from Java using JDBC or similar.

edited May 08 '14 at 23:57

answered May 07 '14 at 01:35

khampson

14,700
4
41
43

But where should create the store procedure, taking in consideration I don't have a database? Or,trying be more clear, where I should place the code above? – Kleber Mota May 07 '14 at 01:39
That's what I meant by `template`. That's actually a database. http://www.postgresql.org/docs/9.3/static/manage-ag-templatedbs.html Keep in mind that'll be used in every DB, so it shouldn't be crammed with lots of DB-specific stuff, but for a utility function like this, I think it's a reasonable place to put it. You could also create a special template DB just for this function and any similar ones. – khampson May 07 '14 at 01:42
Ok, I get that now. But I have only the entity classes, which are templates for the tables from database. I never worked with a template for the database. – Kleber Mota May 07 '14 at 01:43
Well, reading the link you indicate to me, I am guessing I need create a new template IN the database, am I right? I wish create and run this procedure without need manually add something to the server. Any ideas how to do this? Or I understand wrong, and I don't need create anything in the server? – Kleber Mota May 07 '14 at 01:51
1

There should be a template there already -- it's what `CREATE DATABASE` bootstraps off of. It should be called `template1`. See http://www.postgresql.org/docs/9.3/static/sql-createdatabase.html. Also, note that pg_database is a special catalog table that is shared across DBs (you should be able to access it by connecting to the template1 DB). – khampson May 07 '14 at 01:53
Is there any way to do this directly from java code, without manually add something in the database? – Kleber Mota May 08 '14 at 12:22

How call a stored procedure from Postgresql in the Java code

1 Answers1