baseop.java

JSP_博客网站系统,功能完整。学习JSP的好资料。开发时采用Eclipse+MyEclipse结合Tomcat部署。

package huc.blog.op;

import huc.blog.util.DB;
import huc.blog.util.PageObject;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BaseOp {
	
	protected DB db = null;
	
	protected Connection conn = null;
	
	protected PreparedStatement pst = null;
	
	protected ResultSet rs = null;
	
	public BaseOp(){
		this.db = new DB();
	}
	
	/**
	 * 关闭打开的ResultSet,PreparedStatement,Connection
	 *
	 */
	public void close(){
		try {
			if(rs != null)
				rs.close();
			if(pst != null)
				pst.close();
			if(conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 得到分页的sql语句
	 * @param page 		页面对象
	 * @param keySQL	查询的字段和数据库	like(" * from table ")
	 * @param otherSQL	查询条件
	 * @param orderBy	排序方式			like(" order by time desc")
	 * @return pageCount 当前分页开始的记录号
	 */
	public StringBuffer getSQL(PageObject page, String keySQL, String otherSQL, String orderBy){
		StringBuffer sql = new StringBuffer();
		
		//检查Page对象的页号是否合法,根据toatl和pageSize合理设置页号
		if(page.getPageNo() < 1)
			page.setPageNo(1);
		int count = (page.getPageNo() - 1 ) * page.getPageSize();
		if(count >= page.getTotal()){
			if(page.getTotal() % page.getPageSize() == 0)
				page.setPageNo(page.getTotal()/page.getPageSize());
			else
				page.setPageNo((int)page.getTotal()/page.getPageSize() + 1);
			
			count = (page.getPageNo() - 1 ) * page.getPageSize();
		}

		if(orderBy == null || orderBy.trim().equals("")){
			orderBy = " order by id asc";
		}		
		
		StringBuffer tempOrderBy = new StringBuffer();
		if(orderBy.indexOf("desc") != -1){
			tempOrderBy.append(" ");
			tempOrderBy.append(orderBy.substring(0,orderBy.indexOf("desc")));
			tempOrderBy.append(" asc");
		}
		else if(orderBy.indexOf("asc") != -1){
			tempOrderBy.append(" ");
			tempOrderBy.append(orderBy.substring(0,orderBy.indexOf("asc")));
			tempOrderBy.append(" desc");
		}
		else{
			orderBy = (new StringBuffer(orderBy)).append(" asc").toString();
			tempOrderBy.append(" ");
			tempOrderBy.append(orderBy.substring(0,orderBy.indexOf("asc")));
			tempOrderBy.append(" desc");
		}
		sql.append("select top ");
		sql.append(page.getPageSize());
		sql.append(" page.* from (");
		sql.append(" select top ");
		sql.append(page.getTotal() - count);
		sql.append(keySQL);
		if(otherSQL != null && !otherSQL.trim().equals("")){
			sql.append(" where 1 = 1");
			sql.append(otherSQL);
		}
		sql.append(tempOrderBy);
		sql.append(") page ");
		sql.append(orderBy);
		
		return sql;
	}

}