pagesqlbuilder.java

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

package cn.com.iaspec.workflow.extbusiness.pagemanage.business;

import cn.com.iaspec.workflow.extbusiness.pagemanage.vo.PageInfo;
import cn.com.iaspec.workflow.db.WorkflowDBConnectionManager;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import org.apache.log4j.Logger;
import java.util.List;
/**
 * <p>Title: </p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2005</p>
 *
 * <p>Company: IASPEC Technologies</p>
 *
 * @author leigm
 * @version 1.0
 */

public class PageSqlBuilder{
  private static PageSqlBuilder instance = null;
  private static transient Logger logger = Logger.getLogger(PageSqlBuilder.class);

  private PageSqlBuilder(){

  }

  public static PageSqlBuilder getInstance(){
    if(instance == null){
      instance = new PageSqlBuilder();
    }

    return instance;
  }

  /**
   * 封装SQL，查询指定页
   * @param sql String
   * @param pageVar PageInfo
   * @return String
   */
  public String buildSql(String sql,PageInfo pageVars,List values)throws Exception{
    //统计查询的总记录数
    StringBuffer sqlBuffer =
        new StringBuffer(" select count(*) from (").append(sql).append(" )");

    Connection conn = WorkflowDBConnectionManager.getInstance().getConnection();
    PreparedStatement ptmt = conn.prepareStatement(sqlBuffer.toString());

    for(int i=0;values != null && i<values.size();i++){
      ptmt.setObject(i+1,values.get(i));
    }
    ResultSet rs = ptmt.executeQuery();

    if(rs.next()){
      pageVars.setTotalRecCnt(rs.getInt(1));
    }
    WorkflowDBConnectionManager.getInstance().close(rs);
    WorkflowDBConnectionManager.getInstance().close(ptmt,false);
    WorkflowDBConnectionManager.getInstance().close(conn);

    logger.debug("\n\t######## totalRecCnt = " + pageVars.getTotalRecCnt());

    sqlBuffer = new StringBuffer("select a.*,rownum as orderid ")
        .append("from (").append ( sql ).append(") a ") ;

    //按指定的字段和升降序排序
    if(pageVars.getOrderField() != null && !"".equals(pageVars.getOrderField())){
      sqlBuffer.append(" order by ")
          .append(pageVars.getOrderField()).append(" ").append(pageVars.getOrderType());
    }

    //分页的起始、结束位置
    int startRow = (pageVars.getPageNo() -1) * pageVars.getCntPerPage() + 1;
    int endRow = pageVars.getPageNo() * pageVars.getCntPerPage();

    StringBuffer endSql =
        new StringBuffer("select * from (").append(sqlBuffer).append(" )")
       .append(" where orderid >=").append(startRow).append(" and orderid <= ")
       .append(endRow);

    return endSql.toString();
  }
}