Using Spring Batch Database Reader

In an earlier post, I described some of the features and benefits that the Spring Batch framework could bring to Oracle SOA projects that need to handle large data volumes.  One of the components that I really like is the JDBC Database Reader because it offers a number of features that are not available with the standard Oracle DB Adapter.

Most of the Spring Batch examples on the internet are geared for Eclipse IDE users and Java projects using Maven and it might seem a bit difficult to see how Spring Batch could be used with Oracle SOA Suite.  However, thanks to the excellent Spring Service Component introduced in Oracle JDeveloper 11.1.1.3, its actually very simple.  In this blog post, I will provide a step-by-step example.      

In the code and screen shots listed below, I am using:

• Oracle JDeveloper 11.1.1.4
• Oracle SOA Suite 11.1.1.4 running on a Weblogic server
• Spring Batch 2.1.8

Example Scenario

Assume that there is a Product table in a database that contains a large number of rows.   We have a BPEL process that needs to consume this data and can not use the standard Oracle DB Adapter because the large number of rows causes out-of-memory errors when run. 


Setup Environment

Before you begin, you will need to do a few things:

• Download the Spring Batch distribution from here and unzip the files to a local directory.
• Copy the Spring Batch distribution JAR files to the Weblogic server SOA domain directory and restart the Weblogic servers.  In my environment, the Weblogic directory is named C:\Oracle\Middleware1034\user_projects\domains\soa\lib 
• Install the JDeveloper extension named Spring and Oracle Weblogic SCA.  You can do this from the JDeveloper update menu or obtain it from here.
• Use the Weblogic Administrators Console to create a new JDBC data source that points to the database where the Product table exists.  In my environment, this data source is named jdbc/ProductDBDS.
• Use JDeveloper to create a new SOA project.
• Add the Spring Batch JAR files to the JDeveloper project by selecting Project Properties / Libraries and Classpath / Add Jars.  Here's a screen shot of what it should look like.

 

   
Create Java Files

The next step is to create four new Java class files. First, we will create a Java class named Product.java to represent the the Product object that we want to read from the database.  This class will contain attributes like ID, name and price and will also contain a simple method  to convert the object to XML.

public class Product implements Serializable {
    private Integer ID;
    private Integer categoryID;
    private String name;
    private String manufacturer;
    private Integer price;

    // standard setters and getters

...

    public String toXML() {
      return "<Product><ID>" + ID + "</ID>" +
             "<CategoryID>" + categoryID + "</CategoryID>" +
             "<Name>" + name + "</Name>" +
             "<Manufacturer>" + manufacturer + "</Manufacturer>" +
             "<Price>" + price + "</Price>" +
             "</Product>";
    }

Next, we create a Java interface file named ProductReaderInterface.java.  This file contains the methods that will be exposed to the SOA BPEL application.  We will have a method named initialize that will be used to specify the query that we want to execute.  The method getNextRecord will be used to fetch the next available record from the database and return it to the SOA BPEL code.

/**
 * Interface definition for the methods exposed to the SOA composite application
 */
public interface ProductReaderInterface { 
  String initialize(String SQL);
  String getNextRecord();
}

Then, we create a Java class named ProductReader.java that implements the methods defined in the interface that we just created.  The complete source code is listed below.

package test;

import java.sql.Connection;
import java.sql.ResultSet;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import org.springframework.batch.item.ExecutionContext;
import org.springframework.batch.item.database.JdbcCursorItemReader;

/**
 * Provides methods to retrieve Product information using the
 * Spring Batch JdbcCursorItemReader interface.
 *
 */
public class ProductReader implements ProductReaderInterface {
    Connection connection;
    ResultSet resultSet;
    JdbcCursorItemReader itemReader;
    ExecutionContext executionContext;
   
    /**
       * Constructor
     */
    public ProductReader() {     
    }

