账务账号查询的基础上增加分页

先搜索再分页,分页查询要带搜索的条件,注意用隐藏框暂存页码

代码书写顺序:
--DAO
--Action
--struts.xml
--JSP

DAO:

public class AccountDAOImpl implements IAccountDAO{

	public List<Account> findByConndition(String idCardNo, String realName,
			String loginName, String status, Integer page, Integer pageSize) throws DAOException {
		//拼sql,动态地拼条件,拼条件的同时,拼对应的参数值
		String sql = "select * from ("+
				"select a.* ,rownum r from account a where 1=1 ";
		List<Object> params = new ArrayList<Object>();
		if(idCardNo!=null && idCardNo.length()>0){
			sql += "and idcard_no=? ";
			params.add(idCardNo);
		}
		if(realName!=null && realName.length()>0){
			sql += "and real_name=? ";
			params.add(realName);
		}
		if(loginName!=null && loginName.length()>0){
			sql += "and login_name=? ";
			params.add(loginName);
		}
		if(status!=null && status.length()>0 && !status.equals("-1")){
			sql += "and status=? ";
			params.add(status);
		}
		sql += ") where r>? and r<?";
		//查找的当前页的范围,大于上一页的最大页,小于下一页的最小页
		params.add(pageSize*(page-1));
		params.add(pageSize*page+1);
		
		List<Account> list = new ArrayList<Account>();
		Connection con = DBUtil.getConnection();
		try {
			PreparedStatement ps = con.prepareStatement(sql);
			for(int i=0;i<params.size();i++){
				ps.setObject(i+1, params.get(i));
			}
			
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				Account a = createAccount(rs);
				list.add(a);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOException("查询账务账号失败",e);
		}finally{
			DBUtil.close();
		}
		
		return list;
	}
	
	private Account createAccount(ResultSet rs) throws SQLException{
		Account a = new Account();
		a.setId(rs.getInt("id"));
		a.setRecommenderId(rs.getObject("RECOMMENDER_ID")==null?null:rs.getInt("RECOMMENDER_ID"));
		a.setLoginName(rs.getString("LOGIN_NAME"));
		a.setLoginPassword(rs.getString("LOGIN_PASSWD"));
		a.setStatus(rs.getString("STATUS"));
		a.setCreateDate(rs.getDate("CREATE_DATE"));
		a.setPauseDate(rs.getDate("PAUSE_DATE"));
		a.setCloseDate(rs.getDate("CLOSE_DATE"));
		a.setRealName(rs.getString("REAL_NAME"));
		a.setIdcardNo(rs.getString("IDCARD_NO"));
		a.setBirthDate(rs.getDate("BIRTHDATE"));
		a.setGender(rs.getString("GENDER"));
		a.setOccupation(rs.getString("OCCUPATION"));
		a.setTelephone(rs.getString("TELEPHONE"));
		a.setEmail(rs.getString("EMAIL"));
		a.setMailaddress(rs.getString("MAILADDRESS"));
		a.setZipcode(rs.getString("ZIPCODE"));
		a.setQq(rs.getString("QQ"));
		a.setLastLoginTime(rs.getDate("LAST_LOGIN_TIME"));
		a.setLastLoginIp(rs.getString("LAST_LOGIN_IP"));
		
		
		return a;
	}

	public Integer findTotalPage(String idcardNo, String realName,
			String loginName, String status, Integer pageSize)
			throws DAOException {
		String sql = "select count(*) from account where 1=1 ";
		List<Object> params = new ArrayList<Object>();
		if(idcardNo!=null && idcardNo.length()>0){
			sql += "and idcard_no=? ";
			params.add(idcardNo);
		}
		
		if(realName != null && realName.length()>0){
			sql += "and real_name=? ";
			params.add(realName);
		}
		
		if(loginName != null && loginName.length()>0){
			sql += "and login_name=? ";
			params.add(loginName);
		}
		
		if(status != null && status.length()>0 && !status.equals("-1")){
			sql += "and status=? ";
			params.add(status);
		}
		
		Connection con = DBUtil.getConnection();
		try {
			PreparedStatement ps = con.prepareStatement(sql);
			for(int i=0; i<params.size();i++){
				ps.setObject(i+1, params.get(i));
			}
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				int rows = rs.getInt(1);
				if(rows%pageSize==0){
					return rows/pageSize;
				}else{
					return rows/pageSize+1;
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOException("查询总页数失败", e);
		} finally{
			DBUtil.close();
		}
		return 0;
	}
	
}

Action:

public class FindAccountAction {
	
