/* * Copyright (c) 2002-2003 by OpenSymphony * All rights reserved. */package com.opensymphony.workflow.spi.jdbc;import com.opensymphony.module.propertyset.PropertySet;import com.opensymphony.module.propertyset.PropertySetManager;import com.opensymphony.workflow.StoreException;import com.opensymphony.workflow.query.Expression;import com.opensymphony.workflow.query.FieldExpression;import com.opensymphony.workflow.query.NestedExpression;import com.opensymphony.workflow.query.WorkflowExpressionQuery;import com.opensymphony.workflow.query.WorkflowQuery;import com.opensymphony.workflow.spi.SimpleStep;import com.opensymphony.workflow.spi.SimpleWorkflowEntry;import com.opensymphony.workflow.spi.Step;import com.opensymphony.workflow.spi.WorkflowEntry;import com.opensymphony.workflow.spi.WorkflowStore;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import java.sql.*;import java.util.*;import java.util.Date;import javax.naming.InitialContext;import javax.naming.NamingException;import javax.sql.DataSource;/** * JDBC implementation. * <p> * * The following properties are all <b>required</b>: * <ul> *  <li><b>datasource</b> - the JNDI location for the DataSource that is to be used.</li> *  <li><b>entry.sequence</b> - SQL query that returns the next ID for a workflow entry</li> *  <li><b>entry.table</b> - table name for workflow entry</li> *  <li><b>entry.id</b> - column name for workflow entry ID field</li> *  <li><b>entry.name</b> - column name for workflow entry name field</li> *  <li><b>entry.state</b> - column name for workflow entry state field</li> *  <li><b>step.sequence</b> - SQL query that returns the next ID for a workflow step</li> *  <li><b>history.table</b> - table name for steps in history</li> *  <li><b>current.table</b> - table name for current steps</li> *  <li><b>step.id</b> - column name for step ID field</li> *  <li><b>step.entryId</b> - column name for workflow entry ID field (foreign key relationship to [entry.table].[entry.id])</li> *  <li><b>step.stepId</b> - column name for step workflow definition step field</li> *  <li><b>step.actionId</b> - column name for step action field</li> *  <li><b>step.owner</b> - column name for step owner field</li> *  <li><b>step.caller</b> - column name for step caller field</li> *  <li><b>step.startDate</b> - column name for step start date field</li> *  <li><b>step.dueDate</b> - column name for optional step due date field</li> *  <li><b>step.finishDate</b> - column name for step finish date field</li> *  <li><b>step.status</b> - column name for step status field</li> *  <li><b>currentPrev.table</b> - table name for the previous IDs for current steps</li> *  <li><b>historyPrev.table</b> - table name for the previous IDs for history steps</li> *  <li><b>step.previousId</b> - column name for step ID field (foreign key relation to [history.table].[step.id] or [current.table].[step.id])</li> * </ul> * * @author <a href="mailto:plightbo@hotmail.com">Pat Lightbody</a> */public class JDBCWorkflowStore implements WorkflowStore {    //~ Static fields/initializers /////////////////////////////////////////////    private static final Log log = LogFactory.getLog(JDBCWorkflowStore.class);    //~ Instance fields ////////////////////////////////////////////////////////    protected DataSource ds;    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;    //~ Methods ////////////////////////////////////////////////////////////////    public void setEntryState(long id, int state) throws StoreException {        Connection conn = null;        PreparedStatement ps = null;        try {            conn = getConnection();            String sql = "UPDATE " + entryTable + " SET " + entryState + " = ? WHERE " + entryId + " = ?";            ps = conn.prepareStatement(sql);            ps.setInt(1, state);            ps.setLong(2, id);            ps.executeUpdate();        } catch (SQLException e) {            throw new StoreException("Unable to update state for workflow instance #" + id + " to " + state, e);        } finally {            cleanup(conn, ps, null);        }    }    public PropertySet getPropertySet(long entryId) {        HashMap args = new HashMap(1);        args.put("globalKey", "osff_" + entryId);        return PropertySetManager.getInstance("jdbc", args);    }    ////////////METHOD #2 OF 3 //////////////////    ////////// ...gur;  ////////////////////    //kiz    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);            }        }        //System.out.println("!!!...........false is returned ..!!!");        return false;    }    public Step createCurrentStep(long entryId, int wfStepId, String owner, Date startDate, Date dueDate, String status, long[] previousIds) throws StoreException {        Connection conn = null;        try {            conn = getConnection();            long id = createCurrentStep(conn, entryId, wfStepId, owner, startDate, dueDate, status);            addPreviousSteps(conn, id, previousIds);            return new SimpleStep(id, entryId, wfStepId, 0, owner, startDate, dueDate, null, status, previousIds, null);        } catch (SQLException e) {            throw new StoreException("Unable to create current step for workflow instance #" + entryId, e);        } finally {            cleanup(conn, null, null);        }    }    public WorkflowEntry createEntry(String workflowName) throws StoreException {        Connection conn = null;        PreparedStatement stmt = null;        try {            conn = getConnection();            String sql = "INSERT INTO " + entryTable + " (" + entryId + ", " + entryName + ", " + entryState + ") VALUES (?,?,?)";            if (log.isDebugEnabled()) {                log.debug("Executing SQL statement: " + sql);            }            stmt = conn.prepareStatement(sql);            long id = getNextEntrySequence(conn);            stmt.setLong(1, id);            stmt.setString(2, workflowName);            stmt.setInt(3, WorkflowEntry.CREATED);            stmt.executeUpdate();            return new SimpleWorkflowEntry(id, workflowName, WorkflowEntry.CREATED);        } catch (SQLException e) {            throw new StoreException("Error creating new workflow instance", e);        } finally {            cleanup(conn, stmt, null);        }    }    public List findCurrentSteps(long entryId) throws StoreException {        Connection conn = null;        PreparedStatement stmt = null;        ResultSet rset = null;        PreparedStatement stmt2 = null;        try {            conn = getConnection();            String sql = "SELECT " + stepId + ", " + stepStepId + ", " + stepActionId + ", " + stepOwner + ", " + stepStartDate + ", " + stepDueDate + ", " + stepFinishDate + ", " + stepStatus + ", " + stepCaller + " FROM " + currentTable + " WHERE " + stepEntryId + " = ?";            String sql2 = "SELECT " + stepPreviousId + " FROM " + currentPrevTable + " WHERE " + stepId + " = ?";            if (log.isDebugEnabled()) {                log.debug("Executing SQL statement: " + sql);            }            stmt = conn.prepareStatement(sql);            if (log.isDebugEnabled()) {                log.debug("Executing SQL statement: " + sql2);            }            stmt2 = conn.prepareStatement(sql2);            stmt.setLong(1, entryId);            rset = stmt.executeQuery();            ArrayList currentSteps = new ArrayList();            while (rset.next()) {                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);                ArrayList prevIdsList = new ArrayList();                stmt2.setLong(1, id);                ResultSet rs = stmt2.executeQuery();                while (rs.next()) {                    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 (SQLException e) {            throw new StoreException("Unable to locate current steps for workflow instance #" + entryId, e);        } finally {            cleanup(null, stmt2, null);            cleanup(conn, stmt, rset);        }    }    public WorkflowEntry findEntry(long theEntryId) throws StoreException {        Connection conn = null;        PreparedStatement stmt = null;        ResultSet rset = null;        try {            conn = getConnection();            String sql = "SELECT " + entryName + ", " + entryState + " FROM " + entryTable + " WHERE " + entryId + " = ?";            if (log.isDebugEnabled()) {                log.debug("Executing SQL statement: " + sql);            }            stmt = conn.prepareStatement(sql);            stmt.setLong(1, theEntryId);            rset = stmt.executeQuery();            rset.next();            String workflowName = rset.getString(1);            int state = rset.getInt(2);            return new SimpleWorkflowEntry(theEntryId, workflowName, state);        } catch (SQLException e) {            throw new StoreException("Error finding workflow instance #" + entryId);        } finally {            cleanup(conn, stmt, rset);        }    }    public List findHistorySteps(long entryId) throws StoreException {        Connection conn = null;        PreparedStatement stmt = null;        PreparedStatement stmt2 = null;        ResultSet rset = null;        try {            conn = getConnection();            String sql = "SELECT " + stepId + ", " + stepStepId + ", " + stepActionId + ", " + stepOwner + ", " + stepStartDate + ", " + stepDueDate + ", " + stepFinishDate + ", " + stepStatus + ", " + stepCaller + " FROM " + historyTable + " WHERE " + stepEntryId + " = ? ORDER BY " + stepId + " DESC";            String sql2 = "SELECT " + stepPreviousId + " FROM " + historyPrevTable + " WHERE " + stepId + " = ?";            if (log.isDebugEnabled()) {                log.debug("Executing SQL statement: " + sql);            }            stmt = conn.prepareStatement(sql);            if (log.isDebugEnabled()) {                log.debug("Executing SQL statement: " + sql2);            }            stmt2 = conn.prepareStatement(sql2);            stmt.setLong(1, entryId);            rset = stmt.executeQuery();            ArrayList currentSteps = new ArrayList();            while (rset.next()) {                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);                ArrayList prevIdsList = new ArrayList();                stmt2.setLong(1, id);                ResultSet rs = stmt2.executeQuery();                while (rs.next()) {                    long prevId = rs.getLong(1);                    prevIdsList.add(new Long(prevId));                }                long[] prevIds = new long[prevIdsList.size()];                int i = 0;                for (Iterator iterator = prevIdsList.iterator();