Mule - Build REST API for Database CRUD Operations

MuleSoft development - Build REST API for Database CRUD Operations using Mule/JPA/Spring/Hibernate/JAX-RS

Abstract

In the last years the industry has identified the demand in development and operation automation which led to a growing number of framework and libraries.
Recently, I got a requirement to build a REST API for backend database table- CRUD operations, keeping in view scalable feature - with minimal code changes and simply pointing the configuration to any backend db source in order to publish the API for outside world.

This blog article describes REST API implementation for database for CRUD operations using MuleESB with JPA/Spring/Hibernate/JAX-RS considering database-as-a-service feature.

Now in the context of a project which requires a MuleESB v3.5.1 development environment along with Spring & Hibernate libraries.

This Mule project implementation has selected one simple table; ‘Employees’, storing Employee information, like firstName, lastName, email, hireDate, and employeeID to build a RESTful web-service. The project is constructed with Mule+Spring+Hibernate+JPA2.0+JAX-RS with given below libraries:

• Java Persistence API (JPA) 2.0 (version: 1.0.2)
• Hibernate Core API (version: 4.1.9.Final)
• Hibernate JPA 2.0 API (version: 1.0.1.Final)
• Hibernate Commons Annotations (version: 3.2.0.Final)
• SpringSource (version: 3.2.1-Release)
• Oracle JDBC Driver version: 12.1.0.1
• Jackson JAX-RS (version: 1.9.11)

Concept Overview

JPA (Java Persistence API) - JPA is a persistence framework for Object Relational Mapping. It is based on POJO (Plain Old Java Object). It was born out of the complexity experienced in EJB2. It can be used in both EJB and non-EJB context.

EntityManager- EntityManager is part of the persistence API. The instance of EntityManager is associated with a persistence context. The EntityManager has methods such as persist, merge, remove, find etc. The persist method, for example, takes in an Entity and persists it. The EntityManager is produced by an implementation of EntityManagerFactory. The Entity Manager may be managed by the application (Application managed Entity Manager) or by the container (Container Manager Entity Manager).

LocalEntityManagedFactoryBean - This is a spring FactoryBean that creates an EntityManagerFactory. This is configured as spring bean and then injected into the DAO as a property. The configuration settings are passed through META-INF/persistence.xml. persistence.xml contains one or more persistence units. The persistence units contains one or more persistence classes along with the data source and mapping files. The persistence Unit is then injected into the LocalEntityManagedFactoryBean configuration in spring xml. Note that for spring, there is not much advantage in using a LocalEntityManagedFactoryBean.

JPA implementation patterns: Data Access Objects

The JPA is part of the Java EE 5 specification and has been implemented by Hibernate, TopLink, EclipseLink, OpenJPA, and a number of other object-relational mapping (ORM) frameworks. It provides only a thin layer on top of JPA, but more importantly making a DAO per entity type offers given below advantages:

• Theoretically, by using DAO's it's easier to switch to another persistence system (like plain JDBC or iBATIS). 
• Easier to maintain the the queries in a centralized fashion on a certain entity type instead of scattering them through code. Also, it offers named queries with parameters to keep queries with the entity type. 
• It defines standard methods to perform CRUD operation. Its easier to create DAO implementation using 3rd party libraries e.g. EmployeesDAO interface can be implemented using HibernateEmployeesDAOImpl or JdbcEmployeesDAOImpl - which can use JDBC API or Spring JdbcTemplate along with SQL Queries to perform operations.

The Benefits of Data Access Objects

Data Access Object (DAO) design pattern is a popular design pattern to implement persistence layer of Java application. DAO pattern is based on abstraction and encapsulation design principles and shields rest of application from any change on persistence layer e.g. change of database from Oracle to MySQL.

In the core of Data Access Object or DAO pattern is a Java interface, which defines various method to perform CRUD operation e.g. Create, Read, Update and Delete.

