Wednesday, 23 September 2015

Drools 6.1.0 --> Rules in a Database Table.



Tags: Java, Drools, Dynamic Rules

This post will show a simple example using  Drools 6.1.0 where the rules are stored in a Database table. Advantages of storing rules in a table is that rules can be modified at run-time dynamically.
A User-Interface can be created to manage rules data in the table. 
In this example a Person object is supplied to the rules engine which determines the person's status.


If person's age will be matched with Min Age  and Max Age and the Person will get the appropriate Status.
The Age: Status mapping Table is as follows.
Min Age
Max Age
Status
0
2
Infant
2
6
Baby
6
13
Young Age
13
18
Juvenile
18
41
Youth
41
61
Middle Aged
61
80
Senior Citizen
81
200
Old Aged



Sql to Create the Tables and insert test data


CREATE TABLE age_rules(id INTEGER, min_age INTEGER, max_age INTEGER, status VARCHAR(256))
INSERT INTO age_rules(id, min_age, max_age, status) VALUES(1, 0, 2,'Infant')
INSERT INTO age_rules(id, min_age, max_age, status) VALUES(2, 2, 6,'Baby')
INSERT INTO age_rules(id, min_age, max_age, status) VALUES(3, 6, 13,'Young Age')
INSERT INTO age_rules(id, min_age, max_age, status) VALUES(4, 13,18,'Juvenile')
INSERT INTO age_rules(id, min_age, max_age, status) VALUES(5, 18,41,'Youth')
INSERT INTO age_rules(id, min_age, max_age, status) VALUES(6, 41,61,'Middle Aged')
INSERT INTO age_rules(id, min_age, max_age, status) VALUES(7, 61,81,'Senior Citizen')
INSERT INTO age_rules(id, min_age, max_age, status) VALUES(8, 81, 100,'Old Aged')





C:/age_rules.drt
template header
id
min_age
max_age
status
package org.drools.template.jdbc;
dialect "mvel"
template "ageRule"
rule "ageRule_@{row.rowNumber}"
    when
        $person : Person(age>=@{min_age} && age<@{max_age})
    then
     $person.status=":" + "@{status}";
end
end template


ResultSetDroolsTest.java
package org.drools.template.jdbc;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.kie.api.io.ResourceType;
import org.kie.internal.KnowledgeBase;
import org.kie.internal.KnowledgeBaseFactory;
import org.kie.internal.builder.KnowledgeBuilder;
import org.kie.internal.builder.KnowledgeBuilderFactory;
import org.kie.internal.io.ResourceFactory;
import org.kie.internal.runtime.StatefulKnowledgeSession;
public class ResultSetGeneratorTest {
      public static void main(String[] args) {
            try {
                  testResultSet();
            } catch (Exception e) {
                  e.printStackTrace();
            }
      }
      public static void testResultSet() throws Exception {
            Class.forName("com.ibm.db2.jcc.DB2Driver");
            Connection conn = DriverManager.getConnection(
                         "jdbc:dbType://DBServerIP:Port/DBName",
                         "DBUser", "Password");
            Statement sta = conn.createStatement();
            String sql="SELECT id, min_age, max_age, status FROM age_rules";
            ResultSet rs = sta.executeQuery(sql);
            final ResultSetGenerator converter = new ResultSetGenerator();
            final String drl = converter.compile(rs, getRulesStream());
            System.out.println(drl);
            sta.close();
            KnowledgeBuilder kbuilder =
                      KnowledgeBuilderFactory.newKnowledgeBuilder();
            kbuilder.add(ResourceFactory.newByteArrayResource(drl.getBytes()),
                           ResourceType.DRL);
            KnowledgeBase kbase = KnowledgeBaseFactory.newKnowledgeBase();
            kbase.addKnowledgePackages(kbuilder.getKnowledgePackages());
            StatefulKnowledgeSession kSession = 
               kbase.newStatefulKnowledgeSession();
            // now create some test data
            Person a = new Person("A", 22);     
            Person b = new Person("B", 52);
            Person c = new Person("C", 61);
            kSession.insert(a); 
            kSession.insert(b);   
            kSession.insert(c);
            kSession.fireAllRules();
            System.out.println(a.getName() + "," + a.getStatus());
            System.out.println(b.getName() + "," + b.getStatus());
            System.out.println(c.getName() + "," + c.getStatus());
            kSession.destroy();      
            kSession.dispose();
            kSession = kbase.newStatefulKnowledgeSession();
            Person aa = new Person("AA", 82); 
            Person bb = new Person("BB", 2);
            Person cc = new Person("CC", 41);
            kSession.insert(aa);
            kSession.insert(bb);    
            kSession.insert(cc);
            kSession.fireAllRules();
            System.out.println(aa.getName() + "," + aa.getStatus());
            System.out.println(bb.getName() + "," + bb.getStatus());
            System.out.println(cc.getName() + "," + cc.getStatus());
      }

      private static InputStream getRulesStream() throws FileNotFoundException {
            return new FileInputStream("C:/age_rules.drt");
      }

      private static void dbOperation(String expression, Connection conn)
        throws SQLException {
            Statement st;
            st = conn.createStatement();
            int i = st.executeUpdate(expression);
            if (i == -1) {
                  System.out.println("db error : " + expression);
            }
            st.close();
      }
}



Person.java



package org.drools.template.jdbc;
public class Person {
    private String name;
    private int age;
    private String status="";
    public Person() {
    }
    public Person(String name, int age) {
        this.name = name;
        this.age = age;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public String getStatus() {
        return status;
    }
    public void setStatus(String status) {
        this.status += status;
    }
}

4 comments:

  1. HI Dilip Sarangi,

    I out put is not printing the status for some reason for me ..

    The generated rules files has the correct placeholder

    ReplyDelete
    Replies
    1. you are not facing any error , while runing the above application , for me getting error at resultsergenerator

      Delete
  2. i am getting an error in ResultSetGenerator , it is saying as not available

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete