[rules-users] Here is a ResultSetCompiler for drools-templates

[Bill Tarr]

I’ve been trying out the DROOLS-TEMPLATES project, really impressed so far, surprised there isn’t more buzz about this project.
 
I wrote a new version of the DataProviderCompiler that takes a SQL ResultSet instead of a org.drools.template.DataProvider.  
 
I don’t think it will be useful to that many people, so I’m not going to try and add it to the code base, but I’ll provide it here.  I used it for testing out templates, but for our actual production code, I will naturally be using Hibernate to retrieve the rules from the DB (so I can take advantage of Hibernate cache.)  It is much quicker to set up a template test with this compiler though.  
 
It also allows you to put the data in whatever types you like in the database, as long as you add a String converter for that type in processData(), like:
 
<pre>
                    case java.sql.Types.DOUBLE:
                        cell = String.valueOf(rs.getInt(cellNum));
                        break;
</pre>
 
Calling Code (based on drools-examples\drools-examples-drl\src\main\java\org\drools\examples\DataDrivenTemplateExample.java)
 
<pre>
    ResultSet rs;
    try {
        Statement sta = conn.createStatement();
        rs = sta.executeQuery("SELECT * FROM RulePricingPolicy");
 
    // TODO: you need to catch or handle 
    } catch (SQLException e) {
    }
 
    final ResultSetCompiler converter = new ResultSetCompiler();
    final String drl = converter.compile(rs, getTemplate());
</pre>
 
Here is the ResultSet Compiler:
 
<pre>
import org.drools.template.parser.TemplateContainer;
import org.drools.template.parser.DefaultTemplateContainer;
import org.drools.template.parser.TemplateDataListener;
import org.drools.template.parser.DataListener;
 
import java.io.InputStream;
import java.util.List;
import java.util.ArrayList;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
 
/**
 * <p> A Drools template compiler which takes a ResultSet and compiles it into
 * a template using DefaultTemplateContainer.
 * </p>
 *
 * @author bxt, Aug 19, 2009 1:53:38 PM
 *         <br>
 *         Copyright © 2008 Customized Services Administrators, Inc.
 *         All Rights Reserved.
 */
public class ResultSetCompiler {
 
/**
 * Generates DRL from a data provider for the spreadsheet data and templates.
 *
 * @param rs       the resultset for the table data
 * @param template the string containing the template resource name
 * @return the generated DRL text as a String
 */
public String compile(final ResultSet rs,
                      final String template) {
    final InputStream templateStream = this.getClass().getResourceAsStream(template);
    return compile(rs,
            templateStream);
}
 
/**
 * Generates DRL from a data provider for the spreadsheet data and templates.
 *
 * @param rs             the resultset for the table data
 * @param templateStream the InputStream for reading the templates
 * @return the generated DRL text as a String
 */
public String compile(final ResultSet rs,
                      final InputStream templateStream) {
    TemplateContainer tc = new DefaultTemplateContainer(templateStream);
    closeStream(templateStream);
    return compile(rs,
            new TemplateDataListener(tc));
}
 
/**
 * Generates DRL from a data provider for the spreadsheet data and templates.
 *
 * @param rs       the resultset for the table data
 * @param listener a template data listener
 * @return the generated DRL text as a String
 */
public String compile(final ResultSet rs,
                      final TemplateDataListener listener) {
    List<DataListener> listeners = new ArrayList<DataListener>();
    listeners.add(listener);
    processData(rs,
            listeners);
    return listener.renderDRL();
}
 
/**
 * Iterate through the resultset.
 * @param rs       the resultset for the table data
 * @param listeners list of template data listener
 */
private void processData(final ResultSet rs,
                         List<DataListener> listeners) {
 
    try {
        ResultSetMetaData rsmd = rs.getMetaData();
        int colCount = rsmd.getColumnCount();
 
        int i = 0;
 
        while (rs.next()) {
            newRow(listeners, i, colCount);
            for (int cellNum = 1; cellNum < colCount + 1; cellNum++) {
                String cell;
 
                int sqlType = rsmd.getColumnType(cellNum);
                switch (sqlType) {
                    case java.sql.Types.DATE:
                        cell = rs.getDate(cellNum).toString();
                        break;
                    case java.sql.Types.INTEGER:
                    case java.sql.Types.DOUBLE:
                        cell = String.valueOf(rs.getInt(cellNum));
                        break;
                    default:
                        cell = rs.getString(cellNum);
                }
 
                newCell(listeners,
                        i,
                        cellNum -1,
                        cell,
                        DataListener.NON_MERGED);
            }
            i++;
        }
 
        //TODO: you need to throw or handle
    } catch (SQLException e) {
    }
 
    finishData(listeners);
}
 
private void finishData(List<DataListener> listeners) {
    for (DataListener listener : listeners) {
        listener.finishSheet();
    }
}
 
private void newRow(List<DataListener> listeners,
                    int row,
                    int cols) {
    for (DataListener listener : listeners) {
        listener.newRow(row,
                cols);
    }
}
 
public void newCell(List<DataListener> listeners,
                    int row,
                    int column,
                    String value,
                    int mergedColStart) {
    for (DataListener listener : listeners) {
        listener.newCell(row,
                column,
                value,
                mergedColStart);
    }
}
 
protected void closeStream(final InputStream stream) {
    try {
        stream.close();
    } catch (final Exception e) {
        System.err.print("WARNING: Wasn't able to correctly close stream for rule template. " + e.getMessage());
    }
}
}
</pre>