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;
}
}
dbConfigdefines the database connector’s configuration.db:insertdefines 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
