package com.jsptest.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.jsptest.dao.TbItemDao;
import com.jsptest.domain.TbItem;
import com.jsptest.utils.JDBCUtils;
import com.jsptest.utils.PageBean;
/**处理sql语句
* @author Administrator
*
*/
public class TbItemImpl implements TbItemDao {
@Override
public PageBean<TbItemDao> getAllItem(PageBean pagebean) throws SQLException {
//获取连接
Connection conn = JDBCUtils.getConnection();
//sql语句
String sql="SELECT*FROM tb_item LIMIT ?,? ";
//预处理
PreparedStatement ps = conn.prepareStatement(sql);
//设置占位符,防止sql注入;计算起始条数:因为分页的下标是从0开始的limit(0,10)/(10,10)/(20,10),所有需要用当前页数-1
ps.setInt(1, (pagebean.getCurrentPageNo()-1)*pagebean.getPerPageNO());
//每页的条数
ps.setInt(2, pagebean.getPerPageNO());
//使用preparedstatement对象执行查询
ResultSet rs = ps.executeQuery();
//创建一个集合用于存放返回的数据
List<TbItem> tbItems=new ArrayList<>();
while (rs.next()) {
TbItem tbItem=new TbItem();
tbItem.setId(rs.getInt("id"));
tbItem.setTitle(rs.getString("title"));
tbItem.setSell_point(rs.getString("sell_point"));
tbItem.setPrice(rs.getDouble("price"));
//将对象数据放到集合中存储
tbItems.add(tbItem);
}
//将数据放到分页集合中
pagebean.setDatas(tbItems);
//获取总记录数
sql="select count(1) from tb_item" ;
ps=conn.prepareStatement(sql);
rs=ps.executeQuery();
int totalRecords=0;
if(rs.next()) {
//返回的只有一条数据,不带字段的直接给1
totalRecords=rs.getInt(1);
}
//设置总条数
pagebean.setTotalRecords(totalRecords);
//关闭流
JDBCUtils.release(rs, ps, conn);
return pagebean;
}
}
//biz业务层页面:
public class TbItemBizImpl implements TbItemBiz{
private TbItemDao tbItemDao=new TbItemDaoImpl();
public PageBean<TbItem> getAllItem(PageBean pageBean) {
PageBean<TbItem> allItem =null;
try {
allItem= tbItemDao.getAllItem(pageBean);
} catch (SQLException e) {
e.printStackTrace();
}
return allItem;
}
}
2.实体类
package com.jsptest.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.jsptest.dao.TbItemDao;
import com.jsptest.domain.TbItem;
import com.jsptest.utils.JDBCUtils;
import com.jsptest.utils.PageBean;
/**处理sql语句
* @author Administrator
*
*/
public class TbItemImpl implements TbItemDao {
@Override
public PageBean<TbItemDao> getAllItem(PageBean pagebean) throws SQLException {
//获取连接
Connection conn = JDBCUtils.getConnection();
//sql语句
String sql="SELECT*FROM tb_item LIMIT ?,? ";
//预处理
PreparedStatement ps = conn.prepareStatement(sql);
//设置占位符,防止sql注入;计算起始条数:因为分页的下标是从0开始的limit(0,10)/(10,10)/(20,10),所有需要用当前页数-1
ps.setInt(1, (pagebean.getCurrentPageNo()-1)*pagebean.getPerPageNO());
//每页的条数
ps.setInt(2, pagebean.getPerPageNO());
//使用preparedstatement对象执行查询
ResultSet rs = ps.executeQuery();
//创建一个集合用于存放返回的数据
List<TbItem> tbItems=new ArrayList<>();
while (rs.next()) {
TbItem tbItem=new TbItem();
tbItem.setId(rs.getInt("id"));
tbItem.setTitle(rs.getString("title"));
tbItem.setSell_point(rs.getString("sell_point"));
tbItem.setPrice(rs.getDouble("price"));
//将对象数据放到集合中存储
tbItems.add(tbItem);
}
//将数据放到分页集合中
pagebean.setDatas(tbItems);
//获取总记录数
sql="select count(1) from tb_item" ;
ps=conn.prepareStatement(sql);
rs=ps.executeQuery();
int totalRecords=0;
if(rs.next()) {
//返回的只有一条数据,不带字段的直接给1
totalRecords=rs.getInt(1);
}
//设置总条数
pagebean.setTotalRecords(totalRecords);
//关闭流
JDBCUtils.release(rs, ps, conn);
return pagebean;
}
}
3.action页面相当于scontrol层:
package com.jsptest.action;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;s
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.jsptest.biz.TbItemBiz;
import com.jsptest.biz.impl.TbItemBizImpl;
import com.jsptest.domain.TbItem;
import com.jsptest.utils.PageBean;
/**action 相当于mvc三层架构的control层,和视图层进行交互数据传到前端显示
* @author Administrator
* 使用http传输数据
*/
public class PageTbItemAction extends HttpServlet{
private TbItemBiz tbItemBiz =new TbItemBizImpl();
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//获取前端传送过来的当前页
int currentPageNo = Integer.parseInt(req.getParameter("currentPageNo"));
//获取前端传送过来的每页的条数
int perPageNO = Integer.parseInt(req.getParameter("perPageNO"));
//System.out.println(currentPageNo+"--------------"+perPageNO);
//将参数封装到对象中,用于请求数据的查询
PageBean<TbItem> pageBean=new PageBean<>();
pageBean.setCurrentPageNo(currentPageNo);
pageBean.setPerPageNO(perPageNO);
//根据对象获取返回值进行分页
pageBean= tbItemBiz.getAllItem(pageBean);
//将数据设置到http中用于传到前端显示
req.setAttribute("pageBean",pageBean);
//传送到相关的页面
req.getRequestDispatcher("/index.jsp").forward(req, resp);
}
}
4.jsp 页面:
<%@page import="org.apache.jasper.tagplugins.jstl.core.ForEach"%>
<%@page import="java.util.List"%>
<%@page import="com.jsptest.domain.TbItem"%>
<%@page import="com.jsptest.utils.PageBean"%>
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Insert title here</title>
<%
//获取分页的对象
PageBean<TbItem> pageBean=(PageBean<TbItem>)request.getAttribute("pageBean");
if(pageBean!=null){
//获取分页的数据
List<TbItem> tbItems=pageBean.getDatas();
%>
</head>
<body>
<table>
<tr style="background:dark;color:white">
<th>商品编号</th>
<th>商品名称</th>
<th>商品买点</th>
<th>商品价格</th>
</tr>
<tr>
<%
if(tbItems.size()>0){
for(TbItem tbItem:tbItems){
%>
<tr style="background:darkblue;color:white">
<td><%=tbItem.getId() %></td>
<td><%=tbItem.getTitle() %>/td>
<td><%=tbItem.getSell_point() %></td>
<td><%=tbItem.getPrice() %>/td>
</tr>
<%
}
}else{
out.print("么有数据!");
}
%>
</table>
<%
//显示所有的页数
for(int i=0;i<pageBean.getTotalPageNo();i++){
out.print("<a href='PageTbItemAction?currentPageNo="+i+"&perPageNO=10'>"+i+"</a> ");
}
%>
<a href="PageTbItemAction?currentPageNo=<%=pageBean.getPreviewPageNO()%>&perPageNO=10">上一页</a>
<a href="PageTbItemAction?currentPageNo=<%=pageBean.getNextPageNo()%>&perPageNO=10">下一页</a>
总页数:<%=pageBean.getCurrentPageNo() %>/<%=pageBean.getTotalPageNo() %>
总记录数:<%=pageBean.getTotalRecords() %>
<%
//刚进入时候每有数据,
}else{
out.print("数据没有正确读取!<br/>");
out.print("点击以下链接获取数据:<br/>") ;
out.print("<a href='PageTbItemAction?currentPageNo=1&perPageNO=10'>获取数据</a>") ;
}
%>
</body>
</html>