How to create and drop database, schema PostgreSQL by Java code?

Question

I try

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import com.ibatis.common.jdbc.ScriptRunner;

public static void createDatabase() throws ClassNotFoundException, SQLException {
        Class.forName("org.postgresql.Driver");
        Connection connection = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/postgres", "postgres", "123456a@");
        Statement stmt = connection.createStatement();
        stmt.executeQuery("CREATE DATABASE IF NOT EXISTS foo");
        stmt.executeQuery("USE foo");
        connection.close();
    }

and

public static void dropDatabase() throws ClassNotFoundException, SQLException {
    Class.forName("org.postgresql.Driver");
    Connection connection = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/", "postgres", "123456a@");
    Statement statement = connection.createStatement();
    statement.executeUpdate("DROP DATABASE foo");
    connection.close();
}

but create, also drop method not success.

Error when call create method:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: syntax error at or near "NOT"
  Position: 20
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2453)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2153)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:286)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:432)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:358)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:305)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:291)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:269)
    at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:236)
    at com.nttdata.RunSqlScript.createDatabase(RunSqlScript.java:57)
    at com.nttdata.RunSqlScript.main(RunSqlScript.java:27)

Error when call drop method:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: database "foo" is being accessed by other users
  Detail: There is 1 other session using the database.
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2453)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2153)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:286)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:432)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:358)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:305)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:291)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:269)
    at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:249)
    at com.nttdata.RunSqlScript.dropDatabase(RunSqlScript.java:71)
    at com.nttdata.RunSqlScript.main(RunSqlScript.java:28)

I must manipulating PostgreSQL from Java, I willingness use 3th party tools — Vy Do
– Vy Do, Commented Sep 16, 2016 at 4:29
Where in the Postgres manual did you find the USE command or the IF NOT EXISTS for DROP DATABASE? — user330315
– user330315, Commented Sep 16, 2016 at 5:47

N00b Pr0grammer · Accepted Answer · 2016-09-16 08:08:44Z

3

Firstly, the SQL syntax used while creating a database is incorrect in your question. The stack trace says it all about the incorrect syntax.

If you want to check whether the database exists or not, then you might have to do something like this in your Java code:

ResultSet rs = stmt.executeQuery("select datname from pg_database where datname like 'foo';");

not by the IF NOT EXISTS approach

Accessing this rs object will let you know whether the database exists or not. Then you can fire either your CREATE or DELETE database operations accordingly.

String databaseName = "";
if(rs.next()) {
    databaseName = rs.getString("datname");
}
stmt.executeQuery("DROP DATABASE " + databaseName);

If a direct DROP DATABASE doesn't work (which I had faced a lot many times), you might consider using the dropdb utility or by one of the following approaches.

APPROACH-1

Use the following query to prevent future connections to your database(s):

REVOKE CONNECT ON DATABASE foo FROM public;

You can then terminate all connections to this database except your own:

SELECT pid, pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = current_database() AND pid <> pg_backend_pid();

Since that you have revoked the CONNECT rights to the corresponding database, no external auto-connect's will no longer be able to do so. You'll now be able to drop the database without any issues.

APPROACH-2:

This approach goes by the batch job way, where you can invoke this class from the corresponding jar:

Process batchProcess = Runtime.getRuntime().exec("C:/Program Files/PostgreSQL/9.5/bin/psql -h \"DB SERVER ADDRESS\" -U postgres -f C:/batch.sql");

batch.sql holds the SQL DROP DATABASE statements, which will be dropped when executed.

Hope this helps!

edited Sep 16, 2016 at 8:08

answered Sep 16, 2016 at 3:54

N00b Pr0grammer

4,6676 gold badges36 silver badges49 bronze badges

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

11 Comments

Vy Do Over a year ago

Exception in thread "main" org.postgresql.util.PSQLException: ResultSet not positioned properly, perhaps you need to call next.

N00b Pr0grammer Over a year ago

Obvious thing is to check whether the ResultSet contains anything or not using the rs.next(), updated the answer. Please check!

Vy Do Over a year ago

Use Process is nice idea. C:/Program Files/PostgreSQL/9.5/bin/psql -h \"DB SERVER ADDRESS\" -U postgres -f C:/batch.sql how to add password?

N00b Pr0grammer Over a year ago

Aren't you able to DROP the database with a normal DROP DATABASE command. The approach of calling the utility directly goes by a batch job! It will prompt for password then!

Vy Do Over a year ago

Oh, no. It must run automatically.

|

Ramachandran.A.G · Accepted Answer · 2016-09-16 04:50:18Z

1

An option that you can try to use is to use a database migration tool like liquibase. There are couple of options that you can try from liquibase. One is to have an executable directly executed from the code (You first create a database change log file , with change sets. One of the commands in the change sets will be an executable

<changeSet author="exec-change-drop" id="drop-foo">
    <executeCommand executable="<bat file with drop for PSQL or dropdb>"/>
</changeSet>

Another option that you can try is to write a sql and call it

<changeSet id="exec-change-drop2" author="drop-foo-2">  
   <sql>DROP DATABASE foo;</sql>  
</changeSet>

You can then execute this from your code as follows

Class.forName("org.postgresql.Driver");
    Connection connection = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/postgres", "postgres", "123456a@");

Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(new JdbcConnection(connection));

Liquibase liquibase = new liquibase.Liquibase("path/to/changelog.xml", new ClassLoaderResourceAccessor(), database);

liquibase.update(new Contexts(), new LabelExpression());

Note that your changeLogSchema may need to be in a different schema so that it executes seamlessly.

Additionally liquibase can be added with maven (this was the way it was supposed to be) and executed as well

answered Sep 16, 2016 at 4:50

Ramachandran.A.G

4,9481 gold badge14 silver badges28 bronze badges

1 Comment

Vy Do Over a year ago

Thank you for your suggestion! I hope have a chance to try your approach, then reply the answer.

Collectives™ on Stack Overflow

How to create and drop database, schema PostgreSQL by Java code?

2 Answers 2

11 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related