OpenRules allows you to keep your business rules in regular database tables which structures correspond to the columns (conditions and actions) of Excel's templates based on which the proper rule tables will be executed.  This way the standard DB management tools can be used as your own rules management environments.

The project "ExternalRulesFromDB" demonstrates how to define rules in a MS Access database with regular tables (without Excel files saved as blobs). Because we are using a standard JDBC interface, this project should work similarly with other database management systems.

This project intends to produce greetings like "Good Morning, Mrs. Robinson!" based on the current time and different customer's attributes such as gender and marital status. In a way it is similar to the standard project "HelloJavaTemplates" but instead of using Excel-based rule tables it is using DB -based rule tables.

Step 1. Setting Up Rule Templates       ►top

The business logic for producing greetings and salutations is presented in the Excel file HelloTemplates.xls. The first template

Rules void defineGreeting(App app, int hour)
C1	C2	A1
min <= hour	hour <= max	app.greeting =  greeting;
int min	int max	String greeting
Hour From	Hour To	Set Greeting
		Unknown Greeting

specifies how to define different greetings (Good Morning, Good Afternoon, etc.) based on the hour of the day. If the parameter "hour" belongs to the interval [min;max] defined by a concrete rule then the attribute "greeting" of the parameter "app" will be set to the proper greeting. If no rules are satisfied, this multi-hit table will use the default greeting "Unknown Greeting". 

The second template

Rules void defineSalutation(App app, Customer c)
C1	C2	C3	A1
c.gender.equals(gender)	c.maritalStatus.equals(status)	c.age < age	app.salutation = salutation;
String gender	String status	int age	String salutation
Gender	Marital Status	Age Less Than	Set Salutation
			Unknown Salutation

specifies how to define different salutations (Mr., Mrs., etc.) based on customer attributes Gender, Marital Status, and Age. If no rules are satisfied, this multi-hit table will use the default salutation "Unknown Salutation". 

Step 2. Setting Up Database with Rule Tables     ►top

Use MS Access to create a new database, call it "OpenRulesDB", and save in the subdirectory "DB" of the directory ExternalRulesFromDB. Using the MS Access, create the table "AllRules" that looks like this one:

RulesName TemplateName greetingRules defineGreeting salutationRules defineSalutation

This DB table has only two text columns "RulesName' and "TemplateName". Now we have to create a simple DB table "greetingRules" with a structure that corresponds to our template "defineGreeting":

From To Greetings 0 11 Good Morning 12 15 Good Afternoon 16 21 Good Evening 22 24 Good Night

Similarly we will create a table "salutationRules" that corresponds to our template "defineSalutation":

Gender MaritalStatus AgeLessThan Salutation Male     Mr. Male Single 3 Little Female Single   Ms. Female Married   Mrs.

To make this database being accessible from from a Java program we have to setup the proper data source. In Windows, we have to go to Control Panel, open Administrative Tools, then select Data Sources (ODBC). Add a new User Data Source with the following information:

Data Source Name: OpenRulesDB

Description:  HelloExternalRulesFromDB

Click on the "Select" button and chose your just created OpenRulesDB.mdb file.

This above DB structure serves only as an example. You may organize your database with rule tables differently with additional information about rule tables including such attributes as "CreatedBy", "CreatedAt", "LastModifiedBy", "LastModifiedAt", "Category", and many more attributes that accommodate your particular needs.

Step 3. Defining a DB interface in Java      ►top

To inform an OpenRulesEngine about external rules, you need to create an object of the type ExternalRules and add to it all external RuleTables. Each instance of the class RuleTable consists of:

- rule table name (String)

- template name (String)

- a grid of objects that represent the content of a rule tables (Object[][])

In our case, to create an instance of the class External Rules we should:

1) read all rows from the DB table "AllRules"

2) for every pair (RuleName;TemplateName) find the proper DB table and create the required grid of the type Object[][] all rows and all columns of such tables.  

To do this we have created the class OpenRulesDB.java that contains the method "readRuleTables()" that does this work. This class is inherited from the standard JDBC interface supported by the class DbUtil included in the OpenRules installation within the project "com.openrules.tools".  Here is the code of this method with comments:

public synchronized ExternalRules readRuleTables() { String mainTable = "AllRules"; String columnWithRuleNames = "RulesName"; String columnWithTemplateNames = "TemplateName"; ExternalRules externalRules = new ExternalRules(); try { String mainSQL = "SELECT * FROM " + mainTable; ResultSet mainRS = executeQuery(mainSQL); // FOR ALL RULE TABLES while(mainRS.next()) { String ruleTableName = mainRS.getString(columnWithRuleNames); String ruleTemplateName = mainRS.getString(columnWithTemplateNames); System.out.println("Rules " + ruleTableName + " template " + ruleTemplateName); try { int numberOfRows = count(ruleTableName); System.out.println("Total " + numberOfRows + " rows"); String insideSQL = "SELECT * FROM " + ruleTableName; ResultSet rs = executeQuery(insideSQL); ResultSetMetaData md = rs.getMetaData(); int numberOfColumns = md.getColumnCount(); System.out.println("Total " + numberOfColumns + " columns"); Object[][] grid = new Object[numberOfRows][numberOfColumns]; int rowIndex = 0; // FOR ALL TABLE's ROWS while (rs.next()) { Object[] row = new Object[numberOfColumns]; // FOR ALL TABLE's COLUMNS for (int i=0; i< numberOfColumns; i++) { // Add grid element row[i] = rs.getObject(i+1); if (row[i] == null) row[i] = ""; } grid[rowIndex++] = row; } rs.close(); // ADD RULE TABLE externalRules.addRuleTable(ruleTableName,ruleTemplateName,grid); } catch(Exception e) { System.err.println("ERROR in the DB table " + ruleTableName + "\n" + e.toString()); } } mainRS.close(); } catch(Exception e) { System.err.println("ERROR in the DB table " + mainTable + "\n" + e.toString()); } return externalRules; } }

 

Step 4. Creating and Executing Rules from a Java Program     ►top

All other modules are typical for basic rule projects. The main Java file RunExternalRulesFromDB.java is used to test the above rules:

import com.openrules.ruleengine.ExternalRules; import com.openrules.ruleengine.OpenRulesEngine; public class RunExternalRulesFromDB { public static void main(String[] args) { // Read DB to create ExternalRules OpenRulesDB db = new OpenRulesDB(); ExternalRules externalRules = db.readRuleTables(); // Create OpenRulesEngine with external rules String fileName = "file:rules/main/HelloCustomer.xls"; OpenRulesEngine engine = new OpenRulesEngine(fileName,externalRules); System.out.println( "\n=====================================================\n" + "OpenRulesEngine: " + fileName + "\n=====================================================\n"); // Print external rules for (int i = 0; i < externalRules.getRuleTables().size(); i++) { System.out.println(externalRules.getRuleTables().get(i)); } // Create a test App with a test customer from HelloData.xls App app = (App) engine.run("getDefaultApplication"); // Run OpenRulesEngine engine.run("generateGreeting",app); System.out.println("\nGenerated Greeting: " + app.getResult()); } }

Here we create an instance of OpenRulesEngine using the main Excel-file HelloCustomer.xls and external rules received from the DB:

OpenRulesEngine engine = new OpenRulesEngine(fileName,externalRules);

The main file HelloCustomer.xls defines the Environment as follows:

Environment
import.java	hello.*
import.static	com.openrules.tools.Methods
include	../include/HelloTemplates.xls
include	../include/HelloData.xls

This application uses two simple Java beans:

Customer.java:

String name;

String maritalStatus;

String gender;

int age;

App.java:

Customer customer;

String greeting;

String salutation;

String result;

The proper instance of the Customer and App are created based on the Excel file HelloData.xls using these data tables:

Data App apps
customer.name	customer.maritalStatus	customer.gender	customer.age
Customer Name	Marital Status	Gender	Age
Robinson	Married	Female	24
Smith	Single	Male	19
Method App getDefaultApplication()
return apps[0];

And finally, the engine will execute rules by calling the method "run":

engine.run("generateGreeting",app);

The proper method "generateGreeting" is described in the file HelloCustomer.xls in the following table:

Method void generateGreeting(App app)

int hour = Calendar.getInstance().get(Calendar.HOUR_OF_DAY); greetingRules(app, hour); salutationRules(app, app.customer); app.result = app.greeting + ", " + app.salutation + " " + app.customer.name + "!";

You may validate the entire rule project by double-clicking on the file "compile.bat".  Because the actual external rule tables "greetingRules" and "salutationRules" will become known only in run-time the proper OpenRules validator may produce errors (warnings) about unknown rule tables. You may ignore these errors or you may explicitly inform OpenRules about this fact by adding an optional table to the file HelloCustomer.xls:

ExternalRules
greetingRules	defineGreeting
salutationRules	defineSalutation

To run the project you may double-click on the file "run.bat". Here is an expected output:

===================================================== OpenRulesEngine: file:rules/main/HelloCustomer.xls ===================================================== ExternalRules greetingRules template defineGreeting 0 11 Good Morning 12 15 Good Afternoon 16 21 Good Evening 22 24 Good Night ExternalRules salutationRules template defineSalutation Male Mr. Male Single 3 Little Female Single Ms. Female Married Mrs. Generated Greeting: Good Morning, Mrs. Robinson!

►top