Transforming Data

Exclusive offer: get 50% off this eBook here
Drools JBoss Rules 5.X Developer’s Guide

Drools JBoss Rules 5.X Developer’s Guide — Save 50%

Define and execute your business rules with Drools with this book and ebook

$29.99    $15.00
by Michal Bali | August 2013 | JBoss Open Source

In this article by Michal Bali, the author of Drools JBoss Rules 5.X Developer's Guide, we will look at Almost any rewrite of an existing legacy system needs to do some kind of data transformation with the old legacy data before it can be used in the new system. It needs to load the data and transform them so that they meet the requirements of the new system and finally store them. This is just one example of where data transformation is needed.

Drools can help us with this data transformation task as well. Depending on our requirements it might be a good idea to isolate this transformation process in the form of rules. The rules can be reused later, maybe when our business will expand and we'll be converting data from a different third-party system. Of course, other advantages of using rules apply.

If performance is the most important requirement (for example, all data has to be converted within a specified time frame), rules may not be the ideal approach. Probably, the biggest disadvantage of using rules is that they need the legacy data in memory, so they are best suited to more complex data transformation tasks.

However, consider carefully if your data transformation will grow in complexity as more requirements are added.When writing these transformation rules, care should be taken not to confuse them with validation rules. In a nutshell, if a rule can be written working just with the domain model, it is most likely a validation rule. If it uses concepts that cannot be represented with our domain model, it is probably a transformation rule.

(For more resources related to this topic, see here.)

Process overview

Let's demonstrate this data transformation on an example. Imagine that we need to convert some legacy customer data into our new system.

Since not all legacy data is perfect, we'll need a way to report data that, for some reason, we've failed to transfer. For example, our system allows only one address per customer. Legacy customers with more than one address will be reported.

In this article we'll:

  • Load customer data from the legacy system. The data will include address and account information.
  • Run transformation rules over this data and build an execution report.
  • Populate the domain model with transformed data, running validation rules (from the previous section) and saving it into our system.

Getting the data

As a good practice, we'll define an interface for interacting with the other system. We'll introduce a LegacyBankService interface for this purpose. It will make it easier to change the way we communicate with the legacy system, and also the tests will be easier to write.

package droolsbook.transform.service;
import java.util.List;
import java.util.Map;
public interface LegacyBankService {
* @return all customers
List<Map<String, Object>> findAllCustomers();
* @return addresses for specified customer id
List<Map<String, Object>> findAddressByCustomerId(
Long customerId);
* @return accounts for specified customer id
List<Map<String, Object>> findAccountByCustomerId(
Long customerId);

Code listing 1: Interface that abstracts the legacy system interactions

The interface defines three methods. The first one can retrieve a list of all customers, and the second and third ones retrieve a list of addresses and accounts for a specific customer. Each list contains zero or many maps. One map represents one object in the legacy system. The keys of this map are object property names (for example, addressLine1), and the values are the actual properties.

We've chosen a map because it is a generic data type that can store almost any data, which is ideal for a data transformation task. However, it has a slight disadvantage in that the rules will be a bit harder to write.

The implementation of this interface will be defined at the end of this article.

Loading facts into the rule session

Before writing some transformation rules, the data needs to be loaded into the rule session. This can be done by writing a specialized rule just for this purpose,as follows:

package droolsbook.transform;
import java.util.*;
import droolsbook.transform.service.LegacyBankService;
global LegacyBankService legacyService;
rule findAllCustomers
$customerMap : Map( )
$customerMap.put("_type_", "Customer");
insert( $customerMap );

Code listing 2: Rule that loads all Customers into the rule session (the dataTransformation.drl file).

The preceding findAllCustomers rule matches on a Map instance that is obtained from our legacyService. In the consequence part, it adds the type (so that we can recognize that this map represents a customer) and inserts this map into the session.

There are a few things to be noted here, as follows:

