sqlworkitemcoder.java

一个工作流设计及定义的系统,可以直接与数据库结合进行系统工作流程的定义及应用.

        final FlowExpressionId fei = new FlowExpressionId();

        int i = 3;

        fei.setEngineId(rs.getString(i++));
        //fei.setInitialEngineId(rs.getString(i++));
        fei.setInitialEngineId( fei.getEngineId() );
        fei.setWorkflowDefinitionUrl(rs.getString(i++));
        fei.setWorkflowDefinitionName(rs.getString(i++));
        fei.setWorkflowDefinitionRevision(rs.getString(i++));
        fei.setWorkflowInstanceId(rs.getString(i++));
        fei.setExpressionName(rs.getString(i++));
        //fei.setExpressionId(rs.getInt(i++));
        fei.setExpressionId(rs.getString(i++));

        log.debug("extractFlowExpressionId() extracted "+fei);

        return fei;
    }

    protected static FlowExpressionId determineFlowExpressionId
        (final java.sql.Connection con,
         final long workitemId)
    throws
        java.sql.SQLException, CodingException
    {
        java.util.List feis = determineFlowExpressionIds
            (con, workitemId, -1, -1);

        if (feis == null || feis.size() < 1) return null;

        return (FlowExpressionId)feis.get(0);
    }

    protected static java.util.List determineFlowExpressionIds
        (final java.sql.Connection con,
         final long workitemId,
         final long workflowInstanceId,
         final int limit)
    throws
        java.sql.SQLException, CodingException
    {
        //
        // build query

        final StringBuffer sb = new StringBuffer();
        sb.append("stack_index = -1");
        if (workitemId > -1)
        {
            sb.append(" AND ");
            sb.append(WID); sb.append(" = '"); sb.append(workitemId); sb.append("'");
        }
        if (workflowInstanceId > -1)
        {
            sb.append(" AND ");
            sb.append(WFID); sb.append(" = "); sb.append(workflowInstanceId);
        }
        final String sWhere = sb.toString();

        final String sQuery = SqlUtils.buildQueryString
            (FEI_TABLE, FEI_COLS, sWhere);

        log.debug("determineFlowExpressionIds() query :\n"+sQuery);

        //
        // do query

        java.sql.Statement st = null;
        java.sql.ResultSet rs = null;
        try
        {
            st = con.createStatement();
            rs = st.executeQuery(sQuery);

            int size = limit;
            if (size < 1) size = 7;
                // no x?0:1 notation...

            java.util.List result = new java.util.ArrayList(size);

            while (rs.next())
            {
                result.add(extractFlowExpressionId(rs));
                if (limit > 0 && result.size() >= limit) break;
            }

            log.debug
                ("determineFlowExpressionIds() found "+result.size()+
                 " results");

            return result;
        }
        catch (java.sql.SQLException se)
        {
                // XXX pass in DataSource rather than connection
                // ds.logSQLException( "determineFlowExpressionIds", log, se );
                throw new CodingException( "Could not determineFlowExpressionIds() for workitemId " + workitemId );
        }
        finally
        {
            SqlUtils.closeStatement(st, rs);
        }
    }

    protected static long determineWorkitemId
        (final java.sql.Connection con, final FlowExpressionId fei)
    throws
        java.sql.SQLException, CodingException
    {
        final StringBuffer sb = new StringBuffer();

        sb.append("SELECT ");
        sb.append(WID);
        sb.append(" FROM ");
        sb.append(FEI_TABLE);
        sb.append(" WHERE ");
        sb.append(" stack_index = -1");
        sb.append(" AND");
        sb.append(" engine_id = ");
        sb.append(SqlUtils.prepareString(fei.getEngineId()));
        sb.append(" AND");
        sb.append(" initial_engine_id = ");
        sb.append(SqlUtils.prepareString(fei.getInitialEngineId()));
        sb.append(" AND");
        sb.append(" wfd_url = ");
        sb.append(SqlUtils.prepareString(fei.getWorkflowDefinitionUrl()));
        sb.append(" AND");
        sb.append(" wfd_name = ");
        sb.append(SqlUtils.prepareString(fei.getWorkflowDefinitionName()));
        sb.append(" AND");
        sb.append(" wfd_revision = ");
        sb.append(SqlUtils.prepareString(fei.getWorkflowDefinitionRevision()));
        sb.append(" AND");
        sb.append(" wf_instance_id = ");
        sb.append( SqlUtils.prepareString(fei.getWorkflowInstanceId()));
        sb.append(" AND");
        sb.append(" expression_name = ");
        sb.append(SqlUtils.prepareString(fei.getExpressionName()));
        sb.append(" AND");
        sb.append(" expression_id = ");
        sb.append(SqlUtils.prepareString(fei.getExpressionId()));
        // sb.append(";");

        String sQuery = sb.toString();
        log.debug( "determineWorkitemId() query: " + sQuery );

        //
        // do query

        java.sql.Statement st = null;
        java.sql.ResultSet rs = null;
        try
        {
            st = con.createStatement();
            rs = st.executeQuery(sQuery);

            if ( ! rs.next())
            {
                throw new CodingException
                    ("No workitem stored for "+fei);
            }

            return rs.getLong(1);
        }
        catch (java.sql.SQLException se)
        {
                // XXX pass in DataSource rather than connection
                // ds.logSQLException( "determineWorkitemId", log, se );
                throw new CodingException( "Could not determineWorkitemId() for wf_instance_id " + fei.getWorkflowInstanceId() );
        }
        finally
        {
            SqlUtils.closeStatement(st, rs);
        }
    }

    public static void removeWorkItem
        (final FlowExpressionId fei,
         final ApplicationContext context,
         final java.util.Map params)
    throws
        java.sql.SQLException, CodingException
    {
        OwfeDataSource ds = null;
        java.sql.Statement st = null;
        try
        {
            ds = lookupDataSource(context, params);

            final long workitemId =
                determineWorkitemId(ds.getConnection(), fei);

            log.debug("removeWorkItem() wid is "+workitemId);

            st = ds.getConnection().createStatement();

            final String sWhere = " WHERE "+WID+" = '"+workitemId+"'";
            log.debug("Where clause for deletion of workitem is: " + sWhere);

            st.addBatch("DELETE FROM "+WORKITEM_TABLE+sWhere);
            st.addBatch("DELETE FROM "+FEI_TABLE+sWhere);
            st.addBatch("DELETE FROM "+FILTER_TABLE+sWhere);
            st.addBatch("DELETE FROM "+FILTER_ENTRY_TABLE+sWhere);

            st.addBatch("DELETE FROM "+ATTRIBUTE_TABLE+sWhere);

            st.executeBatch();
        }
        catch (java.sql.SQLException se)
        {
                ds.logSQLException( "removeWorkItem", log, se );
        }
        finally
        {
            SqlUtils.closeStatement(st);
            if (ds != null) ds.releaseConnection();
        }
    }

    public static int countWorkItems (final OwfeDataSource ds)
        throws java.sql.SQLException, CodingException
    {
        java.sql.Statement st = null;
        java.sql.ResultSet rs = null;
        try
        {
            st = ds.getConnection().createStatement();

            rs = st.executeQuery
                ("SELECT count("+WID+") FROM "+WORKITEM_TABLE);

            if ( ! rs.next()) return -1;

            return rs.getInt(1);
        }
        catch (java.sql.SQLException se)
        {
                ds.logSQLException( "countWorkItems", log, se );
                throw new CodingException( "Could not countWorkItems()" );
        }
        finally
        {
            SqlUtils.closeStatement(st, rs);
        }
    }

    public static java.util.List findFlowInstance
        (final OwfeDataSource ds, final long workflowInstanceId)
    throws
        java.sql.SQLException, CodingException
    {
        return determineFlowExpressionIds
            (ds.getConnection(), -1, workflowInstanceId, -1);
    }

    protected static OwfeDataSource lookupDataSource
        (final ApplicationContext context,
         final java.util.Map params)
    throws
        CodingException
    {
        try
        {
            return SqlUtils.lookupDataSource(context, params);
        }
        catch (ServiceException se)
        {
            throw new CodingException
                ("Did not find dataSource", se);
        }
    }

    public static java.util.List listActionEntries (final OwfeDataSource ds)
        throws CodingException
    {
        java.sql.Statement st = null;
        java.sql.ResultSet rs = null;
        try
        {
            st = ds.getConnection().createStatement();

            rs = st.executeQuery
                ("SELECT "+WID+
                 ", action, arg FROM action where msg_err is null");

            final java.util.List result = new java.util.ArrayList();

            while (rs.next())
                result.add(new ActionEntry(rs));

            return result;
        }
        catch (final java.sql.SQLException se)
        {
            ds.logSQLException("listActionEntries()", log, se);
            throw new CodingException("listActionEntries() failed.", se);
        }
        catch (final Throwable t)
        {
            throw new CodingException("listActionEntries() failed", t);
        }
        finally
        {
            SqlUtils.closeStatement(st, rs);
            ds.releaseConnection();
        }
    }

    public static void removeActionEntry
        (final OwfeDataSource ds, final ActionEntry ae)
    throws
        java.sql.SQLException, CodingException
    {
        java.sql.Statement st = null;
        try
        {
            st = ds.getConnection().createStatement();
            st.execute("DELETE FROM action WHERE "+WID+" = "+ SqlUtils.prepareString( java.lang.Long.toString( ae.workitemId ) ) +" and action=" + SqlUtils.prepareString(ae.action) );

            //
            // for the moment, this method remove action entries based
            // on the workitem id, this may change...
            //
        }
        catch (java.sql.SQLException se)
        {
            ds.logSQLException( "removeActionEntry", log, se );
            throw new CodingException( "Could not removeActionEntry() for workitemId " + ae.workitemId );
        }
        finally
        {
            SqlUtils.closeStatement(st);
            ds.releaseConnection();
        }
    }

    public static void errorActionEntry
        (final OwfeDataSource ds, final ActionEntry ae, final String error)
    {
        java.sql.Statement st = null;
        try
        {
            st = ds.getConnection().createStatement();
            String sql = "UPDATE action set msg_err=" + SqlUtils.prepareString( error ) + " where " + WID
                + "=" + SqlUtils.prepareString( java.lang.Long.toString( ae.workitemId ) )
                + " and action=" + SqlUtils.prepareString( ae.action )
                ;
            log.debug( "errorActionEntry sql: " + sql );
            st.execute( sql );
        }
        catch (java.sql.SQLException se)
        {
            ds.logSQLException( "errorActionEntry", log, se );
            // throw new CodingException( "Could not errorActionEntry() for workitemId " + ae.workitemId );
        }
        finally
        {
            SqlUtils.closeStatement(st);
            ds.releaseConnection();
        }
    }

    //
    // AttributeRecord stuff

    static class AttributeRecord
    {
        public long id = -1;
        public long parentId = -1;
        public String type = null;
        public String value = null;

        public AttributeRecord (final java.sql.ResultSet rs)
            throws java.sql.SQLException
        {
            int i = 2;

            this.id = rs.getLong(i++);
            this.parentId = rs.getLong(i++);
            this.type = rs.getString(i++);
            this.value = rs.getString(i++);
        }
    }

    public static void removeRecord
        (final java.util.List attributeRecordList, final Long id)
    {
        final AttributeRecord ar = lookupRecord(attributeRecordList, id);
        attributeRecordList.remove(ar);
    }

    public static AttributeRecord lookupRecord
        (final java.util.List attributeRecordList, final Long id)
    {
        final java.util.Iterator it = attributeRecordList.iterator();
        while (it.hasNext())
        {
            final AttributeRecord record = (AttributeRecord)it.next();
            if (record.id == id.longValue()) return record;
        }

        return null;
    }

    public static java.util.List lookupRecords
        (final java.util.List attributeRecordList, final long parentId)
    {
        final java.util.List result =
            new java.util.ArrayList(attributeRecordList.size());

        final java.util.Iterator it = attributeRecordList.iterator();
        while (it.hasNext())
        {
            final AttributeRecord record = (AttributeRecord)it.next();
            if (record.parentId == parentId) result.add(record);
        }

        return result;
    }

}