If you ever written a Java application that reads and updates into a relational database in the past, most likely you’ll use the JDBC API. Typically you’ll need to represent the database entity as a java object, and write a service/DAO class to provide CRUD (Create Read Update Delete) facility.

Dealing with persistent data became more tricky when the number of entity types keep growing, and also their complexity of relationships. The amount of code keep growing, and it became harder and harder to maintain. More recently, the better approach is to use ORM (Object Relational Mapping) solution such as Hibernate. With the popularity of ORM techniques, the Java community has also introduced JPA (Java Persistence API) which is a standardized ORM API. Hibernate is one of the popular JPA implementation available.

To give a basic idea, consider following simple example about a book entity. A book is identified by book id, and has author, title and year property.

public class Book {
        private int bookId;
        private String title;
        private String author;
        private int year;

        public Book() {}

        public Book(int bookId, String title, String author, int year) {
                this.bookId = bookId;
                this.title = title;
                this.author = author;
                this.year = year;
        }

        // Getters and setters omitted for brevity
}

Let’s create a DAO to provide CRUD facility over book entity. The DAO is implemented as an interface so it doesn’t need to know what the underlying mechanism used. Later we’ll implement the BookDAO interface using both Jdbc and JPA to compare the difference

public interface BookDAO {
        /**
         * Find a book by id
         *  null if can't find bookId
         */
        public Book findById(int bookId);

        /**
         * List all books
         */
        public List list();

        /**
         * Persist a new book object into database. The id attribute of the book object will be set
         * and returned.
         *  id of the newly inserted book
         */
        public int save(Book book);

        /**
         * Persist changes to existing book object into database.
         *  book
         */
        public void update(Book book);

        /**
         * Remove persisted book object from database
         *  book
         */
        public void delete(Book book);
}

Following is example on how to implement BookDAO using plain JDBC. This implementation simply opens and closes connection for every method calls, assuming a connection pooled datasource is used. Note how we have to manually construct our CRUD SQL and map the ResultSet object returned by JDBC.

/**
 * Implementation of BookDAO using jdbc, creates and close a new connection for every
 * CRUD method calls. Use connection pooled datasource for efficiency.
 *
 *  gerry
 *
 */
public class BookJdbcDAOImpl implements BookDAO {

        private DataSource dataSource;

        public BookJdbcDAOImpl (DataSource dataSource) {
                this.dataSource = dataSource;
        }

        
        public Book findById(int bookId) {
                Connection connection = null;
                try {
                        connection = dataSource.getConnection();
                        Statement statement = connection.createStatement();
                        ResultSet rs = statement.executeQuery("SELECT * FROM book WHERE book_id = " + bookId);
                        if (!rs.next()) {
                                return null;
                        }
                        Book result = mapRow(rs);
                        statement.close();
                        return result;

                } catch (SQLException e) {
                        throw new RuntimeException(e.getMessage(), e);
                } finally {
                        try {
                                connection.close();
                        } catch (SQLException e) {
                                throw new RuntimeException(e.getMessage(), e);
                        }
                }

        }

        
        public List list() {
                Connection connection = null;
                try {
                        connection = dataSource.getConnection();
                        Statement statement = connection.createStatement();
                        ResultSet rs = statement.executeQuery("SELECT * FROM book");
                        ArrayList result = new ArrayList();
                        while (rs.next()) {
                                result.add(mapRow(rs));
                        }
                        statement.close();
                        return result;
                } catch (SQLException e) {
                        throw new RuntimeException(e.getMessage(), e);
                } finally {
                        try {
                                connection.close();
                        } catch (SQLException e) {
                                throw new RuntimeException(e.getMessage(), e);
                        }
                }
        }

        
        public int save(Book book) {
                Connection connection = null;
                try {
                        connection = dataSource.getConnection();
                        Statement statement = connection.createStatement();
                        statement.executeUpdate(
                                String.format("INSERT INTO book (title, author, year) VALUES ('%s','%s',%s)",
                                        book.getTitle(),
                                        book.getAuthor(),
                                        book.getYear()),
                                Statement.RETURN_GENERATED_KEYS);
                        ResultSet rs = statement.getGeneratedKeys();
                        if (!rs.next()) {
                                throw new IllegalStateException("Error when inserting book to database " + book);
                        }
                        int generatedKey = rs.getInt(1);
                        book.setBookId(generatedKey);
                        statement.close();
                        return generatedKey;
                } catch (SQLException e) {
                        throw new RuntimeException(e.getMessage(), e);
                } finally {
                        try {
                                connection.close();
                        } catch (SQLException e) {
                                throw new RuntimeException(e.getMessage(), e);
                        }
                }
        }

        
        public void update(Book book) {
                Connection connection = null;
                try {
                        connection = dataSource.getConnection();
                        Statement statement = connection.createStatement();
                        int rowUpdated = statement.executeUpdate(
                                        String.format(
                                                "UPDATE book " +
                                                "SET title = '%s', " +
                                                "author = '%s', " +
                                                "year = %s " +
                                                "WHERE book_id = %s",
                                                book.getTitle(),
                                                book.getAuthor(),
                                                book.getYear(),
                                                book.getBookId()));
                        if (rowUpdated != 1) {
                                throw new IllegalStateException("Error when trying to update " + book);
                        }
                        statement.close();
                } catch (SQLException e) {
                        throw new RuntimeException(e.getMessage(), e);
                } finally {
                        try {
                                connection.close();
                        } catch (SQLException e) {
                                throw new RuntimeException(e.getMessage(), e);
                        }
                }

        }

        
        public void delete(Book book) {
                Connection connection = null;
                try {
                        connection = dataSource.getConnection();
                        Statement statement = connection.createStatement();
                        int rowUpdated = statement.executeUpdate(
                                        String.format(
                                                "DELETE FROM book WHERE book_id = %s",
                                                book.getBookId()));
                        if (rowUpdated != 1) {
                                throw new IllegalStateException("Error when trying to delete " + book);
                        }
                        statement.close();
                } catch (SQLException e) {
                        throw new RuntimeException(e.getMessage(), e);
                } finally {
                        try {
                                connection.close();
                        } catch (SQLException e) {
                                throw new RuntimeException(e.getMessage(), e);
                        }
                }

        }

