DBCP2 Spring database connector

MuleSoft Database connector allow us to connect with any database using Spring configuration. Let’s see how we can connect with Database connector using DBCP2 Spring configuration.

Create new project

Now in pom.xml add below XML fragment –

Add below in sharedLibraries

						<sharedLibrary>
							<groupId>org.springframework</groupId>
							<artifactId>spring-beans</artifactId>
						</sharedLibrary>
						<sharedLibrary>
							<groupId>org.springframework.security</groupId>
							<artifactId>spring-security-core</artifactId>
						</sharedLibrary>
						<sharedLibrary>
							<groupId>org.springframework</groupId>
							<artifactId>spring-context</artifactId>
						</sharedLibrary>
						<sharedLibrary>
							<groupId>org.springframework</groupId>
							<artifactId>spring-core</artifactId>
						</sharedLibrary>
						<sharedLibrary>
							<groupId>org.apache.commons</groupId>
		    				<artifactId>commons-dbcp2</artifactId>
						</sharedLibrary>

And below dependency in dependencies

		<dependency>
			<groupId>org.mule.modules</groupId>
			<artifactId>mule-spring-module</artifactId>
			<version>1.1.0</version>
			<classifier>mule-plugin</classifier>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-beans</artifactId>
			<version>4.1.9.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.security</groupId>
			<artifactId>spring-security-core</artifactId>
			<version>4.1.5.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>4.1.9.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>4.1.9.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.apache.commons</groupId>
		    <artifactId>commons-dbcp2</artifactId>
		    <version>2.5.0</version>
		</dependency>
		<dependency>
			<groupId>org.apache.commons</groupId>
		    <artifactId>commons-dbcp2</artifactId>
		    <version>2.5.0</version>
		</dependency>

Dependency is already available in MuleSoft central repository

Create a spring-beans.xml and add it to resource folder

<spring:beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:spring="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 

	xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans-4.1.xsd
    ">
	<spring:bean id="xe-datasource" class="org.apache.commons.dbcp2.BasicDataSource"
		destroy-method="close">
		<spring:property name="driverClassName"
			value="oracle.jdbc.driver.OracleDriver" />
		<spring:property name="url"
			value="jdbc:oracle:thin:@localhost:1521/xe" />
		<spring:property name="username" value="HR" />
		<spring:property name="password" value="hr" />
		<spring:property name="initialSize" value="10" />
		<spring:property name="maxTotal" value="25" />
		<spring:property name="maxOpenPreparedStatements" value="10" />
	</spring:bean>
</spring:beans>

We need to update the mule-artifact.json also

{
  "minMuleVersion": "4.1.3",
  "classLoaderModelLoaderDescriptor": {
    "id": "mule",
    "attributes": {
      "exportedResources": [
      	"spring-beans.xml"
      ]
    }
  }
}

Add spring bean definition in global elements –

<spring:config name="Spring_Config" doc:name="Spring Config" doc:id="a3a2a010-4869-43df-85eb-1f95547f4c30" files="spring-beans.xml" />

Now create a DataSorce global element

We will connect with the Oracle XE database

Add ojdbc7.jar in project

Import the jar in .m2 repository

Test the connection

Click ok

It will show the entry in error, but we can ignore it

Add a select call in mule flow

Add a json transformation

Now if we call the API it will return all the rows in employees table

Before using dbcp2 please go through the details – https://commons.apache.org/proper/commons-dbcp

Sample project – http://mulesy.com/wp-content/uploads/2018/12/dbcp2.zip