Postgres Plus Cloud Database
"Postgres Plus Cloud Database" offered by Enterprise DB is a "Verified Solutions Partner" with CloudBees.
This writeup shows how to use Postgres Cloud with CloudBees.
1.0 Setting up the environment
It is fairly easy to set up a database and configuring an app to use it. These are the few things you need to do:
- Get an account on CloudBees and Postgres on Cloud
- Create the database & tables
- Configure local development environment with Maven and Bees SDK
- Build the application and
- Deploy it
This tutorial goes through all the steps.
1.1 Create a database
Follow the steps outlined on the Enterprise DB site to setup an account. The documentation on the site is pretty good and the UI is self-explanatory.
Create a new database cluster. Click on the clusters tab to see information about your database (as shown in the following image). The DNS Name and LBPORT settings will be used in the application code to connect to the database cluster.
The default database is named after the username given while creating the cluster. The tutorial uses a database (named "postgres") while connecting in the sample application.
Note: Download the database package to get tools like "createdb", "psql" to do anything useful with the database instance. (This is a bummer but nevertheless...). The default location for the installation tools on mac is /Library/PostgreSQL/9.1/bin.
1.1.1 Create a table
Run usual SQL commands through psql to create a table and initialize it with data.
The sample application is seeded as follows:
# cd $POSTGRESQL_HOME/bin
# ./psql -h=DNSNAME -p=LBPORT -U=postgres -W=postgres
psql> create table countries (
id INT,
country VARCHAR (50),
captial VARCHAR (50),
primary key (id)
);
psql> INSERT INTO COUNTRIES VALUES (1, 'USA', 'Washingto DC');
psql> INSERT INTO COUNTRIES VALUES (2, 'India', 'New Delhi');
psql> INSERT INTO COUNTRIES VALUES (3, 'Malaysia', 'Kuala Lumpur');
psql> INSERT INTO COUNTRIES VALUES (4, 'Singapore', 'Singapore');
psql> INSERT INTO COUNTRIES VALUES (5, 'Thailand', 'Bangkok');
1.2 CloudBees Account
Get a CloudBees account. Signing up is really easy.
1.2.1 Bees SDK
The Bees SDK is used to deploy applications from the command line. The SDK offers capabilities of delta uploads (uploads changed files only). This feature is very handy as one iterates through application development. Setting it up is just a matter of the SDK in the path. Follow instructions listed here.
1.3 Maven Environment Settings
Maven is used to build the application; consequently updates to pom.xml are required to download Postgres & JPA (EclipseLink) libraries.
1.3.1 CloudBees Related Settings
Follow this guide to configure maven for CloudBees. The command used to to deploy applications is:
mvn bees:deploy -Dbees.appid=appname
1.3.2 Postgres Libraries Settings
Postgres libraries are hosted on maven central. Add the following snippet once project pom.xml is setup (section 2)
<dependency>
<groupId>postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>9.1-901-1.jdbc4</version>
</dependency>
1.3.3 JPA Libraries
The JPA libraries (and Eclipselink) libraries are hosted by Eclipse. Add the following in the dependencies section in pom.xml:
<dependency>
<groupId>org.eclipse.persistence</groupId>
<artifactId>eclipselink</artifactId>
<version>2.3.0</version>
</dependency>
<dependency>
<groupId>org.eclipse.persistence</groupId>
<artifactId>javax.persistence</artifactId>
<version>2.0.3</version>
</dependency>
<dependency>
<groupId>org.eclipse.persistence</groupId>
<artifactId>org.eclipse.persistence.jpa.modelgen.processor</artifactId>
<version>2.3.0</version>
<scope>provided</scope>
</dependency>
Also point to Eclipselink repositories. The following modification in pom.xml accomplishes it:
<repositories>
<repository>
<url>http://download.eclipse.org/rt/eclipselink/maven.repo/</url>
<id>eclipselink</id>
<layout>default</layout>
<name>Repository for library Library[eclipselink]</name>
</repository>
</repositories>
2.0 Application Setup
Java web applications can use two different ways to access the database (JPA or JDBC). Both require different configurations and code as they access the database. Applications only use one of these two methods to access the database. The tutorial shows both setups.
2.1 Create a web app
Use maven to create a web app:
# mvn archetype:create \
-DarchetypeArtifactId=maven-archetype-webapp \
-DgroupId=com.cloudbees -DartifactId=postgres
2.2 Using Postgres with JPA
JPA requires a persistence.xml file with database configuration options. This file is placed in the META-INF directory
The persistence.xml looks like the following for the sample database created earlier:
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
<persistence-unit name="PostgresqlPU" transaction-type="RESOURCE_LOCAL">
<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
<exclude-unlisted-classes>false</exclude-unlisted-classes>
<properties>
<property name="javax.persistence.jdbc.url" value="jdbc:postgresql://HOSTNAME:LBPORT/postgres"/>
<property name="javax.persistence.jdbc.password" value="PASSWORD"/>
<property name="javax.persistence.jdbc.user" value="USER"/>
<property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver"/>
<!-- <property name="eclipselink.ddl-generation" value="create-tables"/> -->
</properties>
</persistence-unit>
</persistence
Java Code:
The Java code to get a connection to database and run a query looks like the following:
EntityManagerFactory emf = Persistence.createEntityManagerFactory("PostgresqlPU");
EntityManager em = emf.createEntityManager();
Query queryAll = em.createNamedQuery("Countries.findAll");
out.println("Getting results");
Collection c = queryAll.getResultList();
The Entity class in the sample code ("Countries.java") shows defines the various queries.
2.3 Using Postgres with JDBC
To use JDBC in the application, requires addition of a datasource to the cloudbees-web.xml file and specifying a resource-reference to this datasource in web.xml
cloudbees-web.xml
<resource name="jdbc/postgresql" auth="Container" type="javax.sql.DataSource">
<param name="username" value="USERNAME" />
<param name="password" value="PASSWORD" />
<param name="url" value="jdbc:postgresql://DNSNAME:LBPORT/postgres" />
<param name="driverClassName" value="org.postgresql.Driver"/>
</resource>
web.xml
<resource-ref>
<res-ref-name>jdbc/postgresql</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>
Java Code
Context ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/postgresql");
Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();
ResultSet rst = stmt.executeQuery("select * from countries");
while (rst.next()) {
int id = rst.getInt(1);
String foo = rst.getString(2);
String bar = rst.getString(3);
out.println (foo + ", " + bar + "<br/>");
}
conn.close();
3.0 Running the Application
Use the following command to package these changes and deploy the application to CloudBees
mvn package bees:deploy -Dbees.appid=ACCOUNTNAME/APPNAME -Dbees.message="Hello World"
Sample App
Use the sample application to get your projects bootstrapped. See the application in action here (JPA URL and JDBC URL).
The application source code is available at github.
4.0 Postgresql Support Questions
As a Verified Solutions Partner, CloudBees has verified that the solution works with CloudBees. If you have specific support questions on Postgres or want Postgres support in general, you should contact support_at_enterprisedb.com. In your subject indicate that you are working with CloudBees and your CloudBees subscription level.