mysql+servlet 分页-优快云博客

本文链接：https://blog.youkuaiyun.com/asunjianren/article/details/38709361

// html代码

</div>

// jquery 代码

//获取地理位置信息分页条
function getPageBar() {
//页码大于最大页数
if (curPage > totalPage)
curPage = totalPage;
//页码小于1
if (curPage < 1)
curPage = 1;
pageStr = "<span>共 " + total + " 页</span><span> " + curPage + "/"
+ totalPage + " </span>";

//如果是第一页
if (curPage == 1) {
pageStr += " <span>首页</span> <span>上一页</span> ";
} else {
pageStr += "<span> <a href='javascript:getData(1)' rel='1'>首页</a></span> <span><a href='javascript:getData("+(curPage - 1)+")' rel='"
+ (curPage - 1) + "'>上一页</a></span>";
}

//如果是最后页
if (curPage >= totalPage) {
pageStr += " <span>下一页</span> <span>尾页</span>";
} else {
pageStr += " <span><a href='javascript:getData("+((curPage) + 1)+")' rel='"
+ (parseInt(curPage) + 1)
+ "'>下一页</a></span> <span><a href='javascript:getData("+totalPage+")' rel='"
+ totalPage + "'>尾页</a></span>";
}

$("#pageCount").html(pageStr);

}

// 点击按钮调用的方法

// servlet 代码

public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {

SearchEmployeeDao searchEmployeeDao = new SearchEmployeeDao();
response.setContentType("text/json;charset=UTF-8");
PrintWriter out = response.getWriter();
String beginTime = request.getParameter("beginTime");
String endTime = request.getParameter("endTime");

Integer pageNo=Integer.parseInt(request.getParameter("pageno"));
//返回相关数据信息
List<UserInfo> list = new ArrayList<UserInfo>();
list = searchEmployeeDao.queryByTime(beginTime, endTime, pageNo);
//总页面数
Integer totalpage=searchEmployeeDao.getPageCount(beginTime,endTime);
//数据总记录数
Integer total=searchEmployeeDao.geTotalPage(beginTime,endTime);

StringBuilder builder=new StringBuilder();
builder.append("[");
for(int i=0;i<list.size();i++){
StringBuilder content=new StringBuilder();
String address=list.get(i).getAddress();
content.append("{ \"address\":\"").append(list.get(i).getAddress()).append("\"");
String lat=list.get(i).getLat();
content.append(",\"lat\":").append(list.get(i).getLat()).append("");
content.append(",\"lng\":").append(list.get(i).getLng()).append("");
content.append(",\"mobile\":\"").append(list.get(i).getuMobile()).append("\"");
content.append(",\"urealname\":\"").append(list.get(i).getuRealName()).append("\"}");
if(i<list.size()-1){
content.append(",");
}
builder.append(content.toString());

}
builder.append("]");

StringBuilder json=new StringBuilder();
json.append("{\"total\":").append(totalpage).append("");
json.append(",\"totalPage\":").append(totalpage).append("");
json.append(",\"page\":").append(pageNo).append("");
json.append(",\"pageSize\":").append("3").append("");
json.append(",\"list\":").append(builder.toString()).append("}");

out.print(json.toString());

out.flush();
out.close();
}

// dao层代码

// 根据时间获得员工位置信息
public List<UserInfo> queryByTime(String beginTime,String endTime,int currentPageNo){
int BeginRecord;
ResultSet rSet = null;
BeginRecord = (currentPageNo - 1) * pagesize; // 开始位置
String sql = "SELECT u_real_name as uRealName,lat as lat,lng as lng,umobile as uMobile,address as address FROM t_userinfo userinfo INNER JOIN t_locationinfo locationinfo ON userinfo.u_mobile=locationinfo.umobile LIMIT ?,?";
String sql2 = "SELECT u_real_name as uRealName,lat as lat,lng as lng,umobile as uMobile,address as address FROM t_userinfo userinfo INNER JOIN t_locationinfo locationinfo ON userinfo.u_mobile=locationinfo.umobile where locationinfo.update_time BETWEEN ? AND ? LIMIT ?,?";

if(beginTime==null){

rSet = MySQLUtil.query(sql,BeginRecord,pagesize);
}else{

rSet = MySQLUtil.query(sql2, beginTime,endTime,BeginRecord,pagesize);
}

List<UserInfo> list = new ArrayList<UserInfo>();
UserInfo userInfo = null;
try {
while(rSet.next()){
userInfo = new UserInfo();
userInfo.setuRealName(rSet.getString("uRealName"));
userInfo.setuMobile(rSet.getString("uMobile"));
userInfo.setLat(rSet.getString("lat"));
userInfo.setLng(rSet.getString("lng"));
userInfo.setAddress(rSet.getString("address"));
list.add(userInfo);

}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}

// 分页统计
public static int getPageCount(String beginTime,String endTime) {
int total = 0; // 总记录数
int PageCount = 0; // 页码总数
ResultSet resultset = null;
String sql = "SELECT count(*) FROM t_userinfo userinfo INNER JOIN t_locationinfo locationinfo ON userinfo.u_mobile=locationinfo.umobile";
String sql2 = "SELECT count(*) FROM t_userinfo userinfo INNER JOIN t_locationinfo locationinfo ON userinfo.u_mobile=locationinfo.umobile where locationinfo.update_time BETWEEN ? AND ?";

if(beginTime==null){

resultset = MySQLUtil.query(sql);
}else{

resultset = MySQLUtil.query(sql2, beginTime,endTime);
}
try {
if (resultset.next()) {
total = resultset.getInt(1);
PageCount = (total - 1) / pagesize + 1;

}
} catch (SQLException e) {
e.printStackTrace();
}
return PageCount;
}

public static int geTotalPage(String beginTime,String endTime) {
int total = 0; // 总记录数
ResultSet resultset = null;
String sql = "SELECT count(*) FROM t_userinfo userinfo INNER JOIN t_locationinfo locationinfo ON userinfo.u_mobile=locationinfo.umobile";
String sql2 = "SELECT count(*) FROM t_userinfo userinfo INNER JOIN t_locationinfo locationinfo ON userinfo.u_mobile=locationinfo.umobile where locationinfo.update_time BETWEEN ? AND ?";
if(beginTime==null){

resultset = MySQLUtil.query(sql);
}else{

resultset = MySQLUtil.query(sql2, beginTime,endTime);
}
try {
if (resultset.next()) {
total = resultset.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
return total;
}