  • A rule is being used to insert objects into the rule session; this just shows a different way of loading objects into the rule session.
  • Every customer returned from the findAllCustomers method is being inserted into the session. This is reasonable only if there is a small number of customers. If it is not the case, we can paginate, that is, process only N customers at once, then start over with the next N customers, and so on. Alternatively, the findAllCustomers rule can be removed and customers could be inserted into the rule session at session-creation time. We'll now focus on this latter approach (for example, only one Customer instance is in the rule session at any given time); it will make the reporting easier.
  • A type of the map is being added to the map. This is a disadvantage of using a Map object for every type (Customer, Address, and so on): the type information is lost. It can be seen in the following rule that finds addresses for a customer:

    rule findAddress
    dialect "mvel"
    $customerMap : Map( this["_type_"] == "Customer" )
    $addressMap : Map( )
    (Long) $customerMap["customer_id"] )
    $addressMap.put("_type_", "Address");
    insert( $addressMap )

Code listing 3: Rule that loads all Addresses for a Customer into the rule session (the dataTransformation.drl file)

Let's focus on the first condition line. It matches on customerMap. It has to test if this Map object contains a customer's data by executing ["_type_"] == "Customer". To avoid doing these type checks in every condition, a new map can be extended from HashMap, for example LegacyCustomerHashMap. The rule might look like the following line of code:

$customerMap :LegacyCustomerHashMap( )

The preceding line of code performs matching on the customer map without doing the type check.

We'll continue with the second part of the condition. It matches on addressMap that comes from our legacyService as well. The from keyword supports parameterized service calls. customer_id is passed to the findAddressByCustomerId method. Another nice thing about this is that we don't have to cast the parameter to java.lang.Long; it is done automatically.

The consequence part of this rule just sets the type and inserts addressMap into the knowledge session. Please note that only addresses for loaded customers are loaded into the session. This saves memory but it could also cause a lot of "chattiness" with the LegacyBankService interface if there are many child objects. It can be fixed by pre-loading those objects. The implementation of this interface is the right place for this.

Similar data-loading rules can be written for other types, for example Account.

Writing transformation rules

Now that all objects are in the knowledge session, we can start writing some transformation rules. Let's imagine that in the legacy system there are many duplicate addresses. We can write a rule that removes such duplication:

rule twoEqualAddressesDifferentInstance
$addressMap1 : Map( this["_type_"] == "Address" )
$addressMap2 : Map( this["_type_"] == "Address",
eval( $addressMap1 != $addressMap2 ),
this == $addressMap1 )
retract( $addressMap2 );
kcontext.getRule().getName(), $addressMap2));

Code listing 4: Rule that loads all Addresses for a Customer into the rule session (file dataTransformation.drl)

The rule matches two addresses. It checks that they don't have the same object identities by doing eval( $addressMap1 != $addressMap2 ). Otherwise, the rule could match on a single address instance. The next part, this == $addressMap1 , translates behind the scenes to $addressMap1.equal($addressMap2) . If this equal check is true that means one of the addresses is redundant and can be removed from the session. The address map that is removed is added to the report as a warning message.


Before we'll continue with the rest of the rules, we'll set up unit tests. We'll still use a stateless session:

session = knowledgeBase.newStatelessKnowledgeSession();

Code listing 5: Section of the test setUpClass method (the DataTransformationTest file)

The legacyService global is set to a new instance of MockLegacyBankService. It is a dummy implementation that simply returns null from all methods. In most tests we'll insert objects directly into the knowledge session (and not through legacyService ).

