jdbcworkflowstore.java

一个很好实用的工作流OSWORKFLOW开发例子.有着非常优秀的灵活性.

                        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 history steps for workflow instance #" + entryId, e);
        } finally {
            cleanup(null, stmt2, null);
            cleanup(conn, stmt, rset);
        }
    }

    public void init(Map props) throws StoreException {
        entrySequence = getInitProperty(props, "entry.sequence", "SELECT nextVal('seq_os_wfentry')");
        stepSequence = getInitProperty(props, "step.sequence", "SELECT nextVal('seq_os_currentsteps')");
        entryTable = getInitProperty(props, "entry.table", "OS_WFENTRY");
        entryId = getInitProperty(props, "entry.id", "ID");
        entryName = getInitProperty(props, "entry.name", "NAME");
        entryState = getInitProperty(props, "entry.state", "STATE");
        historyTable = getInitProperty(props, "history.table", "OS_HISTORYSTEP");
        currentTable = getInitProperty(props, "current.table", "OS_CURRENTSTEP");
        currentPrevTable = getInitProperty(props, "currentPrev.table", "OS_CURRENTSTEP_PREV");
        historyPrevTable = getInitProperty(props, "historyPrev.table", "OS_HISTORYSTEP_PREV");
        stepId = getInitProperty(props, "step.id", "ID");
        stepEntryId = getInitProperty(props, "step.entryId", "ENTRY_ID");
        stepStepId = getInitProperty(props, "step.stepId", "STEP_ID");
        stepActionId = getInitProperty(props, "step.actionId", "ACTION_ID");
        stepOwner = getInitProperty(props, "step.owner", "OWNER");
        stepCaller = getInitProperty(props, "step.caller", "CALLER");
        stepStartDate = getInitProperty(props, "step.startDate", "START_DATE");
        stepFinishDate = getInitProperty(props, "step.finishDate", "FINISH_DATE");
        stepDueDate = getInitProperty(props, "step.dueDate", "DUE_DATE");
        stepStatus = getInitProperty(props, "step.status", "STATUS");
        stepPreviousId = getInitProperty(props, "step.previousId", "PREVIOUS_ID");

        String jndi = (String) props.get("datasource");

        if (jndi != null) {
            try {
                ds = (DataSource) lookup(jndi);

                if (ds == null) {
                    ds = (DataSource) new javax.naming.InitialContext().lookup(jndi);
                }
            } catch (Exception e) {
                throw new StoreException("Error looking up DataSource at " + jndi, e);
            }
        }
    }

    public Step markFinished(Step step, int actionId, Date finishDate, String status, String caller) throws StoreException {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = getConnection();

            String sql = "UPDATE " + currentTable + " SET " + stepStatus + " = ?, " + stepActionId + " = ?, " + stepFinishDate + " = ?, " + stepCaller + " = ? WHERE " + stepId + " = ?";

            if (log.isDebugEnabled()) {
                log.debug("Executing SQL statement: " + sql);
            }

            stmt = conn.prepareStatement(sql);
            stmt.setString(1, status);
            stmt.setInt(2, actionId);
            stmt.setTimestamp(3, new Timestamp(finishDate.getTime()));
            stmt.setString(4, caller);
            stmt.setLong(5, step.getId());
            stmt.executeUpdate();

            SimpleStep theStep = (SimpleStep) step;
            theStep.setActionId(actionId);
            theStep.setFinishDate(finishDate);
            theStep.setStatus(status);
            theStep.setCaller(caller);

            return theStep;
        } catch (SQLException e) {
            throw new StoreException("Unable to mark step finished for #" + step.getEntryId(), e);
        } finally {
            cleanup(conn, stmt, null);
        }
    }

    public void moveToHistory(Step step) throws StoreException {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = getConnection();

            String sql = "INSERT INTO " + historyTable + " (" + stepId + ',' + stepEntryId + ", " + stepStepId + ", " + stepActionId + ", " + stepOwner + ", " + stepStartDate + ", " + stepFinishDate + ", " + stepStatus + ", " + stepCaller + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

            if (log.isDebugEnabled()) {
                log.debug("Executing SQL statement: " + sql);
            }

            stmt = conn.prepareStatement(sql);
            stmt.setLong(1, step.getId());
            stmt.setLong(2, step.getEntryId());
            stmt.setInt(3, step.getStepId());
            stmt.setInt(4, step.getActionId());
            stmt.setString(5, step.getOwner());
            stmt.setTimestamp(6, new Timestamp(step.getStartDate().getTime()));

            if (step.getFinishDate() != null) {
                stmt.setTimestamp(7, new Timestamp(step.getFinishDate().getTime()));
            } else {
                stmt.setNull(7, Types.TIMESTAMP);
            }

            stmt.setString(8, step.getStatus());
            stmt.setString(9, step.getCaller());
            stmt.executeUpdate();

            long[] previousIds = step.getPreviousStepIds();

            if ((previousIds != null) && (previousIds.length > 0)) {
                sql = "INSERT INTO " + historyPrevTable + " (" + stepId + ", " + stepPreviousId + ") VALUES (?, ?)";
                log.debug("Executing SQL statement: " + sql);
                cleanup(null, stmt, null);
                stmt = conn.prepareStatement(sql);

                for (int i = 0; i < previousIds.length; i++) {
                    long previousId = previousIds[i];
                    stmt.setLong(1, step.getId());
                    stmt.setLong(2, previousId);
                    stmt.executeUpdate();
                }
            }

            sql = "DELETE FROM " + currentPrevTable + " WHERE " + stepId + " = ?";

            if (log.isDebugEnabled()) {
                log.debug("Executing SQL statement: " + sql);
            }

            cleanup(null, stmt, null);
            stmt = conn.prepareStatement(sql);
            stmt.setLong(1, step.getId());
            stmt.executeUpdate();

            sql = "DELETE FROM " + currentTable + " WHERE " + stepId + " = ?";

            if (log.isDebugEnabled()) {
                log.debug("Executing SQL statement: " + sql);
            }

            cleanup(null, stmt, null);
            stmt = conn.prepareStatement(sql);
            stmt.setLong(1, step.getId());
            stmt.executeUpdate();
        } catch (SQLException e) {
            throw new StoreException("Unable to move current step to history step for #" + step.getEntryId(), e);
        } finally {
            cleanup(conn, stmt, null);
        }
    }

    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);
        }
    }

    public List query(WorkflowQuery query) throws StoreException {
        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);
        }

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sel);

            while (rs.next()) {
                // get entryIds and add to results list
                Long id = new Long(rs.getLong(stepEntryId));
                results.add(id);
            }
        } catch (SQLException ex) {
            throw new StoreException("SQL Exception in query: " + ex.getMessage());
        } finally {
            cleanup(conn, stmt, rs);
        }

        return results;
    }

    protected Connection getConnection() throws SQLException {