wforguserroledao.java

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

package cn.com.iaspec.workflow.organize.dao;

import org.apache.log4j.Logger;
import cn.com.iaspec.workflow.db.WorkflowDBConnectionManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.List;
import java.sql.Connection;
import cn.com.iaspec.workflow.vo.db.WfOrgUserRole;
import java.util.ArrayList;

/**
 * <p>Title: </p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2005</p>
 *
 * <p>Company: IASPEC Technologies</p>
 *
 * @author xiesonglin
 * @version 1.0
 */
public class WfOrgUserRoleDAO{
  private static Logger logger=Logger.getLogger(WfOrgUserRoleDAO.class);
  public WfOrgUserRoleDAO(){

  }

  public List query(WfOrgUserRole rel)
      throws SQLException{
    Connection conn=null;
    Statement stm=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      String sql=" select * from wf_org_user_role ";
      StringBuffer sbWhere=getQueryString(rel);
      sql=sql+sbWhere.toString();
      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);
    }
  }

  /**
   * 往表wf_user插入记录
   * @param user WfUser
   * @throws Exception
   */
  public void insert(WfOrgUserRole rel)
      throws Exception{
    logger.info("begin insert(WfOrgUserRole rel)...");
    Connection conn=null;
    Statement stm=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      StringBuffer sbInsertValue=this.getStrInsertValues(rel);
      String sql=" insert into wf_org_user_role ("+this.getStrFields()+") "+
          " values ("+sbInsertValue.toString()+")";
      logger.info("......insert sql:"+sql);
      stm.execute(sql);
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(stm,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
  }

  /**
   * 将指定用户的关联信息删除
   * @param user WfUser
   * @throws Exception
   */
  public void deleteUserRel(String userId)
      throws Exception{
    logger.info("begin deleteUserRel...");
    Connection conn=null;
    Statement stm=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      String sql=" delete from wf_org_user_role where user_id ='"+userId+"' ";
      logger.info("......deleteUserRel sql:"+sql);
      stm.execute(sql);
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(stm,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
  }

  /**
   * 将指定用户的关联信息删除
   * @param user WfUser
   * @throws Exception
   */
  public void deleteUserFromOrg(String userId,String orgId)
      throws Exception{
    logger.info("begin deleteUserFromOrg...");
    Connection conn=null;
    Statement stm=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      String sql=" delete from wf_org_user_role where user_id ='"+userId+
          "' and org_id='"+orgId+"' ";
      logger.info("......deleteUserRel sql:"+sql);
      stm.execute(sql);
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(stm,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
  }

  /**
   * 往表wf_user插入记录,
   * @param relList 由WfUser组成的list
   * @throws Exception
   */
  public void insert(List relList)
      throws Exception{
    logger.info("begin insert(List relList)...");
    if(relList!=null&&relList.size()>0){
      for(int i=0;i<relList.size();i++){
        WfOrgUserRole rel=(WfOrgUserRole)relList.get(i);
        if(!checkExists(rel)){
          insert(rel);
        }
      }
    }
  }

  /**
   * 检查是否已经存在记录
   * @param rel WfOrgUserRole
   * @return boolean
   */
  public boolean checkExists(WfOrgUserRole rel)
      throws Exception{
    logger.info("begin checkExists...");
    boolean b=false;
    if(rel!=null){
      List list=query(rel);
      if(list!=null&&list.size()>0){
        b=true;
      }
    }
    return b;
  }

  /**
   * 更新wf_org_user_role
   * @param newData WfOrgUserRole
   * @param oldData WfOrgUserRole
   * @throws SQLException
   */
  public void update(WfOrgUserRole newData,WfOrgUserRole oldData)
      throws SQLException{
    Connection conn=null;
    Statement stm=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      String sql=" update wf_org_user_role set ";
      StringBuffer sbWhere=getQueryString(oldData);
      StringBuffer sbUpdata=getStrUpdateValues(newData);
      if(sbWhere.length()<=0||sbUpdata.length()<=0){
        throw new SQLException("更新数据为空！");
      }
      sql=sql+sbUpdata.toString()+sbWhere.toString();
      logger.info("......query sql:"+sql);
      stm.execute(sql);
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(stm,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
  }

  private StringBuffer getQueryString(WfOrgUserRole rel){
    StringBuffer sb=new StringBuffer();
    if(rel!=null){
      if(rel.getOrgId()!=null&&!rel.getOrgId().equals("")){
        sb.append(" org_id = '"+rel.getOrgId()+"' ");
        sb.append(" and ");
      }
      if(rel.getRoleId()!=null&&!rel.getRoleId().equals("")){
        sb.append(" role_id = '"+rel.getRoleId()+"' ");
        sb.append(" and ");
      }
      if(rel.getUserId()!=null&&!rel.getUserId().equals("")){
        sb.append(" user_id = '"+rel.getUserId()+"' ");
        sb.append(" and ");
      }
    }
    if(sb.length()>0){
      //增加查询条件关键字
      sb.insert(0," where ");
      //去除最后一个" AND "操作符
      sb.delete(sb.length()-5,sb.length());
    }
    return sb;
  }

  /**
   * 根据user取得insert语句
   * @param user WfUser
   * @throws Exception
   * @return StringBuffer
   */
  private StringBuffer getStrInsertValues(WfOrgUserRole rel)
      throws Exception{
    StringBuffer sbSQL=new StringBuffer();
    if(rel.getOrgId()!=null&&!rel.getOrgId().equals("")){
      sbSQL.append("'");
      sbSQL.append(rel.getOrgId());
      sbSQL.append("'");
      sbSQL.append(",");
    }
    else{
      sbSQL.append("null");
      sbSQL.append(",");
    }

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

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

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

  /**
   * 根据user取得update语句
   * @param user WfUser
   * @throws Exception
   * @return StringBuffer
   */
  private StringBuffer getStrUpdateValues(WfOrgUserRole rel){
    StringBuffer sbSQL=new StringBuffer();
    if(rel.getOrgId()!=null&&!rel.getOrgId().equals("")){
      sbSQL.append("org_id=");
      sbSQL.append("'");
      sbSQL.append(rel.getOrgId());
      sbSQL.append("'");
      sbSQL.append(",");
    }
    if(rel.getRoleId()!=null&&!rel.getRoleId().equals("")){
      sbSQL.append("role_id=");
      sbSQL.append("'");
      sbSQL.append(rel.getRoleId());
      sbSQL.append("'");
      sbSQL.append(",");
    }
    if(rel.getUserId()!=null&&!rel.getUserId().equals("")){
      sbSQL.append("user_id=");
      sbSQL.append("'");
      sbSQL.append(rel.getUserId());
      sbSQL.append("'");
      sbSQL.append(",");
    }
    //删除最后一个逗号
    sbSQL.delete(sbSQL.length()-1,sbSQL.length());
    return sbSQL;
  }

  private List resultsetToVO(ResultSet rs)
      throws SQLException{
    List list=new ArrayList();
    while(rs.next()){
      WfOrgUserRole rel=new WfOrgUserRole();
      rel.setOrgId(rs.getString("org_id"));
      rel.setRoleId(rs.getString("role_id"));
      rel.setUserId(rs.getString("user_id"));
      list.add(rel);
    }
    return list;
  }

  /**
   * 得到本表内所有字段名，以","分开
   * @throws Exception
   * @return String
   */
  private String getStrFields()
      throws Exception{
    return "org_id,role_id,user_id";
  }

  /**
   * 将指定用户的关联信息删除
   * @param user WfUser
   * @throws Exception
   */
  public void deleteUserFromRole(String userId,String roleId)
      throws Exception{
    logger.info("begin deleteUserFromRole...");
    Connection conn=null;
    Statement stm=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      String sql=" delete from wf_org_user_role where user_id ='"+userId+
          "' and role_id='"+roleId+"' ";
      logger.info("......deleteUserFromRole sql:"+sql);
      stm.execute(sql);
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(stm,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
  }
}