PART 3: Using JPA and Postgresql in your application

(This is part of a series

Previously, in our build.gradle file we included these jars to allow us to talk to a Postgresql database through JPA.

compile 'postgresql:postgresql:9.1-901-1.jdbc4'
compile 'org.eclipse.persistence:eclipselink:2.4.0'

We needed the eclipse link repository for that.

repositories {
    maven {
        url ''

Now we need to create a Postgresql database user and database to talk to. We'd normally set this up in the environment somehow before running the project.

(as root)
su - postgres
psql -c "create user test_username password 'test_password';"
psql -c "create database test_database owner test_username"
(logout from postgres and root)

Now we can create the persistence.xml file that tells JPA how to connect to our database. (Ensure there's no whitespace at the start of the file)

echo '<?xml version="1.0" encoding="UTF-8" ?>
<persistence xmlns:xsi=""
  version="2.0" xmlns="">
  <persistence-unit name="PERSISTENCE_UNIT_NAME" transaction-type="RESOURCE_LOCAL">
      <property name="javax.persistence.jdbc.url" value="jdbc:postgresql://localhost:5432/test_database" />
      <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver" />
      <property name="javax.persistence.jdbc.user" value="test_username" />
      <property name="javax.persistence.jdbc.password" value="test_password" />
      <property name="eclipselink.logging.level" value="ALL" />
' > src/main/resources/META-INF/persistence.xml

This is doing a couple of things

  • Naming the persistence-unit which we'll use when we come to initialise JPA
  • Makeing our database transactions will be local to this machine
  • Saying there's a mapping file in META-INF/mapping.xml that maps our Objects to the database
  • Saying we're using EclipseList for the persistence provider
  • Pointing JPA to our database
  • Providing the username and password to that database
  • Making EclipseLink log everything

Next we'll create a simple Object, or Entity, which we'll persist in the database. It's just a POJO.

echo '
package com.example.YOURPROJECT;

public class ExampleEntity {
	private long id; 
	private String talky;

	public String getTalky() {
		return talky;
	public void setTalky(String talky) {
		this.talky = talky;
	public long getId() {
		return id;
	public void setId(long id) { = id;

' > src/main/java/com/example/YOURPROJECT/

We could use annotations on the object to map it to the database, and leave JPA to sort out the tables etc, but that always leads to pain.

So we're creating the mapping.xml file we referred to earlier. (Ensure there's no whitespace at the start of the file)

echo '<?xml version="1.0" encoding="UTF-8" ?>
<entity-mappings xmlns=""
    <entity class="com.example.YOURPROJECT.ExampleEntity"> 
      <table name="example" />
      <named-query name="list">
        <query>select p from ExampleEntity p</query>
        <id name="id">
          <generated-value strategy="auto" />
        <basic name="talky">
          <column name="talky" nullable="false"/>
' > src/main/resources/META-INF/mapping.xml

This is saying:

  • Specifying the entity you created above
  • Specifying the database table where this entity lives
  • Creating a named query called 'list' that lists all the entities
  • Defining an primary key id attribute, relating to 'id' in entity, that is an automatically generated value in the database, using the AUTO strategy (see
  • Defining an talky attribute, relating to 'talky' in the entity, which is the 'talky' column in the database which cannot be null.

We'd normally use a database migration to ensure the table 'example' above exists, but in our case here, let's just create it in the database directly. We're also creating a sequence table so JPA can create unique primary keys.

psql -h localhost -U test_username -d test_database -c "create table example (id bigserial not null primary key, talky varchar(1000) not null);"
psql -h localhost -U test_username -d test_database -c "create table sequence (seq_name varchar(50) not null primary key, seq_count int);insert into sequence (seq_name, seq_count) values('SEQ_GEN', 1);"    

Now let's create a new request that create a JPA connections, add something to our database and lists everything in it to return.

The comments should example the basics of JPA and EntityManagers.

echo '
package com.example.YOURPROJECT;

import java.util.ArrayList;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;
import org.apache.log4j.Logger;

public class ExampleJPARequest {

	public List<ExampleResource> example(@PathParam("example") String example) {
		// Get the EntityManager by creating an EntityManagerFactory via the persistence-unit name we provided.
		EntityManager entityManager = Persistence.createEntityManagerFactory("PERSISTENCE_UNIT_NAME").createEntityManager();
		EntityTransaction transaction = entityManager.getTransaction(); // Not useful here, but useful to see
		List<ExampleEntity> list  = null;
		try {
			// Add an entity
			ExampleEntity entity = new ExampleEntity();
			// List entities, via the named query we defined in mapping.xml
			TypedQuery<ExampleEntity> nq = entityManager.createNamedQuery("list", ExampleEntity.class);
			list = nq.getResultList();
			// Commit the transaction
		} catch (Exception e) {
			Logger.getLogger(getClass()).error("Problem persisting", e);
			throw e; // Ergo showing a 500 error. You may want to throw an exception that is not detailing stuff about your JPA connection
		} finally {
			entityManager.clear(); // Clears all the entities from the EntityManager
		// Adapt the entities into objects to return as JSON
		ArrayList<ExampleResource> resList = new ArrayList<ExampleResource>();
		for (ExampleEntity exampleEntity : list) {
			ExampleResource exampleItem = new ExampleResource();
		return resList;
' > src/main/java/com/example/YOURPROJECT/

You should normally separate the database layer and entity adapters from the request layer, which can do nicely with Jersey's dependency injection, whic we'll come to later.

We can again run the project to see it in action:

gradle build
java -jar jetty-runner-9.1.0.M0.jar --port 8081 build/libs/YOUR_PROJECT_DIR.war

If you visit


and then visit


You should see the JSON

java java-jpa java-web-quick-start java-jersey

Edit on github
comments powered by Disqus
Click me