	public String execute(){
		
		IAccountDAO dao = DAOFactory.getAccountDAO();
		try {
			accounts = dao.findByConndition(idcardNo, realName, loginName, status, page, pageSize);
			totalPage = dao.findTotalPage(idcardNo, realName, loginName, status, pageSize);
		} catch (DAOException e) {
			e.printStackTrace();
			return "error";
		}
		return "success";
	}
	
	public String getIdcardNo() {
		return idcardNo;
	}
	public void setIdcardNo(String idcardNo) {
		this.idcardNo = idcardNo;
	}
	public String getRealName() {
		return realName;
	}
	public void setRealName(String realName) {
		this.realName = realName;
	}
	public String getLoginName() {
		return loginName;
	}
	public void setLoginName(String loginName) {
		this.loginName = loginName;
	}
	public String getStatus() {
		return status;
	}
	public void setStatus(String status) {
		this.status = status;
	}
	public List<Account> getAccounts() {
		return accounts;
	}
	public void setAccounts(List<Account> accounts) {
		this.accounts = accounts;
	}
	
	
	public Integer getPage() {
		return page;
	}

	public void setPage(Integer page) {
		this.page = page;
	}

	public Integer getPageSize() {
		return pageSize;
	}

	public void setPageSize(Integer pageSize) {
		this.pageSize = pageSize;
	}

	public Integer getTotalPage() {
		return totalPage;
	}

	public void setTotalPage(Integer totalPage) {
		this.totalPage = totalPage;
	}


	//input
	private String idcardNo;
	private String realName;
	private String loginName;
	private String status;
	private Integer page = 1;
	private Integer pageSize;
	//output
	private List<Account> accounts;
	private Integer totalPage;
}

struts.xml:

<!--账务账号模块-->
<package name="account" extends="netctoss" namespace="/account">
	<!--查询Action-->
	<action name="findAccount" class="netctoss.action.account.FindAccountAction">
		<!--设置每页的容量-->
		<param name="pageSize">5</param>
		<result name="success">
			/WEB-INF/account/findAccount.jsp			
		</result>
	</action>
</package>

Jsp:

<!--分页-->
<!-- 隐藏框用于暂存页码,提交表单时将页码同时传入Action,使分页查询可用该表单提交-->
<input name="page" id="page" type="hidden"/>

<!--搜索-->
<input type="submit" value="搜索" οnclick="javascript:toPage(1);" class="btn_search" />

<!--javascript-->
//分页查询,隐藏框设置为用户选中的页
function toPage(currPage){
	document.getElementById("page").value = currPage;
	document.forms[0].submit();
}

<div id="pages">
    <a href="javascript:toPage(1);">首页</a>
			<s:if test="page==1">                   
		<a href="#">上一页</a>
	</s:if> 
	<s:else>
		<a href="javascript:toPage(<s:property value="page-1"/>);">上一页</a>
	</s:else>
	
	<s:iterator begin="1" end="totalPage" var="p">
		<s:if test="#p==page">
			<a href="javascript:toPage(<s:property value="#p"/>);" class="current_page">
				<s:property value="#p"/>
			</a>
		</s:if>
		<s:else>
			<a href="javascript:toPage(<s:property value="#p"/>);">
				<s:property value="#p"/> 
			</a>
		</s:else>
	</s:iterator>
	
	<s:if test="page==totalPage">
	<a href="#">下一页</a>
    </s:if>
    <s:else>
	<a href="javascript:toPage(<s:property value="page+1"/>);">下一页</a>
    </s:else>
    <a href="javascript:toPage(<s:property value="totalPage"/>);">末页</a>
</div>