spring2.5整合osworkflow2.8

最新推荐文章于 2021-01-28 10:59:25 发布

scott.cgi

最新推荐文章于 2021-01-28 10:59:25 发布

阅读量2.5k

点赞数

分类专栏： J2ee 文章标签： spring string sql workflow null object

本文链接：https://blog.csdn.net/tom_221x/article/details/4487990

版权

J2ee 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

这里是spring2.5 用spring jdbc template的方式整合osworkflow2.8,数据库是mysql的。整合过后，由spring来管理osworkflow的配置类，由于是用spring jdbc才操作osworkflow的数据库，所以datasource可以纳入spring统一控制。

下面是spring整合osworkflow的配置文件：

<?xml version="1.0" encoding="UTF-8"?>  <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd"> <description>spring JDBCTemplate 方式</description>  <bean id="mysqlTemplateWorkflowStore" class="com.opensymphony.workflow.spi.jdbc.MySQLTemplateWorkflowStore" scope="prototype"> <property name="dataSource"> <ref bean="dataSource" /> </property> <property name="propertySetDelegate"> <ref bean="propertySetDelegate" /> </property> <property name="jdbcTemplateProperties"> <props> <prop key="history.table">OS_HISTORYSTEP</prop> <prop key="historyPrev.table">OS_HISTORYSTEP_PREV</prop> <prop key="current.table">OS_CURRENTSTEP</prop> <prop key="currentPrev.table">OS_CURRENTSTEP_PREV</prop> <prop key="step.id">ID</prop> <prop key="step.entryId">ENTRY_ID</prop> <prop key="step.stepId">STEP_ID</prop> <prop key="step.actionId">ACTION_ID</prop> <prop key="step.owner">OWNER</prop> <prop key="step.caller">CALLER</prop> <prop key="step.startDate">START_DATE</prop> <prop key="step.finishDate">FINISH_DATE</prop> <prop key="step.dueDate">DUE_DATE</prop> <prop key="step.status">STATUS</prop> <prop key="step.previousId">PREVIOUS_ID</prop> <prop key="step.sequence">SELECT max(ID)+1 FROM OS_STEPIDS</prop> <prop key="entry.sequence">SELECT max(ID)+1 FROM OS_WFENTRY</prop> <prop key="entry.table">OS_WFENTRY</prop> <prop key="entry.id">ID</prop> <prop key="entry.name">NAME</prop> <prop key="entry.state">STATE</prop>  <prop key="step.sequence.increment">INSERT INTO OS_STEPIDS (ID) values (null)</prop> <prop key="step.sequence.retrieve">SELECT max(ID) FROM OS_STEPIDS</prop> <prop key="entry.sequence.increment">INSERT INTO OS_ENTRYIDS (ID) values (null)</prop> <prop key="entry.sequence.retrieve">SELECT max(ID) FROM OS_ENTRYIDS</prop> </props> </property> </bean>  <bean id="propertySetDelegate" class="com.opensymphony.workflow.spi.jdbc.DefaultJDBCTemplatePropertySetDelegate" scope="prototype"> <property name="configurationProvider"> <ref bean="defaultJDBCTemplateConfigurationProvider"/> </property> </bean> <bean id="defaultJDBCTemplateConfigurationProvider" class="com.opensymphony.module.propertyset.database.DefaultJDBCTemplateConfigurationProvider" scope="prototype"> <property name="propertySetDAO"> <ref bean="jdbcTemplatePropertySetDAOImpl"/> </property> </bean> <bean id="jdbcTemplatePropertySetDAOImpl" class="com.opensymphony.module.propertyset.database.JDBCTemplatePropertySetDAOImpl" scope="prototype"> <property name="dataSource"> <ref bean="dataSource" /> </property> </bean>  <bean id="workflowFactory" class="com.opensymphony.workflow.spi.hibernate.SpringWorkflowFactory" init-method="init"> <property name="resource"> <value>../osworkflow/workflows.xml</value> </property> <property name="reload"> <value>true</value> </property> </bean>  <aop:config> <aop:pointcut id="oswfMethod1" expression="execution(* com.opensymphony.workflow.spi.WorkflowStore.*(..))" /> <aop:pointcut id="oswfMethod2" expression="execution(* com.opensymphony.module.propertyset.database.JDBCTemplatePropertySetDAO.*(..))" /> <aop:advisor pointcut-ref="oswfMethod1" advice-ref="txAdvice_oswf" /> <aop:advisor pointcut-ref="oswfMethod2" advice-ref="txAdvice_oswf" /> </aop:config> <tx:advice id="txAdvice_oswf" transaction-manager="txManager"> <tx:attributes> <tx:method name="*" /> </tx:attributes> </tx:advice>  <bean id="osworkflowConfiguration" class="com.opensymphony.workflow.config.SpringConfiguration" scope="prototype"> <property name="store"> <ref local="mysqlTemplateWorkflowStore" /> </property> <property name="factory"> <ref local="workflowFactory" /> </property> </bean>  <bean id="workflowTypeResolver" class="com.opensymphony.workflow.util.SpringTypeResolver"></bean> </beans>

用到了一些自己扩展的类(是网上一个牛人的写的，我只抽取了mysql jdbc那部分)见下图的结构：

代码依次如下：

DefaultJDBCTemplateConfigurationProvider.java

package com.opensymphony.module.propertyset.database; /** * @author */ public class DefaultJDBCTemplateConfigurationProvider implements JDBCTemplateConfigurationProvider { private JDBCTemplatePropertySetDAO propertySetDAO; public JDBCTemplatePropertySetDAO getPropertySetDAO() { return this.propertySetDAO; } public void setPropertySetDAO(JDBCTemplatePropertySetDAO propertySetDAO) { this.propertySetDAO = propertySetDAO; } }

JDBCTemplateConfigurationProvider.java

package com.opensymphony.module.propertyset.database; /** * @author */ public interface JDBCTemplateConfigurationProvider { JDBCTemplatePropertySetDAO getPropertySetDAO(); }

JDBCTemplatePropertySet.java

