Heroku, Java, Jersey and postgres

We first need to setup our postgres

This page has instructions on how to do that https://devcenter.heroku.com/articles/heroku-postgresql, or follow the steps below


Install postgres on your local machine:

Instructions for Mac users: https://devcenter.heroku.com/articles/heroku-postgresql#set-up-postgres-on-mac

For Windows users: http://www.enterprisedb.com/products-services-training/pgdownload#windows

Installation Guides
GUI:
http://www.enterprisedb.com/docs/en/9.3/pginstguide/PostgreSQL_Installation_Guide-07.htm#TopOfPage

Terminal installation:
http://www.enterprisedb.com/docs/en/9.3/pginstguide/PostgreSQL_Installation_Guide-10.htm#TopOfPage

add the path to postgres bin folder to your environmental variables under PATH variable

To Connect to your heroku database with JDBC:

heroku pg:psql

Create a test table to work with:
CREATE TABLE GOODS(THING CHAR(50));
INSERT INTO GOODS(THING) VALUES('Ball');
INSERT INTO GOODS(THING) VALUES('Shoes');
INSERT INTO GOODS(THING) VALUES('Pant');


To quit: \q

Get your Database URL:
heroku pg:credentials DATABASE

copy the entire line right after
Connection URL: that starts with postgres://

Go to your Eclipse, click on the root of your project, go to Run->Run Configurations->Environment->New
Name: DATABASE_URL
Value:<whatever you just copied from above>

Add postgres dependency in your pom.xml:

link to unhoven code is here: ​https://github.com/gnurmatova/unhoven_

<dependency>
	<groupId>org.postgresql</groupId>
	<artifactId>postgresql</artifactId>
	<version>9.3-1100-jdbc41</version>
</dependency>

update your project either using maven plugin "update project"

OR 
​
run:
mvn eclipse:eclipse

You may need to add your mvn dependencies back in eclipse(depending on your Eclipse version):
right click on <project name> -> Properties->Deployment Assembly->Add-> Java Build Path Entries -> choose maven dependencies


Now you should be able to run following code to test your connectivity (RUN AS JAVA PROGRAM)

import java.net.URI;
import java.net.URISyntaxException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ConnectionProvider {
	private static Connection getConnection() throws URISyntaxException,
			SQLException {
		URI dbUri = new URI(System.getenv("DATABASE_URL"));

		String username = dbUri.getUserInfo().split(":")[0];
		String password = dbUri.getUserInfo().split(":")[1];
                try {
			Class.forName("org.postgresql.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		String dbUrl = "jdbc:postgresql://"
				+ dbUri.getHost()
				+ ':'
				+ dbUri.getPort()
				+ dbUri.getPath()
				+ "?sslmode=require&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory";
		System.out.println(dbUrl);
		return DriverManager.getConnection(dbUrl, username, password);
	}

	public static void main(String[] args) {
		try {
			Connection connection = getConnection();
			Statement stmt = connection.createStatement();
			stmt.executeUpdate("INSERT INTO GOODS(THING) VALUES('My cool Item');");
			ResultSet rs = stmt.executeQuery("SELECT Thing FROM Goods");
			while (rs.next()) {
				System.out.println("Thing: " + rs.getString("thing"));
			}
		} catch (URISyntaxException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

​

OPTIONAL - Running local database, a copy from heroku

Pull postgres from Heroku:

heroku pg:pull  <database name> mylocaldb --app unh


heroku pg:pull  HEROKU_POSTGRESQL_PINK_URL mylocaldb --app unh

Connect to local DB:

psql

to quit: \q