The DAO design pattern provides a technique for separating object persistence and data access logic from any particular persistence mechanism or API. There are clear benefits to this approach from an architectural perspective. The DAO approach provides flexibility to change an application’s persistence mechanism over time without the need to re-engineer application logic that interacts with the Data Access Object tier. For example, there may be performance benefits in changing an application’s performance mechanism to move to an alternative persistence framework, such as Spring Framework.

The DAO pattern which abstracts the details of the storage mechanism – be it a relational database, OO database, an XML file or whatever. The advantage of this is that you can easily implement different methods to persist objects without having to rewrite parts of your code.

The Data Access Object design pattern also provides a simple, consistent API for data access that does not require knowledge of JDBC, EJB, Hibernate, or Spring interfaces.

Mule Project Implementation

In the below example I would like to create a simple REST web service using HTTP Inbound Endpoint with Http-Connector along with REST/Jersey component. The HTTP request is handed by the REST/Jersey component. In addition to mule-flow:

• A Java class (Employees.java) created to represent the entity and annotated using Java persistence annotations.
• A REST resource (EmployeesResource.java) handles all employees in the database, search by a employee’s lastName, or retrieve a employee by ID. From the REST interface, the user can also generate a new employee and store it in the database. 
• The DAO class (EmployeesDao.java) handles database CRUD operations using Hibernate EntityManager. 
• The API can support both types of payload - XML or JSON. In this implementation, JSON is used & defined as as a payload (see: resource class for @Produces and @Consumes).

The Entity Class - Employees

This is the entity (Employee), which is inserted and selected from database using Hibernate. Hibernate based mapping is done using annotations @Entity and @Table which tells Hibernate which table to map this entity to DB Table.

• The mapping of identifier is done using annotations @Id and @Column (see lines 10-22 below). This demonstrates the usage of JPA annotations for Object Relational Mapping. 
• The class listing is shown below.
• The first objective was achieved by using Java persistence annotations. The Employee class was annotated using @Entity, while the ID getter was given annotations, @Id, which corresponds to the primary key of the EMPLOYEE table. The Employee class also annotated with the @XmlRootElement annotation for JAXB. In this example, default settings were used for both JAXB and JPA. 
• Entity class has two very important roles. Firstly, it will be used when entity-manager perform load or persist operation to the DB table; data from the database will be mapped into this class and vice versa. Secondly, this class will also translated the payload - XML/SON.

@Entity
@Table(name = "EMPLOYEES")
@XmlRootElement
@JsonPropertyOrder({"employeeId","firstName","lastName","email","phoneNumber","hireDate","departmentName"})
@NamedQueries({
@NamedQuery(name = "Employees.findAll", query = "SELECT e FROM Employees e"),
@NamedQuery(name = "Employees.findByEmployeeId", query = "SELECT e FROM Employees e WHERE e.employeeId = :employeeId")})
public class Employees implements Serializable {

@Id
@Basic(optional = false)
@Column(name = "EMPLOYEE_ID")
private String employeeId;

@Column(name = "FIRST_NAME")
private String firstName;

@Basic(optional = false)
@Column(name = "LAST_NAME")
private String lastName;

@Column(name = "EMAIL")
private String email;

@Column(name = "PHONE_NUMBER")
private String phoneNumber;

@Column(name = "HIRE_DATE", columnDefinition="TIMESTAMP DEFAULT CURRENT_TIMESTAMP")
@Temporal(TemporalType.TIMESTAMP)
private Date hireDate;

//Getters & Setters... {code left for brevity}
}

Derquinse Gist.GitHub Gadget

The DAO Interfaces
A typical Data Access Object interface is shown below.

DAOInterface
public interface EmployeesDAO{
public T persist(T entity);
public T update(T entity);
public T findById(K id);
public void delete(K id);
}

The first type parameter, K, is the type to use as the key and the second type parameter, T, is the type of the entity. Next to the basic persist, remove, and findById methods, you might also like to add a List findAll() method.

