paginationaction.java

检测CPU 内存的 检测CPU 内存的

package com.m2.web.action;

import java.util.Map;
import java.util.StringTokenizer;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.struts2.interceptor.RequestAware;


/***
 * 
 * @author Augustan http://yuetong.javaeye.com
 * 这里提供了一个直接执行sql来分页的封装实现
 * 对于不同的数据库，分页的sql语句要分别拼凑
 * 
 *
 */


public abstract class PaginationAction extends BaseAction implements RequestAware{
	
	private static final Log logger = LogFactory.getLog(PaginationAction.class);
	
	private int pageNo;  //当前页码
	
	private int pageSize =15; //每页最大记录数
	
	private int totalSize; //总共记录数
	
	private int orderIndex; //按照页面上第几列进行排序（排除操作列）
	
	private int totalPageCount;//总共页数
	
	private int start; 
	
	private int avaCount;//当前页的记录数	
	
	
	private String dataGrid;
	
	private String pageBar;
	
	
	private DataSource dataSource;
	
	
	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	
	private Map request;

	public void setRequest(Map request) {
		this.request = request;
	}

	public Map getRequest() {
		return request;
	}	

	abstract public String[] getOperationURLs();//操作栏的三个操作地址，分别对应查看，修改和删除
	
	abstract public String[] getURLParameters(); //每个操作地址后面挂接的参数
	
	abstract public String   getOrderField();   //按照数据库中哪一列进行排序
	
	abstract public String   getViewName();     //待分页的View,可以是一个查询语句 
	
	abstract public String   getKeyId();        //主键
	
	abstract public String[] getSQLFields();    //要显示的数据库的列名
	 
	abstract public String[] getHeaders();      //列的标题，和列名一一对应
		
	abstract public String   getWhere();        //拼凑查询的where子句  
	
	public int   getPageSize(){
		return 15;
	}
	
	public String   getResult(){   //页面导向
		
		return SUCCESS;
		
	}       
	
	
	public String execute(){
		
        try{
        	execPaginationForMySQL();
        	String result=getResult();	
        	return result;
        }catch(Exception e){
        	logger.error(e);
        	addActionError("系统错误，请重试");
        	return ERROR;
        }
		
	}
	
	/*
	 * 这里采用了mysql的特有分页语句
	 * mysql专有的分页语句形如：
	 * 
	 *  select * from m2_user 
	 *  where id>100               
	 *  order by name asc 
	 *  limit 0,14 ;
	 *  选择了筛选后结果集的第1到15条数据
	 * 
	 * @throws Exception
	 */
	
	
	public void execPaginationForMySQL()  throws Exception{  
		
		
        try{
        	String curPage=(String)getRequest().get("pageno");
        	pageNo=Integer.valueOf(curPage);
        }catch(NumberFormatException e){
        	pageNo=1;
        }				
		String sql = null;
		Connection conn=null;
		Statement stmt =null;
		ResultSet rs=null;
		try{
		    conn = getDataSource().getConnection();
		    sql = " select count(*) from  "+getViewName()+" where "+getWhere()+";";
		    stmt = conn.createStatement();
		    rs = stmt.executeQuery(sql);
		    rs.next();
		    totalSize = rs.getInt(1);		    
		    if (totalSize==0) pageNo=1;	
    	    int startRecord = (pageNo-1)* getPageSize() + 1;
    	    int endRecord = startRecord + getPageSize() - 1;
    		String orderField=null;
    		String[]SQLFields=getSQLFields();
    		String[]operationURLs=getOperationURLs();		
    		int index=-1;
       		String order=null;
       		StringTokenizer st=null; 
       		String orderParam=(String)getRequest().get("orderParam");
       		if ((orderParam!=null)&&(!"".equals(orderParam))){
       			st=new StringTokenizer(orderParam,",");
       			try{
       			    index=Integer.parseInt(st.nextToken());
       			}catch(NumberFormatException e){
       				index=-1;
       			}
       			order=st.nextToken();
       		}    		
    		if (index!=-1){
    			if ((operationURLs!=null)&&(operationURLs.length>0)){
    				index--;
    			}
    			orderField=SQLFields[index]+" "+order;
    		}else{
    			orderField=getOrderField();
    		}    	    
    		StringBuffer sb = new StringBuffer(" select * from ");
    		sb.append(getViewName());
            String where =getWhere();
            if ((where!=null)&&!"".equals(where))
            	sb.append(" where ").append(where);
            sb.append(" order by ").append(orderField)
            .append(" limit ").append(startRecord-1).append(",").append(endRecord-1).append(";");
    	    sql=sb.toString();	 
    	    this.start=getStartOfAnyPage(pageNo, pageSize);
    	    this.totalPageCount = (totalSize + pageSize -1) / pageSize;
    	    if (totalPageCount<=0) 
    	    	totalPageCount=1;
            if(this.pageNo==this.totalPageCount){
            	this.avaCount=this.totalSize-(this.pageNo-1)*this.pageSize;
            }else if (this.totalPageCount==0) 
            	this.avaCount=0;
             else 
            	this.avaCount=pageSize;	 
    		String [] headers =getHeaders();     
        	boolean hasOperation=false;
        	if ((operationURLs!=null)&&(operationURLs.length>0)) hasOperation=true;
        	
        	sb=new StringBuffer();
        	sb.append("<input type='hidden' id='hasOperation' value='").append(hasOperation).append("'>");
        	if (orderIndex==-1)
        	    sb.append("<input type='hidden' id='orderParam' name='orderParam' >");
        	else
        		sb.append("<input type='hidden' id='orderParam' name='orderParam' value='")
        		.append(hasOperation?(orderIndex+1):orderIndex).append(",").append(order).append("'>");
        	int len=headers.length; 
        	sb.append("<TR>");
        	if (hasOperation){
        		sb.append("<TH width='60px' id='operation_th'>操作</TH>");
        	}    	   
        	for(int i=0;i<len;i++){
        		if (i==orderIndex)
        			sb.append("<TH class='").append(order).append("'>").append(headers[i]).append("</TH>");
        		else
        			sb.append("<TH>").append(headers[i]).append("</TH>");
        	}
        	sb.append("</TR>");
        	len=SQLFields.length;
        	int i=0;
        	if (len==0){
        		this.dataGrid="";
        		return;
        	}    	    
        	String keyId=getKeyId();
        	String []URLParameters=getURLParameters();
    		rs=stmt.executeQuery(sql);
    		logger.info(sql);
    		while(rs.next()){
    			sb.append("<TR>");
    			if ((operationURLs!=null)&&(operationURLs.length>0)&&(keyId!=null)){
    				sb.append("<TD>");
    				for(int j=0;j<operationURLs.length;j++){
    					if ((j==0)&&(operationURLs[0]!=null)){
    						sb.append("&nbsp;<a href='")
    						.append(operationURLs[0]).append("?")
    						.append(keyId).append("=")
    						.append(rs.getString(keyId))
    						.append(URLParameters[0].toString()).append("'>查看</a>");
    					}
    					if ((j==1)&&(operationURLs[1]!=null)){
    						sb.append("&nbsp;<a href='")
    						.append(operationURLs[1]).append("?")
    						.append(keyId).append("=")
    						.append(rs.getString(keyId))
    						.append(URLParameters[1].toString()).append("'>修改</a>");
    					}
    					if ((j==2)&&(operationURLs[2]!=null)){
    						sb.append("&nbsp;<a href='")
    						.append(operationURLs[2]).append("?")
    						.append(keyId).append("=")
    						.append(rs.getString(keyId))
    						.append(URLParameters[2].toString())
    						.append("' onclick=\"return confirm('确定要删除么？')\"").append(">删除</a>");
    					}    					
    				}
    				sb.append("</TD>");
    			}
    		   for(i=0;i<len;i++){
    		       sb.append("<TD><div class=divout>")
    		       .append(rs.getString(SQLFields[i])==null?"":rs.getString(SQLFields[i]))	
    			   .append("</div></TD>");
    		   }    			
    		   sb.append("</TR>");
    		}
    		this.dataGrid = sb.toString();
    		renderPageBar("submitForm", null);
		}catch(Exception e){
			logger.error(e);
			throw  e;
		}finally{
			closeAll(rs, stmt, conn);
		}
	    
	}
	
	
	
	public void execPaginationForSqlServer2005()throws Exception{  //这里采用了sqlserver2005的分页语句

		try{
        	String curPage=(String)getRequest().get("pageno");
        	pageNo=Integer.valueOf(curPage);
        }catch(NumberFormatException e){
        	pageNo=1;
        }				
		String sql = null;
		Connection conn=null;
		Statement stmt =null;
		ResultSet rs=null;
		try{
		    conn = getDataSource().getConnection();
		    sql = " select count(*) from  "+getViewName()+" where "+getWhere();
		    stmt = conn.createStatement();
		    rs = stmt.executeQuery(sql);
		    rs.next();
		    totalSize = rs.getInt(1);
		    if (totalSize==0) pageNo=1;	
    	    int startRecord = (pageNo-1)* getPageSize() + 1;
    	    int endRecord = startRecord + getPageSize() - 1;
    		String orderField=null;
    		String[]SQLFields=getSQLFields();
    		String[]operationURLs=getOperationURLs();		
    		int index=-1;
       		String order=null;
       		StringTokenizer st=null; 
       		String orderParam=(String)getRequest().get("orderParam");
       		if ((orderParam!=null)&&(!"".equals(orderParam))){
       			st=new StringTokenizer(orderParam,",");
       			try{
       			    index=Integer.parseInt(st.nextToken());
       			}catch(NumberFormatException e){
       				index=-1;