wfpublicprojectinfodao.java

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

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

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

public class WfPublicProjectInfoDAO{
  SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
  String dataBaseType=WorkflowDBConnectionManager.getInstance().getDataBaseType();
  public WfPublicProjectInfoDAO(){

  }

  private static Logger logger=Logger.getLogger(WfPublicProjectInfoDAO.class);

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

  /**
   * 往表wf_public_projct_info插入记录
   * @param info LaProcessCaseInfo
   * @throws Exception
   */
  public void insert(WfPublicProjectInfo info)
      throws Exception{
    logger.info("begin insert(WfPublicProjectInfo info)...");
    Connection conn=null;
    Statement stm=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      StringBuffer sbInsertValue=this.getStrInsertValues(info);
      String sql=" insert into wf_public_project_info ("+this.getStrFields()+
          ") "+" values ("+sbInsertValue.toString()+")";
      logger.info("......insert 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()){
      WfPublicProjectInfo info=new WfPublicProjectInfo();
      info.setPrjBussName(rs.getString("prj_buss_name"));
      info.setPrjBussType(rs.getString("prj_buss_type"));
      info.setPrjCreateDate(rs.getDate("prj_create_date"));
      info.setPrjCreateUserId(rs.getString("prj_create_user_id"));
      info.setPrjFinishedDate(rs.getDate("prj_finished_date"));
      info.setPrjState(rs.getString("prj_state"));
      info.setPrjTitle(rs.getString("prj_title"));
      info.setProjectId(rs.getLong("project_id"));
      list.add(info);
    }
    return list;
  }

