JBRMS v4.01 &150; create enumerations via database access

contributed by Steve Barfield:

The latest release of JBRMS (v4.0.1) allows you to write rules using values from an external database.

This has 2 advantages :

1. The values used in the LHS rule statements will be derived using the same tables that the calling application will use.

   EG. in the rule

     WHEN

          Attributes.favouriteCheese==&148;CHD&148;

  &147;CHD&148; will be taken from the CHEESE table.

In the Favourite Cheese Survey application the list of cheeses to choose from is also derived from the CHEESE table.

When the data from the Cheese Survey is submitted to the rule engine this ensures the code

  &147;CHD&148; (not &147;CHE&148; or &148;CHED&148;) is submitted and hence will match the rule.

*2. The values used in the LHS will automatically appear as drop down lists within JBRMS. *

You can even get a descriptor

   &147;Chedder&148; to appear in the drop down list and the key field &147;CHD&148; will be saved in the rule.

-

This example uses a BEA Weblogic webserver and an Oracle database but the principle is the same for any webserver or database.

To achieve this follow these 4 steps :

Create helper class

Add helper class to jbrms war file

Reference helper class in Technical Rule Asset Data Enumeration

Use Enumeration in rule

1.Create helper class

This helper class ListLoader has 2 methods that return an ArrayList, the first method returns a hardcoded list,

the second uses an additional class DBHandler to return an ArrayList derived from a database.

The DBHandler class references an Oracle database but any database could be used.

package com.mycompany.rules.data;
import java.util.ArrayList;

public class ListLoader {
     public ListLoader(){}
     
     // hardcoded list
public ArrayList loadList(){
        ArrayList aL=new ArrayList();
        aL.add("CHD=Chedder");
        aL.add("STI=Stilton");
        return aL;
     }
     
     //list derived from db
public ArrayList loadCheeses(){
          
        DBHandler db=new DBHandler();
        ArrayList aL=new ArrayList();
        String q="select cheese_id, cheese_name from cheeses";
        try{
            java.sql.ResultSet r=db.runQuery(q);
           while(r.next()){
         System.out.println("loadCheeses-"+r.getString("cheese_name"));
                         aL.add(r.getString("cheese_id")+"="+r.getString("cheese_name"));
            }
                      Db.closeQuery(r);
        }
        catch(Exception e){
           System.out.println(e.toString());
        }
        return aL;
     }
}


package com.mycompany.rules.data;

import java.sql.*;
import java.util.*;
import javax.sql.*;

import java.sql.Connection;
import oracle.jdbc.pool.OracleDataSource;

public class DBHandler {

  //Gets a connection from the Connection pool
  private Connection getConnection (){

  Connection conn=null;

  try{
       String serverName="localhost";
       String serverPort="1521";
       String sid="xe";
       String userName="hr";
       String userPswd="hr";
       String connectionString=serverName+":"+serverPort+":"+sid;
       
       String url="jdbc:oracle:thin:@"+connectionString;
       
       // Set DataSource properties
       OracleDataSource ods=new OracleDataSource();
       ods.setURL(url);
       System.out.println("ContextListener.contextInitialized:url="+url);
       
       ods.setUser(userName);
       ods.setPassword(userPswd);
       conn = ods.getConnection();
    }
    catch (Exception ex) { }

    return conn;
  }

  //runs select queries.
  public ResultSet runQuery (String query) throws SQLException {

    Connection c = getConnection();
    Statement statement = c.createStatement();
    ResultSet r = statement.executeQuery(query);
    System.out.println(r.getFetchSize());
    return r;
 }

// free resources by closing the Statement and Connection that created r.
  public void closeQuery (ResultSet r) throws SQLException {
    Statement s = r.getStatement();
    Connection c = s.getConnection();
    r.close();
    s.close();
    c.close();
  }
} 

2. Add helper class to jbrms war file

Put helper classes in jar file :

D:\beaNew\user_projects\w4WP_workspaces\beaNew\HelperClasses>"C:\Program Files\Java\jdk1.5.0_11\bin\jar" 
\\-cf c:\myHelperClasses.jar com\mycompany\rules\data\ListLoader.class com.mycompany.rules.data.DBHandler

Add jar files to drools-jbrms.war

Note the path is set as WEB-INF\lib\

Note the jdbc files included in war.

Note this deployment of jbrms is for BEA Weblogic so jsf files are included, this is not necessary for JBOSS deployment.

3. Reference helper class in Technical Rule Asset Data Enumeration

Within Packages, under Explore, use the last icon on the right &147;Create a new data enumeration (drop down List)&148;

Enter the fact property that the enumeration applies to and enter your helper class plus method :

'Attributes.favouriteCheese' : (new com.mycompany.rules.data.ListLoader()).loadCheeses()

4. Use Enumeration in rule

When jbrms started, note output to console showing helper class has been used.

When rule containing Attributes.favouriteCheese written &150; drop down list appears for value !!