Database Access Object (DAO) Implementation Class - EmployeesDao

The DAO class (EmployeesDao.java) provides a link between resource class and Hibernate db-layer to translate the HTTP Methods call into the DB CRUD operations. This class maintains the persistence context of the database - Hibernate EntityManager objects, which performs CRUD operations against the database object.
Use the @PersistenceContext annotation which tells Spring to inject an instance of EntityManager into EmployeesDao.
Use JPA annotation @Transactional to define that transactions need to be used. Also note propagation = Propagation.REQUIRED which defines the transaction propagation is required.
The createEmployees() method inserts an Employee object into database (see lines 25-36 below). It uses JPA class EntityManager to insert the Employee object into database (see line 28 below)
Similarly, a list of all Employees is fetched from database using the findAll() method (see lines 7-11 below). It uses JPA class EntityManager to create the query and select all employees from database. The query to fetch all the Employees is based on JPA's object querying capabilities.

The DAO class creates & initialize the JPA’s entity-manager using Spring configurations defined under the file "spring/spring-config.xml" (covered in next section). The persistence unit-name ("empPU") should be same as defined under "META-INF/persistence.xml" and Spring configuration (spring-config.xml).
The DAO class invokes JPA queries using named-queries defined under Entity class to make the SQL operation against the database. The basic CRUD operations are handled by below methods:

1. A findAll() method to return all employees currently in the database
2. A findByEmployeeId() method which will return all employees with a given Id
3. A createEmployees() method which will create a new employees and persist it in the database,
4. A updateById() method.
The following is a code snippet for our EmployeesDao class:

Derquinse Gist.GitHub Gadget

REST Resource Class - EmployeesResource

From JPA, lets now move onto REST and define our REST interface, which is basically our communication channel with the consumer. Here we are using a couple of JAX-RS annotations, together with reference to EmployeesDao class implemented above to perform our database calls and mappings. Firstly, we will be annotating this class with the @Path annotation, denoting on which path we want our REST resource to be available. Furthermore, all methods are also annotated with the @Path annotation denoting on which path that particular method will be exposed. The @PathParam annotation is used to extract data from the URL and have that data mapped onto a simple Java parameter. @GET simply denotes that we are expecting a GET HTTP request. Other options are @POST, @DELETE, @PUT.
The Resource class (EmployeesResource.java) has important roles to expose the HTTP operations using GET/PUT/POST/DELETE Methods using JAX-RS standards. The Resource class consumes & produces the payload (JSON/Text) for HTTP requests.

As you can see in our REST resource class, methods is the @Produces annotation are annotated using @Produces(“application/json”). This simple annotation tells our REST resource whether it should use JAXB to return JSON data. Here, we need not to write any Java code to add this feature, only a simple annotation. And the @Path("/emp") annotation in the EmployeeResource specifies the path to the resource beyond the base-URL: http://localhost:8081/api

This class maintains the instance of the DAO objects in order to invoke the CRUD operations corresponding to HTTP Methods. If you have noticed about above code, it's annotated (@autowired) to bring dependency injection for DAO and EntityManager object.

The following is a code snippet for our EmployeesResource class:

Derquinse Gist.GitHub Gadget

Persistence.xml

JPA also requires a small configuration file persistence.xml which contains database information such as Hibernate dialect, JDBC driver, connection URL, username and password for database access, and the entities (the Java classes annotated with @Entity).
Create the persistence.xml file (see below) which is part of JPA's specification and store this file under 'src/main/resources/META-INF' directory.
Declare the persistence unit for this application (see line 4 below).
Mention the class that should be used for persistence. In this case, the Employee class is used for persistence (see line 6 below)
Here is the persistence.xml file from our project.
Derquinse Gist.GitHub Gadget

Spring-config.xml