    /**
     * Prepare the Spring Batch Reader for operations.
     * @param sql - The SQL statement that should be executed
     * @return value of Success or Exception
     */
    public String initialize(String sql) {
        String returnValue = "Success";
       
        itemReader = new JdbcCursorItemReader();

        try {

          itemReader.setDataSource(getDataSource());
           
          itemReader.setSql(sql);
          itemReader.setRowMapper(new ProductRowMapper());
       
          executionContext = new ExecutionContext();
          itemReader.open(executionContext);
           
        } catch (Exception e) {
          System.out.println("Initialize method:  Error " + e.toString());    
          returnValue = "Exception";
        }
       
      return returnValue;           
    }

    /**
     * Fetch the next record and return an XML representation.
     * If no record is available, return an empty string.
     * @return XML representation of Product
     */
    public String getNextRecord() {
      String returnValue = "";     
 
      try {     
        Product product = (Product)itemReader.read();
   
        if (product != null) {
          returnValue = product.toXML();           
        }
      } catch (Exception e) {
        System.out.println("Read method: Error " + e.toString());
        returnValue = e.toString();
      }
     
      return returnValue;
    }

    /**
     * Get the JDBC data source from the application server
     * @return DataSource
     */
    private DataSource getDataSource() {
      Context ctx;
      DataSource ds = null;
     
      try {
        ctx = new InitialContext();
        ds = (DataSource)ctx.lookup("/jdbc/ProductDBDS");
      } catch (NamingException e) {
        System.out.println("GetDataSource Method: Error " + e.toString());    
      }
     
      return ds;     
    }
}

Finally, create a Java class named ProductRowMapper.java that will convert the database table columns into the attributes needed by the Product object.

package test;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

/**
 * Converts a row of data into an instance of a Product object.
 */
public class ProductRowMapper implements RowMapper{

  // List of Product database table column names    
  public static final String ID_COLUMN = "ID";
  public static final String CATEGORY_ID_COLUMN = "CATEGORY_ID";
  public static final String NAME_COLUMN = "NAME";
  public static final String MANUFACTURER_COLUMN = "MANUFACTURER";
  public static final String PRICE_COLUMN = "PRICE";

  public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
      Product product = new Product();

      product.setID(rs.getInt(ID_COLUMN));
      product.setCategoryID(rs.getInt(CATEGORY_ID_COLUMN));
      product.setName(rs.getString(NAME_COLUMN));
      product.setManufacturer(rs.getString(MANUFACTURER_COLUMN));
      product.setPrice(rs.getInt(PRICE_COLUMN));     
      return product;
  }
}

Create SOA Components

The next major step is to create the SOA components needed by our application.  First, drag a Spring Context Component from the JDeveloper component palette into the main canvas for the composite.xml file and name it BatchProductReader.

JDeveloper will create a source file named BatchProductReader.xml and you will need to add the following lines.

  <!--Spring Bean definitions go here-->
  <bean class="test.ProductReader" id="impl" />
  <sca:service name="BatchProductReader" target="impl" type="test.ProductReaderInterface" />

These statements establish the relationship between the Spring Component (BatchProductReader), the Java Interface (ProductReaderInterface) and the Java Bean (ProductReader) that implements the interface.

Next, drag a BPEL Component from the JDeveloper component palette into the main canvas for the composite.xml file and name it ExampleBPELProcess.

Next, wire the BPEL process and the Spring Component together as shown below.






Our last step will be to add logic to the ExampleBPELProcess to be able to invoke and process the records returned by the BatchProductReader component.  The complete BPEL process is shown below.



It's hard to see the details from this diagram, so let me explain the major steps in more detail.
When  the BPEL process begins, we will add an Invoke activity that calls the initialize method of the BatchProductReader.  Next, we add an Assign activity that sets the value for the input parameter -- which is the SQL query.  In our example, this query will be simply "select * from products".





Once the BatchProductReader is initialized, we will create a simple loop that gets the next available record and performs some additional processing.  In our example, this will look like:






Wrap Up

While these examples may look complicated, they are actually very simple and quick to create.  During actual operation, the performance is excellent and the ability to process one database record at a time makes for a very simple BPEL process.

The complete code for this example can be found here.