05: Drools with rules in an excel spreadsheet – Global variables & variable capture

Posted on June 1, 2016 by

This extends 04: Drools with rules in an excel spreadsheet – Data validation .

Step 1: Enhance the Trade.java POJO with “actualValues” field and “getFieldValue(String fieldNames)” method.


package com.mytutorial.pojo;

import java.lang.reflect.Field;
import org.apache.commons.lang.StringUtils;

public class Trade {
    private String tradeId;
    private String tradeDate;
    private String symbol;
    private String price;
    private String volume;
    private String totalPrice;
    private String buyerId;
    private String sellerId;

    private String actualValues;

    public Trade(String tradeId, String tradeDate, String symbol, String price, String volume, String totalPrice,
            String buyerId, String sellerId) {
        super();
        this.tradeId = tradeId;
        this.tradeDate = tradeDate;
        this.symbol = symbol;
        this.price = price;
        this.volume = volume;
        this.totalPrice = totalPrice;
        this.buyerId = buyerId;
        this.sellerId = sellerId;
    }

    public String getTradeId() {
        return tradeId;
    }

    public void setTradeId(String tradeId) {
        this.tradeId = tradeId;
    }

    public String getTradeDate() {
        return tradeDate;
    }

    public void setTradeDate(String tradeDate) {
        this.tradeDate = tradeDate;
    }

    public String getSymbol() {
        return symbol;
    }

    public void setSymbol(String symbol) {
        this.symbol = symbol;
    }

    public String getPrice() {
        return price;
    }

    public void setPrice(String price) {
        this.price = price;
    }

    public String getVolume() {
        return volume;
    }

    public void setVolume(String volume) {
        this.volume = volume;
    }

    public String getTotalPrice() {
        return totalPrice;
    }

    public void setTotalPrice(String totalPrice) {
        this.totalPrice = totalPrice;
    }

    public String getBuyerId() {
        return buyerId;
    }

    public void setBuyerId(String buyerId) {
        this.buyerId = buyerId;
    }

    public String getSellerId() {
        return sellerId;
    }

    public void setSellerId(String sellerId) {
        this.sellerId = sellerId;
    }

    public String getActualValues() {
        return actualValues;
    }

    public void setActualValues(String actualValues) {
        this.actualValues = actualValues;
    }

    //reflection to get field values
    public String getFieldValue(String fieldNames) throws NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException {

        if (StringUtils.isEmpty(fieldNames)) {
            throw new IllegalArgumentException();
        }

        String[] splits = fieldNames.split("\\,");
        StringBuilder sb = new StringBuilder();

        for (int i = 0; i < splits.length; i++) {
            Field field = this.getClass().getDeclaredField(splits[i].trim());
            field.setAccessible(true);
            sb.append((String) field.get(this));

            if (i < splits.length - 1) {
                sb.append("|");
            }
        }

        return sb.toString();
    }

