C3P0 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 C3P0 Spring configuration.

Create new project

Now in pom.xml add below XML fragment –

Add below in sharedLibraries

<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>com.mchange</groupId>
		<artifactId>c3p0</artifactId>
	</sharedLibrary>
</sharedLibraries>

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>com.mchange</groupId>
	<artifactId>c3p0</artifactId>
	<version>0.9.5.2</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="com.mchange.v2.c3p0.ComboPooledDataSource"
		destroy-method="close">
		<spring:property name="driverClass"
			value="oracle.jdbc.driver.OracleDriver" />
		<spring:property name="jdbcUrl"
			value="jdbc:oracle:thin:@localhost:1521/xe" />
		<spring:property name="user" value="HR" />
		<spring:property name="password" value="hr" />
		<spring:property name="minPoolSize" value="10" />
		<spring:property name="maxPoolSize" value="25" />
		<spring:property name="maxStatements" 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 – click configure

Import the jar in local m2 repository

Test the connection

Click ok

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

Add a select component in mule flow

Add a json transformation

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

For more details on c3p0 – please go through the c3p0 details – https://www.mchange.com/projects/c3p0

Source code for the project – http://mulesy.com/wp-content/uploads/2018/12/c3p0.zip