How to create a PostgreSQL database using Java

and/or connect to an existing database.

Example Code

 

This turns out to be a rather tricky problemÖ which it shouldnít be!

Iíll assume you have some Java skills, so Iíll skip all of that. Also, I assume you have PostgreSQL properly installed on your computer, youíve created an account in it that has CREATE DATABASE privilege, and your java code links into the jar files properly. These examples were created under PostgreSQL 9.5 and Java JDK 8.

My coding style is fairly simplistic so everybody can understand what Iím doing. Although my examples were created on a Windows machine, I ran into this mess because I was having trouble using PostgreSQL on a Unix server. Bottom line, Iím hoping this is all applicable regardless of what platform youíre using.

You can grab the actual files here: Create, the main program, and Database, the class to create/connect.

Here they are as text. First, the main program (Create.java):

package create;

 

/* HOW TO CREATE A POSTGRESQL DATABASE IN JAVA.

*

* Using PostgreSQL version 9.5.1. This example created on

* a Windows 7 machine.

*

* G.E.Grant / 60south.com / 2016

* http://hackles.60south.com/postgresql.htm

*/

 

public class Create {

 

†††††† public static void main(String[] args) {

 

††††††††††††† // We'll use the default path.

††††††††††††† String path = "jdbc:postgresql://localhost/";

††††††††††††† String name = "myDB";

 

††††††††††††† // You'll need a PostgreSQL account with 'create database' privileges.

††††††††††††† String username = "user";

††††††††††††† String password = "password";

†††††††††††††

††††††††††††† // Let's create a database

††††††††††††† Database db = new Database(path, name, username, password);

†††††††††††††

††††††††††††† // Now connect to it.

††††††††††††† if (db.connect()) {

†††††††††††††††††††† System.out.println("Yay! Success.");††††

††††††††††††† }

††††††††††††† else {

†††††††††††††††††††† System.out.println("Boo! Failure.");†††††††††††††††††††††††††

††††††††††††† }††††††††††††

†††††† }

}

 

Now, the Database.java class:

package create;

 

/* Database

*

* Connect to a PostgreSQL database using Java, or create one

* if it does not exist.

*

* http://hackles.60south.com/postgresql.htm

*/

 

import java.sql.*;

 