    @Override
    public String toString() {
        return "Trade [tradeId=" + tradeId + ", tradeDate=" + tradeDate + ", symbol=" + symbol + ", price=" + price
                + ", volume=" + volume + ", totalPrice=" + totalPrice + ", buyerId=" + buyerId + ", sellerId="
                + sellerId + ", actualValues=" + actualValues + "]";
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

package com.mytutorial.pojo;

import java.lang.reflect.Field;

import org.apache.commons.lang.StringUtils;

public class Trade {

private String tradeId;

private String tradeDate;

private String symbol;

private String price;

private String volume;

private String totalPrice;

private String buyerId;

private String sellerId;

private String actualValues;

public Trade(String tradeId, String tradeDate, String symbol, String price, String volume, String totalPrice,

String buyerId, String sellerId) {

super();

this.tradeId = tradeId;

this.tradeDate = tradeDate;

this.symbol = symbol;

this.price = price;

this.volume = volume;

this.totalPrice = totalPrice;

this.buyerId = buyerId;

this.sellerId = sellerId;

}

public String getTradeId() {

return tradeId;

}

public void setTradeId(String tradeId) {

this.tradeId = tradeId;

}

public String getTradeDate() {

return tradeDate;

}

public void setTradeDate(String tradeDate) {

this.tradeDate = tradeDate;

}

public String getSymbol() {

return symbol;

}

public void setSymbol(String symbol) {

this.symbol = symbol;

}

public String getPrice() {

return price;

}

public void setPrice(String price) {

this.price = price;

}

public String getVolume() {

return volume;

}

public void setVolume(String volume) {

this.volume = volume;

}

public String getTotalPrice() {

return totalPrice;

}

public void setTotalPrice(String totalPrice) {

this.totalPrice = totalPrice;

}

public String getBuyerId() {

return buyerId;

}

public void setBuyerId(String buyerId) {

this.buyerId = buyerId;

}

public String getSellerId() {

return sellerId;

}

public void setSellerId(String sellerId) {

this.sellerId = sellerId;

}

public String getActualValues() {

return actualValues;

}

public void setActualValues(String actualValues) {

this.actualValues = actualValues;

}

//reflection to get field values

public String getFieldValue(String fieldNames) throws NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException {

if (StringUtils.isEmpty(fieldNames)) {

throw new IllegalArgumentException();

}

String[] splits = fieldNames.split("\\,");

StringBuilder sb = new StringBuilder();

for (int i = 0; i < splits.length; i++) {

Field field = this.getClass().getDeclaredField(splits[i].trim());

field.setAccessible(true);

sb.append((String) field.get(this));

if (i < splits.length - 1) {

sb.append("|");

}

return sb.toString();

}

@Override

public String toString() {

return "Trade [tradeId=" + tradeId + ", tradeDate=" + tradeDate + ", symbol=" + symbol + ", price=" + price

+ ", volume=" + volume + ", totalPrice=" + totalPrice + ", buyerId=" + buyerId + ", sellerId="

+ sellerId + ", actualValues=" + actualValues + "]";

}

Step 2: trades-rules.xls with “variables” column.

xls file with rules

Step 3: The trades.drt template file invoking the “getFieldValues(…)” method & declaring global variables.


template header

metaRule
metaCondition
metaVariables
metaActive

package com.mytutorial.drools

import com.mytutorial.pojo.*;
import com.mytutorial.util.DroolsUtil;
import org.apache.commons.lang.StringUtils;

global java.util.Set result;

template "trade-data-validation-rules"

rule "@{metaRule}"

when
  $trade : Trade("@{metaActive}" == "Y" && !(@{metaCondition}))
then
   $trade.setActualValues((String)$trade.getFieldValue("@{metaVariables}"));
   result.add($trade);
   System.out.println("@{metaRule} ...failed for record = " + $trade);
end

end template

template header

metaRule

metaCondition

metaVariables

metaActive

package com.mytutorial.drools

import com.mytutorial.pojo.*;

import com.mytutorial.util.DroolsUtil;

import org.apache.commons.lang.StringUtils;

global java.util.Set result;

template "trade-data-validation-rules"

rule "@{metaRule}"

when

$trade : Trade("@{metaActive}" == "Y" && !(@{metaCondition}))

then

$trade.setActualValues((String)$trade.getFieldValue("@{metaVariables}"));

result.add($trade);

System.out.println("@{metaRule} ...failed for record = " + $trade);

end

end template

Step 4: Finally, the “DataProfiling .java” file. It declares a global variable named “result”, which is of type “Set”.


package com.mytutorial.drools;

import java.io.IOException;
import java.net.URISyntaxException;
import java.net.URL;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Set;
import java.util.stream.Stream;

import org.drools.KnowledgeBase;
import org.drools.KnowledgeBaseFactory;
import org.drools.builder.KnowledgeBuilder;
import org.drools.builder.KnowledgeBuilderError;
import org.drools.builder.KnowledgeBuilderFactory;
import org.drools.builder.ResourceType;
import org.drools.decisiontable.ExternalSpreadsheetCompiler;
import org.drools.io.ResourceFactory;
import org.drools.io.impl.ByteArrayResource;
import org.drools.runtime.StatefulKnowledgeSession;
import org.mvel2.optimizers.OptimizerFactory;

import com.mytutorial.pojo.Trade;

public class DataProfiling {
    private static KnowledgeBase kbase;

    private static final String DATA_FILE = "data/trades.csv";

    public static void main(String[] args) throws IOException, URISyntaxException {
        OptimizerFactory.setDefaultOptimizer("reflective");
        KnowledgeBuilder kbuilder = KnowledgeBuilderFactory.newKnowledgeBuilder();
        loadKnowledgeBase("drools/trades-rules.xls", "drools/trades.drt", "Sheet1", 2, 1, kbuilder);
        kbase = KnowledgeBaseFactory.newKnowledgeBase();
        kbase.addKnowledgePackages(kbuilder.getKnowledgePackages());
        process();
    }

    private static void process() throws URISyntaxException, IOException {
        StatefulKnowledgeSession ksession = kbase.newStatefulKnowledgeSession();
        KnowledgeBase kbase = KnowledgeBaseFactory.newKnowledgeBase();
        kbase.newStatefulKnowledgeSession();
        URL resource = ClassLoader.getSystemResource(DATA_FILE);
        Path inputFile = Paths.get(resource.toURI());
        Stream<String> lines = Files.lines(inputFile, StandardCharsets.UTF_8);

        Set<Trade> result = new HashSet<>();
        ksession.setGlobal("result", result);

        int i = 0;

        for (String line : (Iterable<String>) lines::iterator) {
            String[] split = line.split(",");
            Trade trade = new Trade(split[0], split[1], split[2], split[3], split[4], split[5], split[6], split[7]);

            if (i > 0) {
                ksession.insert(trade);
            }
            i++;
        }
        lines.close();
        ksession.fireAllRules();

        Set<Trade> failedValidationRecords = (Set<Trade>) ksession.getGlobal("result");

        int unmatchedRecordsCount = 0;
        if (failedValidationRecords != null) {
            unmatchedRecordsCount = failedValidationRecords.size();
        }
        
        System.out.println("unmatchedRecordsCount=" + unmatchedRecordsCount);
        System.out.println("failedValidationRecords=" + failedValidationRecords);
    }

    private static void loadKnowledgeBase(String aXlsName, String aDrlName, String aWorksheetName, int aStartRow,
            int aStartCol, KnowledgeBuilder aKbuilder) throws IOException {
        ExternalSpreadsheetCompiler sc = new ExternalSpreadsheetCompiler();
        String drlstr = sc.compile(ResourceFactory.newClassPathResource(aXlsName).getInputStream(), aWorksheetName,

        ResourceFactory.newClassPathResource(aDrlName).getInputStream(), aStartRow, aStartCol);

        System.out.println(drlstr);
        aKbuilder.add(new ByteArrayResource(drlstr.getBytes()), ResourceType.DRL);
        Iterator<KnowledgeBuilderError> errors = aKbuilder.getErrors().iterator();

        if (errors.hasNext()) {
            StringBuilder errorMsg = new StringBuilder("Error compiling rules:");
            while (errors.hasNext()) {
                errorMsg.append("\t" + errors.next().getMessage());
            }

            System.out.println(errorMsg.toString());
        }
    }
}

100

101

package com.mytutorial.drools;

import java.io.IOException;

import java.net.URISyntaxException;

import java.net.URL;

import java.nio.charset.StandardCharsets;

import java.nio.file.Files;

import java.nio.file.Path;

import java.nio.file.Paths;

import java.util.HashSet;

import java.util.Iterator;

import java.util.Set;

import java.util.stream.Stream;

import org.drools.KnowledgeBase;

import org.drools.KnowledgeBaseFactory;

import org.drools.builder.KnowledgeBuilder;

import org.drools.builder.KnowledgeBuilderError;

import org.drools.builder.KnowledgeBuilderFactory;

import org.drools.builder.ResourceType;

import org.drools.decisiontable.ExternalSpreadsheetCompiler;

import org.drools.io.ResourceFactory;

import org.drools.io.impl.ByteArrayResource;

import org.drools.runtime.StatefulKnowledgeSession;

import org.mvel2.optimizers.OptimizerFactory;

import com.mytutorial.pojo.Trade;

public class DataProfiling {

private static KnowledgeBase kbase;

private static final String DATA_FILE = "data/trades.csv";

public static void main(String[] args) throws IOException, URISyntaxException {

OptimizerFactory.setDefaultOptimizer("reflective");

KnowledgeBuilder kbuilder = KnowledgeBuilderFactory.newKnowledgeBuilder();

loadKnowledgeBase("drools/trades-rules.xls", "drools/trades.drt", "Sheet1", 2, 1, kbuilder);

kbase = KnowledgeBaseFactory.newKnowledgeBase();

kbase.addKnowledgePackages(kbuilder.getKnowledgePackages());

process();

}

private static void process() throws URISyntaxException, IOException {

StatefulKnowledgeSession ksession = kbase.newStatefulKnowledgeSession();

KnowledgeBase kbase = KnowledgeBaseFactory.newKnowledgeBase();

kbase.newStatefulKnowledgeSession();

URL resource = ClassLoader.getSystemResource(DATA_FILE);

Path inputFile = Paths.get(resource.toURI());

Stream<String> lines = Files.lines(inputFile, StandardCharsets.UTF_8);

Set<Trade> result = new HashSet<>();

ksession.setGlobal("result", result);

int i = 0;

for (String line : (Iterable<String>) lines::iterator) {

String[] split = line.split(",");

Trade trade = new Trade(split[0], split[1], split[2], split[3], split[4], split[5], split[6], split[7]);

if (i > 0) {

ksession.insert(trade);

}

i++;

}

lines.close();

ksession.fireAllRules();

Set<Trade> failedValidationRecords = (Set<Trade>) ksession.getGlobal("result");

int unmatchedRecordsCount = 0;

if (failedValidationRecords != null) {

unmatchedRecordsCount = failedValidationRecords.size();

}

System.out.println("unmatchedRecordsCount=" + unmatchedRecordsCount);

System.out.println("failedValidationRecords=" + failedValidationRecords);

}

private static void loadKnowledgeBase(String aXlsName, String aDrlName, String aWorksheetName, int aStartRow,

int aStartCol, KnowledgeBuilder aKbuilder) throws IOException {

ExternalSpreadsheetCompiler sc = new ExternalSpreadsheetCompiler();

String drlstr = sc.compile(ResourceFactory.newClassPathResource(aXlsName).getInputStream(), aWorksheetName,

ResourceFactory.newClassPathResource(aDrlName).getInputStream(), aStartRow, aStartCol);

System.out.println(drlstr);

aKbuilder.add(new ByteArrayResource(drlstr.getBytes()), ResourceType.DRL);

Iterator<KnowledgeBuilderError> errors = aKbuilder.getErrors().iterator();

if (errors.hasNext()) {

StringBuilder errorMsg = new StringBuilder("Error compiling rules:");

while (errors.hasNext()) {

errorMsg.append("\t" + errors.next().getMessage());

}

System.out.println(errorMsg.toString());

}

Modify the CSV file so so that the data fails rules validation


tradeId, tradeDate, symbol, price, volume, totalPrice, buyerId, sellerId
1, 2015/08/02,BHP,79.5000,1200,95400.00,ABC,ABC
2, 20150604,NAB,55.4550,100,5545.50,PQR,IJK

tradeId, tradeDate, symbol, price, volume, totalPrice, buyerId, sellerId

1, 2015/08/02,BHP,79.5000,1200,95400.00,ABC,ABC

2, 20150604,NAB,55.4550,100,5545.50,PQR,IJK

300+ Java Interview FAQs

200+ Java Key Areas FAQs

150+ Java Architect FAQs

150+ Java Coding FAQs

80+ Java Code Quality FAQs

300+ Big Data Interview FAQs

Big Data Tutorials

05: Drools with rules in an excel spreadsheet – Global variables & variable capture

Java developer & architect Q&As

Big Data engineer Q&As

Java developers Q&As

Disclaimer