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 linking with 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;
}
}