wfareadao.java

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

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

import java.sql.*;
import java.util.*;
import org.apache.log4j.*;
import cn.com.iaspec.workflow.db.*;
import cn.com.iaspec.workflow.*;
import cn.com.iaspec.workflow.util.*;
import cn.com.iaspec.workflow.vo.db.*;

public class WfAreaDAO{
  private static Logger logger=Logger.getLogger(WfAreaDAO.class);
  public WfAreaDAO(){
  }

  /**
   * 查询LaWorkItemRel信息
   * @param info LaWorkItemRel
   * @throws SQLException
   * @return List
   */
  public List query(WfArea area)
      throws SQLException{
    Connection conn=null;
    Statement stm=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      String sql=" select * from wf_area ";
      StringBuffer sbWhere=getQueryString(area);
      sql=sql+sbWhere.toString();
      logger.info("......query sql:"+sql);
      ResultSet rs=stm.executeQuery(sql);
      List list=this.resultsetToVO(rs);
      return list;
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(stm,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
  }

  public List query(WfArea area,StringBuffer sbWhereSQL)
      throws SQLException{
    Connection conn=null;
    Statement stm=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      String sql=" select * from wf_area ";
      StringBuffer sbWhere=getQueryString(area);
      sql=sql+sbWhere.toString()+sbWhereSQL.toString();
      logger.info("......query sql:"+sql);
      ResultSet rs=stm.executeQuery(sql);
      List list=this.resultsetToVO(rs);
      return list;
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(stm,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
  }

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

  /**
   * 修改wf_area记录
   * @param area LaArea
   * @throws Exception
   */
  public void modify(WfArea area)
      throws Exception{
    logger.info("begin insert(LaArea area)...");
    if(area==null||area.getAreaId()==null||area.getAreaId().equals("")){
      return;
    }
    Connection conn=null;
    Statement stm=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      StringBuffer sbModify=this.getStrModify(area);
      if(sbModify!=null&&sbModify.length()>0){
        String sql=" update wf_area set "+sbModify.toString()+
            " where area_id='"+area.getAreaId()+"'";
        logger.info("......update sql:"+sql);
        stm.execute(sql);
      }
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(stm,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
  }

  private List resultsetToVO(ResultSet rs)
      throws SQLException{
    List list=new ArrayList();
    while(rs.next()){
      WfArea area=new WfArea();
      area.setAreaCode(rs.getString("area_code"));
      area.setAreaDesc(rs.getString("area_desc"));
      area.setAreaId(rs.getString("area_id"));
      area.setAreaName(rs.getString("area_name"));
      area.setAreaState(rs.getString("area_state"));
      area.setGrade(rs.getLong("grade"));
      area.setUpAreaId(rs.getString("up_area_id"));
      list.add(area);
    }
    return list;
  }

  /**
   * 取得查询条件
   * @param area LaArea
   * @return StringBuffer
   */
  private StringBuffer getQueryString(WfArea area){
    StringBuffer sb=new StringBuffer();
    if(area!=null){
      if(area.getAreaCode()!=null&&!area.getAreaCode().equals("")){
        sb.append(" area_code = '"+area.getAreaCode()+"' ");
        sb.append(" and ");
      }
      if(area.getAreaDesc()!=null&&!area.getAreaDesc().equals("")){
        sb.append(" area_desc = '"+area.getAreaDesc()+"' ");
        sb.append(" and ");
      }
      if(area.getAreaId()!=null&&!area.getAreaId().equals("")){
        sb.append(" area_id = '"+area.getAreaId()+"' ");
        sb.append(" and ");
      }

      if(area.getAreaName()!=null&&!area.getAreaName().equals("")){
        sb.append(" area_name = '"+area.getAreaName()+"' ");
        sb.append(" and ");
      }
      if(area.getAreaState()!=null&&!area.getAreaState().equals("")){
        sb.append(" area_state = '"+area.getAreaState()+"' ");
        sb.append(" and ");
      }
      if(area.getGrade()!=WorkflowConstant.LONG_INIT_VALUE){
        sb.append(" grade = "+area.getGrade()+" ");
        sb.append(" and ");
      }
      if(area.getUpAreaId()!=null&&!area.getUpAreaId().equals("")){
        sb.append(" up_area_id = '"+area.getUpAreaId()+"' ");
        sb.append(" and ");
      }
    }
    if(sb.length()>0){
      //增加查询条件关键字
      sb.insert(0," where ");
      //去除最后一个" AND "操作符
      sb.delete(sb.length()-5,sb.length());
    }
    return sb;
  }

  /**
   * 得到本表内所有字段名，以","分开
   * @throws Exception
   * @return String
   */
  private String getStrFields()
      throws Exception{
    return "area_code,area_desc,area_id,area_name,area_state,grade,up_area_id";
  }

  /**
   * 根据info取得insert语句
   * @param area LaArea
   * @throws Exception
   * @return StringBuffer
   */
  private StringBuffer getStrInsertValues(WfArea area,Connection connection)
      throws Exception{
    StringBuffer sbSQL=new StringBuffer();
    if(area.getAreaCode()!=null&&!area.getAreaCode().equals("")){
      sbSQL.append("'");
      sbSQL.append(area.getAreaCode());
      sbSQL.append("'");
      sbSQL.append(",");
    }
    else{
      sbSQL.append("null");
      sbSQL.append(",");
    }
    if(area.getAreaDesc()!=null&&!area.getAreaDesc().equals("")){
      sbSQL.append("'");
      sbSQL.append(area.getAreaDesc());
      sbSQL.append("'");
      sbSQL.append(",");
    }
    else{
      sbSQL.append("null");
      sbSQL.append(",");
    }
    String seqId=GetTableSequenceId.getTableId("wf_area");
    sbSQL.append(seqId);
    sbSQL.append(",");
    sbSQL.append("'");
    sbSQL.append(seqId);
    sbSQL.append("',");
    if(area.getAreaName()!=null&&!area.getAreaName().equals("")){
      sbSQL.append("'");
      sbSQL.append(area.getAreaName());
      sbSQL.append("'");
      sbSQL.append(",");
    }
    else{
      sbSQL.append("null");
      sbSQL.append(",");
    }
    if(area.getAreaState()!=null&&!area.getAreaState().equals("")){
      sbSQL.append("'");
      sbSQL.append(area.getAreaState());
      sbSQL.append("'");
      sbSQL.append(",");
    }
    else{
      sbSQL.append("null");
      sbSQL.append(",");
    }

    if(area.getGrade()!=WorkflowConstant.LONG_INIT_VALUE){
      sbSQL.append(area.getGrade());
      sbSQL.append(",");
    }
    else{
      sbSQL.append("null");
      sbSQL.append(",");
    }

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

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

  /**
   * 根据info取得insert语句
   * @param area LaArea
   * @throws Exception
   * @return StringBuffer
   */
  private StringBuffer getStrModify(WfArea area)
      throws Exception{
    StringBuffer sbSQL=new StringBuffer();
    if(area.getAreaCode()!=null&&!area.getAreaCode().equals("")){
      sbSQL.append(" area_code='");
      sbSQL.append(area.getAreaCode());
      sbSQL.append("'");
      sbSQL.append(",");
    }
    if(area.getAreaDesc()!=null&&!area.getAreaDesc().equals("")){
      sbSQL.append(" area_desc='");
      sbSQL.append(area.getAreaDesc());
      sbSQL.append("'");
      sbSQL.append(",");
    }

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

    if(area.getAreaName()!=null&&!area.getAreaName().equals("")){
      sbSQL.append(" area_name='");
      sbSQL.append(area.getAreaName());
      sbSQL.append("'");
      sbSQL.append(",");
    }

    if(area.getAreaState()!=null&&!area.getAreaState().equals("")){
      sbSQL.append(" area_state='");
      sbSQL.append(area.getAreaState());
      sbSQL.append("'");
      sbSQL.append(",");
    }

    if(area.getGrade()!=WorkflowConstant.LONG_INIT_VALUE){
      sbSQL.append(" grade=");
      sbSQL.append(area.getGrade());
      sbSQL.append("");
      sbSQL.append(",");
    }

    if(area.getUpAreaId()!=null&&!area.getUpAreaId().equals("")){
      sbSQL.append(" up_area_id='");
      sbSQL.append(area.getUpAreaId());
      sbSQL.append("'");
      sbSQL.append(",");
    }

    //删除最后一个逗号
    if(sbSQL!=null&&sbSQL.length()>0){
      sbSQL.delete(sbSQL.length()-1,sbSQL.length());
    }
    return sbSQL;
  }

  public int delete(WfArea area)
      throws SQLException{
    Connection conn=null;
    Statement stm=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      String sql=" delete from wf_area ";
      StringBuffer sbWhere=getQueryString(area);
      sql=sql+sbWhere.toString();
      logger.info("......delete sql:"+sql);
      return stm.executeUpdate(sql);
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(stm,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
  }

}