public class Database {

 

†††††† Connection conn;

††††††

†††††† String path;

†††††† String name;

†††††† String username;

†††††† String password;

††††††

†††††† // Constructor

†††††† public Database(String dbPath, String dbName, String dbUsername, String dbPassword) {

†††††††††††††

††††††††††††† // The database name in the getConnection command is case sensitive.

††††††††††††† // But when Postgres creates a database it silently makes it all

††††††††††††† // lower case, so we can never connect to a database with upper case

††††††††††††† // letters! However, it lets us try... and will fail every time. Grr!

††††††††††††† //

††††††††††††† // So we set the database name to lower case here.

††††††††††††† name = dbName.toLowerCase();

†††††††††††††

††††††††††††† path = dbPath;

††††††††††††† username = dbUsername;

††††††††††††† password = dbPassword;

†††††† }

 

†††††† /*

†††††† * connect

†††††† *

†††††† * Connect to an existing database, or create it if it doesn't exist

†††††† */

†††††† public boolean connect() {

††††††††††††††††††††

††††††††††††† // Open the database.

††††††††††††† try {

††††††††††††††††††††

†††††††††††††††††††† // Load the postgresql driver. I am unclear on why we have to do this

†††††††††††††††††††† // when we're already linkingwith the postgres library (isn't that

†††††††††††††††††††† // enough?). But if you don't do this the executable may fail to find

†††††††††††††††††††† // the driver class.

†††††††††††††††††††† Class.forName("org.postgresql.Driver");

 

†††††††††††††††††††† // First, we try the database name provided by the user.

†††††††††††††††††††† // Note that I'm using the option to connect to an existing schema, not

†††††††††††††††††††† // the default public schema. If you don't care, you can drop the option.

†††††††††††††††††††† // Postgres may silently ignore this (grr) if the schema doesn't exist.

†††††††††††††††††††† conn = DriverManager.getConnection(path + name +

††††††††††††††††††††††††††††††††† "?currentSchema = " + name, username, password);

 

†††††††††††††††††††† System.out.println(name + " connected");

††††††††††††† }

††††††††††††† catch (Exception e) {

 

†††††††††††††††††††† System.out.println("Could not connect to database: " + path + name);

†††††††††††††††††††† System.out.println("Now trying to connect to the default database.");

††††††††††††††††††††††††††

†††††††††††††††††††† // Failed to connect. This may mean it doesn't exist, so let's try to

†††††††††††††††††††† // create one. But there's a catch: To create a database in Java, we

†††††††††††††††††††† // first have to be connected to another database. By default, PostgresQL

†††††††††††††††††††† // creates a default database on installation called "postgres". We'll

†††††††††††††††††††† // use that one.

†††††††††††††††††††† //

†††††††††††††††††††† // Isn't this all stupid? Why can't I simply tell Postgres to create it without

†††††††††††††††††††† // having to go through this rigamarole? Or better yet, if it doesn't exist

†††††††††††††††††††† // when I try to connect, create it by default? Whaa, whaa, whaa.

†††††††††††††††††††† try {

†††††††††††††

†††††††††††††††††††††††††† conn = DriverManager.getConnection(path + "postgres", username, password);

†††††††††††††††††††††††††† System.out.println("Connected with the default database.");

 

†††††††††††††††††††††††††† // We're connected to the default DB, and Postgres. Now we can create our own DB.

†††††††††††††††††††††††††† try {

††††††††††††††††††††††††††††††††† // Note the absence of the "path" in the CREATE command. Apparently Postgres

††††††††††††††††††††††††††††††††† // needs to create the new database in the default path; there may be some

††††††††††††††††††††††††††††††††† // way to tell it to put it elsewhere, but that's another issue. For now,

††††††††††††††††††††††††††††††††† // if you specify a path in the CREATE command it will bomb out.

††††††††††††††††††††††††††††††††† Statement statement = conn.createStatement();

††††††††††††††††††††††††††††††††† statement.execute("CREATE DATABASE " + name);

†††††††††††††††††††††††††††††††††

††††††††††††††††††††††††††††††††† // Now close the default DB so that we can connect to the new DB.

††††††††††††††††††††††††††††††††† conn.close();

†††††††††††††††††††††††††††††††††

††††††††††††††††††††††††††††††††† // Connect to "our" database.

††††††††††††††††††††††††††††††††† conn = DriverManager.getConnection(path+name, username, password);

††††††††††††††††††††††††††††††††† System.out.println("Connected with the new database.");

 

††††††††††††††††††††††††††††††††† // If you want to create a new schema in the database...

††††††††††††††††††††††††††††††††† // For uniqueness, I just use the database name as the schema name. YMMV.

††††††††††††††††††††††††††††††††† statement = conn.createStatement();

††††††††††††††††††††††††††††††††† statement.execute("CREATE SCHEMA IF NOT EXISTS " + name);

††††††††††††††††††††††††††††††††† conn.setSchema(name);

†††††††††††††††††††††††††††††††††

††††††††††††††††††††††††††††††††† // It worked.

††††††††††††††††††††††††††††††††† System.out.println("Database " + name + " created.");

†††††††††††††††††††††††††† }

†††††††††††††††††††††††††† catch(Exception e2)

†††††††††††††††††††††††††† {

††††††††††††††††††††††††††††††††† // Bomb out on connecting to our database.

††††††††††††††††††††††††††††††††† System.out.println("Could not connect with new database: \n" + e2);

††††††††††††††††††††††††††††††††† return false;

†††††††††††††††††††††††††† }

†††††††††††††††††††† }

†††††††††††††††††††† catch (Exception e3)

†††††††††††††††††††† {

†††††††††††††††††††††††††† // Huh? We can't even connect to the default database. Bail out.

†††††††††††††††††††††††††† System.out.println("Could not connect with default database: \n" + e3);

 

†††††††††††††††††††††††††† return false;

†††††††††††††††††††† }

††††††††††††† }

 

 

††††††††††††† // Make sure we're using our schema -- so we aren't using the public schema.

††††††††††††† // Again, if you don't care, delete this try/catch block.

††††††††††††† try {

†††††††††††††††††††† Statement statement = conn.createStatement();

 

†††††††††††††††††††† // Note that "conn.setSchema(name);" doesn't necessarily connect to the

†††††††††††††††††††† // schema we request! I don't know why, I just know that it silently fails

†††††††††††††††††††† // when I do it (gotcha again!). So here we use a execute command instead.

 

†††††††††††††††††††† // Set which schema we're going to use

†††††††††††††††††††† statement.execute("SET search_path to " + name);

 

††††††††††††† } catch (Exception badSchema) {

†††††††††††††††††††† System.out.println("Failed to set schema. Exception:\n"

††††††††††††††††††††††††††††††††† + badSchema);

†††††††††††††††††††† return false;

††††††††††††† }

†††††††††††††

††††††††††††† // Success!

††††††††††††† return true;

†††††† }

}

 

Return to Hackles home page