  /**
   * 取得查询条件
   * @param info WfPublicProjectInfo
   * @return StringBuffer
   */
  private StringBuffer getQueryString(WfPublicProjectInfo info){
    StringBuffer sb=new StringBuffer();
    if(info!=null){
      if(info.getPrjBussName()!=null&&!info.getPrjBussName().equals("")){
        sb.append(" prj_buss_name = '"+info.getPrjBussName()+"' ");
        sb.append(" and ");
      }
      if(info.getPrjBussType()!=null&&!info.getPrjBussType().equals("")){
        sb.append(" prj_buss_type = '"+info.getPrjBussType()+"' ");
        sb.append(" and ");
      }
      if(dataBaseType.equals("ORACLE")){
        if(info.getPrjCreateDate()!=null&&!info.getPrjCreateDate().equals("")){
          sb.append(" prj_create_date = to_date('"+
              sdf.format(info.getPrjCreateDate()));
          sb.append("','yyyy-mm-dd hh24:mi:ss') and ");
        }
        if(info.getPrjFinishedDate()!=null&&
            !info.getPrjFinishedDate().equals("")){
          sb.append(" prj_finished_date = to_date('"+
              sdf.format(info.getPrjFinishedDate()));
          sb.append("','yyyy-mm-dd hh24:mi:ss') and ");
        }

      }
      else if(dataBaseType.equals("MSSQLSERVER")){
        if(info.getPrjCreateDate()!=null&&!info.getPrjCreateDate().equals("")){
          sb.append(" prj_create_date = '"+sdf.format(info.getPrjCreateDate()));
          sb.append("' and ");
        }
        if(info.getPrjFinishedDate()!=null&&
            !info.getPrjFinishedDate().equals("")){
          sb.append(" prj_finished_date = '"+sdf.format(info.getPrjFinishedDate()));
          sb.append("' and ");
        }
      }

      if(info.getPrjCreateUserId()!=null&&!info.getPrjCreateUserId().equals("")){
        sb.append(" prj_create_user_id = '"+info.getPrjCreateUserId()+"' ");
        sb.append(" and ");
      }

      if(info.getPrjState()!=null&&!info.getPrjState().equals("")){
        sb.append(" prj_state = '"+info.getPrjState()+"' ");
        sb.append(" and ");
      }

      if(info.getPrjTitle()!=null&&!info.getPrjTitle().equals("")){
        sb.append(" prj_title = '"+info.getPrjTitle()+"' ");
        sb.append(" and ");
      }

      if(info.getProjectId()!=WorkflowConstant.LONG_INIT_VALUE){
        sb.append(" project_id = "+info.getProjectId()+" ");
        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 "prj_buss_name,prj_buss_type,prj_create_date,prj_create_user_id,prj_finished_date,prj_state,prj_title,project_id";
  }

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

    if(info.getPrjBussType()!=null&&!info.getPrjBussType().equals("")){
      sbSQL.append("'");
      sbSQL.append(info.getPrjBussType());
      sbSQL.append("'");
      sbSQL.append(",");
    }
    else{
      sbSQL.append("null");
      sbSQL.append(",");
    }
    if(dataBaseType.equals("ORACLE")){
      if(info.getPrjCreateDate()!=null&&!info.getPrjCreateDate().equals("")){
        sbSQL.append("to_date('");
        sbSQL.append(sdf.format(info.getPrjCreateDate()));
        sbSQL.append("','yyyy-mm-dd hh24:mi:ss')");
        sbSQL.append(",");
      }
      else{
        sbSQL.append("null");
        sbSQL.append(",");
      }
    }
    else if(dataBaseType.equals("MSSQLSERVER")){
      if(info.getPrjCreateDate()!=null&&!info.getPrjCreateDate().equals("")){
        sbSQL.append("'");
        sbSQL.append(sdf.format(info.getPrjCreateDate()));
        sbSQL.append("'");
        sbSQL.append(",");
      }
      else{
        sbSQL.append("null");
        sbSQL.append(",");
      }
    }

    if(info.getPrjCreateUserId()!=null&&!info.getPrjCreateUserId().equals("")){
      sbSQL.append("'");
      sbSQL.append(info.getPrjCreateUserId());
      sbSQL.append("'");
      sbSQL.append(",");
    }
    else{
      sbSQL.append("null");
      sbSQL.append(",");
    }
    if(dataBaseType.equals("ORACLE")){
      if(info.getPrjFinishedDate()!=null&&!info.getPrjFinishedDate().equals("")){
        sbSQL.append("to_date('");
        sbSQL.append(sdf.format(info.getPrjFinishedDate()));
        sbSQL.append("','yyyy-mm-dd hh24:mi:ss')");
        sbSQL.append(",");
      }
      else{
        sbSQL.append("null");
        sbSQL.append(",");
      }
    }
    else if(dataBaseType.equals("MSSQLSERVER")){
      if(info.getPrjFinishedDate()!=null&&!info.getPrjFinishedDate().equals("")){
        sbSQL.append("'");
        sbSQL.append(sdf.format(info.getPrjFinishedDate()));
        sbSQL.append("'");
        sbSQL.append(",");
      }
      else{
        sbSQL.append("null");
        sbSQL.append(",");
      }
    }

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

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

    if(info.getProjectId()!=WorkflowConstant.INT_INIT_VALUE){
      sbSQL.append(info.getProjectId());
      sbSQL.append(",");
    }
    else{
      sbSQL.append("null");
      sbSQL.append(",");
    }

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

  /**
   * 更新WfPublicProjectInfo信息
   * @param info WfPublicProjectInfo
   * @throws SQLException
   * @return List
   */
  public void update(WfPublicProjectInfo info,long projectId)
      throws Exception{
    Connection conn=null;
    Statement stm=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      String sql=" update wf_public_project_info set ";
      StringBuffer sbWhere=new StringBuffer(" where project_id="+projectId);
      StringBuffer sbUpdata=getStrUpdateValues(info);
      if(sbWhere.length()<=0||sbUpdata.length()<=0){
        throw new Exception("更新数据为空！");
      }
      sql=sql+sbUpdata.toString()+sbWhere.toString();
      logger.info("......update sql:"+sql);
      stm.execute(sql);
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(stm,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
  }

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

    if(info.getPrjBussType()!=null&&!info.getPrjBussType().equals("")){
      sbSQL.append("prj_buss_type=");
      sbSQL.append("'");
      sbSQL.append(info.getPrjBussType());
      sbSQL.append("'");
      sbSQL.append(",");
    }

    if(info.getPrjCreateDate()!=null&&!info.getPrjCreateDate().equals("")){
      sbSQL.append("prj_create_date=");
      sbSQL.append("to_date(");
      sbSQL.append(sdf.format(info.getPrjCreateDate()));
      sbSQL.append("','yyyy-mm-dd hh24:mi:ss')");
      sbSQL.append(",");
    }

    if(info.getPrjCreateUserId()!=null&&!info.getPrjCreateUserId().equals("")){
      sbSQL.append("prj_create_user_id=");
      sbSQL.append("'");
      sbSQL.append(info.getPrjCreateUserId());
      sbSQL.append("'");
      sbSQL.append(",");
    }

    if(info.getPrjFinishedDate()!=null&&!info.getPrjFinishedDate().equals("")){
      sbSQL.append("prj_finished_date=");
      sbSQL.append("to_date(");
      sbSQL.append(sdf.format(info.getPrjFinishedDate()));
      sbSQL.append("','yyyy-mm-dd hh24:mi:ss')");
      sbSQL.append(",");
    }

    if(info.getPrjState()!=null&&!info.getPrjState().equals("")){
      sbSQL.append("prj_state=");
      sbSQL.append("'");
      sbSQL.append(info.getPrjState());
      sbSQL.append("'");
      sbSQL.append(",");
    }

    if(info.getPrjTitle()!=null&&!info.getPrjTitle().equals("")){
      sbSQL.append("prj_title=");
      sbSQL.append("'");
      sbSQL.append(info.getPrjTitle());
      sbSQL.append("'");
      sbSQL.append(",");
    }

    if(info.getProjectId()!=WorkflowConstant.INT_INIT_VALUE){
      sbSQL.append("project_id=");
      sbSQL.append(info.getProjectId());
      sbSQL.append(",");
    }

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

}