External Rules Coming from Database

Simple rule project with external rules defined in database tables.

 


Rule Project "ExternalRulesFromDB"

Step 1. Setting Up Rule Templates

Step 2. Setting Up Database with Rule Tables

Step 3. Defining a DB interface in Java

Step 4. Creating and Executing Rules from a Java Program

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

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

 

 

 

Welcome
Welcome
Business Rules Repository Decisions Top-Down Semantics In Concert Business Analyst Decisioning
Welcome
themed object
Business Rules - Time to Excel
Bookmark and Share