package com.opensymphony.module.propertyset.database; import com.opensymphony.module.propertyset.AbstractPropertySet; import com.opensymphony.module.propertyset.PropertyException; import com.opensymphony.util.ClassLoaderUtil; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import java.util.Collection; import java.util.Map; /** * @author */ public class JDBCTemplatePropertySet extends AbstractPropertySet { protected static Log log = LogFactory.getLog(JDBCTemplatePropertySet.class); private JDBCTemplateConfigurationProvider configProvider; private JDBCTemplatePropertySetDAO dao; @SuppressWarnings("unchecked") public void init(Map config, Map args) { log.debug("(JDBCTemplatePropertySet) top of init."); super.init(config, args); // first let's see if we got given a configuration provider to use // already this.configProvider = (JDBCTemplateConfigurationProvider) args.get("configurationProvider"); //$NON-NLS-1$ /* if we did not get given one in the args, we need to set a config provider up */ if (this.configProvider == null) { // lets see if we need to use a configurationProvider from a class String configProviderClass = (String) config.get("configuration.provider.class");//$NON-NLS-1$ if (configProviderClass != null) { if (log.isDebugEnabled()) { log.debug("Setting up property set provider of class: " + configProviderClass);//$NON-NLS-1$ } try { this.configProvider = (JDBCTemplateConfigurationProvider) ClassLoaderUtil .loadClass(configProviderClass, this.getClass()).newInstance(); } catch (Exception e) { log.error("Unable to load configuration provider class: " + configProviderClass, e);//$NON-NLS-1$ return; } } else { if (log.isDebugEnabled()) { log.debug("Setting up property set with DefaultJDBCTemplateConfigurationProvider");//$NON-NLS-1$ } this.configProvider = new DefaultJDBCTemplateConfigurationProvider(); } } else { if (log.isDebugEnabled()) { log.debug("Setting up property set with jdbcTemplate provider passed in args."); //$NON-NLS-1$ } } dao = configProvider.getPropertySetDAO(); dao.setGlobalKey((String) args.get("globalKey")); dao.setTableName((String) config.get("table.name")); dao.setColGlobalKey((String) config.get("col.globalKey")); dao.setColItemKey((String) config.get("col.itemKey")); dao.setColItemType((String) config.get("col.itemType")); dao.setColString((String) config.get("col.string")); dao.setColDate((String) config.get("col.date")); dao.setColData((String) config.get("col.data")); dao.setColFloat((String) config.get("col.float")); dao.setColNumber((String) config.get("col.number")); } @SuppressWarnings("unchecked") public Collection getKeys(String prefix, int type) throws PropertyException { return dao.getKeys(prefix, type); } public int getType(String key) throws PropertyException { return dao.getType(key); } public boolean exists(String key) throws PropertyException { return dao.exists(key); } public void remove(String key) throws PropertyException { dao.remove(key); } protected void setImpl(int type, String key, Object value) throws PropertyException { dao.setImpl(type, key, value); } protected Object get(int type, String key) throws PropertyException { return dao.get(type, key); } public void remove() throws PropertyException { } }

JDBCTemplatePropertySetDAO.java

package com.opensymphony.module.propertyset.database; import com.opensymphony.module.propertyset.PropertyException; import java.util.Collection; /** * @author */ public interface JDBCTemplatePropertySetDAO { @SuppressWarnings("unchecked") public Collection getKeys(String prefix, int type) throws PropertyException; public int getType(String key) throws PropertyException; public boolean exists(String key) throws PropertyException; public void remove(String key) throws PropertyException; public void setImpl(int type, String key, Object value) throws PropertyException; public Object get(int type, String key) throws PropertyException; public void setColData(String colData); public void setColDate(String colDate); public void setColFloat(String colFloat); public void setColGlobalKey(String colGlobalKey); public void setColItemKey(String colItemKey); public void setColItemType(String colItemType); public void setColNumber(String colNumber); public void setColString(String colString); public void setGlobalKey(String globalKey); public void setTableName(String tableName); }

JDBCTemplatePropertySetDAOImpl.java

package com.opensymphony.module.propertyset.database; import com.opensymphony.module.propertyset.InvalidPropertyTypeException; import com.opensymphony.module.propertyset.PropertyException; import com.opensymphony.module.propertyset.PropertySet; import com.opensymphony.util.Data; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.RowCallbackHandler; import org.springframework.jdbc.core.support.JdbcDaoSupport; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; import java.util.ArrayList; import java.util.Collection; import java.util.List; /** * @author */ public class JDBCTemplatePropertySetDAOImpl extends JdbcDaoSupport implements JDBCTemplatePropertySetDAO { private String colData; private String colDate; private String colFloat; private String colGlobalKey; private String colItemKey; private String colItemType; private String colNumber; private String colString; // args private String globalKey; private String tableName; public void setColData(String colData) { this.colData = colData; } public void setColDate(String colDate) { this.colDate = colDate; } public void setColFloat(String colFloat) { this.colFloat = colFloat; } public void setColGlobalKey(String colGlobalKey) { this.colGlobalKey = colGlobalKey; } public void setColItemKey(String colItemKey) { this.colItemKey = colItemKey; } public void setColItemType(String colItemType) { this.colItemType = colItemType; } public void setColNumber(String colNumber) { this.colNumber = colNumber; } public void setColString(String colString) { this.colString = colString; } public void setGlobalKey(String globalKey) { this.globalKey = globalKey; } public void setTableName(String tableName) { this.tableName = tableName; } @SuppressWarnings("unchecked") public Collection getKeys(String prefix, int type) throws PropertyException { if (prefix == null) { prefix = ""; } try { String sql = "SELECT " + colItemKey + " FROM " + tableName + " WHERE " + colItemKey + " LIKE ? AND " + colGlobalKey + " = ?"; final ArrayList list = new ArrayList(); if (type == 0) { this.getJdbcTemplate().query(sql, new Object[]{prefix + "%", globalKey}, new RowCallbackHandler() { public void processRow(ResultSet rs) throws SQLException { list.add(rs.getString(colItemKey)); } }); } else { sql = sql + " AND " + colItemType + " = ?"; this.getJdbcTemplate().query(sql, new Object[]{prefix + "%", globalKey, new Integer(type)}, new RowCallbackHandler() { public void processRow(ResultSet rs) throws SQLException { list.add(rs.getString(colItemKey)); } }); } return list; } catch (DataAccessException e) { throw new PropertyException(e.getMessage()); } } public int getType(String key) throws PropertyException { try { String sql = "SELECT " + colItemType + " FROM " + tableName + " WHERE " + colGlobalKey + " = ? AND " + colItemKey + " = ?"; return this.getJdbcTemplate().queryForInt(sql, new Object[]{globalKey, key}); } catch (DataAccessException e) { throw new PropertyException(e.getMessage()); } } public boolean exists(String key) throws PropertyException { return getType(key) != 0; } public void remove(String key) throws PropertyException { try { String sql = "DELETE FROM " + tableName + " WHERE " + colGlobalKey + " = ? AND " + colItemKey + " = ?"; this.getJdbcTemplate().update(sql, new Object[]{globalKey, key}); } catch (DataAccessException e) { throw new PropertyException(e.getMessage()); } } public void setImpl(int type, String key, Object value) throws PropertyException { if (value == null) { throw new PropertyException("JDBCTemplatePropertySet does not allow for null values to be stored"); } try { int rows = updateValues(type, key, value); if (rows != 1) { insertValues(type, key, value); } } catch (Exception e) { throw new PropertyException(e.getMessage()); } } @SuppressWarnings("unchecked") public Object get(final int type, String key) throws PropertyException { String sql = "SELECT " + colItemType + ", " + colString + ", " + colDate + ", " + colData + ", " + colFloat + ", " + colNumber + " FROM " + tableName + " WHERE " + colItemKey + " = ? AND " + colGlobalKey + " = ?"; final List list = new ArrayList(); try { this.getJdbcTemplate().query(sql, new Object[]{key, globalKey}, new RowCallbackHandler() { public void processRow(ResultSet rs) throws SQLException { int propertyType = rs.getInt(colItemType); if (propertyType != type) { throw new InvalidPropertyTypeException(); } Object o; switch (type) { case PropertySet.BOOLEAN: int boolVal = rs.getInt(colNumber); o = new Boolean(boolVal == 1); break; case PropertySet.DATA: o = rs.getBytes(colData); break; case PropertySet.DATE: o = rs.getTimestamp(colDate); break; case PropertySet.DOUBLE: o = new Double(rs.getDouble(colFloat)); break; case PropertySet.INT: o = new Integer(rs.getInt(colNumber)); break; case PropertySet.LONG: o = new Long(rs.getLong(colNumber)); break; case PropertySet.STRING: o = rs.getString(colString); break; default: throw new InvalidPropertyTypeException("JDBCPropertySet doesn't support this type yet."); } list.add(o); } }); } catch (NumberFormatException e) { throw new PropertyException(e.getMessage()); } return list.get(0); } private int updateValues(int type, String key, Object value) throws DataAccessException, PropertyException { //String sql = "UPDATE " + tableName + " SET " + colString + " = ?, " + colDate + " = ?, " + colData + " = ?, " + colFloat + " = ?, " + colNumber + " = ?, " + colItemType + " = ? " + " WHERE " + colGlobalKey + " = ? AND " + colItemKey + " = ?"; String sql = ""; switch (type) { case PropertySet.BOOLEAN: Boolean boolVal = (Boolean) value; sql = "UPDATE " + tableName + " SET " + colNumber + " = ?, " + colItemType + " = ? " + " WHERE " + colGlobalKey + " = ? AND " + colItemKey + " = ?"; return this.getJdbcTemplate().update(sql, new Object[]{(boolVal.booleanValue() ? 1 : 0), type, globalKey, key}); case PropertySet.DATA: Data data = (Data) value; sql = "UPDATE " + tableName + " SET " + colData + " = ?, " + colItemType + " = ? " + " WHERE " + colGlobalKey + " = ? AND " + colItemKey + " = ?"; return this.getJdbcTemplate().update(sql, new Object[]{data.getBytes(), type, globalKey, key}); case PropertySet.DATE: java.util.Date date = (java.util.Date) value; sql = "UPDATE " + tableName + " SET " + colDate + " = ?, " + colItemType + " = ? " + " WHERE " + colGlobalKey + " = ? AND " + colItemKey + " = ?"; return this.getJdbcTemplate().update(sql, new Object[]{new Timestamp(date.getTime()), type, globalKey, key}); case PropertySet.DOUBLE: Double d = (Double) value; sql = "UPDATE " + tableName + " SET " + colFloat + " = ?, " + colItemType + " = ? " + " WHERE " + colGlobalKey + " = ? AND " + colItemKey + " = ?"; return this.getJdbcTemplate().update(sql, new Object[]{d, type, globalKey, key}); case PropertySet.INT: Integer i = (Integer) value; sql = "UPDATE " + tableName + " SET " + colNumber + " = ?, " + colItemType + " = ? " + " WHERE " + colGlobalKey + " = ? AND " + colItemKey + " = ?"; return this.getJdbcTemplate().update(sql, new Object[]{i, type, globalKey, key}); case PropertySet.LONG: Long l = (Long) value; sql = "UPDATE " + tableName + " SET " + colNumber + " = ?, " + colItemType + " = ? " + " WHERE " + colGlobalKey + " = ? AND " + colItemKey + " = ?"; return this.getJdbcTemplate().update(sql, new Object[]{l, type, globalKey, key}); case PropertySet.STRING: sql = "UPDATE " + tableName + " SET " + colString + " = ?, " + colItemType + " = ? " + " WHERE " + colGlobalKey + " = ? AND " + colItemKey + " = ?"; return this.getJdbcTemplate().update(sql, new Object[]{(String) value, type, globalKey, key}); default: throw new PropertyException("This type isn't supported!"); } } private void insertValues(int type, String key, Object value) throws DataAccessException, PropertyException { //String sql = "INSERT INTO " + tableName + " (" + colString + ", " + colDate + ", " + colData + ", " + colFloat + ", " + colNumber + ", " + colItemType + ", " + colGlobalKey + ", " + colItemKey + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?)"; String sql = ""; switch (type) { case PropertySet.BOOLEAN: Boolean boolVal = (Boolean) value; sql = "INSERT INTO " + tableName + " (" + colNumber + ", " + colItemType + ", " + colGlobalKey + ", " + colItemKey + ") VALUES (?, ?, ?, ?)"; this.getJdbcTemplate().update(sql, new Object[]{(boolVal.booleanValue() ? 1 : 0), type, globalKey, key}); break; case PropertySet.DATA: Data data = (Data) value; sql = "INSERT INTO " + tableName + " (" + colData + ", " + colItemType + ", " + colGlobalKey + ", " + colItemKey + ") VALUES (?, ?, ?, ?)"; this.getJdbcTemplate().update(sql, new Object[]{data.getBytes(), type, globalKey, key}); break; case PropertySet.DATE: java.util.Date date = (java.util.Date) value; sql = "INSERT INTO " + tableName + " (" + colDate + ", " + colItemType + ", " + colGlobalKey + ", " + colItemKey + ") VALUES (?, ?, ?, ?)"; this.getJdbcTemplate().update(sql, new Object[]{new Timestamp(date.getTime()), type, globalKey, key}); break; case PropertySet.DOUBLE: Double d = (Double) value; sql = "INSERT INTO " + tableName + " (" + colFloat + ", " + colItemType + ", " + colGlobalKey + ", " + colItemKey + ") VALUES (?, ?, ?, ?)"; this.getJdbcTemplate().update(sql, new Object[]{d, type, globalKey, key}); break; case PropertySet.INT: Integer i = (Integer) value; sql = "INSERT INTO " + tableName + " (" + colNumber + ", " + colItemType + ", " + colGlobalKey + ", " + colItemKey + ") VALUES (?, ?, ?, ?)"; this.getJdbcTemplate().update(sql, new Object[]{i, type, globalKey, key}); break; case PropertySet.LONG: Long l = (Long) value; sql = "INSERT INTO " + tableName + " (" + colNumber + ", " + colItemType + ", " + colGlobalKey + ", " + colItemKey + ") VALUES (?, ?, ?, ?)"; this.getJdbcTemplate().update(sql, new Object[]{l, type, globalKey, key}); break; case PropertySet.STRING: sql = "INSERT INTO " + tableName + " (" + colString + ", " + colItemType + ", " + colGlobalKey + ", " + colItemKey + ") VALUES (?, ?, ?, ?)"; this.getJdbcTemplate().update(sql, new Object[]{(String) value, type, globalKey, key}); break; default: throw new PropertyException("This type isn't supported!"); } } }

DefaultJDBCTemplatePropertySetDelegate.java

package com.opensymphony.workflow.spi.jdbc; import com.opensymphony.module.propertyset.PropertySet; import com.opensymphony.module.propertyset.PropertySetManager; import com.opensymphony.module.propertyset.database.DefaultJDBCTemplateConfigurationProvider; import com.opensymphony.workflow.util.PropertySetDelegate; import java.util.HashMap; import java.util.Map; /** * @author */ public class DefaultJDBCTemplatePropertySetDelegate implements PropertySetDelegate { private DefaultJDBCTemplateConfigurationProvider configurationProvider; public void setConfigurationProvider( DefaultJDBCTemplateConfigurationProvider configurationProvider) { this.configurationProvider = configurationProvider; } public DefaultJDBCTemplatePropertySetDelegate() { super(); } @SuppressWarnings("unchecked") public PropertySet getPropertySet(long entryId) { Map args = new HashMap(1); args.put("globalKey", "osff_temp_" + entryId); args.put("configurationProvider", configurationProvider); return PropertySetManager.getInstance("jdbcTemplate", args); } }

JDBCTemplateWorkflowStore.java

package com.opensymphony.workflow.spi.jdbc; import com.opensymphony.module.propertyset.PropertySet; import com.opensymphony.workflow.StoreException; import com.opensymphony.workflow.query.*; import com.opensymphony.workflow.spi.*; import com.opensymphony.workflow.util.PropertySetDelegate; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowCallbackHandler; import org.springframework.jdbc.core.support.JdbcDaoSupport; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; import java.util.*; /** * JDBC implementation. * * * The following properties are all required: * <ul> * <li>datasource - the JNDI location for the DataSource that is to be used.</li> * <li>entry.sequence - SQL query that returns the next ID for a workflow entry</li> * <li>entry.table - table name for workflow entry</li> * <li>entry.id - column name for workflow entry ID field</li> * <li>entry.name - column name for workflow entry name field</li> * <li>entry.state - column name for workflow entry state field</li> * <li>step.sequence - SQL query that returns the next ID for a workflow step</li> * <li>history.table - table name for steps in history</li> * <li>current.table - table name for current steps</li> * <li>step.id - column name for step ID field</li> * <li>step.entryId - column name for workflow entry ID field (foreign key relationship to [entry.table].[entry.id])</li> * <li>step.stepId - column name for step workflow definition step field</li> * <li>step.actionId - column name for step action field</li> * <li>step.owner - column name for step owner field</li> * <li>step.caller - column name for step caller field</li> * <li>step.startDate - column name for step start date field</li> * <li>step.dueDate - column name for optional step due date field</li> * <li>step.finishDate - column name for step finish date field</li> * <li>step.status - column name for step status field</li> * <li>currentPrev.table - table name for the previous IDs for current steps</li> * <li>historyPrev.table - table name for the previous IDs for history steps</li> * <li>step.previousId - column name for step ID field (foreign key relation to [history.table].[step.id] or [current.table].[step.id])</li> * </ul> * * @author */ public class JDBCTemplateWorkflowStore extends JdbcDaoSupport implements WorkflowStore { private PropertySetDelegate propertySetDelegate; private static final Log log = LogFactory.getLog(JDBCTemplateWorkflowStore.class); protected Properties jdbcTemplateProperties; protected String currentPrevTable; protected String currentTable; protected String entryId; protected String entryName; protected String entrySequence; protected String entryState; protected String entryTable; protected String historyPrevTable; protected String historyTable; protected String stepActionId; protected String stepCaller; protected String stepDueDate; protected String stepEntryId; protected String stepFinishDate; protected String stepId; protected String stepOwner; protected String stepPreviousId; protected String stepSequence; protected String stepStartDate; protected String stepStatus; protected String stepStepId; protected boolean closeConnWhenDone = false; public void setPropertySetDelegate(PropertySetDelegate propertySetDelegate) { this.propertySetDelegate = propertySetDelegate; } public PropertySetDelegate getPropertySetDelegate() { return propertySetDelegate; } public Properties getJdbcTemplateProperties() { return this.jdbcTemplateProperties; } public void setJdbcTemplateProperties(Properties jdbcTemplateProperties) { this.jdbcTemplateProperties = jdbcTemplateProperties; init(); } public void setEntryState(long id, int state) throws StoreException { try { String sql = "UPDATE " + entryTable + " SET " + entryState + " = ? WHERE " + entryId + " = ?"; this.getJdbcTemplate().update(sql, new Object[]{new Integer(state), new Long(id)}); } catch (DataAccessException e) { throw new StoreException("Unable to update state for workflow instance #" + id + " to " + state, e); } } public PropertySet getPropertySet(long entryId) throws StoreException { if (getPropertySetDelegate() == null) { throw new StoreException("PropertySetDelegate is not properly configured"); } return getPropertySetDelegate().getPropertySet(entryId); } public boolean checkIfORExists(NestedExpression nestedExpression) { //GURKAN; //This method checks if OR exists in any nested query //This method is used by doNestedNaturalJoin() to make sure //OR does not exist within query int numberOfExp = nestedExpression.getExpressionCount(); if (nestedExpression.getExpressionOperator() == NestedExpression.OR) { return true; } for (int i = 0; i < numberOfExp; i++) { Expression expression = nestedExpression.getExpression(i); if (expression.isNested()) { NestedExpression nestedExp = (NestedExpression) expression; return checkIfORExists(nestedExp); } } return false; } public Step createCurrentStep(long entryId, int wfStepId, String owner, Date startDate, Date dueDate, String status, long[] previousIds) throws StoreException { try { long id = createCurrentStep(entryId, wfStepId, owner, startDate, dueDate, status); addPreviousSteps(id, previousIds); return new SimpleStep(id, entryId, wfStepId, 0, owner, startDate, dueDate, null, status, previousIds, null); } catch (DataAccessException e) { throw new StoreException("Unable to create current step for workflow instance #" + entryId, e); } } public WorkflowEntry createEntry(String workflowName) throws StoreException { try { String sql = "INSERT INTO " + entryTable + " (" + entryId + ", " + entryName + ", " + entryState + ") VALUES (?,?,?)"; if (log.isDebugEnabled()) { log.debug("Executing SQL statement: " + sql); } long id = getNextEntrySequence(); this.getJdbcTemplate().update(sql, new Object[]{new Long(id), new String(workflowName), new Integer(WorkflowEntry.CREATED)}); return new SimpleWorkflowEntry(id, workflowName, WorkflowEntry.CREATED); } catch (DataAccessException e) { throw new StoreException("Error creating new workflow instance", e); } } @SuppressWarnings("unchecked") public List findCurrentSteps(final long entryId) throws StoreException { try { String sql = "SELECT " + stepId + ", " + stepStepId + ", " + stepActionId + ", " + stepOwner + ", " + stepStartDate + ", " + stepDueDate + ", " + stepFinishDate + ", " + stepStatus + ", " + stepCaller + " FROM " + currentTable + " WHERE " + stepEntryId + " = ?"; final String sql2 = "SELECT " + stepPreviousId + " FROM " + currentPrevTable + " WHERE " + stepId + " = ?"; if (log.isDebugEnabled()) { log.debug("Executing SQL statement: " + sql); } if (log.isDebugEnabled()) { log.debug("Executing SQL statement: " + sql2); } final ArrayList currentSteps = new ArrayList(); final JdbcTemplate jdbcTemplate = this.getJdbcTemplate(); jdbcTemplate.query(sql, new Object[]{new Long(entryId)}, new RowCallbackHandler() { public void processRow(ResultSet rset) throws SQLException { long id = rset.getLong(1); int stepId = rset.getInt(2); int actionId = rset.getInt(3); String owner = rset.getString(4); Date startDate = rset.getTimestamp(5); Date dueDate = rset.getTimestamp(6); Date finishDate = rset.getTimestamp(7); String status = rset.getString(8); String caller = rset.getString(9); final ArrayList prevIdsList = new ArrayList(); jdbcTemplate.query(sql2, new Object[]{new Long(id)}, new RowCallbackHandler() { public void processRow(ResultSet rs) throws SQLException { long prevId = rs.getLong(1); prevIdsList.add(new Long(prevId)); } }); long[] prevIds = new long[prevIdsList.size()]; int i = 0; for (Iterator iterator = prevIdsList.iterator(); iterator.hasNext();) { Long aLong = (Long) iterator.next(); prevIds[i] = aLong.longValue(); i++; } SimpleStep step = new SimpleStep(id, entryId, stepId, actionId, owner, startDate, dueDate, finishDate, status, prevIds, caller); currentSteps.add(step); } }); return currentSteps; } catch (DataAccessException e) { throw new StoreException("Unable to locate current steps for workflow instance #" + entryId, e); } } @SuppressWarnings("unchecked") public WorkflowEntry findEntry(long theEntryId) throws StoreException { try { String sql = "SELECT " + entryName + ", " + entryState + " FROM " + entryTable + " WHERE " + entryId + " = ?"; if (log.isDebugEnabled()) { log.debug("Executing SQL statement: " + sql); } // List list = this.getJdbcTemplate().queryForList(sql, new Object[]{new Long(theEntryId)}); if (list.isEmpty()) { return null; } Map map = (Map) list.get(0); String workflowName = (String) map.get(entryName); int state = ((Integer) map.get(entryState)).intValue(); return new SimpleWorkflowEntry(theEntryId, workflowName, state); } catch (DataAccessException e) { throw new StoreException("Error finding workflow instance #" + entryId); } } @SuppressWarnings("unchecked") public List findHistorySteps(final long entryId) throws StoreException { try { String sql = "SELECT " + stepId + ", " + stepStepId + ", " + stepActionId + ", " + stepOwner + ", " + stepStartDate + ", " + stepDueDate + ", " + stepFinishDate + ", " + stepStatus + ", " + stepCaller + " FROM " + historyTable + " WHERE " + stepEntryId + " = ? ORDER BY " + stepId + " DESC"; final String sql2 = "SELECT " + stepPreviousId + " FROM " + historyPrevTable + " WHERE " + stepId + " = ?"; if (log.isDebugEnabled()) { log.debug("Executing SQL statement: " + sql); } if (log.isDebugEnabled()) { log.debug("Executing SQL statement: " + sql2); } final JdbcTemplate jdbcTemplate = this.getJdbcTemplate(); final ArrayList currentSteps = new ArrayList(); jdbcTemplate.query(sql, new Object[]{new Long(entryId)}, new RowCallbackHandler() { public void processRow(ResultSet rset) throws SQLException { long id = rset.getLong(1); int stepId = rset.getInt(2); int actionId = rset.getInt(3); String owner = rset.getString(4); Date startDate = rset.getTimestamp(5); Date dueDate = rset.getTimestamp(6); Date finishDate = rset.getTimestamp(7); String status = rset.getString(8); String caller = rset.getString(9); final ArrayList prevIdsList = new ArrayList(); jdbcTemplate.query(sql2, new Object[]{new Long(id)}, new RowCallbackHandler() { public void processRow(ResultSet rs) throws SQLException { long prevId = rs.getLong(1); prevIdsList.add(new Long(prevId)); } }); long[] prevIds = new long[prevIdsList.size()]; int i = 0; for (Iterator iterator = prevIdsList.iterator(); iterator.hasNext();) { Long aLong = (Long) iterator.next(); prevIds[i] = aLong.longValue(); i++; } SimpleStep step = new SimpleStep(id, entryId, stepId, actionId, owner, startDate, dueDate, finishDate, status, prevIds, caller); currentSteps.add(step); } }); return currentSteps; } catch (DataAccessException e) { throw new StoreException("Unable to locate history steps for workflow instance #" + entryId, e); } } @SuppressWarnings("unchecked") public void init(Map props) throws StoreException { } public void init() { entrySequence = getInitProperty("entry.sequence", "SELECT nextVal('seq_os_wfentry')"); stepSequence = getInitProperty("step.sequence", "SELECT nextVal('seq_os_currentsteps')"); entryTable = getInitProperty("entry.table", "OS_WFENTRY"); entryId = getInitProperty("entry.id", "ID"); entryName = getInitProperty("entry.name", "NAME"); entryState = getInitProperty("entry.state", "STATE"); historyTable = getInitProperty("history.table", "OS_HISTORYSTEP"); currentTable = getInitProperty("current.table", "OS_CURRENTSTEP"); currentPrevTable = getInitProperty("currentPrev.table", "OS_CURRENTSTEP_PREV"); historyPrevTable = getInitProperty("historyPrev.table", "OS_HISTORYSTEP_PREV"); stepId = getInitProperty("step.id", "ID"); stepEntryId = getInitProperty("step.entryId", "ENTRY_ID"); stepStepId = getInitProperty("step.stepId", "STEP_ID"); stepActionId = getInitProperty("step.actionId", "ACTION_ID"); stepOwner = getInitProperty("step.owner", "OWNER"); stepCaller = getInitProperty("step.caller", "CALLER"); stepStartDate = getInitProperty("step.startDate", "START_DATE"); stepFinishDate = getInitProperty("step.finishDate", "FINISH_DATE"); stepDueDate = getInitProperty("step.dueDate", "DUE_DATE"); stepStatus = getInitProperty("step.status", "STATUS"); stepPreviousId = getInitProperty("step.previousId", "PREVIOUS_ID"); } public Step markFinished(Step step, int actionId, Date finishDate, String status, String caller) throws StoreException { try { String sql = "UPDATE " + currentTable + " SET " + stepStatus + " = ?, " + stepActionId + " = ?, " + stepFinishDate + " = ?, " + stepCaller + " = ? WHERE " + stepId + " = ?"; if (log.isDebugEnabled()) { log.debug("Executing SQL statement: " + sql); } this.getJdbcTemplate().update(sql, new Object[]{new String(status), new Integer(actionId), new Timestamp(finishDate.getTime()), new String(caller), new Long(step.getId())}); SimpleStep theStep = (SimpleStep) step; theStep.setActionId(actionId); theStep.setFinishDate(finishDate); theStep.setStatus(status); theStep.setCaller(caller); return theStep; } catch (DataAccessException e) { throw new StoreException("Unable to mark step finished for #" + step.getEntryId(), e); } } public void moveToHistory(Step step) throws StoreException { try { String sql = "INSERT INTO " + historyTable + " (" + stepId + ',' + stepEntryId + ", " + stepStepId + ", " + stepActionId + ", " + stepOwner + ", " + stepStartDate + ", " + stepFinishDate + ", " + stepStatus + ", " + stepCaller + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"; if (log.isDebugEnabled()) { log.debug("Executing SQL statement: " + sql); log.debug("Executing SQL statementxxxxxxxxxxxx: " + step.getId() + "____" + step.getEntryId() + "______" + step.getStepId() + "______"); } Object _finishData = null; if (step.getFinishDate() != null) { _finishData = new Timestamp(step.getFinishDate().getTime()); } this.getJdbcTemplate().update(sql, new Object[]{ new Long(step.getId()), new Long(step.getEntryId()), new Integer(step.getStepId()), new Integer(step.getActionId()), new String(step.getOwner()), new Timestamp(step.getStartDate().getTime()), _finishData, new String(step.getStatus()), new String(step.getCaller()) }); long[] previousIds = step.getPreviousStepIds(); if ((previousIds != null) && (previousIds.length > 0)) { sql = "INSERT INTO " + historyPrevTable + " (" + stepId + ", " + stepPreviousId + ") VALUES (?, ?)"; log.debug("Executing SQL statement: " + sql); for (int i = 0; i < previousIds.length; i++) { long previousId = previousIds[i]; this.getJdbcTemplate().update(sql, new Object[]{new Long(step.getId()), new Long(previousId)}); } } sql = "DELETE FROM " + currentPrevTable + " WHERE " + stepId + " = ?"; if (log.isDebugEnabled()) { log.debug("Executing SQL statement: " + sql); } this.getJdbcTemplate().update(sql, new Object[]{new Long(step.getId())}); sql = "DELETE FROM " + currentTable + " WHERE " + stepId + " = ?"; if (log.isDebugEnabled()) { log.debug("Executing SQL statement: " + sql); } this.getJdbcTemplate().update(sql, new Object[]{new Long(step.getId())}); } catch (DataAccessException e) { throw new StoreException("Unable to move current step to history step for #" + step.getEntryId(), e); } } @SuppressWarnings("unchecked") public List query(WorkflowExpressionQuery e) throws StoreException { //GURKAN; // If it is simple, call buildSimple() // SELECT DISTINCT(ENTRY_ID) FROM OS_HISTORYSTEP WHERE FINISH_DATE < ? // // If it is nested, call doNestedNaturalJoin() if and only if the query is // ANDed including nested-nestd queries // If OR exists in any query call buildNested() // //doNestedNaturalJoin() // This doNestedNaturalJoin() method improves performance of the queries if and only if // the queries including nested queries are ANDed // // SELECT DISTINCT (a1.ENTRY_ID) AS retrieved // FROM OS_CURRENTSTEP AS a1 , OS_CURRENTSTEP AS a2 , OS_CURRENTSTEP AS a3 , OS_CURRENTSTEP AS a4 // WHERE ((a1.ENTRY_ID = a1.ENTRY_ID AND a1.ENTRY_ID = a2.ENTRY_ID) AND // (a2.ENTRY_ID = a3.ENTRY_ID AND a3.ENTRY_ID = a4.ENTRY_ID)) // AND ( a1.OWNER = ? AND a2.STATUS != ? AND a3.OWNER = ? AND a4.STATUS != ? ) // //doNestedLeftJoin() //not used // For this method to work, order of queries is matter // This doNestedLeftJoin() method will generate the queries but it works if and only if // the query is in correct order -- it is your luck // SELECT DISTINCT (a0.ENTRY_ID) AS retrieved FROM OS_CURRENTSTEP AS a0 // LEFT JOIN OS_CURRENTSTEP a1 ON a0.ENTRY_ID = a1.ENTRY_ID // // LEFT JOIN OS_CURRENTSTEP a2 ON a1.ENTRY_ID = a2.ENTRY_ID // LEFT JOIN OS_CURRENTSTEP a3 ON a2.ENTRY_ID = a3.ENTRY_ID // WHERE a1.OWNER = ? AND (a2.STATUS = ? OR a3.OWNER = ?) // if (log.isDebugEnabled()) { log.debug("Starting Query"); } Expression expression = e.getExpression(); if (log.isDebugEnabled()) { log.debug("Have all variables"); } if (expression.isNested()) { NestedExpression nestedExp = (NestedExpression) expression; StringBuffer sel = new StringBuffer(); StringBuffer columns = new StringBuffer(); StringBuffer leftJoin = new StringBuffer(); StringBuffer where = new StringBuffer(); StringBuffer whereComp = new StringBuffer(); StringBuffer orderBy = new StringBuffer(); List values = new LinkedList(); List queries = new LinkedList(); String columnName; String selectString; //Expression is nested and see if the expresion has OR if (checkIfORExists(nestedExp)) { //For doNestedLeftJoin() uncomment these -- again order is matter //and comment out last two lines where buildNested() is called // //columns.append("SELECT DISTINCT ("); //columns.append("a0" + "." + stepEntryId); //columnName = "retrieved"; //columns.append(") AS " + columnName); //columns.append(" FROM "); //columns.append(currentTable + " AS " + "a0"); //where.append("WHERE "); //doNestedLeftJoin(e, nestedExp, leftJoin, where, values, queries, orderBy); //selectString = columns.toString() + " " + leftJoin.toString() + " " + where.toString() + " " + orderBy.toString(); //System.out.println("LEFT JOIN ..."); // // columnName = buildNested(nestedExp, sel, values); selectString = sel.toString(); } else { columns.append("SELECT DISTINCT ("); columns.append("a1" + '.' + stepEntryId); columnName = "retrieved"; columns.append(") AS " + columnName); columns.append(" FROM "); where.append("WHERE "); doNestedNaturalJoin(e, nestedExp, columns, where, whereComp, values, queries, orderBy); selectString = columns.toString() + ' ' + leftJoin.toString() + ' ' + where.toString() + " AND ( " + whereComp.toString() + " ) " + ' ' + orderBy.toString(); // System.out.println("NATURAL JOIN ..."); } //System.out.println("number of queries is : " + queries.size()); //System.out.println("values.toString() : " + values.toString()); //System.out.println("columnName : " + columnName); //System.out.println("where : " + where); //System.out.println("whereComp : " + whereComp); //System.out.println("columns : " + columns); // System.out.println("Query is : " + selectString + "/n"); return doExpressionQuery(selectString, columnName, values); } else { // query is not empty ... it's a SIMPLE query // do what the old query did StringBuffer qry; List values = new LinkedList(); qry = new StringBuffer(); String columnName = buildSimple((FieldExpression) expression, qry, values); if (e.getSortOrder() != WorkflowExpressionQuery.SORT_NONE) { qry.append(" ORDER BY "); if (e.getOrderBy() != 0) { String fName = fieldName(e.getOrderBy()); qry.append(fName); // To help w/ MySQL and Informix, you have to include the column in the query String current = qry.toString(); String entry = current.substring(0, current.indexOf(columnName)) + columnName + "), " + fName + ' '; entry += current.substring(current.indexOf(columnName) + columnName.length() + 1); qry = new StringBuffer(entry); if (e.getSortOrder() == WorkflowExpressionQuery.SORT_DESC) { qry.append(" DESC"); } else { qry.append(" ASC"); } } else { qry.append(columnName); } } //System.out.println("Query is: " + qry.toString()); return doExpressionQuery(qry.toString(), columnName, values); } } @SuppressWarnings({ "deprecation", "unchecked" }) public List query(WorkflowQuery query) throws StoreException { final List results = new ArrayList(); // going to try to do all the comparisons in one query String sel; String table; int qtype = query.getType(); if (qtype == 0) { // then not set, so look in sub queries // todo: not sure if you would have a query that would look in both old and new, if so, i'll have to change this - TR // but then again, why are there redundant tables in the first place? the data model should probably change if (query.getLeft() != null) { qtype = query.getLeft().getType(); } } if (qtype == WorkflowQuery.CURRENT) { table = currentTable; } else { table = historyTable; } sel = "SELECT DISTINCT(" + stepEntryId + ") FROM " + table + " WHERE "; sel += queryWhere(query); if (log.isDebugEnabled()) { log.debug(sel); } try { this.getJdbcTemplate().query(sel, new RowCallbackHandler() { public void processRow(ResultSet rs) throws SQLException { // get entryIds and add to results list Long id = new Long(rs.getLong(stepEntryId)); results.add(id); } }); } catch (DataAccessException ex) { throw new StoreException("SQL Exception in query: " + ex.getMessage()); } return results; } protected long getNextEntrySequence() throws DataAccessException { if (log.isDebugEnabled()) { log.debug("Executing SQL statement: " + entrySequence); } return this.getJdbcTemplate().queryForLong(entrySequence); } protected long getNextStepSequence() throws DataAccessException { if (log.isDebugEnabled()) { log.debug("Executing SQL statement: " + stepSequence); } return this.getJdbcTemplate().queryForLong(stepSequence); } protected void addPreviousSteps(long id, long[] previousIds) throws DataAccessException { if ((previousIds != null) && (previousIds.length > 0)) { if (!((previousIds.length == 1) && (previousIds[0] == 0))) { String sql = "INSERT INTO " + currentPrevTable + " (" + stepId + ", " + stepPreviousId + ") VALUES (?, ?)"; log.debug("Executing SQL statement: " + sql); for (int i = 0; i < previousIds.length; i++) { long previousId = previousIds[i]; this.getJdbcTemplate().update(sql, new Object[]{new Long(id), new Long(previousId)}); } } } } protected long createCurrentStep(long entryId, int wfStepId, String owner, Date startDate, Date dueDate, String status) throws DataAccessException { String sql = "INSERT INTO " + currentTable + " (" + stepId + ',' + stepEntryId + ", " + stepStepId + ", " + stepActionId + ", " + stepOwner + ", " + stepStartDate + ", " + stepDueDate + ", " + stepFinishDate + ", " + stepStatus + ", " + stepCaller + " ) VALUES (?, ?, ?, null, ?, ?, ?, null, ?, null)"; long id = getNextStepSequence(); if (log.isDebugEnabled()) { log.debug("Executing SQL statement: " + sql); } Object _dueDate = null; if (dueDate != null) { _dueDate = new Timestamp(dueDate.getTime()); } this.getJdbcTemplate().update(sql, new Object[]{new Long(id), new Long(entryId), new Integer(wfStepId), new String(owner), new Timestamp(startDate.getTime()), _dueDate, new String(status)}); return id; } METHOD #3 OF 3 // // ...gur; //kardes @SuppressWarnings("unchecked") void doNestedNaturalJoin(WorkflowExpressionQuery e, NestedExpression nestedExpression, StringBuffer columns, StringBuffer where, StringBuffer whereComp, List values, List queries, StringBuffer orderBy) { // throws StoreException { Object value; int currentExpField; int numberOfExp = nestedExpression.getExpressionCount(); for (int i = 0; i < numberOfExp; i++) { //ori //for (i = numberOfExp; i > 0; i--) { //reverse 1 of 3 Expression expression = nestedExpression.getExpression(i); //ori //Expression expression = nestedExpression.getExpression(i - 1); //reverse 2 of 3 if (!(expression.isNested())) { FieldExpression fieldExp = (FieldExpression) expression; FieldExpression fieldExpBeforeCurrent; queries.add(expression); int queryId = queries.size(); if (queryId > 1) { columns.append(" , "); } //do; OS_CURRENTSTEP AS a1 .... if (fieldExp.getContext() == FieldExpression.CURRENT_STEPS) { columns.append(currentTable + " AS " + 'a' + queryId); } else if (fieldExp.getContext() == FieldExpression.HISTORY_STEPS) { columns.append(historyTable + " AS " + 'a' + queryId); } else { columns.append(entryTable + " AS " + 'a' + queryId); } / beginning of WHERE JOINS/s : // //do for first query; a1.ENTRY_ID = a1.ENTRY_ID if (queryId == 1) { where.append("a1" + '.' + stepEntryId); where.append(" = "); if (fieldExp.getContext() == FieldExpression.CURRENT_STEPS) { where.append("a" + queryId + '.' + stepEntryId); } else if (fieldExp.getContext() == FieldExpression.HISTORY_STEPS) { where.append("a" + queryId + '.' + stepEntryId); } else { where.append("a" + queryId + '.' + entryId); } } //do; a1.ENTRY_ID = a2.ENTRY_ID if (queryId > 1) { fieldExpBeforeCurrent = (FieldExpression) queries.get(queryId - 2); if (fieldExpBeforeCurrent.getContext() == FieldExpression.CURRENT_STEPS) { where.append("a" + (queryId - 1) + '.' + stepEntryId); } else if (fieldExpBeforeCurrent.getContext() == FieldExpression.HISTORY_STEPS) { where.append("a" + (queryId - 1) + '.' + stepEntryId); } else { where.append("a" + (queryId - 1) + '.' + entryId); } where.append(" = "); if (fieldExp.getContext() == FieldExpression.CURRENT_STEPS) { where.append("a" + queryId + '.' + stepEntryId); } else if (fieldExp.getContext() == FieldExpression.HISTORY_STEPS) { where.append("a" + queryId + '.' + stepEntryId); } else { where.append("a" + queryId + '.' + entryId); } } / end of LEFT JOIN : "LEFT JOIN OS_CURRENTSTEP a1 ON a0.ENTRY_ID = a1.ENTRY_ID // BEGINNING OF WHERE clause // value = fieldExp.getValue(); currentExpField = fieldExp.getField(); //if the Expression is negated and FieldExpression is "EQUALS", we need to negate that FieldExpression if (expression.isNegate()) { //do ; a2.STATUS != whereComp.append("a" + queryId + '.' + fieldName(fieldExp.getField())); switch (fieldExp.getOperator()) { //WHERE a1.STATUS != case FieldExpression.EQUALS: if (value == null) { whereComp.append(" IS NOT "); } else { whereComp.append(" != "); } break; case FieldExpression.NOT_EQUALS: if (value == null) { whereComp.append(" IS "); } else { whereComp.append(" = "); } break; case FieldExpression.GT: whereComp.append(" < "); break; case FieldExpression.LT: whereComp.append(" > "); break; default: whereComp.append(" != "); break; } switch (currentExpField) { case FieldExpression.START_DATE: case FieldExpression.FINISH_DATE: values.add(new Timestamp(((Date) value).getTime())); break; default: if (value == null) { values.add(null); } else { values.add(value); } break; } } else { //do; a1.OWNER = whereComp.append("a" + queryId + '.' + fieldName(fieldExp.getField())); switch (fieldExp.getOperator()) { //WHERE a2.FINISH_DATE < case FieldExpression.EQUALS: if (value == null) { whereComp.append(" IS "); } else { whereComp.append(" = "); } break; case FieldExpression.NOT_EQUALS: if (value == null) { whereComp.append(" IS NOT "); } else { whereComp.append(" <> "); } break; case FieldExpression.GT: whereComp.append(" > "); break; case FieldExpression.LT: whereComp.append(" < "); break; default: whereComp.append(" = "); break; } switch (currentExpField) { case FieldExpression.START_DATE: case FieldExpression.FINISH_DATE: values.add(new Timestamp(((Date) value).getTime())); break; default: if (value == null) { values.add(null); } else { values.add(value); } break; } } //do; a1.OWNER = ? ... a2.STATUS != ? whereComp.append(" ? "); END OF WHERE clause if ((e.getSortOrder() != WorkflowExpressionQuery.SORT_NONE) && (e.getOrderBy() != 0)) { System.out.println("ORDER BY ; queries.size() : " + queries.size()); orderBy.append(" ORDER BY "); orderBy.append("a1" + '.' + fieldName(e.getOrderBy())); if (e.getSortOrder() == WorkflowExpressionQuery.SORT_ASC) { orderBy.append(" ASC"); } else if (e.getSortOrder() == WorkflowExpressionQuery.SORT_DESC) { orderBy.append(" DESC"); } } } else { NestedExpression nestedExp = (NestedExpression) expression; where.append('('); doNestedNaturalJoin(e, nestedExp, columns, where, whereComp, values, queries, orderBy); where.append(')'); } //add AND or OR clause between the queries if (i < (numberOfExp - 1)) { //ori //if (i > 1) { //reverse 3 of 3 if (nestedExpression.getExpressionOperator() == NestedExpression.AND) { where.append(" AND "); whereComp.append(" AND "); } else { where.append(" OR "); whereComp.append(" OR "); } } } } protected String getInitProperty(String strName, String strDefault) { return this.jdbcTemplateProperties.getProperty(strName, strDefault); } @SuppressWarnings("unchecked") private String buildNested(NestedExpression nestedExpression, StringBuffer sel, List values) { sel.append("SELECT DISTINCT("); // Changed by Anthony on 2 June 2004, to query from OS_CURRENTSTEP instead //sel.append(entryId); sel.append(stepEntryId); sel.append(") FROM "); // Changed by Anthony on 2 June 2004, to query from OS_CURRENTSTEP instead // sel.append(entryTable); sel.append(currentTable); if (log.isDebugEnabled()) { log.debug("Thus far, query is: " + sel.toString()); } for (int i = 0; i < nestedExpression.getExpressionCount(); i++) { Expression expression = nestedExpression.getExpression(i); if (i == 0) { sel.append(" WHERE "); } else { if (nestedExpression.getExpressionOperator() == NestedExpression.AND) { sel.append(" AND "); } else { sel.append(" OR "); } } if (expression.isNegate()) { sel.append(" NOT "); } // Changed by Anthony on 2 June 2004, to query from OS_CURRENTSTEP instead // sel.append(entryId); sel.append(stepEntryId); sel.append(" IN ("); if (expression.isNested()) { this.buildNested((NestedExpression) nestedExpression.getExpression(i), sel, values); } else { FieldExpression sub = (FieldExpression) nestedExpression.getExpression(i); this.buildSimple(sub, sel, values); } sel.append(')'); } // Changed by Anthony on 2 June 2004, to query from OS_CURRENTSTEP instead // return (entryId); return (stepEntryId); } @SuppressWarnings("unchecked") private String buildSimple(FieldExpression fieldExpression, StringBuffer sel, List values) { String table; String columnName; if (fieldExpression.getContext() == FieldExpression.CURRENT_STEPS) { table = currentTable; columnName = stepEntryId; } else if (fieldExpression.getContext() == FieldExpression.HISTORY_STEPS) { table = historyTable; columnName = stepEntryId; } else { table = entryTable; columnName = entryId; } sel.append("SELECT DISTINCT("); sel.append(columnName); sel.append(") FROM "); sel.append(table); sel.append(" WHERE "); queryComparison(fieldExpression, sel, values); return columnName; } @SuppressWarnings("unchecked") private List doExpressionQuery(String sel, final String columnName, List values) throws StoreException { if (log.isDebugEnabled()) { log.debug(sel); } @SuppressWarnings("unused") ResultSet rs = null; final List results = new ArrayList(); try { Object obj [] = null; if (!values.isEmpty()) { obj = new Object[values.size()]; for (int i = 0; i < values.size(); i++) { obj[i] = values.get(i); } } this.getJdbcTemplate().query(sel, obj, new RowCallbackHandler() { public void processRow(ResultSet rs) throws SQLException { // get entryIds and add to results list Long id = new Long(rs.getLong(columnName)); results.add(id); } }); return results; } catch (DataAccessException ex) { throw new StoreException("SQL Exception in query: " + ex.getMessage()); } } private static String escape(String s) { StringBuffer sb = new StringBuffer(s); char c; char[] chars = s.toCharArray(); for (int i = 0; i < chars.length; i++) { c = chars[i]; switch (c) { case '/'': sb.insert(i, '/''); i++; break; case '//': sb.insert(i, '//'); i++; } } return sb.toString(); } private String fieldName(int field) { switch (field) { case FieldExpression.ACTION: // actionId return stepActionId; case FieldExpression.CALLER: return stepCaller; case FieldExpression.FINISH_DATE: return stepFinishDate; case FieldExpression.OWNER: return stepOwner; case FieldExpression.START_DATE: return stepStartDate; case FieldExpression.STEP: // stepId return stepStepId; case FieldExpression.STATUS: return stepStatus; case FieldExpression.STATE: return entryState; case FieldExpression.NAME: return entryName; case FieldExpression.DUE_DATE: return stepDueDate; default: return "1"; } } @SuppressWarnings("deprecation") private String queryComparison(WorkflowQuery query) { Object value = query.getValue(); int operator = query.getOperator(); int field = query.getField(); //int type = query.getType(); String oper; switch (operator) { case WorkflowQuery.EQUALS: oper = " = "; break; case WorkflowQuery.NOT_EQUALS: oper = " <> "; break; case WorkflowQuery.GT: oper = " > "; break; case WorkflowQuery.LT: oper = " < "; break; default: oper = " = "; } String left = fieldName(field); String right; if (value != null) { right = '/'' + JDBCTemplateWorkflowStore.escape(value.toString()) + '/''; } else { right = "null"; } return left + oper + right; } /** * Method queryComparison * * @param expression a FieldExpression * @param sel a StringBuffer */ @SuppressWarnings("unchecked") private void queryComparison(FieldExpression expression, StringBuffer sel, List values) { Object value = expression.getValue(); int operator = expression.getOperator(); int field = expression.getField(); String oper; switch (operator) { case FieldExpression.EQUALS: if (value == null) { oper = " IS "; } else { oper = " = "; } break; case FieldExpression.NOT_EQUALS: if (value == null) { oper = " IS NOT "; } else { oper = " <> "; } break; case FieldExpression.GT: oper = " > "; break; case FieldExpression.LT: oper = " < "; break; default: oper = " = "; } String left = fieldName(field); String right = "?"; switch (field) { case FieldExpression.FINISH_DATE: values.add(new Timestamp(((Date) value).getTime())); break; case FieldExpression.START_DATE: values.add(new Timestamp(((Date) value).getTime())); break; case FieldExpression.DUE_DATE: values.add(new Timestamp(((Date) value).getTime())); break; default: if (value == null) { right = "null"; } else { values.add(value); } } sel.append(left); sel.append(oper); sel.append(right); } @SuppressWarnings("deprecation") private String queryWhere(WorkflowQuery query) { if (query.getLeft() == null) { // leaf node return queryComparison(query); } else { int operator = query.getOperator(); WorkflowQuery left = query.getLeft(); WorkflowQuery right = query.getRight(); switch (operator) { case WorkflowQuery.AND: return '(' + queryWhere(left) + " AND " + queryWhere(right) + ')'; case WorkflowQuery.OR: return '(' + queryWhere(left) + " OR " + queryWhere(right) + ')'; case WorkflowQuery.XOR: return '(' + queryWhere(left) + " XOR " + queryWhere(right) + ')'; } } return ""; // not sure if we should throw an exception or how this should be handled } }

MySQLTemplateWorkflowStore.java

package com.opensymphony.workflow.spi.jdbc; import org.springframework.dao.DataAccessException; import java.util.Properties; /** * @author */ public class MySQLTemplateWorkflowStore extends JDBCTemplateWorkflowStore { protected String entrySequenceIncrement = null; protected String entrySequenceRetrieve = null; protected String stepSequenceIncrement = null; protected String stepSequenceRetrieve = null; public void setJdbcTemplateProperties(Properties jdbcTemplateProperties) { this.jdbcTemplateProperties = jdbcTemplateProperties; super.init(); this.init(); } public void init() { stepSequenceIncrement = getInitProperty("step.sequence.increment", "INSERT INTO OS_STEPIDS (ID) values (null)"); stepSequenceRetrieve = getInitProperty("step.sequence.retrieve", "SELECT max(ID) FROM OS_STEPIDS"); entrySequenceIncrement = getInitProperty("entry.sequence.increment", "INSERT INTO OS_ENTRYIDS (ID) values (null)"); entrySequenceRetrieve = getInitProperty("entry.sequence.retrieve", "SELECT max(ID) FROM OS_ENTRYIDS"); } protected long getNextEntrySequence() throws DataAccessException { this.getJdbcTemplate().update(entrySequenceIncrement); long id = this.getJdbcTemplate().queryForLong(entrySequenceRetrieve); return id; } protected long getNextStepSequence() throws DataAccessException { this.getJdbcTemplate().update(stepSequenceIncrement); long id = this.getJdbcTemplate().queryForLong(stepSequenceRetrieve); return id; } }

要注意配置文件里，spring关于osworkflow事务的aop配置，一定对应扩展的类，否则流程过程中的sql语句不能即使的执行，配了就完全ok了。

下面是数据库的sql,我把原数据库里的用户权限的3个表去了，这个可以用自己的，用它的好不爽。

# MySQL-Front 5.1 (Build 1.5) /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE */; /*!40101 SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES */; /*!40103 SET SQL_NOTES='ON' */; # Host: 127.0.0.1 Database: osworkflow # ------------------------------------------------------ # Server version 5.0.67-community-nt DROP DATABASE IF EXISTS `osworkflow`; CREATE DATABASE `osworkflow` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `osworkflow`; # # Source for table os_currentstep # CREATE TABLE `os_currentstep` ( `ID` bigint(20) NOT NULL default '0' COMMENT '当前步骤id', `ENTRY_ID` bigint(20) default NULL COMMENT '工作流程实例ID，与os_wfentry的ID相关', `STEP_ID` int(11) default NULL COMMENT '当前步骤ID', `ACTION_ID` int(11) default NULL COMMENT '当前动作ID', `OWNER` varchar(35) default NULL COMMENT '当前状态下流程所有者，与os_user用户ID关联', `START_DATE` datetime default NULL COMMENT '当前流程开始时间', `FINISH_DATE` datetime default NULL COMMENT '当前流程结束时间', `DUE_DATE` datetime default NULL, `STATUS` varchar(40) default NULL COMMENT '流程当前状态', `CALLER` varchar(35) default NULL COMMENT '当前流程调用者，与os_user用户ID关联', PRIMARY KEY (`ID`), KEY `ENTRY_ID` (`ENTRY_ID`), KEY `OWNER` (`OWNER`), KEY `CALLER` (`CALLER`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; # # Dumping data for table os_currentstep # # # Source for table os_currentstep_prev # CREATE TABLE `os_currentstep_prev` ( `ID` bigint(20) NOT NULL default '0' COMMENT '当前步骤ID，与os_currentstep的ID作关联', `PREVIOUS_ID` bigint(20) NOT NULL default '0' COMMENT '当前步骤之前步骤ID，与os_historystep的ID作关联', PRIMARY KEY (`ID`,`PREVIOUS_ID`), KEY `ID` (`ID`), KEY `PREVIOUS_ID` (`PREVIOUS_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; # # Dumping data for table os_currentstep_prev # # # Source for table os_entryids # CREATE TABLE `os_entryids` ( `ID` bigint(20) NOT NULL auto_increment COMMENT '由数据库自动生成的workflow_entry_id。供os_wfentry表用', PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8; # # Dumping data for table os_entryids # # # Source for table os_historystep # CREATE TABLE `os_historystep` ( `ID` bigint(20) NOT NULL default '0' COMMENT '历史步骤的ID号', `ENTRY_ID` bigint(20) default NULL COMMENT '工作流程实例ID，与os_wfentry的ID相关联', `STEP_ID` int(11) default NULL COMMENT '历史步骤ID', `ACTION_ID` int(11) default NULL COMMENT '历史动作ID', `OWNER` varchar(35) default NULL COMMENT '历史状态下流程所有者，与os_user用户ID关联', `START_DATE` datetime default NULL COMMENT '历史流程开始时间', `FINISH_DATE` datetime default NULL COMMENT '历史流程结束时间', `DUE_DATE` datetime default NULL, `STATUS` varchar(40) default NULL COMMENT '流程历史状态', `CALLER` varchar(35) default NULL COMMENT '历史流程调用者，与os_user用户ID关联', PRIMARY KEY (`ID`), KEY `ENTRY_ID` (`ENTRY_ID`), KEY `OWNER` (`OWNER`), KEY `CALLER` (`CALLER`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; # # Dumping data for table os_historystep # # # Source for table os_historystep_prev # CREATE TABLE `os_historystep_prev` ( `ID` bigint(20) NOT NULL default '0' COMMENT '历史步骤ID，与os_historystep的ID作关联', `PREVIOUS_ID` bigint(20) NOT NULL default '0' COMMENT '历史步骤之前步骤ID，与os_historystep的ID作关联', PRIMARY KEY (`ID`,`PREVIOUS_ID`), KEY `ID` (`ID`), KEY `PREVIOUS_ID` (`PREVIOUS_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; # # Dumping data for table os_historystep_prev # # # Source for table os_propertyentry # CREATE TABLE `os_propertyentry` ( `GLOBAL_KEY` varchar(250) NOT NULL default '' COMMENT '全局变量名，如果为JDBCWorkflowStore即为osff_加上流程实例ID；如果为JDBCTemplateWorkflowStore即为osff_temp_加上流程实例ID', `ITEM_KEY` varchar(250) NOT NULL default '' COMMENT '局部变量名，通常是指真正要在程序中调用的”key”', `ITEM_TYPE` tinyint(3) default NULL COMMENT '以数字标明其property的类型。如5是String型', `STRING_VALUE` varchar(255) default NULL, `DATE_VALUE` datetime default NULL COMMENT '如果是Date类型的数据，存入这个字段里', `DATA_VALUE` blob, `FLOAT_VALUE` float default NULL COMMENT '如果是Float类型的数据，存入这个字段里', `NUMBER_VALUE` bigint(10) default NULL COMMENT '如果是Number类型的数据，存入这个字段里', PRIMARY KEY (`GLOBAL_KEY`,`ITEM_KEY`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; # # Dumping data for table os_propertyentry # # # Source for table os_stepids # CREATE TABLE `os_stepids` ( `ID` bigint(20) NOT NULL auto_increment COMMENT '由数据库自动生成的step_id。供os__currentstep表或os_historytstep表使用', PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=120 DEFAULT CHARSET=utf8; # # Dumping data for table os_stepids # # # Source for table os_wfentry # CREATE TABLE `os_wfentry` ( `ID` bigint(20) NOT NULL default '0' COMMENT '流程的ID号', `NAME` varchar(60) default NULL COMMENT '流程名称', `STATE` int(11) default NULL COMMENT '流程状态。有五种：CREATED = 0;ACTIVATED = 1;SUSPENDED = 2;KILLED = 3;COMPLETED = 4;UNKNOWN = -1', PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; # # Dumping data for table os_wfentry # # # Foreign keys for table os_currentstep # ALTER TABLE `os_currentstep` ADD CONSTRAINT `os_currentstep_ibfk_1` FOREIGN KEY (`ENTRY_ID`) REFERENCES `os_wfentry` (`ID`); # # Foreign keys for table os_currentstep_prev # ALTER TABLE `os_currentstep_prev` ADD CONSTRAINT `os_currentstep_prev_ibfk_1` FOREIGN KEY (`ID`) REFERENCES `os_currentstep` (`ID`), ADD CONSTRAINT `os_currentstep_prev_ibfk_2` FOREIGN KEY (`PREVIOUS_ID`) REFERENCES `os_historystep` (`ID`); # # Foreign keys for table os_historystep # ALTER TABLE `os_historystep` ADD CONSTRAINT `os_historystep_ibfk_1` FOREIGN KEY (`ENTRY_ID`) REFERENCES `os_wfentry` (`ID`); # # Foreign keys for table os_historystep_prev # ALTER TABLE `os_historystep_prev` ADD CONSTRAINT `os_historystep_prev_ibfk_1` FOREIGN KEY (`ID`) REFERENCES `os_historystep` (`ID`), ADD CONSTRAINT `os_historystep_prev_ibfk_2` FOREIGN KEY (`PREVIOUS_ID`) REFERENCES `os_historystep` (`ID`); /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

对了还有个propertyset.xml文件：

至于自定义的流程的xml就参看osworkflow官方的例子吧，这样配置好以后osworkflowConfiguration就成为了spring的bean了可以注入了，当然也可以把baseWorkflow也配置成bean，进行注入。

最后提供一个svn:http://xeducation.googlecode.com/svn/trunk,这里是一个spring2.5+hibernate3.x+struts2.1.x+osworkflow2.8的一个空架子，可以运行的。注释也写的比较明白。(myeclipse的工程)

有兴趣的可以当下来看看。

scott.cgi

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
spring2.5整合osworkflow2.8

这里是spring2.5 用spring jdbc template的方式整合osworkflow2.8,数据库是mysql的。整合过后，由spring来管理osworkflow的配置类，由于是用spring jdbc才操作osworkflow的数据库，所以datasource可以纳入spring统一控制。下面是spring整合osworkflow的配置文件：
复制链接

扫一扫

专栏目录