Developing a CRUD REST API on IBM Bluemix

On our last post Developing a JEE REST API on IBM Bluemix we show you how to create the base REST services for our CRUD application. On todays post we are adding a Database and a JPA connection for the services. On the next post we will show you how to test the system using Unit testing.

This post 1) we create the database connection, 2)prepare the JPA persistence unit, 3)Implement the code that connects the rest services to the storage, and finally 4)Review the app is working so we can test the REST services.

1. Create a database on Bluemix

For this post we are using a DB2 database or SQL Database as shown on the Data Management section.

– Go to your Bluemix console and login. Then select the app you are using.
– Select Add A Service
Screen Shot 2014-08-01 at 5.04.29 PM
– Go to Data Management and select SQL Database from IBM
– Select the app you are associating the service
– Use a DB Name, this name will be the same you use on your persistence unit with the prefix jdbc/
– Finally create the Database

2. Prepare persistence unit

The persistence unit defines what database the EntityManager is using to store the date.

– Create a file persistence.xml and add the following:

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns=""
	xmlns:xsi="" version="2.0"
	<persistence-unit name="PeoplePU">
		<!-- exclude-unlisted-classes>true</exclude-unlisted-classes -->
		    <property name="openjpa.LockTimeout" value="30000" />
			<property name="openjpa.jdbc.TransactionIsolation" value="read-committed" />
			<property name="openjpa.Log" value="none" />
			<property name="openjpa.jdbc.UpdateManager" value="operation-order" />
			<property name="jdbc.SynchronizeMappings" value="buildSchema(ForeignKeys=true)"/>

– The persistence unit name is the one you will use on your annotated POJO(Plain Old Java Object)
– The Jta data source is the name of your database with the prefix jdbc/
– The class is the class with the Entity annotation we did on our post before, please note that we use a table name with the prefix t_ since the person table is part of the system tables.

3. Modify the REST POJO to connect to the DB using JPA

Now we are going to provide the necessary coding to allow Java Objects to be stored on the Database via the OpenJPA ORM(object-relational mapping) implementation.

– First we add the stateless annotation

public class PeopleAdminService {

-We now define the entity manager unit using the unitName defined on the persisten unit XML, on this case “PeoplePU”

@PersistenceContext(unitName = "PeoplePU")
private EntityManager em;
private CriteriaBuilder builder;

-Finally add the implementation of each of the methods

	public List<Person> list() {
		CriteriaQuery<Person> cq = builder.createQuery(Person.class);;
		return getManager().createQuery(cq).getResultList();
	public String createTables() {
		Query q = getManager()
						"CREATE TABLE t_person(id SMALLINT NOT NULL, lastname CHARACTER(30) NOT NULL, name CHARACTER(30) NOT NULL, city CHARACTER(30),country CHARACTER(30),PRIMARY KEY(id))");
		return "finished";
	public Person find(@PathParam("id") Integer id) {

		return getManager().find(Person.class, id);
	public Integer create(Person person) {
		return person.getId();
	public Integer create(Person person) {
		return person.getId();
	public void delete(@PathParam("id") Integer id) {
		Person p = getManager().find(Person.class, id);

If you have questions on the code please review our solution at Code

– Review there are no errors and publish to Bluemix

4. Review the server.xml file for the database connection

Finally we need to review that the database Stanza is generated on our server configuration.
– Go to the Bluemix dashboard
– Select your App
– Go to Files and Logs
– Go to the file app/.liberty/usr/servers/defaultServer/server.xml
Screen Shot 2014-07-30 at 12.44.35 PM
– At this point you should be able to see this on your Bluemix console, the datasource is generated by Bluemix, for now you can just auto generate a connection to DB2, if not try to republish your app.

4. Test The Project

If everything is ok so far, you are able to deploy your app and test it using your url adding /api/people.

On the next post we will show you how to write a JUnit test to review everything works as expected.