Java Articles

Advertisement

Spring Integration Jdbc Inbound Adapter

Share

The JDBC inbound channel adapter’s basic function is to execute a SQL query, extract the data and pass the result set encapsulated in the form of a Message onto the local channels.
Whereas the outbound adapter persists the data records into Database by reading off the channel. In this article, we will look into an example of JDBC inbound adapter.

The message payload will contain the entire result set as a List where each element represents one row and each row represents a Map of column values with the column name being the key value.

For simplicity sake, we have mentioned ResultSet in the diagram as the ResultSet is the base object from which the List object is created.

JDBC Inbound Adapter
JDBC Inbound Adapter

This example uses the following frameworks:

Advertisement
  1. Maven 3.2.3
  2. Spring 4.1.5.RELEASE
  3. Spring Integration 4.1.2.RELEASE
  4. Eclipse  as the IDE, version Luna 4.4.1.

Let’s begin with the example!
we will first add dependencies to your pom.xml.

Dependencies

Add the following dependencies:

  1. spring-core
  2. spring-context
  3. spring-integration-core
  4. spring-integration-jdbc – This is requited to access the jdbc adapter
  5. mysql-connector-java – We will be using MySQL as the Database so you need to add MySql driver

pom.xml:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.javarticles.spring.integration.jms</groupId>
<artifactId>springintegrationjms</artifactId>
<version>0.0.1-SNAPSHOT</version>

<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.integration</groupId>
<artifactId>spring-integration-core</artifactId>
<version>4.1.2.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework.integration</groupId>
<artifactId>spring-integration-jdbc</artifactId>
<version>4.1.2.RELEASE</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.26</version>
</dependency>
</dependencies>

<properties>
<spring.version>4.1.4.RELEASE</spring.version>
</properties>

</project>

JDBC Inbound Adapter Configuration

We will use the inbound adapters to query database and convert them to messages. Let’s first create some sample data.

We will use a table called ‘articles’ to store article name, category, tags and author.

db-schema.sq:

drop table if exists `articles`;
CREATE TABLE `articles` (
`ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`NAME` VARCHAR(100) NOT NULL,
`CATEGORY` VARCHAR(50) NOT NULL,
`TAGS` VARCHAR(100) NOT NULL,
`AUTHOR` VARCHAR(50) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

db-test-data.sql:

insert into articles(id, name, category, tags, author) values (1, "JdbcTemplate Example", "spring", "spring,jdbcTemplate", "Joe");
insert into articles(id, name, category, tags, author) values (2, "NamedParameterJdbcTemplate Example", "spring", "spring,jdbcTemplate", "Sam");
insert into articles(id, name, category, tags, author) values (3, "MVC Example", "spring", "spring", "Joe");

To initialize the database, we will use the element jdbc:initialize-database. We want both the scripts to be executed, first db-schema.sql to create schema and then db-test-data.sql which contains some insert statements.

The inbound channel adapter is declared using the jdbc integration element int-jdbc:inbound-channel-adapter. The SQL query is specified in the query attribute and the converted message is posted to channel specified in the id attribute. It is also given an instance of Datasource which will provide the relevant database connection details.

	<int-jdbc:inbound-channel-adapter id="dataChannel"
query="select * from articles where author='Joe'" data-source="dataSource" />

Based on the above inbound channel adapter, you can figure out the flow. First it will use the DataSource object to connect to the database. It will then issue query specified in the query attribute. The fetched records are transformed into spring integration’s Message object and then published on to the channel specified in id attribute.

The whole result set is converted into a single message with a payload of List records. By default, the type of payload is List<Map<String, Object>>. You may want you payload to be in form of POJO object in which case you need to specify your own row mapping strategy. See Spring Integration JDBC RowMapper Example for more details.

In case you want the query to be executed at regular intervals then you need to provide a poller element. For example,

<int-jdbc:inbound-channel-adapter id="dataChannel"
query="select * from articles where author='Joe'" data-source="dataSource">
<int:poller fixed-rate="1000" />
</>

jdbcInputApplicationContext.xml:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:int="http://www.springframework.org/schema/integration"
xmlns:jdbc="http://www.springframework.org/schema/jdbc"
xmlns:int-jdbc="http://www.springframework.org/schema/integration/jdbc"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:p="http://www.springframework.org/schema/p"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/integration
http://www.springframework.org/schema/integration/spring-integration.xsd
http://www.springframework.org/schema/integration/jdbc
http://www.springframework.org/schema/integration/jdbc/spring-integration-jdbc.xsd
http://www.springframework.org/schema/jdbc
http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd">

<int-jdbc:inbound-channel-adapter id="dataChannel"
query="select * from articles where author='Joe'" data-source="dataSource" />

<int:channel id="dataChannel">
<int:queue />
</int:channel>

<int:poller default="true" fixed-rate="100" />

<jdbc:initialize-database data-source="dataSource" enabled="true">
<jdbc:script location="classpath:db-schema.sql"/>
<jdbc:script location="classpath:db-test-data.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="mnrpass" />
</bean>
</beans>

JDBC Inbound Adapter Example

Let’s test it out. First we will load the application context. Next, we will retrieve the channel bean and receive the message posted.

SpringIntegrationJdbcInboundExample:

package com.javarticles.spring.integration.jdbc;

import java.io.IOException;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.messaging.Message;
import org.springframework.messaging.PollableChannel;

public class SpringIntegrationJdbcInboundExample {

public static void main(String[] args) throws InterruptedException, IOException, SQLException {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext(
"jdbcInboundApplicationContext.xml");
try {
PollableChannel pollableChannel = (PollableChannel) context.getBean("dataChannel");
Message<List<Map<String, Object>>> msg = (Message<List<Map<String, Object>>>) pollableChannel.receive(1000);
List<Map<String, Object>> rows = msg.getPayload();
for (Map<String, Object> row : rows) {
String name = (String) row.get("name");
String category = (String) row.get("category");
String author = (String) row.get("author");
System.out.println(name + "-" + category + "-" + author);
}
} finally {
context.close();
}
}

}

Output:

SpringIntegration Example-spring-Joe
MVC Example-spring-Joe

Download the source code

This was an example about JDBC Inbound Adapter. You can download the source code here: springintegrationJdbc.zip

Share

Advertisement

Related

Advertisement

Latest

Advertisement