Mule Db Insert Using Parameterized Query

0

In this article we will see how to perform an insert operation using Mule’s database connector. In this example we will see a parameterized style of query where the data is in form of mule expression.

Database setup

We use spring’s jdbc:initialize-database to create the necessary database setup.

applicationContext.xml:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:jdbc="http://www.springframework.org/schema/jdbc"
       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.xsd
		http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd
		">
	<jdbc:initialize-database data-source="dataSource">
		<jdbc:script location="classpath:db-schema.sql" />
	</jdbc:initialize-database>
	
	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost/test" />
		<property name="username" value="root" />
		<property name="password" value="admin" />
	</bean>
</beans>

We will create table NODE_STATUS during the start of the mule context.

db-schema.sql:

drop table if exists `NODE_STATUS`;
CREATE TABLE `NODE_STATUS` (
  `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `NAME` VARCHAR(100) NOT NULL,
  `STATUS`  VARCHAR(20),
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Database Insert Operation

We will import the spring application context to make sure table is created. Below POJO class represents the node_status table.

Node:

package com.javarticles.mule;

public class Node {
    private String nodeName;
    private String nodeStatus;

    public Node(String nodeName, String status) {
        this.nodeName = nodeName;
        this.nodeStatus = status;
    }

    public String getNodeName() {
        return nodeName;
    }

    public String getNodeStatus() {
        return nodeStatus;
    }

}
  1. dbConfig defines the database connector’s configuration.
  2. db:insert defines the insert operation.

Mule substitutes the Mule Expression Language (MEL) expressions inside a query with “?” to create a prepared statement.
The mule expression used is evaluated using the mule event.

In our example below, node object is passed in as the payload, node name and node status are evaluated from the payload using mule expression #[payload.nodeName] and #[payload.nodeStatus].
In flow insertNodeByParam, we use in-param to substitute the named parameters.

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:spring="http://www.springframework.org/schema/beans" 
      xmlns:springjdbc="http://www.springframework.org/schema/jdbc"
      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
                  http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-current.xsd
                  http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd">
    <spring:beans>
        <spring:import resource="applicationContext.xml"/>
    </spring:beans>
    
	<flow name="insertNode">
		<inbound-endpoint address="vm://insertNode"
			exchange-pattern="request-response" />

		<db:insert config-ref="dbConfig">
			<db:parameterized-query>INSERT INTO NODE_STATUS(NAME, STATUS) VALUES
				(#[payload.nodeName], #[payload.nodeStatus])</db:parameterized-query>
		</db:insert>
	</flow>

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

		<db:insert config-ref="dbConfig">
			<db:parameterized-query>INSERT INTO NODE_STATUS(NAME, STATUS) VALUES
				(:nodeName, :nodeStatus)</db:parameterized-query>
			<db:in-param name="nodeName" value="#[payload.nodeName]"/>
			<db:in-param name="nodeStatus" value="#[payload.nodeStatus]"/>
		</db:insert>
	</flow>
	
	<db:mysql-config name="dbConfig"
		url="jdbc:mysql://localhost:3306/test" password="admin">
		<db:connection-properties>
			<db:property key="user" value="root" />
		</db:connection-properties>
	</db:mysql-config>
</mule>

We will now create and send the node object to each of the above inbound endpoints and verify that the node is created.

MuleDbInsertExample:

package com.javarticles.mule;

import javax.sql.DataSource;

import org.mule.api.MuleContext;
import org.mule.api.MuleException;
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.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.init.DatabasePopulator;
import org.springframework.jdbc.datasource.init.DatabasePopulatorUtils;

public class MuleDbInsertExample {
    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 {            
            MuleClient muleClient = new MuleClient(muleContext);
            DataSource dataSource = (DataSource) muleContext.getRegistry().get("dataSource");
            JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
            
            Node node1 = new Node("Cluster1", "ACTIVE");           
            insertNode(muleClient, jdbcTemplate, node1, "vm://insertNode");
            
            Node node2 = new Node("Cluster2", "STARTING");           
            insertNode(muleClient, jdbcTemplate, node2, "vm://insertNodeByParam");
        } finally {
            muleContext.dispose();
        }
    }

    private static void insertNode(MuleClient muleClient, JdbcTemplate jt, Node node, String endpointUri) throws Exception {
        System.out.println("Invoke " + endpointUri);
        MuleMessage response = muleClient.send(endpointUri, node, null);  
        System.out.println("Response: " + response.getPayloadAsString());    
        verifyNodeStatus(jt, node.getNodeName());            
    }
    
    private static void verifyNodeStatus(JdbcTemplate jdbcTemplate, String nodeName) {
        try {
            String status = jdbcTemplate.queryForObject("SELECT STATUS FROM NODE_STATUS WHERE NAME = '" + 
                    nodeName + "'", String.class);
            System.out.println("Node status " + status);  
        } catch(EmptyResultDataAccessException e) {
        }                     
    }    
}

Output:

Invoke vm://insertNode
Response: 1
Node status ACTIVE
Invoke vm://insertNodeByParam
Response: 1
Node status STARTING

Download the source code

This was an example of Mule database connector performing an insert operation.

You can download the source code here: muleDbInsertExample.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.