| 第三周 | |
| 所花时间 | 15左右 |
| 代码量 | 700行左右 |
| 博客量 | 2篇 |
| 学到的知识点 | 可视化图表的使用、条件查询，分页 |
摘要、本周主要通过视频资源学习了条件查询和分页显示的一些操作，条件查询不是很难，通过获得的查询条件拼接sql语句即可，实现了通过姓名模糊查询，通过性别精确查找。分页操作比较难，通过编写了一个PageBean,提供了相关的构造方法。在给出的代码中有具体的解释
一、代码部分
1、分页功能的相关代码
PageBean
package entity;
import java.util.ArrayList;
import java.util.List;
public class PageBean<T> {
//当前页
private int currentPage;
//当前页显示的条数
private int currentCount;
//总条数
private int totalCount;
//总页数
private int totalPage;
//每页显示的数据
private List<T> list = new ArrayList<T>();
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
public int getCurrentCount() {
return currentCount;
}
public void setCurrentCount(int currentCount) {
this.currentCount = currentCount;
}
public int getTotalCount() {
return totalCount;
}
public void setTotalCount(int totalCount) {
this.totalCount = totalCount;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public List<T> getList() {
return list;
}
public void setList(List<T> list) {
this.list = list;
}
}
Servlet
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
request.setCharacterEncoding("utf-8");
PageService service = new PageService();
// 模拟当前是第一页
String currentPageStr = request.getParameter("currentPage");
if (currentPageStr == null)
currentPageStr = "1";
int currentPage = Integer.parseInt(currentPageStr);
// 认为每页显示2条
int currentCount = 2;
PageBean<User> pageBean = null;
pageBean = service.findPageBean(currentPage, currentCount);
request.setAttribute("pageBean", pageBean);
request.getRequestDispatcher("all.jsp").forward(request, response);
}
service
package service;
import java.util.ArrayList;
import dao.PageDao;
import entity.PageBean;
import entity.User;
public class PageService {
PageDao dao=new PageDao();
public PageBean<User> findPageBean(int currentPage, int currentCount) {
//目的：就是想办法封装一个PageBean 并返回
PageBean pageBean = new PageBean();
//1、当前页private int currentPage;
pageBean.setCurrentPage(currentPage);
//2、当前页显示的条数private int currentCount;
pageBean.setCurrentCount(currentCount);
//3、总条数private int totalCount;
int totalCount = dao.getTotalCount();
System.out.println(totalCount);
pageBean.setTotalCount(totalCount);
//4、总页数private int totalPage;
/*
* 总条数 当前页显示的条数 总页数
* 10 4 3
* 11 4 3
* 12 4 3
* 13 4 4
*
* 公式：总页数=Math.ceil(总条数/当前显示的条数)
*
*/
int totalPage = (int) Math.ceil(1.0*totalCount/currentCount);
pageBean.setTotalPage(totalPage);
//5、每页显示的数据private List<T> productList = new ArrayList<T>();
/*
* 页数与limit起始索引的关系
* 例如 每页显示4条
* 页数 其实索引 每页显示条数
* 1 0 4
* 2 4 4
* 3 8 4
* 4 12 4
*
* 索引index = (当前页数-1)*每页显示的条数
*
*/
int index = (currentPage-1)*currentCount;
ArrayList<User> findlist = dao.findlist(index,currentCount);
pageBean.setList(findlist);
System.out.println("66");
return pageBean;
}
}
dao
public int getTotalCount() {
// TODO 自动生成的方法存根
int ans = 0;
String sql = "select * from user";
Connection con = null;
Statement state = null;
ResultSet rs = null;
con = DBUtil.getConn();
try {
state = con.createStatement();
rs = state.executeQuery(sql);
while (rs.next()) {
ans++;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
DBUtil.close(rs, state, con);
return ans;
}
public ArrayList<User> findlist(int index, int currentCount) {
// TODO 自动生成的方法存根
ArrayList<User> list = new ArrayList<>();
String sql = "select * from user limit ?,?";
Connection con = null;
Statement state = null;
PreparedStatement ps;
con = DBUtil.getConn();
ResultSet rs1 = null;
try {
ps = con.prepareStatement(sql);
ps.setInt(1, index);
ps.setInt(2, currentCount);
rs1 = ps.executeQuery();
} catch (SQLException e1) {
// TODO 自动生成的 catch 块
e1.printStackTrace();
}
User bean = null;
try {
while (rs1.next()) {
String name = rs1.getString("name");
String sex = rs1.getString("sex");
String school = rs1.getString("school");
String adress = rs1.getString("address");
bean = new User(name, sex, school, adress);
list.add(bean);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
DBUtil.close(rs1, state, con);
return list;
}
2、条件查询的相关代码
servlet
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
request.setCharacterEncoding("utf-8");
String name = request.getParameter("name");
String sex = request.getParameter("sex");
String adress = request.getParameter("adress");
// System.out.println(name);
// System.out.println(sex);
User bean = new User(name, sex, adress);
PageBean<User> pageBean = null;
UserService service = new UserService();
pageBean = service.find(bean);
request.setAttribute("pageBean", pageBean);
request.getRequestDispatcher("all.jsp").forward(request, response);
}
dao
public ArrayList<User> find(User bean) {
ArrayList<User> list = new ArrayList<>();
String sql = "select * from user where ";
if ((bean.getName() != null && !bean.getName().trim().equals(""))
&& (bean.getSex() != null && !bean.getSex().trim().equals(""))) {
sql += "name like '%" + bean.getName() + "%' and sex like '%" + bean.getSex() + "%'";
} else if (bean.getName() != "") {
sql += "name like '%" + bean.getName() + "%'";
} else if (bean.getSex() != "") {
sql += "sex ='" + bean.getSex() + "'";
}
if (bean.getName() == "" && bean.getSex() == "") {
sql = "select * from user";
}
Connection con = null;
Statement state = null;
ResultSet rs = null;
con = DBUtil.getConn();
User beans = null;
try {
state = con.createStatement();
rs = state.executeQuery(sql);
while (rs.next()) {
String name = rs.getString("name");
String sex = rs.getString("sex");
String school = rs.getString("school");
String adress = rs.getString("address");
beans = new User(name, sex, school, adress);
list.add(beans);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
DBUtil.close(rs, state, con);
return list;
}