        private Book mapRow(ResultSet rs) throws SQLException {
                return new Book (
                        rs.getInt("book_id"),
                        rs.getString("title"),
                        rs.getString("author"),
                        rs.getInt("year"));
        }

}

With JPA we can achieve the same outcome with much less code. JPA API leverage annotation based entity mapping to make it more easier to map a POJO into database. Let’s decorate our Book entity with some annotation to tell JPA what our database schema looks like.

public class Book {
        @Id  private int bookId;
         private String title;
         private String author;
         private int year;

        public Book() {}

        public Book(int bookId, String title, String author, int year) {
                this.bookId = bookId;
                this.title = title;
                this.author = author;
                this.year = year;
        }

        // Getters and setters omitted for brevity
}

As you can see, we still have the same Book entity POJO class, and the extra , @Id and annotations is enough to tell JPA what the database schema looks like, and how to persist / retrieve the instance from database. Following is a sample of JPA implementation of BookDAO. Note how in this implementation we don’t have to write any native SQL to retrieve, or save the object, and more importantly, no cumbersome code to map the result set row into an entity object!

        /**
         * Simple implementation of BookDAO using JPA. Each method simply starts and closes
         * transaction. Entity object returned by these methods are always detached
         *
         *  gerry
         *
         */
        public class BookJPADAOImpl implements BookDAO {

                private EntityManager em;

                public BookJPADAOImpl (EntityManager entityManager) {
                        this.em = entityManager;
                }

                
                public Book findById(int bookId) {
                        Book result = em.find(Book.class, bookId);
                        em.detach(result);
                        return result;
                }

                
                public List list() {
                        List result = em.createQuery("SELECT b FROM Book b", Book.class)
                                        .getResultList();
                        for (Book b : result) { em.detach(b); }
                        return result;
                }

                
                public int save(Book book) {
                        em.getTransaction().begin();
                        em.persist(book);
                        em.getTransaction().commit();
                        em.detach(book);
                        return book.getBookId();
                }

                
                public void update(Book book) {
                        em.getTransaction().begin();
                        em.merge(book);
                        em.getTransaction().commit();
                        em.detach(book);
                }

                
                public void delete(Book book) {
                        em.remove(book);
                        em.flush();
                }

        }

This is only a very simple example of JPA, but  if you read more, it’s capable of much more such as mapping entity relations, and implementing optimistic locking using row versioning.

You can view or checkout the sample code here: . In the sample code I wrote few unit test classes using HyperSQL in memory database.

Following are good reference links if you want to find out about:

• Java EE Tutorial (persistence chapter): http://docs.oracle.com/javaee/6/tutorial/doc/bnbpy.html
• JPA 3.0 spec:
• Hibernate (entity manager user guide): 
• HyperSQL: http://hsqldb.org/web/hsqlDocsFrame.html