wforganizationdao.java

公司自己开发的工作流引擎

      if(sbWhere.length()<=0||sbUpdata.length()<=0){
        throw new Exception("更新数据为空！");
      }
      sql=sql+sbUpdata.toString()+sbWhere.toString();
      logger.info("......update sql:"+sql);
      stm.execute(sql);

      //更新组织扩充字段
      this.delExtFieldDate(orgId);
      this.saveExtFieldData(orgId,org.getExtFieldList());

    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(stm,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
  }

  /**
   * 往表wf_organization插入记录
   * @param org WfOrganization
   * @throws Exception
   */
  public void insert(WfOrganization org)
      throws Exception{
    logger.info("begin insert(WfOrganization org)...");
    Connection conn=null;
    Statement stm=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      StringBuffer sbInsertValue=this.getStrInsertValues(org);
      String sql=" insert into wf_organization ("+this.getStrFields()+") "+
          " values ("+sbInsertValue.toString()+")";
      logger.info("......insert sql:"+sql);
      stm.execute(sql);

      //保存扩充字段内容
      this.saveExtFieldData(org.getOrgId(),org.getExtFieldList());
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(stm,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
  }

  public List getUserOrgInfoByUserId(String userId)
      throws SQLException{
    {
      Connection conn=null;
      Statement stm=null;
      try{
        conn=WorkflowDBConnectionManager.getInstance().getConnection();
        stm=conn.createStatement();
        String sql=
            " select * from wf_organization,wf_area where wf_organization.area_id "+
            " = wf_area.area_id and org_id in (select distinct org_id from "+
            "wf_org_user_role where user_id='"+userId+"') ";

        logger.info("......query sql:"+sql);
        ResultSet rs=stm.executeQuery(sql);
        List list=resultsetToVO(rs);
        return list;
      }
      finally{
        WorkflowDBConnectionManager.getInstance().close(stm,false);
        WorkflowDBConnectionManager.getInstance().close(conn);
      }
    }
  }

  public List getAllParentOrgInfoByUserId(String userId)
      throws SQLException{
    {
      Connection conn=null;
      Statement stm=null;
      try{
        conn=WorkflowDBConnectionManager.getInstance().getConnection();
        stm=conn.createStatement();
        String sql= null;

        if("oracle".equalsIgnoreCase(dataBaseType)){
          sql = " select * from WF_ORGANIZATION where WF_ORGANIZATION.ORG_ID in " +
              "(Select Distinct org_id From wf_org_user_role " +
              "Where org_id != '0' And user_id = '" + userId + "' And Rownum < 2) " +
              "or org_id in(Select org_id From wf_organization Start With org_id = " +
              "(Select Distinct org_id From wf_org_user_role Where user_id = '" + userId + "'" +
              "And org_id != '0' And rownum <2) Connect By Prior up_org = org_id) ";
        }else{
          sql = " select * from WF_ORGANIZATION "+
             " where  WF_ORGANIZATION.ORG_ID in(select * from dbo.FUNC_GET_USER_CURR_ORG('"+userId+"')) " +
             " or  WF_ORGANIZATION.ORG_ID in(select * from dbo.FUNC_GET_USER_ALL_PARENT_ORG('"+userId+"')) ";
        }

        logger.info("......query sql:"+sql);
        ResultSet rs=stm.executeQuery(sql);
        List list=resultsetToVO(rs);
        return list;
      }
      finally{
        WorkflowDBConnectionManager.getInstance().close(stm,false);
        WorkflowDBConnectionManager.getInstance().close(conn);
      }
    }
  }

  public List getAllParentOrgInfoByOrgId(String orgId)
    throws SQLException{
  {
    Connection conn=null;
    Statement stm=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      String sql= null;
      if("oracle".equalsIgnoreCase(dataBaseType)){
        sql = "select * from wf_organization start with org_id='"+orgId+"'"+
          " connect by prior up_org=org_id";
      }else{
        sql = " select * from WF_ORGANIZATION "+
          " where ORG_ID in (select ORG_ID from dbo.FUNC_GET_ORG_ALL_PARENT_ORG('"+orgId+"'))";
      }

      logger.info("......query sql:"+sql);
      ResultSet rs=stm.executeQuery(sql);
      List list=resultsetToVO(rs);
      return list;
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(stm,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
  }
}

public List getAllChildOrgInfoByOrgId(String orgId)
  throws SQLException{
{
  Connection conn=null;
  Statement stm=null;
  try{
    conn=WorkflowDBConnectionManager.getInstance().getConnection();
    stm=conn.createStatement();
    String sql=
        " select * from WF_ORGANIZATION "+
        " where ORG_ID in (select ORG_ID from dbo.FUNC_GET_ORG_ALL_CHILD_ORG('"+orgId+"'))";
    logger.info("......query sql:"+sql);
    ResultSet rs=stm.executeQuery(sql);
    List list=resultsetToVO(rs);
    return list;
  }
  finally{
    WorkflowDBConnectionManager.getInstance().close(stm,false);
    WorkflowDBConnectionManager.getInstance().close(conn);
  }
}
}

  private List resultsetToVO(ResultSet rs)
      throws SQLException{
    List list=new ArrayList();
    while(rs.next()){
      WfOrganization org=new WfOrganization();
      org.setAreaId(rs.getString("area_id"));
      if(checkHasColumnName(rs.getMetaData(),"area_name")){
        org.setAreaName(rs.getString("area_name"));
      }
      org.setOrgAddress(rs.getString("org_address"));
      org.setOrgCode(rs.getString("org_code"));
      org.setOrgDesc(rs.getString("org_desc"));
      org.setOrgId(rs.getString("org_id"));
      org.setOrgLevel(rs.getLong("org_level"));
      org.setOrgName(rs.getString("org_name"));
      org.setOrgOrder(rs.getLong("org_order"));
      org.setOrgState(rs.getString("org_state"));
      org.setUpOrg(rs.getString("up_org"));
      if(checkHasColumnName(rs.getMetaData(),"HAS_CHILDREN")){
        if(rs.getString("HAS_CHILDREN")!=null&&
            rs.getString("HAS_CHILDREN").equals("1")){
          org.setHasChildren(true);
        }
      }
      if(checkHasColumnName(rs.getMetaData(),"HAS_USER")){
        if (rs.getString("HAS_USER") !=null && rs.getString("HAS_USER").equals("1")){
          org.setHasUser(true);
        }
      }
      list.add(org);
    }
    return list;
  }

  /**
   * 返回的结果集中是否存在指定的列名
   * @param metaData ResultSetMetaData
   * @param columnName String
   * @return boolean
   * @throws SQLException
   */
  private boolean checkHasColumnName(ResultSetMetaData metaData,
      String columnName)
      throws SQLException{
    boolean b=false;
    if(metaData!=null){
      for(int i=0;i<metaData.getColumnCount();i++){
        if(metaData.getColumnName(i+
            1).toUpperCase().equals(columnName.toUpperCase())){
          b=true;
          break;
        }
      }
    }
    return b;
  }

  /**
   * 得到本表内所有字段名，以","分开
   * @throws Exception
   * @return String
   */
  private String getStrFields()
      throws Exception{
    return "area_id,org_address,org_code,org_desc,org_id,org_level,org_name,org_order,org_state,up_org,creator";
  }

  private StringBuffer getQueryString(WfOrganization org){
    StringBuffer sb=new StringBuffer();
    if(org!=null){
      if(org.getAreaId()!=null&&!org.getAreaId().equals("")){
        sb.append(" wf_organization.area_id = '"+org.getAreaId()+"' ");
        sb.append(" and ");
      }
      if(org.getAreaName()!=null&&!org.getAreaName().equals("")){
        sb.append(" area_name = '"+org.getAreaName()+"' ");
        sb.append(" and ");
      }
      if(org.getOrgAddress()!=null&&!org.getOrgAddress().equals("")){
        sb.append(" org_address = '"+org.getOrgAddress()+"' ");
        sb.append(" and ");
      }
      if(org.getOrgCode()!=null&&!org.getOrgCode().equals("")){
        sb.append(" org_code = '"+org.getOrgCode()+"' ");
        sb.append(" and ");
      }
      if(org.getOrgDesc()!=null&&!org.getOrgDesc().equals("")){
        sb.append(" org_desc = '"+org.getOrgDesc()+"' ");
        sb.append(" and ");
      }
      if(org.getOrgId()!=null&&!org.getOrgId().equals("")){
        sb.append(" org_id = '"+org.getOrgId()+"' ");
        sb.append(" and ");
      }
      if(org.getOrgLevel()!=WorkflowConstant.LONG_INIT_VALUE){
        sb.append(" org_level = "+org.getOrgLevel()+" ");
        sb.append(" and ");
      }
      if(org.getOrgName()!=null&&!org.getOrgName().equals("")){
        sb.append(" org_name = '"+org.getOrgName()+"' ");
        sb.append(" and ");
      }
      if(org.getOrgOrder()!=WorkflowConstant.LONG_INIT_VALUE){
        sb.append(" org_order = "+org.getOrgOrder()+" ");
        sb.append(" and ");
      }
      if(org.getOrgState()!=null&&!org.getOrgState().equals("")){
        sb.append(" org_state = '"+org.getOrgState()+"' ");
        sb.append(" and ");
      }
      if(org.getUpOrg()!=null&&!org.getUpOrg().equals("")){
        sb.append(" up_org = '"+org.getUpOrg()+"' ");
        sb.append(" and ");
      }
    }
    //增加查询条件关键字
    sb.insert(0," where wf_organization.area_id = wf_area.area_id and ");
    if(sb.length()>0){
      //去除最后一个" AND "操作符
      sb.delete(sb.length()-5,sb.length());
    }

    return sb;
  }

  /**
   * 根据org取得update语句
   * @param org WfOrganization
   * @throws Exception
   * @return StringBuffer
   */
  private StringBuffer getStrUpdateValues(WfOrganization org)
      throws Exception{
    StringBuffer sbSQL=new StringBuffer();
    if(org.getAreaId()!=null&&!org.getAreaId().equals("")){
      sbSQL.append("area_id=");
      sbSQL.append("'");
      sbSQL.append(org.getAreaId());
      sbSQL.append("'");
      sbSQL.append(",");
    }

    if(org.getOrgAddress()!=null&&!org.getOrgAddress().equals("")){
      sbSQL.append("org_address=");
      sbSQL.append("'");
      sbSQL.append(org.getOrgAddress());
      sbSQL.append("'");
      sbSQL.append(",");
    }

    if(org.getOrgCode()!=null&&!org.getOrgCode().equals("")){
      sbSQL.append("org_code=");
      sbSQL.append("'");
      sbSQL.append(org.getOrgCode());
      sbSQL.append("'");
      sbSQL.append(",");
    }

    if(org.getOrgDesc()!=null&&!org.getOrgDesc().equals("")){
      sbSQL.append("org_desc=");
      sbSQL.append("'");
      sbSQL.append(org.getOrgDesc());
      sbSQL.append("'");
      sbSQL.append(",");
    }

    if(org.getOrgLevel()!=WorkflowConstant.LONG_INIT_VALUE){
      sbSQL.append("org_level=");
      sbSQL.append(org.getOrgLevel());
      sbSQL.append(",");
    }

    if(org.getOrgName()!=null&&!org.getOrgName().equals("")){
      sbSQL.append("org_name=");
      sbSQL.append("'");
      sbSQL.append(org.getOrgName());
      sbSQL.append("'");
      sbSQL.append(",");
    }

    if(org.getOrgOrder()!=WorkflowConstant.LONG_INIT_VALUE){
      sbSQL.append("org_order=");
      sbSQL.append(org.getOrgOrder());
      sbSQL.append(",");
    }

    if(org.getOrgState()!=null&&!org.getOrgState().equals("")){
      sbSQL.append("org_state=");
      sbSQL.append("'");
      sbSQL.append(org.getOrgState());
      sbSQL.append("'");
      sbSQL.append(",");
    }

    if(org.getUpOrg()!=null&&!org.getUpOrg().equals("")){
      sbSQL.append("up_org=");
      sbSQL.append("'");
      sbSQL.append(org.getUpOrg());
      sbSQL.append("'");
      sbSQL.append(",");
    }

    //删除最后一个逗号
    sbSQL.delete(sbSQL.length()-1,sbSQL.length());
    return sbSQL;
  }

  /**
   * 根据org取得insert语句
   * @param org WfOrganization
   * @throws Exception
   * @return StringBuffer
   */
  private StringBuffer getStrInsertValues(WfOrganization org)
      throws Exception{
    StringBuffer sbSQL=new StringBuffer();

    if(org.getAreaId()!=null&&!org.getAreaId().equals("")){
      sbSQL.append("'");
      sbSQL.append(org.getAreaId());
      sbSQL.append("'");
      sbSQL.append(",");
    }
    else{
      sbSQL.append("null");
      sbSQL.append(",");