pagectl.java

J2EE客户关系管理系统!!!!!!!!!!!!!!!!!!!!

package com.accp.util;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Session;

import org.hibernate.Query;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import com.accp.HibernateSessionFactory;


import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ResourceBundle;
import java.text.SimpleDateFormat;
import org.apache.log4j.Logger;




/**
 * 自封装dwr框架分页类
 * @author 珠海前程-李侃
 *
 */
public class PageCtl {
	
	//log4j日志工具
	final static Logger log = Logger.getLogger(PageCtl.class);
	
	static ResourceBundle sqlbundle; //从sql资源文件里面取sql查询语句
	static {
		String pkg = PageCtl.class.getPackage().getName();
		sqlbundle=ResourceBundle.getBundle(pkg+".PageCtlSql");
	}
	
	//static ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
	//EmpDAO emp = EmpDAO.getFromApplicationContext(context);
	int rowcount;     //共多少行
	int pagesize;     //每页多少行
	int pageno;       //当前是第几页
	int pagecount;    //总共多少页
	StringBuffer json;
	String cols;

	public String getJson() {
		return json.toString();
	}
	public void setJson(String json) {
		this.json = new StringBuffer(json);
	}
	public int getPageno() {
		return pageno;
	}
	public void setPageno(int pageno) {
		this.pageno = pageno;
	}
	public int getPagesize() {
		return pagesize;
	}
	public void setPagesize(int pagesize) {
		this.pagesize = pagesize;
	}
	public int getRowcount() {
		return rowcount;
	}

	public void setRowcount(int rowcount) {
		this.rowcount = rowcount;
	}
	
	public int getPagecount() {
		return pagecount;
	}
	public void setPagecount(int pagecount) {
		this.pagecount = pagecount;
	}
	

	/**
	 * pageno 		当前页面的页码
	 * pagesize 	每页显示条数
	 * sqlkey 		从资源文件里面取得sql的key
	 * filter 		过滤条件
	 */
	public PageCtl go(int pageno, int pagesize, String sqlkey, String filter, String dateformat)
	 throws IllegalAccessException, InstantiationException
	{	
		this.pagesize = pagesize;
		this.pageno = pageno;
		System.out.println("====="+sqlkey+"======");
		String sql = sqlbundle.getString(sqlkey);
		Session session = HibernateSessionFactory.getSession();		
		Connection conn = session.connection();
		SimpleDateFormat sf = new SimpleDateFormat(dateformat);
		if(!"".equals(filter))
		{
			sql += filter;
		}
		log.debug("分页控件执行的sql:" + sql);

		try {
			Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			ResultSet rs=stmt.executeQuery(sql);
			
			if(rs.last())
			    this.rowcount = rs.getRow();
			else
			    this.rowcount = 0;

			rs.beforeFirst();
			
			this.pagesize = pagesize;
			//计算页数
			if((float)rowcount / pagesize != (int)rowcount / pagesize) { //不能整除
			   pagecount=(int)rowcount / pagesize + 1;
		    } else { //能整除
			   pagecount=(int)rowcount / pagesize;
		    }

			//修正页数
			if(pagecount<1)
				pagecount = 1;

			this.pageno = pageno;
			//修正页码
			if(this.pageno> pagecount-1)
				this.pageno=pagecount;
			
			//修正页码
			if(pageno<1)
				this.pageno=1;
			
			int position = (this.pageno -1) * pagesize;
			
			if(position  > 0)
			rs.absolute(position);
			
			int j=0;
			Object[] obs = getResultGetColumnNames(rs);
			List<String> columNames = (ArrayList<String>) obs[0];
			List<String> columnTypes = (ArrayList<String>) obs[1];
			
			cols="[";
			for(int i = 0; i < columNames.size(); i ++)
			{
			 cols += "'" + columNames.get(i)+ "'";
			 if(i < columNames.size() - 1)
			 cols +=",";
			}
			cols+="]";
			if(json==null)
				json = new StringBuffer("[\r\n");
			else
				json.append("[\r\n");
			
  	        while(rs.next() && j < this.pagesize ) {
			    int colcount = columNames.size();
			    //System.out.println(colcount);
			    //json+="{";
			    if(j==0)
			      json.append("{");
			    else
			      json.append(",\r\n{");
			    j++;
			    for(int i = 0; i < colcount; i++) {
			      String colname = columNames.get(i).toLowerCase();
			      String coltype = columnTypes.get(i);

			      String colvalue = "";
			      if(coltype.toUpperCase().indexOf("DATE")>=0)
			      {
			    	  Timestamp timestamp = rs.getTimestamp(colname);
			    	  if(timestamp!=null)
			    	  {
			    	   colvalue = sf.format(timestamp);
			    	  }
			      } else {
			    	  colvalue = rs.getString(colname);
			      }
			      
			      
			      if(colvalue==null)
			      {
			    	  colvalue = "";
			      }
			      json.append("'" + colname + "':'"+ colvalue+"'");
			      //json+="'" + colname + "':'"+ colvalue+"'";
			      if(i< colcount - 1)
			      {
			    	  json.append(",");
			    	  //json+=",";
			      }
			    }
			    //json+="},\r\n";
			    json.append("}\r\n");
			   }
  	           json.append("]");
		       //json+="\r\n'length':'"+j+"']\r\n";
  	           //打印所有读取到的信息
  	           //log.debug(json.toString());
  	           rs.close();
  	           stmt.close();
			  } catch (SQLException ex) {
			    ex.printStackTrace();
			  } finally {
				  HibernateSessionFactory.closeSession();
			  }
			  return  this;
	}


	//得到查询的表结构
	public Object[] getResultGetColumnNames(ResultSet rs) {
		Object[] object = new Object[2];
    	List<String> columnNames = new ArrayList<String>();
    	List<String> columnTypes = new ArrayList<String>();
      try{    	
        //get a reference to the ResultSet's meta data
        ResultSetMetaData md=rs.getMetaData();
        int count=md.getColumnCount();
        columnNames=new ArrayList<String>();

        for(int x=0; x<count; x++) {
          columnTypes.add(md.getColumnTypeName(x+1));
         // log.debug(md.getColumnTypeName(x+1));
          columnNames.add(md.getColumnName(x+1).toLowerCase());
        }
      } catch(SQLException sqlex) {
    	 sqlex.printStackTrace();
      }
      object[0] =  columnNames;
      object[1] =  columnTypes;
      return object;
 }
	


	public String getCols() {
		return cols;
	}
	public void setCols(String cols) {
		this.cols = cols;
	}
	
	//测试
	public static void main(String[] args) throws IllegalAccessException, InstantiationException, InvocationTargetException, NoSuchMethodException {
		//PageCtl p = new PageCtl().test(1, 5, "select empno, ename from emp", "");
		
		//System.out.println(p.getJson());
		//List ls = p.test(1, 5, "select * from emp", "");
		//System.out.println(ls.size());
		
	}
	
	
}