Create the spring-config.xml file (see below) and store this file under 'src/main/resources/spring' directory.
Declare the context:component-scan and specify the package from which Spring will load the classes based on their annotations (see line 2 below).
Here is the spring-confg.xml file from our project.
Derquinse Gist.GitHub Gadget

The dependency injection chart for the spring-config is as follows:
Note the LocalContainerEntityManagerFactoryBean class which is the JPA entity manager for container managed persistence (see lines 4-15 below). Also note that the persistence unit name 'personPersistenceUnit' matches in name with the declaration in persistence.xml (listed above)
JPA Adapter for Hibernate is specified in lines 8-13.
JPA dialect for Hibernate is specified in line 9.
The transaction manager to be used for JPA persistence is specified in lines 16-18.
We tell Spring to use annotation driven transaction management (see line 3).
The database parameters defined for dataSource bean (see lines 19-24) correspond to the Oracle database.

Mule Flow

Now, more interesting & core parts, the Mule configuration. The Mule configuration is small & simple to define a HTTP inbound endpoint along with HTTP connector.

The Mule flow will go something like this:

Mule-Flow (mule-config.xml)

1. Jersey will be used in conjunction with the HTTP transport to expose the REST resource.
2. After Jersey invokes the appropriate method, the result is transformed into JSON using transformer.
The component element is wrapped in <jersey:resources> tags to be able to read and react to the JAX-RS annotations present in our component. The result is simply transformed to String using the standard Mule object-to-string-transformer.

Note: At the end of this blog, you will find a GitHub URL for the Mule project source code.

Here, we will tie up all the pieces described above using Mule-flow with REST/Jersey component. There are two (2) beans are defined in Spring: the employee DAO object which requires the entity manager to be wired in, and the REST resource which needs the Employee DAO to fire the queries on the database.

Derquinse Gist.GitHub Gadget

Sample Anypoint Application XML

The mule flow xml will be like something below:

Derquinse Gist.GitHub Gadget

Project Build & Testing

Build and Run the application as a mule application within the MuleStudio.

Run the above mule project and access the below url to perform the CRUD operations.

Operation HTTP Method URLs Description
get GET http://localhost:8081/api/emp/<id> Returns the specified Employee resource.
list GET http://localhost:8081/api/emp/all Retrieves the list of Employee resources available in the DB.
insert POST http://localhost:8081/api/emp Creates an Employee resource in the DB using the data included in the request.
update PUT http://localhost:8081/api/emp/<id> Updates the specified Employee resource DB using the data included in the request.
delete DELETE http://localhost:8081/api/emp/<id> Deletes the specified Employee resource.

Use any REST Client or CURL command to test the REST API URL after deploying the project in Mule. Some examples are shown below:

1. Get All Employees:

http://localhost:8081/api/emp/all

curl -i -X GET http://localhost:8081/api/emp/all

Output:

2. Get Employee by Id 101:

curl -i -X GET http://localhost:8081/api/emp/101

Response:

{"employeeId":"101","firstName":"John","lastName":"Doe","email":"john.doe","phoneNumber":"514-000-1000","hireDate":null,"departmentName":"Sales"}

3. Create Employee

curl -H "Content-Type: application/json" -d '{"employeeId": "102","firstName": "Jane","lastName": "Miller","email": "jane.miller","phoneNumber": "408-000-1000","departmentName": "IT"}' http://localhost:8081/api/emp

Response:

{"employeeId":"102","firstName":"Jane","lastName":"Miller","email":jane.miller,"phoneNumber":"408-000-1000","hireDate":null,"departmentName":"IT"}

Conclusion

This article discussed the implementation of the Data Access Layer using Hibernate libraries by providing a single, reusable implementation of a generic DAO. This implementation is presented using Hibernate, Spring and a JPA based environment. The result is a streamlined persistence layer, with no unnecessary clutter.

For a step by step details about setting up the Spring context and Mule project code, check out the full project and documentation on Github!