jdbcworkflowstore.java

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

        closeConnWhenDone = true;

        return ds.getConnection();
    }

    protected long getNextEntrySequence(Connection c) throws SQLException {
        if (log.isDebugEnabled()) {
            log.debug("Executing SQL statement: " + entrySequence);
        }

        PreparedStatement stmt = null;
        ResultSet rset = null;

        try {
            stmt = c.prepareStatement(entrySequence);
            rset = stmt.executeQuery();
            rset.next();

            long id = rset.getLong(1);

            return id;
        } finally {
            cleanup(null, stmt, rset);
        }
    }

    protected long getNextStepSequence(Connection c) throws SQLException {
        if (log.isDebugEnabled()) {
            log.debug("Executing SQL statement: " + stepSequence);
        }

        PreparedStatement stmt = null;
        ResultSet rset = null;

        try {
            stmt = c.prepareStatement(stepSequence);
            rset = stmt.executeQuery();
            rset.next();

            long id = rset.getLong(1);

            return id;
        } finally {
            cleanup(null, stmt, rset);
        }
    }

    protected void addPreviousSteps(Connection conn, long id, long[] previousIds) throws SQLException {
        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);

                PreparedStatement stmt = conn.prepareStatement(sql);

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

                cleanup(null, stmt, null);
            }
        }
    }

    protected void cleanup(Connection connection, Statement statement, ResultSet result) {
        if (result != null) {
            try {
                result.close();
            } catch (SQLException ex) {
                log.error("Error closing resultset", ex);
            }
        }

        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException ex) {
                log.error("Error closing statement", ex);
            }
        }

        if ((connection != null) && closeConnWhenDone) {
            try {
                connection.close();
            } catch (SQLException ex) {
                log.error("Error closing connection", ex);
            }
        }
    }

    protected long createCurrentStep(Connection conn, long entryId, int wfStepId, String owner, Date startDate, Date dueDate, String status) throws SQLException {
        String sql = "INSERT INTO " + currentTable + " (" + stepId + ',' + stepEntryId + ", " + stepStepId + ", " + stepActionId + ", " + stepOwner + ", " + stepStartDate + ", " + stepDueDate + ", " + stepFinishDate + ", " + stepStatus + ", " + stepCaller + " ) VALUES (?, ?, ?, null, ?, ?, ?, null, ?, null)";

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

        PreparedStatement stmt = conn.prepareStatement(sql);

        long id = getNextStepSequence(conn);
        stmt.setLong(1, id);
        stmt.setLong(2, entryId);
        stmt.setInt(3, wfStepId);
        stmt.setString(4, owner);
        stmt.setTimestamp(5, new Timestamp(startDate.getTime()));

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

        stmt.setString(7, status);
        stmt.executeUpdate();
        cleanup(null, stmt, null);

        return id;
    }

    ////////////METHOD #3 OF 3 //////////////////
    ////////// ...gur;  ////////////////////
    //kardes
    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(((java.util.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(((java.util.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 ");
                }