We'll now write a helper method for inserting objects into the knowledge session and running the rules. The helper method will create a list of commands, execute them, and the returned object, BatchExecutionResults, will be returned back from the helper method. The following command instances will be created:

  • One for setting the global variable – validationReport; a new validation report will be created.
  • One for inserting all objects into the session.
  • One for firing only rules with a specified name. This will be done through an AgendaFilter. It will help us isolate a rule that we'll be testing.


    When a rule is activated the AgendaFilter determines if this rule can be fired or not. The AgendaFilter interface has one accept method that returns true/false. We'll create our own RuleNameEqualsAgendaFilter that fires only rules with a specific name.

  • One command for getting back all objects in a knowledge session that are of a certain type – filterType method parameter. These objects will be returned from the helper method as part of the results object under a given key – filterOut method parameter.

The following is the helper method:

* creates multiple commands, calls session.execute and
* returns results back
protected ExecutionResults execute(Collection<?> objects,
String ruleName, final String filterType,
String filterOut) {
ValidationReport validationReport = reportFactory
List<Command<?>> commands = new ArrayList<Command<?>>();
"validationReport", validationReport, true));
commands.add(new FireAllRulesCommand(
new RuleNameEqualsAgendaFilter(ruleName)));
if (filterType != null && filterOut != null) {
GetObjectsCommand getObjectsCommand =
new GetObjectsCommand( new ObjectFilter() {
public boolean accept(Object object) {
return object instanceof Map
&& ((Map) object).get("_type_").equals(
ExecutionResults results = session
return results;

Code listing 6: Test helper method for executing the transformation rules (the DataTransformationTest file).

To write a test for the redundant address rule, two address maps will be created. Both will have their street set to "Barrack Street". After we execute rules, only one address map should be in the rule session. The test looks as follows:

public void twoEqualAddressesDifferentInstance()
throws Exception {
Map addressMap1 = new HashMap();
addressMap1.put("_type_", "Address");
addressMap1.put("street", "Barrack Street");
Map addressMap2 = new HashMap();
addressMap2.put("_type_", "Address");
addressMap2.put("street", "Barrack Street");
assertEquals(addressMap1, addressMap2);
ExecutionResults results = execute(Arrays.asList(
addressMap1, addressMap2),
"twoEqualAddressesDifferentInstance", "Address",
Iterator<?> addressIterator = ((List<?>) results
Map addressMapWinner = (Map);
assertEquals(addressMap1, addressMapWinner);
addressMapWinner == addressMap1 ? addressMap2
: addressMap1);

Code listing 7: Test for the redundant address rule

The execute method is called with the two address maps, the agenda filter rule name is set to twoEqualAddressesDifferentInstance (only this rule will be allowed to fire), and after the rules are executed all maps of the Address type are returned as part of the result. We can access them by results.getValue("addresses"). The test verifies that there is only one such map.

Another test helper method – reportContextContains verifies that the validationReport contains expected data. The implementation method, reportContextContains, is shown as follows:

* asserts that the report contains one message with
* expected context (input parameter)
void reportContextContains(ExecutionResults results,
String messgeKey, Object object) {
ValidationReport validationReport = (ValidationReport)
assertEquals(1, validationReport.getMessages().size());
Message message = validationReport.getMessages()
List<Object> messageContext = message.getContextOrdered();
assertEquals(1, messageContext.size());
assertSame(object, messageContext.iterator().next());

Code listing 8: Helper method, which verifies that report the contains a supplied object

Address normalization

Our next rule will be a type conversion rule. It will take a String representation of country and it will convert it into Address.Countryenum. We'll start with a test:

public void addressNormalizationUSA() throws Exception {
Map addressMap = new HashMap();
addressMap.put("_type_", "Address");
addressMap.put("country", "U.S.A");
"addressNormalizationUSA", null, null);
assertEquals(Address.Country.USA, addressMap

Code listing 9: Test for the country type conversion rule

The test creates an address map with country set to "U.S.A". It then calls the execute method, passing in the addressMap and allowing only the addressNormalizationUSA rule to fire (no filter is used in this case). Finally, the test verifies that the address map has the correct country value. Next, we'll write the rule:

rule addressNormalizationUSA
dialect "mvel"
$addressMap : Map( this["_type_"] == "Address",
this["country"] in ("US", "U.S.", "USA", "U.S.A"))
modify( $addressMap ) {
put("country", Country.USA)

Code listing 10: Rule that converts String representation of country into enum representation(the dataTransformation.drl file)

The rule matches an address map. The in operator is used to capture various country representations. The rule's consequence is interesting in this case. Instead of doing simply update( $addressMap ), the modify construct is being used. Modify takes an argument and a block of code. Before executing the block of code it retracts the argument from the rule session, then it executes the block of code, and finally the argument is inserted back into the session. This has to be done because the argument's identity is modified. If we look at the implementation of HashMap equals or the hashCode method, they take into account every element in the map. By doing $addressMap.put("country", Country.USA), we change the address map identity.

Fact's identity

As a general rule, do not change the object identity while it is in the knowledge session, otherwise the rule engine behavior will be undefined (same as changing an object while it is in java.util.HashMap).

Testing the findAddress rule

Before continuing, let's write a test for the findAddress rule from the third rule in the Loading facts into the rule session section. The test will use a special LegacyBankService mock implementation that will return the provided addressMap .

public class StaticMockLegacyBankService extends
MockLegacyBankService {
private Map addressMap;
public StaticMockLegacyBankService(Map addressMap) {
this.addressMap = addressMap;
public List findAddressByCustomerId(Long customerId) {
return Arrays.asList(addressMap);

Code listing 11: StaticMockLegacyBankService which returns provided addressMap

StaticMockLegacyBankService extends MockLegacyBankService and overrides the findAddressByCustomerId method. The findAddress test looks as follows:

public void findAddress() throws Exception {
final Map customerMap = new HashMap();
customerMap.put("_type_", "Customer");
customerMap.put("customer_id", new Long(111));
final Map addressMap = new HashMap();
LegacyBankService service =
new StaticMockLegacyBankService(addressMap);
session.setGlobal("legacyService", service);
ExecutionResults results = execute(Arrays
.asList(customerMap), "findAddress", "Address",
assertEquals("Address", addressMap.get("_type_"));
Iterator<?> addressIterator = ((List<?>) results
// clean-up
new MockLegacyBankService());

Code listing12: Test for the findAddress rule

The test then verifies that the address map is really in the knowledge session. It also verifies that it has the "_type_" key set and that there is no other address map.

Unknown country

The next rule will create an error message if the country isn't recognizable by our domain model. The test creates an address map with some unknown country, executes rules, and verifies that the report contains an error.

public void unknownCountry() throws Exception {
Map addressMap = new HashMap();
addressMap.put("_type_", "Address");
addressMap.put("country", "no country");

ExecutionResults results = execute(Arrays
.asList(addressMap), "unknownCountry", null, null);
ValidationReport report = (ValidationReport) results
reportContextContains(results, "unknownCountry",

Code listing 13: Test for the unknownCountry rule

The rule implementation will test if the country value from the addressMap is of the Address.Country type. If it isn't, an error is added to the report.

rule unknownCountry
salience -10 //should fire after address normalizations
$addressMap : Map( this["_type_"] == "Address",
!($addressMap.get("country") instanceof
kcontext.getRule().getName(), $addressMap));

Code listing 14: Rule that reports unknown countries (the dataTransformation.drl file)

The type checking is done with MVEL's instanceof operator. Note that this rule needs to be executed after all address normalization rules, otherwise we could get an incorrect error message.

Currency conversion

As a given requirement, the data transformation process should convert all accounts to EUR currency. The test for this rule might look like the following code snippet:

public void currencyConversionToEUR() throws Exception {
Map accountMap = new HashMap();
accountMap.put("_type_", "Account");
accountMap.put("currency", "USD");
accountMap.put("balance", "1000");
"currencyConversionToEUR", null, null);
assertEquals("EUR", accountMap.get("currency"));
assertEquals(new BigDecimal("780.000"), accountMap

Code listing 15: Test for the EUR conversion rule

At the end of the code snippet the test verified that currency and balance were correct. The exchange rate of 0.780 was used. The rule implementation is as follows:

rule currencyConversionToEUR
$accountMap : Map( this["_type_"] == "Account",
this["currency"] != null && != "EUR" )
$conversionAmount : String() from
modify($accountMap) {
put("currency", "EUR"),
put("balance", new BigDecimal(
$conversionAmount).multiply(new BigDecimal(

Code listing 16: Rule that converts account balance and currency to EUR (the dataTransformation.drl file) .

The rule uses the default 'java' dialect. It matches on an account map and retrieves the conversion amount using the from conditional element. In this case it is a simple function that returns hardcoded values. However, it can be easily replaced with a service method that could, for example, call some web service in a real bank.

function String getConversionToEurFrom(String currencyFrom) {
String conversion = null;
if ("USD".equals(currencyFrom)) {
conversion = "0.780";
} else if ("SKK".equals(currencyFrom)) {
conversion = "0.033";
return conversion;

Code listing 17: Dummy function for calculating the exchange rate (the dataTransformation.drl file)

Notice how we're calling the function. Instead of calling it directly in the consequence, it is called from a condition. This way our rule will fire only if the function returns some non-null result.

The rule then sets the currency to EUR and multiplies the balance with the exchange rate. This rule doesn't cover currencies for which the getConversionToEurFrom function returns null. We have to write another rule that will report unknown currencies.

rule unknownCurrency
$accountMap : Map( this["_type_"] == "Account",
this["currency"] != null && != "EUR" )
not( String() from
(String)$accountMap["currency"]) )
kcontext.getRule().getName(), $accountMap));

Code listing 18: Rule that adds an error message to the report if there is no conversion for a currency (the dataTransformation.drl file)

Note that in this case the getConversionToEurFrom function is called from within the not construct.

One account allowed

Imagine that we have a business requirement that only one account from the legacy system can be imported into the new system. Our next rule will remove redundant accounts while aggregating their balances.

The test inserts two accounts of the same customer into the rule session and verifies that one of them was removed and the balance has been transferred.

public void reduceLegacyAccounts() throws Exception {
Map accountMap1 = new HashMap();
accountMap1.put("_type_", "Account");
accountMap1.put("customer_id", "00123");
accountMap1.put("balance", new BigDecimal("100.00"));
Map accountMap2 = new HashMap();
accountMap2.put("_type_", "Account");
accountMap2.put("customer_id", "00123");
accountMap2.put("balance", new BigDecimal("300.00"));
ExecutionResults results = execute(Arrays.asList(
accountMap1, accountMap2), "reduceLegacyAccounts",
"Account", "accounts");
Iterator<?> accountIterator = ((List<?>) results
Map accountMap = (Map);
assertEquals(new BigDecimal("400.00"), accountMap

Code listing 19: Test for the reduceLegacyAccounts rule

Before we can write this rule we have to ensure that the Account instance's balance is of the BigDecimal type. This is partially (non-EUR accounts) done by the currency conversion rules. For the EUR accounts a new rule can be written that simply converts the type to BigDecimal (we can even update the unknownCurrency rule to handle this situation).

rule reduceLegacyAccounts
$accountMap1 : Map( this["_type_"] == "Account" )
$accountMap2 : Map( this["_type_"] == "Account",
eval( $accountMap1 != $accountMap2 ),
this["customer_id"] ==$accountMap1["customer_id"],
this["currency"] == $accountMap1["currency"])
modify($accountMap1) {
put("balance", (
retract( $accountMap2 );

Code listing 20: Rule that removes redundant accounts and accumulates their balances (the dataTransformation.drl file)

The rule matches on two accountMap instances; it ensures that they represent two different instances (eval( $accountMap1 != $accountMap2) – note that eval is important here), which both belong to the same customer (this["customer_id"] ==$accountMap1["customer_id"]) and have the same currency (this["currency"] == $accountMap1["currency"]). The consequence sums up the two balances and retracts the second accountMap.

Note that the rule should fire after all currency conversion rules. This is creating dependencies between rules. In this case it is tolerable, as only a few rules are involved. However, with more complex dependencies we'll have to introduce a ruleflow.

Drools JBoss Rules 5.X Developer’s Guide Define and execute your business rules with Drools with this book and ebook
Published: May 2013
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

Transformation results

Now that we've written all transformation rules, data from the legacy system is in a good shape for our model, and we can start with populating it. To extract data from the knowledge session we'll use Drools queries.


Drools query looks like a normal rule without the 'then' part. It can be executed directly from a stateful knowledge session, for example session.getQueryResults("getAllCustomers") or by using a QueryCommand. It returns a QueryResults object that can contain multiple QueryResultsRow objects. Every QueryResultsRow instance represents one match of the query. Individual objects/facts can be retrieved from QueryResultsRow. Drools queries are a convenient way of retrieving objects/facts from the knowledge session that match conditions specified by the query. Queries can be parameterized. In KnowledgeBase , all queries share the same namespace.

Let's implement queries for retrieving transformed data:

query getCustomer
$customerMap : Map( this["_type_"] == "Customer" )
query getAccountByCustomerId(Map customerMap)
$accountMap : Map( this["_type_"] == "Account",
this["customer_id"] == customerMap["customer_id"] )

Code listing 21: Queries for retrieving customer and accounts (the dataTransformation.drl file)

The getCustomer query matches on any customer map. The second query, getAccountByCustomerId, takes one parameter, customerMap. The customerMap parameter is then used to match only the accounts that belong to this customer.

We have the ability to extract data from the knowledge session. Let's write the transformation service. It will have only one method for starting the transformation process. This method calls a processCustomer method for every customer map that comes from legacyService.findAllCustomers. The following is the body of the processCustomer method:

* transforms customerMap, creates and stores new customer
protected void processCustomer(Map customerMap) {
ValidationReport validationReport = reportFactory
List<Command<?>> commands = new ArrayList<Command<?>>();
"validationReport", validationReport));
commands.add(new FireAllRulesCommand());
"address", "getAddressByCustomerId",
new Object[] { customerMap }));
"accounts", "getAccountByCustomerId",
new Object[] { customerMap }));
ExecutionResults results = session.execute(
if (!validationReport.getMessagesByType(Type.ERROR)
.isEmpty()) {
} else {
Customer customer = buildCustomer(customerMap,
bankingService.add(customer); // runs validation

Code listing 22: Executing the transformation rules and retrieving transformed customer data (the DataTransformationServiceImpl file)

A new validationReport is created; rules are executed in a stateless session and the customer map is passed in. If the validation report contains any errors, all messages are logged and this method finishes. In case there is no error, only warnings are logged, and the customer is built and added to the system. The buildCustomer method takes BatchExecutionResults, which contains the results of our queries, as an argument. The add service call validates the customer (in this case represented in our domain model) before saving.

An excerpt from the buildCustomer method can be seen in the following code snippet. It creates all accounts for the customer. The accounts are retrieved from the knowledge session with the getAccountByCustomerId query.

QueryResults accountQueryResults = (QueryResults)
for (QueryResultsRow accountQueryResult :
accountQueryResults) {
Map accountMap = (Map) accountQueryResult
Account account = new Account();
account.setNumber((Long) accountMap.get("number"));
account.setBalance((BigDecimal) accountMap

Code listing 23: Execution of the parameterized query (the DataTransformationServiceImpl file)

Note that the query command bounds all accountMap instances under the name "accounts" (from the code snippet listing the body of the processCustomer method – CommandFactory.newQuery("accounts", "getAccountByCustomerId", new Object[ ] { customerMap })).

The method retrieves the collection of accountMap instances (results.getValue("accounts")) and for each accountMap creates a new Account object. These accounts are then added to the Customer object( customer.addAccount(account)).

Implementation of the data loading

In this section we'll look closer at getting the data from the legacy system. If you're not interested in actually trying out this example, you can skip this section.

Database setup

The data can come from various sources – database, XML, CSV, and so on. Our application will pull data from a database; however, it shouldn't be a problem to work with any other data source. The table structure looks as follows:

CREATE TABLE `droolsBook`.`customer` (
`customer_id` bigint(20) NOT NULL,
`first_name` varchar(255) NOT NULL,
`last_name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
PRIMARY KEY (`customer_id`)

Code listing 24: Table structure for legacy customers in a MySQL Database

CREATE TABLE `droolsBook`.`address` (
`address_id` bigint(20) NOT NULL default '0',
`parent_id` bigint(20) NOT NULL,
`street` varchar(255) NOT NULL,
`area` varchar(255) NOT NULL,
`town` varchar(255) NOT NULL,
`country` varchar(255) NOT NULL,
PRIMARY KEY (`address_id`)

Code listing 24: Table structure for legacy addresses in a MySQL Database

The 'parent_id' column from the preceding code snippet represents a foreign key to the customer's primary key. The same applies for the 'customer_id' column, as shown next:

CREATE TABLE `droolsBook`.`account` (
`account_id` bigint(20) NOT NULL,
`name` varchar(255) NOT NULL,
`currency` varchar(100) NOT NULL,
`balance` varchar(255) NOT NULL,
`customer_id` bigint(20) NOT NULL,
PRIMARY KEY (`account_id`)

Code listing 25: Table structure for legacy account in a MySQL Database

As can be seen from the table structures, there is a one-to-many relationship between a customer and addresses/accounts. Note that the table column names are different to the property names used in our domain model.

You need to set up a database, create the tables using the previous code snippets, and populate them with some sample data.

Project setup

For loading data from a database we'll use iBatis (more information about project iBatis can be found at o It is an easy-to-use data mapper framework. iBatis has a rich set of functionality; we'll use it only for a simple task – to load data from the database as java.util.Map objects. Our rules will then reason over these objects.

We'll need the following additional libraries on the classpath:

  • ibatis- – binary distribution of iBatis
  • JDBC driver for your database; in the case of MySQL it is mysql-connectorjava-5.1.6-bin.jar (the MySQL database driver for Java can be downloaded from

iBatis configuration

Before any data can be loaded, iBatis needs to be configured. It needs to know about the database and its structure. This is configured in the SqlMapConfig.xml file:

<?xml version="1.0" encoding="UTF-8" ?>
PUBLIC "-// SQL Map Config 2.0//EN"
<transactionManager type="JDBC" commitRequired="false">
<dataSource type="SIMPLE">
<property name="JDBC.Driver"
value="com.mysql.jdbc.Driver" />
<property name="JDBC.ConnectionURL"
ist=true&amp;useUnicode=true&amp;characterEncoding=utf-8" />
<property name="JDBC.Username" value="root" />
<property name="JDBC.Password" value="" />
<sqlMap resource="Banking.xml" />

Code listing 26: Table structure for legacy account in a MySQL Database

The configuration is straightforward. The JDBC driver, connection URL, username, and password are given. Further down the configuration, the sqlMap element refers to an external file (Banking.xml) that specifies the table structure.

<?xml version="1.0" encoding="UTF-8" ?>
PUBLIC "-// SQL Map 2.0//EN"
<sqlMap namespace="Banking">
<select id="findAllCustomers"
select * from customer
<select id="findAddressByCustomerId" parameterClass="long"
resultClass="java.util.HashMap" >
select * from address where parent_id = #id#
<select id="findAccountByCustomerId" parameterClass="long"
resultClass="java.util.HashMap" >
select * from account where customer_id = #id#

Code listing 27: iBatis configuration file – Banking.xml

The sqlMap element defines three select statements: one for loading all customers, one for loading customer' addresses, and one for customers' accounts. All select statements specify java.util.HashMap as the result class. When the select statement executes, it creates and populates this map. Each row in a table will be represented by one HashMap instance. Table column names are mapped to the map's keys and values to the map's values. The two other select elements – findAddressByCustomerId and findAccountByCustomerId – take one parameter of the long type. This parameter is used in the select statement's where clause. It represents the foreign key to the customer table.

Running iBatis

The main interface that will be used to interact with iBatis is com.ibatis.sqlmap. client.SqlMapClient. An instance of this class can be obtained as follows:

Reader reader = Resources
SqlMapClientsqlMapClient = SqlMapClientBuilder

Code listing 28: iBatis set up – building the SqlMapClient instance

After we have the SqlMapClient instance it can be used to load data from the database:

List customers = sqlMapClient
List addresses = sqlMapClient.queryForList(
"findAddressByCustomerId", new Long(654258));

Code listing 29: Running iBatis queries

The second query shows how we can pass parameters to iBatis. The returned object in both cases is of the java.util.List type. The list contains zero or many HashMap instances. Remember? Each map represents one database record.

We can now write the implementation of the LegacyBankService interface from the first code snippet in this article. The implementation is straightforward. It simply delegates to sqlMapClient, as we've seen, for example, in the previous two code snippets.

Alternative data loading

Drools supports various data loaders – Smooks (, JAXB (, and so on. They can be used as an alternative to iBatis. For example, Smooks can load data from various sources such as XML, CSV, Java, and others. It is itself a powerful Extract, Transform, Load(ETL) tool. However, we can use it to do just the data-loading part, probably with some minor transformations.


In this article we've seen how to use rules to perform more complex data transformation tasks. These rules are easy to read and can be expanded without increasing the overall complexity. However, it should be noted that Drools is probably not the best option if we want to do high-throughput/high-performance data transformations.

We've seen how to write rules over a generic data type such as java.util.Map . You should try to avoid using this kind of generic data type. However, it is not always possible, especially when doing data transformation and if you don't know much about the data.

Some testing approaches were shown; the use of AgendaFilter as a way to isolate the individual rule tests. Please note that upon execution, all rules are matched and placed onto the agenda; however, only those that pass this filter are executed. ObjectFilter was used to filter facts from the knowledge session, when we were verifying test assertions.

Finally, some examples were given on how to use Drools queries. They represent a very convenient way of accessing facts in the knowledge session.

Resources for Article :

Further resources on this subject:

Drools JBoss Rules 5.X Developer’s Guide Define and execute your business rules with Drools with this book and ebook
Published: May 2013
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

About the Author :

Michal Bali

Michal Bali, freelance software developer, has more than 8 years of experience working with Drools and has an extensive knowledge of Java, JEE. He designed and implemented several systems for a major dental insurance company. He is an active member of the Drools community and can be contacted at

Books From Packt

Drools JBoss Rules 5.0 Developer's Guide
Drools JBoss Rules 5.0 Developer's Guide

Drools Developer’s Cookbook
Drools Developer’s Cookbook

jBPM Developer Guide
jBPM Developer Guide

Oracle WebLogic Server 12c Advanced Administration Cookbook
Oracle WebLogic Server 12c Advanced Administration Cookbook

Oracle BPM Suite 11g: Advanced BPMN Topics
Oracle BPM Suite 11g: Advanced BPMN Topics

JBoss ESB Beginner’s Guide
JBoss ESB Beginner’s Guide

JBoss AS 5 Development
JBoss AS 5 Development

Business Process Management with JBoss jBPM
Business Process Management with JBoss jBPM

Your rating: None Average: 1.5 (10 votes)

Post new comment

This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software