Alfresco Activiti talking to external database

In this blog, I explain how to connect an Activiti workflow which is integrated with Alfresco, to an external database. Here I assume you are already familiar with Alfresco, Activiti, SQL and Spring. I implemented this sample code in Alfresco 4.2.2 community version. This code should mostly work in Alfresco 5.0, note with the latest version uses Maven while the older ones use ANT.

Click here to check out my follow up blog on unit testing this code.

For the purposes of this exercise, I am using 'Review And Approve Activiti Process' workflow which comes out of the box with Alfresco. Here is the workflow process diagram.

The goal is to record all activities associated with the user task 'Review Task' in an external database. More specifically, every time an editor reviews a document, Activiti process should insert a new record into a table named article_actions.

1. Create a new Acitivit listener class called LogActionListener. Note, I hardcoded some values for simplicity. For this exercise, I placed all Java codes under 'com.example.activiti.workflow' package.

package com.example.activiti.workflow;

import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.activiti.engine.delegate.DelegateExecution;
import org.alfresco.repo.workflow.activiti.BaseJavaDelegate;
import org.activiti.engine.delegate.DelegateTask;
import org.activiti.engine.delegate.TaskListener;
import org.apache.log4j.Logger;

public class LogActionListener extends BaseJavaDelegate
                               implements TaskListener {
   private static final Logger log =
                           Logger.getLogger(LogActionListener.class);
   private JdbcTemplate jdbcTemplate;

   @Override
   public void notify(DelegateTask task) {
     String userName = "reviewer";
     String articleId = "112233";
     String action = "approved";
     /**
      * Specify the statement
      */
     String query =
        "INSERT INTO article_actions (user_name, article_id, action)
                VALUES (?, ?, ?)";
     /**
      * Specify the values 
      */
     jdbcTemplate.update(query, new Object[] { userName, articleId, action });
   }

   public void setExtDBSource(DataSource extDBSource) {
     this.jdbcTemplate = new JdbcTemplate(extDBSource);
   }
 
   @Override
   public void execute(DelegateExecution execution) throws Exception {
     log.error("Should not be called! This is a dummy method!");
   }
}

2. Register LogActionListener as a listener for 'Review Task'. When the reviewer completes the user task, the process invokes the LogActionListener. Refer to the relevent portion of process definition file, review.bpmn20.xml, below.

<userTask id="reviewTask" name="Review Task"
          activiti:formKey="wf:activitiReviewTask">
    <extensionElements>
        ...
        </activiti:taskListener>
            <activiti:taskListener event="complete"
                                   delegateExpression="${LogActionListener}">
        </activiti:taskListener>
        ...
    </extensionElements>
</userTask>

3. In activiti-context.xml file, you define 'extDataSource' bean which holds the database connection information. Spring injects this bean into LogActionListener class when Activiti instantiates it. Note the reference to extDataSource bean in the LogActionClass bean definition. This code segment goes at the end of 'beans' block.

<bean id="extDataSource"
      class="org.apache.commons.dbcp.BasicDataSource"
      destroy-method="close">
    <property name="driverClassName" value="${external_db.driver}"/>
    <property name="url" value="${external_db.url}"/>
    <property name="username" value="${external_db.username}"/>
    <property name="password" value="${external_db.password}"/>
</bean>

<bean id="AbstractUserTaskDelegate" parent="baseJavaDelegate"
      abstract="true" depends-on="activitiBeanRegistry"/>

<bean id="LogActionClass" parent="AbstractUserTaskDelegate"
      class="com.example.activiti.workflow.LogActionListener">
    <property name="extDBSource" ref="extDataSource" />
</bean>

4. Store the database connection information in alfresco-global.properties file under repository project; the schema name in this case is 'reportdb'.

### External database connection properties ###
external_db.driver=org.gjt.mm.mysql.Driver
external_db.username=reporter
external_db.password=password
external_db.name=reportdb
external_db.url=jdbc:mysql://localhost:3306/reportdb?useUnicode=yes&characterEncoding=UTF-8

5. For your reference, here is the article_actions table definition.

CREATE TABLE article_actions (
  id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  user_name varchar(64) NOT NULL,
  article_id varchar(64) DEFAULT NULL,
  action varchar(64) NOT NULL,
  creation_date TIMESTAMP AS CURRENT_TIMESTAMP NOT NULL);

Congratulation, we are all done!

6. Start Alfresco and submit a document for review using the 'Review And Approve Activiti Process' workflow. Once you finish the review process, query the article_actions table, you should see a new record.

Check out my post on unit testing this code!