Using JPA in Mule ESB application

Updated on 30 May, 2018 in Mule ESB

In the previous tutorial, I introduced you to the Database connector to connect and manipulate many types of databases in the Mule ESB application. Unfortunately, when using this Database connector, we have to use Native Query, each type of database, we have to have query syntax that matches that type of database. To solve this problem, we can use the JPA specification with the implementation of Hibernate in our Mule ESB application through the Mule JPA module. What is it in details? In this tutorial, I will guide you all.

First, you need to install Mule JPA module into Anypoint Studio! See instruction in this tutorial.

Now, I will create a new Mule project as an example:

Our Mule Flow will have the following content:

Inside:

HTTP Listener Connector Endpoint: Used to create a request URL for the client to request to our Mule ESB application and retrieve all the information of the students with name passed in the request parameter.
Java Peristence API Endpoint: Used to connect and manipulate the database.
Object to JSON Transformer Endpoint: This transformer converts the result from the database to a JSON string and returns the result to the client.

Endpoint Object to JSON Transformer we do not need to configure anything.

Configuring the HTTP Listener Connector

The HTTP Listener Connector is configured as follows:

General Settings:

If someone does not know how to configure the HTTP Listener Connector, you can refer to this tutorial.

The request URL in this example looks like as below:

http://localhost:8081/jpa/student?name=Khanh

Configuring the Java Persistence API

Before going to the configuration section for Mule JPA module, we need to prepare some things first.

The first is: define a database structure to store student information and define the its entity.

For simplicity, in this example, I only define a table containing student information with two columns:

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

CREATE TABLE `student` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`name` varchar(50) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

Entity of table student has the following contents:

package com.huongdanjava.muleesbjpa.entity;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

@Table(name ="student")
@Entity
public class Student implements Serializable {

	private static final long serialVersionUID = 1L;

	@Id
	@GeneratedValue
	private Long id;

	@Column
	private String name;

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	@Override
	public String toString() {
		return "Student [id=" + id + ", name=" + name + "]";
	}


}

package com.huongdanjava.muleesbjpa.entity;

import java.io.Serializable;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.Id;

import javax.persistence.Table;

@Table(name ="student")

@Entity

