Mule Database Connector Example

0

In this article we will look into the Mule’s database connector. We will first see how to configure the datasource. Next, we will use the configured database to execute DDL.

Include mule-module-db

First in order to use mule database connector, we need to add mule-module-db to the pom.xml. If you are using mysql then the mysql driver is to be added to the pom.xml.

pom.xml:

		<dependency>
			<groupId>org.mule.modules</groupId>
			<artifactId>mule-module-db</artifactId>
			<version>3.8.1</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.26</version>
		</dependency>		

Mule Db Config

In this example, we will be using mysql as the database. We will add db:mysql-config to our mule context and define the configuration parameters for the JDBC connection.

muleContext.xml:

<?xml version="1.0" encoding="UTF-8"?>
<mule xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:db="http://www.mulesoft.org/schema/mule/db"
	xsi:schemaLocation="http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
            http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd">

	<db:mysql-config name="dbConfig" url="jdbc:mysql://localhost:3306"
		password="admin">
		<db:connection-properties>
			<db:property key="user" value="root" />
		</db:connection-properties>
	</db:mysql-config>
</mule>

If it is oracle:

    <;db:oracle-config name="dbConfig" host="localhost" port="1521" instance="test" user="root"
                      password="admin"/>

Mule supports Mysql, oracle and derby out of the box. Any other database needs to be configured using the gereic configuration element <db:generic-config>.

Getting the datasource

In the below example we will show how to retrieve the datasource.

First retrieve the DbConfig component.

DbConfigResolver dbConfigResolver = muleContext.getRegistry().get("dbConfig");
DbConfig dbConfig = dbConfigResolver.resolve(null);

Next the data source.

DataSource dataSource = dbConfig.getDataSource();

Create database using spring JdbcTemplate.

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.update("CREATE DATABASE IF NOT EXISTS " + databaseName);

Verify database exists.

jdbcTemplate.queryForObject("SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA WHERE LOWER(SCHEMA_NAME) = '" + 
                    databaseName + "'", Integer.class);

MuleDbConfigExample:

package com.javarticles.mule;

import javax.sql.DataSource;

import org.mule.api.MuleContext;
import org.mule.api.MuleMessage;
import org.mule.api.context.MuleContextBuilder;
import org.mule.api.context.MuleContextFactory;
import org.mule.config.DefaultMuleConfiguration;
import org.mule.config.spring.SpringXmlConfigurationBuilder;
import org.mule.context.DefaultMuleContextBuilder;
import org.mule.context.DefaultMuleContextFactory;
import org.mule.module.client.MuleClient;
import org.mule.module.db.internal.resolver.database.DbConfigResolver;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;

public class MuleDbConfExample {
    public static void main(String[] args) throws Exception {
        DefaultMuleConfiguration dmc = new DefaultMuleConfiguration();
        dmc.setId("muleexample");
        dmc.setWorkingDirectory("/esb/mule");
        SpringXmlConfigurationBuilder configBuilder = new SpringXmlConfigurationBuilder(
                "muleContext.xml");
        MuleContextBuilder contextBuilder = new DefaultMuleContextBuilder();
        contextBuilder.setMuleConfiguration(dmc);
        MuleContextFactory contextFactory = new DefaultMuleContextFactory();
        MuleContext muleContext = contextFactory.createMuleContext(configBuilder,
                contextBuilder);
        muleContext.start();
        try {
            DbConfigResolver dbConfigResolver = muleContext.getRegistry().get("dbConfig");
            DataSource dataSource = dbConfigResolver.resolve(null).getDataSource();
            String databaseName = "test";
            JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
            jdbcTemplate.update("CREATE DATABASE IF NOT EXISTS " + databaseName);
            verifyDatabaseExists(jdbcTemplate, databaseName);
            jdbcTemplate.update("DROP DATABASE " + databaseName);
            verifyDatabaseExists(jdbcTemplate, databaseName);          
        } finally {
            muleContext.dispose();
        }
    }

    private static void verifyDatabaseExists(JdbcTemplate jdbcTemplate, String databaseName) {
        int result;
        try {
            result = jdbcTemplate.queryForObject("SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA WHERE LOWER(SCHEMA_NAME) = '" + 
                    databaseName + "'", Integer.class);
        } catch(EmptyResultDataAccessException e) {
            result = 0;
        }
        System.out.println("Database test created? " + (result == 1));                
    }    
}

Output:

Database test created? true
Database test created? false

Mule execute-ddl

In our next example, we will see how to execute DDL query against a database using mule flow.
We add <db:execute-ddl> and embed the DDL query within element <db:dynamic-query>. It refers to the database configuration using attribute config-ref. Payload sent to the inbound point is the database name, retrieved using expression #[payload].

muleContext.xml:

<?xml version="1.0" encoding="UTF-8"?>
<mule xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:db="http://www.mulesoft.org/schema/mule/db"
	xsi:schemaLocation="http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
            http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd">

	<flow name="createDatabase">
		<inbound-endpoint address="vm://createDatabase"
			exchange-pattern="request-response" />

		<db:execute-ddl config-ref="dbConfig">
			<db:dynamic-query>CREATE DATABASE #[payload]</db:dynamic-query>
		</db:execute-ddl>
	</flow>

	<db:mysql-config name="dbConfig" url="jdbc:mysql://localhost:3306"
		password="admin">
		<db:connection-properties>
			<db:property key="user" value="root" />
		</db:connection-properties>
	</db:mysql-config>
</mule>

Mule database

We pass the database name to the inbound endpoint vm://createDatabase which is then passed on to the next message processor <db:execute-ddl> in the flow that is responsible to execute the DDL. The DDL query is contained within the element <db:dynamic-query>. The database is retrieved from the payload using the expression #[payload].

CREATE DATABASE #[payload]

MuleDbConfigExample:

package com.javarticles.mule;

import javax.sql.DataSource;

import org.mule.api.MuleContext;
import org.mule.api.MuleMessage;
import org.mule.api.context.MuleContextBuilder;
import org.mule.api.context.MuleContextFactory;
import org.mule.config.DefaultMuleConfiguration;
import org.mule.config.spring.SpringXmlConfigurationBuilder;
import org.mule.context.DefaultMuleContextBuilder;
import org.mule.context.DefaultMuleContextFactory;
import org.mule.module.client.MuleClient;
import org.mule.module.db.internal.resolver.database.DbConfigResolver;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;

public class MuleDbConfExample {
    public static void main(String[] args) throws Exception {
        DefaultMuleConfiguration dmc = new DefaultMuleConfiguration();
        dmc.setId("muleexample");
        dmc.setWorkingDirectory("/esb/mule");
        SpringXmlConfigurationBuilder configBuilder = new SpringXmlConfigurationBuilder(
                "muleContext.xml");
        MuleContextBuilder contextBuilder = new DefaultMuleContextBuilder();
        contextBuilder.setMuleConfiguration(dmc);
        MuleContextFactory contextFactory = new DefaultMuleContextFactory();
        MuleContext muleContext = contextFactory.createMuleContext(configBuilder,
                contextBuilder);
        muleContext.start();
        try {
            DbConfigResolver dbConfigResolver = muleContext.getRegistry().get("dbConfig");
            DataSource dataSource = dbConfigResolver.resolve(null).getDataSource();
            String databaseName = "test";
            JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
            jdbcTemplate.update("CREATE DATABASE IF NOT EXISTS " + databaseName);
            verifyDatabaseExists(jdbcTemplate, databaseName);
            jdbcTemplate.update("DROP DATABASE " + databaseName);
            verifyDatabaseExists(jdbcTemplate, databaseName);
            
            MuleClient muleClient = new MuleClient(muleContext);
            String endpointUri = "vm://createDatabase";
            System.out.println("Invoke " + endpointUri);
            MuleMessage response = muleClient.send(endpointUri, databaseName, null);  
            System.out.println("Response: " + response.getPayloadAsString());          
            verifyDatabaseExists(jdbcTemplate, databaseName);            
        } finally {
            muleContext.dispose();
        }
    }

    private static void verifyDatabaseExists(JdbcTemplate jdbcTemplate, String databaseName) {
        int result;
        try {
            result = jdbcTemplate.queryForObject("SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA WHERE LOWER(SCHEMA_NAME) = '" + 
                    databaseName + "'", Integer.class);
        } catch(EmptyResultDataAccessException e) {
            result = 0;
        }
        System.out.println("Database test created? " + (result == 1));                
    }    
}

Output:

Database test created? true
Database test created? false
Invoke vm://createDatabase
Response: 1
Database test created? true

Download the source code

This was an example about executing ddl using Mule.

You can download the source code here: muleExecuteDdlExample.zip

About Author

Ram's expertise lies in test driven development and re-factoring. He is passionate about open source technologies and loves blogging on various java and open-source technologies like spring. You can reach him at [email protected]

Comments are closed.