public class Student implements Serializable {

private static final long serialVersionUID = 1L;

@Id

@GeneratedValue

private Long id;

@Column

private String name;

public Long getId() {

return id;

}

public void setId(Long id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

@Override

public String toString() {

return "Student [id=" + id + ", name=" + name + "]";

}

Next, to work with JPA with the implementation of Hibernate, we need to add their dependencies.

Open the pom.xml file and add the following dependencies:

Hibernate

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>4.3.11.Final</version>
</dependency>

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-entitymanager</artifactId>
    <version>4.3.11.Final</version>
</dependency>

<groupId>org.hibernate</groupId>

<artifactId>hibernate-core</artifactId>

<version>4.3.11.Final</version>

</dependency>

<groupId>org.hibernate</groupId>

<artifactId>hibernate-entitymanager</artifactId>

<version>4.3.11.Final</version>

</dependency>

MySQL Connector

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>6.0.6</version>
</dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

</dependency>

At this point, you will see in the Referenced Libraries, we have two versions of the Hibernate JPA API library: 2.0 and 2.1.

Version 2.0 is included by mule-module-jpa library and 2.1 is included by Hibernate. To avoid conflict, you should exclude a version. Here, I will exclude version 2.0 from the mule-module-jpa library:

<dependency>
    <groupId>org.mule.modules</groupId>
    <artifactId>mule-module-jpa</artifactId>
    <version>1.2.0</version>
    <exclusions>
        <exclusion>
            <groupId>org.hibernate.javax.persistence</groupId>
            <artifactId>hibernate-jpa-2.0-api</artifactId>
        </exclusion>
    </exclusions>
</dependency>

<groupId>org.mule.modules</groupId>

<artifactId>mule-module-jpa</artifactId>

<groupId>org.hibernate.javax.persistence</groupId>

<artifactId>hibernate-jpa-2.0-api</artifactId>

</exclusion>

</exclusions>

</dependency>

To work with JPA, we need to have the configuration file for it.

Create a new persistence.xml file located in /src/main/resources/META-INF.

The contents of this file may be as follows:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<persistence 
	version="2.1" 
	xmlns="http://xmlns.jcp.org/xml/ns/persistence" 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  	xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
	<persistence-unit name="mulePU" transaction-type="RESOURCE_LOCAL">
		<provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
		<class>com.huongdanjava.muleesbjpa.entity.Student</class>
		<exclude-unlisted-classes />
		<properties>
			<property name="hibernate.show_sql" value="true" />
			<property name="hibernate.format_sql" value="false" />
			<property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect" />
			<property name="hibernate.connection.driver_class" value="com.mysql.cj.jdbc.Driver" />
			<property name="javax.persistence.validation.mode" value="none" />
		</properties>
	</persistence-unit>
</persistence>

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<persistence

version="2.1"

xmlns="http://xmlns.jcp.org/xml/ns/persistence"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">

<persistence-unit name="mulePU" transaction-type="RESOURCE_LOCAL">

<provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>

<class>com.huongdanjava.muleesbjpa.entity.Student</class>

<exclude-unlisted-classes />

</properties>

</persistence-unit>

</persistence>

Depending on your needs, please edit accordingly.

The final step is to declare the EntityManagerFactory object in the Spring container.

This is a must have step, you will use it to configure it for Mule JPA module.

Here, I created the spring.xml file located in /src/main/resources and declared the EntityManagerFactory object as follows:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="
		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd 
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">

	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:3306/muleesb_jpa" />
		<property name="username" value="root" />
		<property name="password" value="123456" />
	</bean>

	<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="persistenceUnitName" value="mulePU" />
		<property name="jpaVendorAdapter">
			<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
		</property>
	</bean>

</beans>

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns:context="http://www.springframework.org/schema/context"

xmlns:tx="http://www.springframework.org/schema/tx"

xsi:schemaLocation="

http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd

http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd

http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">

</bean>

</property>

</bean>

</beans>

And include it in our Mule Flow mule-esb-jpa.xml file:

<spring:beans>
    <spring:import resource="classpath:spring.xml" />
</spring:beans>

<spring:beans>

<spring:import resource="classpath:spring.xml" />

</spring:beans>

OK, so we finished the preparation, now it’s time to configure Mule JPA module.

The configuration section of Mule JPA module has the following contents:

As you can see, here we have 2 sections that need to be configured for the Mule JPA module:

The first is the Config Reference.

This section, if you click the plus sign next to the drop-down menu, you will see the following window:

As you can see, we need to configure the Entity Manager Factory Reference, which is the configuration pointing to the EntityManagerFactory object that we have declared in the Spring container.

Let fill in the id of the EntityManagerFactory object as follows:

The second part is the configuration of Operation that we want to execute.

Here, we have the following operations:

In this example, we will select Operation as Query. At this point, our General will look like this:

We have many fields to configure:

Criteria Reference
Named Query
Statement
Query Parameters Reference.

Here, I will directly declare the SQL statement in the Statement field to find all students by Student name:

FROM Student student WHERE student.name = :name

1	FROM Student student WHERE student.name = :name

In the Query Parameters Reference, we will configure a Map object containing a “name” as key and the value field will be the student name that we want to find.

My configuration is as follows:

Here, I get the request parameter from Mule Message’s inboundProperties. It is a Map object so you do not need to convert anything.

…

OK, so I finished configuring my Mule Flow.

Assume the database has the following contents:

Then when we run our example, the result will look like this:

One note for our application when running can connect to MySQL database or any other database system, you must copy its JDBC Driver dependency to the mule/lib/boot directory in the installation folder of Mule Runtime Server. If you are using Anypoint Studio, then in the Run Configurations section of the application, select the Classpath tab, select Bootstrap Entries and then select Add External JARs …, choose the .jar file of the JDBC Driver dependency.

Myself is as follows:

Previous Post: Configure Database connector in Anypoint Studio

Next Post: Some important concepts of Docker

7 thoughts on “Using JPA in Mule ESB application”

Daniel Perez

12 June, 2018 at 7:07 am
Reply

Hi is me again, you have examples or resources where i found how can I use the other types of operations in the jpa connector (Detach, Find, Merge, Persist, Upsert).. my goal is remove SQL dependecy in case of need make changes to db..
first of all, Thanks
Daniel Perez

29 May, 2018 at 10:53 pm
Reply

yes https://github.com/lafuented/jpa-mule-test
1. Khanh Nguyen
  
  30 May, 2018 at 6:05 am
  Reply
  
  Hi Daniel,
  
  Please see my updates for this tutorial in the end. There was one missing step when running our Mule application.
  
  Thanks for your feedback. That helps me a lot.
  1. Daniel Perez
    
    30 May, 2018 at 7:04 am
    Reply
    
    You are big man, i’m work in a university, developing systems and research new technologies and mule is a great tool, we have some challenges but persons like you are a helpful.
    in this time we have a problem when a team share a project with cmi connector, cloning form GIT, when run, anypoint not recognise you have CMI installed you need make new project and copy the flows from clone project to new project. you konow this problem?.
    1. Khanh Nguyen
      
      30 May, 2018 at 12:31 pm
      Reply
      
      Hi Daniel,
      
      I don’t know what the CMI connector is. If you can share your project, I can take a look and let you know the problem.
Daniel Perez

29 May, 2018 at 9:50 pm
Reply

Hi i try implement your sample but i have a problem

this is my log
in the firs part the system found the postgres driver
INFO 2018-05-29 09:41:25,780 [main] org.mule.lifecycle.AbstractLifecycleManager: Initialising RegistryBroker
INFO 2018-05-29 09:41:25,920 [main] org.mule.module.extension.internal.manager.DefaultExtensionManager: Starting discovery of extensions
INFO 2018-05-29 09:41:26,105 [main] org.mule.module.extension.internal.manager.DefaultExtensionManager: Discovered 1 extensions
INFO 2018-05-29 09:41:26,106 [main] org.mule.module.extension.internal.manager.DefaultExtensionManager: Registering extension validation (version 3.8)
INFO 2018-05-29 09:41:26,246 [main] org.mule.config.spring.MuleArtifactContext: Refreshing org.mule.config.spring.MuleArtifactContext@6bb75258: startup date [Tue May 29 09:41:26 CDT 2018]; root of context hierarchy
INFO 2018-05-29 09:41:28,514 [main] org.mule.config.spring.processors.NoDevkitInjectorProcessor: JSR-330 ‘javax.inject.Inject’ annotation found and supported for autowiring
INFO 2018-05-29 09:41:28,722 [main] org.springframework.jdbc.datasource.DriverManagerDataSource: Loaded JDBC driver: org.postgresql.Driver
INFO 2018-05-29 09:41:28,851 [main] org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean: Building JPA container EntityManagerFactory for persistence unit ‘mulePU’
WARN 2018-05-29 09:41:28,855 [main] org.hibernate.ejb.HibernatePersistence: HHH015016: Encountered a deprecated javax.persistence.spi.PersistenceProvider [org.hibernate.ejb.HibernatePersistence]; use [org.hibernate.jpa.HibernatePersistenceProvider] instead.
INFO 2018-05-29 09:41:28,899 [main] org.hibernate.jpa.internal.util.LogHelper: HHH000204: Processing PersistenceUnitInfo [
name: mulePU
…]
But the next no found the postgres driver where is my problem i folow yours instructions only change the destination db for postgres .. ??
INFO 2018-05-29 09:41:29,228 [main] org.hibernate.Version: HHH000412: Hibernate Core {4.3.11.Final}
INFO 2018-05-29 09:41:29,236 [main] org.hibernate.cfg.Environment: HHH000206: hibernate.properties not found
INFO 2018-05-29 09:41:29,241 [main] org.hibernate.cfg.Environment: HHH000021: Bytecode provider name : javassist
INFO 2018-05-29 09:41:30,167 [main] org.hibernate.annotations.common.Version: HCANN000001: Hibernate Commons Annotations {4.0.5.Final}
WARN 2018-05-29 09:41:30,343 [main] org.hibernate.engine.jdbc.internal.JdbcServicesImpl: HHH000342: Could not obtain connection to query metadata : No suitable driver found for jdbc:postgresql://localhost:5432/muleesb_jpa
INFO 2018-05-29 09:41:30,370 [main] org.hibernate.dialect.Dialect: HHH000400: Using dialect: org.hibernate.dialect.PostgreSQLDialect
INFO 2018-05-29 09:41:30,404 [main] org.hibernate.engine.jdbc.internal.LobCreatorBuilder: HHH000422: Disabling contextual LOB creation as connection was null
INFO 2018-05-29 09:41:30,691 [main] org.hibernate.hql.internal.ast.ASTQueryTranslatorFactory: HHH000397: Using ASTQueryTranslatorFactory
WARN 2018-05-29 09:41:31,474 [main] org.hibernate.internal.SessionFactoryImpl: HHH000008: JTASessionContext being used with JDBCTransactionFactory; auto-flush will not operate correctly with getCurrentSession()
WARN 2018-05-29 09:41:32,896 [main] org.mule.module.ognl.expression.OgnlExpressionEvaluator: OGNL module is deprecated and will be removed in Mule 4.0. Use MEL expressions instead.
INFO 2018-05-29 09:41:33,411 [main] org.mule.util.journal.TransactionJournal: Using files for tx logs C:\Users\Daniel\AnypointStudio\workspace\.mule\.\.mule\jpa-ejemplo\queue-tx-log\tx1.log and C:\Users\Daniel\AnypointStudio\workspace\.mule\.\.mule\jpa-ejemplo\queue-tx-log\tx2.log
INFO 2018-05-29 09:41:33,437 [main] org.mule.util.journal.TransactionJournal: Using files for tx logs C:\Users\Daniel\AnypointStudio\workspace\.mule\.\.mule\jpa-ejemplo\queue-xa-tx-log\tx1.log and C:\Users\Daniel\AnypointStudio\workspace\.mule\.\.mule\jpa-ejemplo\queue-xa-tx-log\tx2.log
INFO 2018-05-29 09:41:33,485 [main] org.mule.lifecycle.AbstractLifecycleManager: Initialising model: _muleSystemModel
INFO 2018-05-29 09:41:33,491 [main] org.mule.construct.FlowConstructLifecycleManager: Initialising flow: jpa-ejemploFlow
INFO 2018-05-29 09:41:33,492 [main] org.mule.exception.DefaultMessagingExceptionStrategy: Initialising exception listener: org.mule.exception.DefaultMessagingExceptionStrategy@78116659
INFO 2018-05-29 09:41:33,554 [main] org.mule.processor.SedaStageLifecycleManager: Initialising service: jpa-ejemploFlow.stage1
INFO 2018-05-29 09:41:33,852 [main] org.mule.config.spring.SpringXmlConfigurationBuilder: Configured Mule using “org.mule.config.spring.SpringXmlConfigurationBuilder” with configuration resource(s): “[ConfigResource{resourceName=’C:\Users\Daniel\AnypointStudio\workspace\.mule\apps\jpa-ejemplo\jpa-ejemplo.xml’}]”
INFO 2018-05-29 09:41:33,853 [main] org.mule.config.builders.AutoConfigurationBuilder: Configured Mule using “org.mule.config.builders.AutoConfigurationBuilder” with configuration resource(s): “[ConfigResource{resourceName=’C:\Users\Daniel\AnypointStudio\workspace\.mule\apps\jpa-ejemplo\jpa-ejemplo.xml’}]”
INFO 2018-05-29 09:41:33,854 [main] org.mule.module.launcher.application.DefaultMuleApplication:

Tnx for your post.
1. Khanh Nguyen
  
  29 May, 2018 at 10:25 pm
  Reply
  
  Hi Daniel,
  
  Can you share your project? I think there should be some missing.
  
  Thanks,

Khanh Nguyen

Share this post

Tags

Using JPA in Mule ESB application

Configuring the HTTP Listener Connector

Configuring the Java Persistence API

7 thoughts on “Using JPA in Mule ESB application”

Add Comment Cancel reply

Khanh Nguyen

Share this post

Tags

Configuring the HTTP Listener Connector

Configuring the Java Persistence API

You might also like...

Using reduce() function in DataWeave

Using pluck() function in DataWeave 2

Dynamic property key in Mule 4

Basic operations with String in DataWeave 1.0

7 thoughts on “Using JPA in Mule ESB application”

Add Comment Cancel reply