10-分页

最新推荐文章于 2020-10-07 13:47:17 发布

我丶秦始皇丶打钱

最新推荐文章于 2020-10-07 13:47:17 发布

阅读量225

点赞数

分类专栏： javaweb

本文链接：https://blog.csdn.net/weixin_47520834/article/details/108904035

版权

javaweb 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

分页

1.分页实现思路

1.查询列表时默认显示固定数量的数据
2.算出当前符合条件的所有数据的总页码数
3.显示上一页下一页按钮
4.每次点击按钮是判断是否是首尾页
分页功能需要页面显示与数据查询同步进行。

分页的功能需要由数据库语句体现，mysql数据库中分条显示数据语句：

select uid,uname,upwd,unick_name,uage,uaddr from my_user limit  0  ,  5
如果page从1开始，那么这两个值的关系      (page-1)*pageSize  ,  pageSize;

在dao中可以接收参数来拼接查询语句。

public interface UserDao {
	List<MyUser> queryUser(int page,int pageSize);
}

在给用户显示页面时，需要把将要访问的页数写进页面，当用户点击翻页时，后台可以接收到数据并根据页数查找数据

<a class="btn" href="${pageContext.request.contextPath }/queryUser?page=${page+1 }">下一页
</a>

判断按钮是否可用时需要知道以当前的每页显示记录数总共有多少页数据，此数据是通过总记录数和每页显示记录数算出的

int totalpage =
totalnum%pageSize==0?totalnum/pageSize:(totalnum/pageSize)+1;
//如果总记录数能被每页显示记录数整除，那总页数直接相除得到，
//如果不能整除，剩余部分需要多占一页

总结：在后台计算页数，并把上一页、下一页要查询的参数提前在后台生成好，生成在页面中，用户点击生成好的链接，在后台查询新的数据，同时生成新的上一页、下一页的参数。以此完成页数前端与后台的同步

2. 分页与条件查询

查询功能中经常有通过特定条件对记录进行筛选。

多条件时，用户如果填写有内容，则数据库语句需要拼接上此条件，如果没有时此条件不拼接。条件查询经常需要和分页功能同时存在

多条件查询拼接

List<Object> params = new ArrayList<Object>();	//存放参数列表	
boolean isWhere = true;     //通过开关判断是否要拼接where
		if(quser.getUname()!=null&&!"".equals(quser.getUname())) {//判断条件是否存在
			if(isWhere) {                     //判断是否拼接where
				sql+=" where "+ " uname = ? ";  //拼接where
				isWhere = false;              //把拼接where的标记改为false
			}else {                          //否则拼接and
				sql+=" and "+ " uname = ? ";
			}
			params.add(quser.getUname());     //把此条参数放入保存参数的list中
		}

psta = conn.prepareStatement(sql);
		int paramIndex = 1;
		for(Object param: params) {         //设置查询参数时 把list列表中的参数直接读出
			psta.setObject(paramIndex, param);//一个个按位设置给prepareStatement对象
			paramIndex++;
		}
	  rs = psta.executeQuery();

注意：查总记录数的语句需要与分条查询语句查询条件相同

页面中翻页时，查询条件需要一起发送到后台，页面中可以利用表单中的隐藏域

<form id="qform" action="${pageContext.request.contextPath }/queryUser" method="post" >
<!--其他查询条件 -->
	  <input id="page"  name="page"  type="hidden">
<input id="pageSize" name="pageSize" type="hidden" name="pageSize">
<!--用隐藏域存放page pagesize -->
	  <input type="submit" class="btn btn-success" value="查询">
	</form>

$("#page").val(gopage); //js操作时 给隐藏域中的属性设置值
$("#qform").submit();  //通过js触发表单提交

3. Ajax分页

当条件查询与分页合在一起使用时，为了处理查询条件与页数同时同步，代码处理上较为麻烦，如果改成页面不刷新，只改变部分数据，逻辑结构上会更加清晰

页面不跳转，查询数据都保留在页面中
页面中通过js创建变量，保存当前翻到第几页
每次查询请求，将页面上的查询数据和页数发送到后台，后台返回数据和最新的页数，页面接收到数据，把数据的部分替换到页面中，并更新js中保存页数相关的变量

服务器部分进行配合ajax响应的改造，改为向页面输出json格式字符串

Map<String,Object> returnMap = new HashMap<String,Object>();
		returnMap.put("returnCode", "10000");  //状态码（公司自己定义）
		returnMap.put("returnData", lu);       //数据库查询出的数据
		returnMap.put("pageInfo", pif);        //页数相关数据
		String returnstr = JSON.toJSONString(returnMap);//转成json格式字符串
		pw.print(returnstr);

前端部分改造成ajax请求

$.ajax({
			url:"ajaxQueryUser",//地址
			type:"post",//请求类型
			data:mydata,   //请求的参数 键值对字符串  json格式
			dataType:"json",//返回数据的类型
			success:function(data){//响应成功时的回调函数
				//console.log(data.returnData);
				$("#mainData").html("");//清空之前的数据
            //遍历返回的数据，填入到页面
				$.each(data.returnData,function(i,d){
					var newdata = "<tr><td>"+d.uid+"</td><td>"+d.uname+"</td>"
						+"<td>"+d.age+"</td><td>"+d.uNickname+"</td>"
						+"<td>"+d.uaddr+"</td></tr>"
					$("#mainData").append(newdata);
				})
				//更新页数相关变量，更新页面显示页码
				page = data.pageInfo.page;
				pagesize = data.pageInfo.pageSize;
				totalpage = data.pageInfo.totalPage;
				//$("#myMsg").html(data);
				$("#showPage").html(page);
				$("#showSize").html(pagesize);
                $("#showTotal").html(totalpage);
                //重置按钮禁用状态
				initBtn();
			}
		});

4.增删改思路

添加
添加页面
后台接收到用户发送的数据 --》做数据合法性验证 --》调用添加方法
跳转到显示结果页面
跳转到查询

修改
把当条记录的标记传递到后台 通过id查询该记录的完整信息
修改页面  展示该记录的信息
后台接收到用户发送的数据 --》做数据合法性验证 --》调用修改方法
跳转到显示结果页面
跳转到查询

删除
把当条记录的标记传递到后台 --》做数据合法性验证 --》调用删除方法
跳转到显示结果页面
跳转到查询

5.案例

//param() 方法创建数组或对象的序列化表示。
//该序列化值可在进行 AJAX 请求时在 URL 查询字符串中使用。
实例
序列化一个 key/value 对象：
var params = { width:1900, height:1200 };
var str = jQuery.param(params);
$("#results").text(str);
结果:
width=1680&height=1050

    
//ajaxShowUser.html  页面
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8"/>
    <title>Title</title>
    <link rel="stylesheet" href="css/bootstrap.css">
    <script src="js/jquery-3.4.1.min.js"></script>
    <script src="js/bootstrap.min.js"></script>
    <script>
        var page;
        var pageSize;
        var totalPage;
        var selectRow=[];                   
/*
*
*       *  page
        *  pageSize
        *  quname
        *  quremark
*
* */
        $(function(){
            myQuery("");                        //初始化myQuery方法  防止查询页面是空的 

            $("#submitBtn").click(function(){
               //取界面上的值放入params
                var params = {"quname":$("#quname").val(),"quremark":$("#quremark").val()};        
                myQuery(params);
            })

            $("#prevBtn").click(function(){
                var params = {"quname":$("#quname").val(),"quremark":$("#quremark").val(),
                              "page":page-1,"pageSize":pageSize};
                myQuery(params);
            })
            $("#nextBtn").click(function(){
                var params = {"quname":$("#quname").val(),"quremark":$("#quremark").val(),
                              "page":page+1,"pageSize":pageSize};
                myQuery(params);
            })
            //打开修改面板
            //优化版本
            //已经在点击某个数据的时候 把数据放进了创建的selectRow[]里
            //点击编辑事件的时候  初始化被选中的数据  放入修改面板中
            $("#editBtn").click(function(){
                //如果列表中数据够用
                //如果修改面板中数据比列表中的多  用id从后台取完整数据
                $("#euid").val(selectRow[0]);
                $("#euname").val(selectRow[1]);
                $("#eupwd").val(selectRow[2]);
                $("#euphone").val(selectRow[3]);
                $("#euremark").val(selectRow[4]);
            })
            //提交修改请求
            $("#editSubmit").click(function(){
                  //普通版本
/*                var myparam = {"euid":$("#euid").val(),
                                "euname":$("#euname").val(),
                                "eupwd":$("#eupwd").val(),
                                "euphone":$("#euphone").val(),
                                "euremark":$("#euremark").val()}*/
                //把指定form中的有name属性的内容 拼成键值对格式
                var newparam = $("#editform").serialize();
                
                $.post("ajaxUser/mymodify",newparam,function(data){
                    //1.给用户展示处理结果
                    var alertcls = "";
                    //判断弹出框属性
                    if(data.returnCode==10000){
                        alertcls = "alert-success";
                    }else{
                        alertcls = "alert-danger";
                    }
                    //写出弹出框html代码  将alertcls属性加入
                    var content = '<div class="alert '+alertcls+' alert-dismissible fade in" role="alert">' +
                        '        <button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">×</span></button>' +
                        '        <h4>操作结果</h4>' +
                        '        <p>'+data.returnMSG+'</p>' +
                        '    </div>'                      
                    $("#myalert").append(content);                   //添加弹出框html代码

                    //2.重新查询 刷新数据
                    var params = {"quname":$("#quname").val(),"quremark":$("#quremark").val(),
                                  "page":page,"pageSize":pageSize};
                    myQuery(params);
                },"json")
            })
        })

        //控制上一页  下一页按钮显示
        function initBtn(){
            $("#nextBtn").prop("disabled",false);
            $("#prevBtn").prop("disabled",false);

            if(page<=1){
                $("#prevBtn").prop("disabled",true);
            }
            if(page>=totalPage){
                $("#nextBtn").prop("disabled",true);
            }
        }

        function myQuery(params){
            $.post("ajaxUser/myquery",params,function(data){
                //清空之前查询的数据
                $("#mainData").html("");
                if(data.returnCode==10000){
                    //判断数据 正确
                    //遍历加入数据库数据
                    $.each(data.returnData,function(i,d){
                        var content = '<tr><td>'+d.userId+'</td><td>'+d.userName
                            +'</td><td>'+d.userPwd+'</td><td>'+d.userPhone
                            +'</td><td>'+d.userRemark+'</td></tr>'

                        $("#mainData").append(content);
                    })
                    //控制选中行的颜色  并将数据填入selectRow[]
                    $("#mainData tr").click(function(){
                        $("#mainData tr").removeClass("success");
                        $(this).addClass("success");
                        selectRow = [];
                        $(this).find("td").each(function(i){
                            selectRow.push($(this).html());
                        })
                    })
                   
                    page = data.returnPif.page;
                    pageSize = data.returnPif.pageSize;
                    totalPage =   data.returnPif.totalPage;

                    $("#totalPageSpan").html(totalPage);
                    $("#pageSpan").html(page);
                    $("#pageSizeSpan").html(pageSize);

                    initBtn();

                }else if(data.returnCode==21000){
                    $("#mainData").html("没有数据");
                    
                    page = data.returnPif.page;
                    pageSize = data.returnPif.pageSize;
                    totalPage =   data.returnPif.totalPage;

                    $("#totalPageSpan").html(totalPage);
                    $("#pageSpan").html(page);
                    $("#pageSizeSpan").html(pageSize);

                    initBtn();
                }
            },"json")
        }


    </script>
    <style>
        #myalert{
            width: 200px;
            height: 150px;
            position: absolute;
            left: 30%;
            top:100px;
        }
    </style>
</head>
<body>
<div class="container">
    <div class="row">
        <form class="form-inline">
            <div class="form-group">
                <label for="quname">用户名</label>
                <input type="text" class="form-control" id="quname" name="quname" value="" placeholder="Jane Doe">
            </div>
            <div class="form-group">
                <label for="quremark">备注</label>
                <input type="text" class="form-control" id="quremark" name="quremark" value="" placeholder="备注信息">
            </div>
            <button id="submitBtn" type="button" class="btn btn-success">查询</button>
            <button id="resetBtn" type="reset" class="btn btn-success">重置</button>
            <!-- Button trigger modal -->
            //data-target 绑定相应的模态框
            <button type="button" class="btn btn-info" data-toggle="modal" data-target="#myAddModal">
                添加
            </button>
            <button id="editBtn" type="button" class="btn btn-warning" data-toggle="modal" 
                data-target="#myEditModal">
                修改
            </button>
        </form>
        <table class="table">
            <thead>
            <tr><th>用户id</th><th>姓名</th><th>密码</th><th>电话</th><th>备注</th></tr>
            </thead>
            <tbody id="mainData">

            </tbody>
        </table>
        <input id="prevBtn" class="btn btn-default" type="button" value="上一页">
        <input id="nextBtn" class="btn btn-default" type="button" value="下一页"><br>
        总共<span id="totalPageSpan"></span> 页， 当前第<span id="pageSpan"></span>页，
        每页显示<span id="pageSizeSpan"></span>条记录
    </div>
</div>



        <!-- Modal -->
        <div class="modal fade" id="myAddModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel">
            <div class="modal-dialog" role="document">
                <div class="modal-content">
                    <div class="modal-header">
                        <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
                        <h4 class="modal-title" id="myModalLabel">添加</h4>
                    </div>
                    <div class="modal-body">
                        <form >
                            <div class="form-group">
                                <label for="auid">用戶id</label>
                                <input type="text" class="form-control" name="auid" id="auid" placeholder="xxx">
                            </div>
                            <div class="form-group">
                                <label for="auname">用戶名</label>
                                <input type="text" class="form-control"  name="auname" id="auname" placeholder="xxx">
                            </div>
                            <div class="form-group">
                                <label for="aupwd">密碼</label>
                                <input type="text" class="form-control"  name="aupwd"   id="aupwd" placeholder="xx">
                            </div>
                            <div class="form-group">
                                <label for="auphone">手機號</label>
                                <input type="text" class="form-control"  name="auphone"  id="auphone" placeholder="xxx">
                            </div>
                            <div class="form-group">
                                <label for="auremark">备注</label>
                                <input type="text" class="form-control"  name="auremark"  id="auremark" placeholder="xxx">
                            </div>

                        </form>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-default" data-dismiss="modal">关闭</button>
                        <button type="button" class="btn btn-primary" data-dismiss="modal">保存</button>
                    </div>
                </div>
            </div>
        </div>



<!-- Modal -->
<div class="modal fade" id="myEditModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel">
    <div class="modal-dialog" role="document">
        <div class="modal-content">
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
                <h4 class="modal-title">修改</h4>
            </div>
            <div class="modal-body">
                <form id="editform">
                    <div class="form-group">
                        <label for="euid">用戶id</label>
                        <input type="text" class="form-control" name="euid" id="euid" readonly placeholder="xxx">
                    </div>
                    <div class="form-group">
                        <label for="euname">用戶名</label>
                        <input type="text" class="form-control"  name="euname" id="euname" placeholder="xxx">
                    </div>
                    <div class="form-group">
                        <label for="eupwd">密碼</label>
                        <input type="text" class="form-control"  name="eupwd"   id="eupwd" placeholder="xx">
                    </div>
                    <div class="form-group">
                        <label for="euphone">手機號</label>
                        <input type="text" class="form-control"  name="euphone"  id="euphone" placeholder="xxx">
                    </div>
                    <div class="form-group">
                        <label for="euremark">备注</label>
                        <input type="text" class="form-control"  name="euremark"  id="euremark" placeholder="xxx">
                    </div>

                </form>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-default" data-dismiss="modal">关闭</button>
                <button id="editSubmit" type="button" class="btn btn-primary" data-dismiss="modal">保存</button>
            </div>
        </div>
    </div>
</div>
<div id="myalert">                     //放弹出框代码
</div>
</body>
</html>

<%--
  Created by IntelliJ IDEA.
  User: JAVASM
  Date: 2020/8/18
  Time: 11:14
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
    <link rel="stylesheet" href="${pageContext.request.contextPath}/css/bootstrap.min.css">
</head>
<body>
<div class="container">
    <div class="row">
        <div class="col-lg-offset-3 col-lg-6">
            <form action="${pageContext.request.contextPath}/user/mymodify" method="post">
                <div class="form-group">
                    <label for="auid">用戶id</label>
                    <input type="text" class="form-control" value="${myuser.userId}" readonly name="auid" id="auid" placeholder="xxx">
                </div>
                <div class="form-group">
                    <label for="auname">用戶名</label>
                    <input type="text" class="form-control" value="${myuser.userName}"  name="auname" id="auname" placeholder="xxx">
                </div>
                <div class="form-group">
                    <label for="aupwd">密碼</label>
                    <input type="text" class="form-control" value="${myuser.userPwd}"  name="aupwd"   id="aupwd" placeholder="xx">
                </div>
                <div class="form-group">
                    <label for="auphone">手機號</label>
                    <input type="text" class="form-control" value="${myuser.userPhone}"  name="auphone"  id="auphone" placeholder="xxx">
                </div>
                <div class="form-group">
                    <label for="auremark">备注</label>
                    <input type="text" class="form-control" value="${myuser.userRemark}"  name="auremark"  id="auremark" placeholder="xxx">
                </div>
                <button type="submit" class="btn btn-default">保存</button>
            </form>
        </div>
    </div>
</div>

</body>
</html>

fade包

//将每个方法都需要调用的步骤抽离出来作为父类
//基础类继承HttpServlet 子类直接继承基础类 就不用继承HttpServlet了
//子类继承后就不用重复写了
//这个就是判断前端传过来哪个方法
public class BaseServlet extends HttpServlet {
    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println(req.getServletPath());//servlet路径
        //用户输入的请求路径    //ajax传过来的
        String requri = req.getRequestURI();
        String methodName = requri.substring(requri.lastIndexOf("/")+1);
        try {
            //用路径中的最后一段 决定调用哪个方法
            // 从路径中的最后一段  抽取出方法对象
            //再用被调用的子类实例 调用方法
            Method mymethod = this.getClass().getMethod
                (methodName,HttpServletRequest.class,HttpServletResponse.class);
            mymethod.invoke(this,req,resp);
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
            //异常信息告知给tomcat 让tomcat给用户报错
            resp.sendError(HttpServletResponse.SC_NOT_FOUND);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
    }
}

@WebServlet("/ajaxUser/*")   //  "/ajaxUser/*"   * 代表ajaxUser里的所有方法的通配
public class AjaxUserServlet extends BaseServlet {

    public void myquery(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //查询用户列表
        req.setCharacterEncoding("utf-8");              //调整前端数据传来的格式
        UserService us = new UserServiceImpl();         
        //设置默认值 防止访问时不传参报错
        Integer page = 1;        Integer pageSize = 3;

        String pageStr = req.getParameter("page");            //获取前端传来的页数
        String pageSizeStr = req.getParameter("pageSize");    //获取前端传来的一页里有多少个
        //如果请求中带参数 用带的参数覆盖默认值
        if(pageStr!=null&&!"".equals(pageStr)){               //判断数据是否正确
            page = Integer.parseInt(pageStr);               
        }
        if(pageSizeStr!=null&&!"".equals(pageSizeStr)){       //判断数据是否正确
            pageSize = Integer.parseInt(pageSizeStr);
        }

        String quname = req.getParameter("quname");            
        String quremark = req.getParameter("quremark");
        User quser = new User(quname,quremark);               //将前端数据实例化为对象

        List<User> allUser = us.getAllUser(page,pageSize,quser);   //根据数据从数据库取数据
        Integer totalPage = us.getTotalPage(pageSize,quser);       //获得总页数

        PageInfo pif = new PageInfo(page,pageSize,totalPage);       //将页数,页码,总页数实例化对象
        ReturnEntity returnEntity = new ReturnEntity();             //实例化对象返回对象returnEntity
        if(allUser.size()>0){                                       //将数据放入实例化对象returnEntity中
            returnEntity.setReturnCode(ReturnCode.SUCCESS.getCode());
            returnEntity.setReturnMSG(ReturnCode.SUCCESS.getMsg());
            returnEntity.setReturnData(allUser);
            returnEntity.setReturnPif(pif);
        }else {                                                   
            returnEntity.setReturnCode(ReturnCode.NO_DATA.getCode());
            returnEntity.setReturnMSG(ReturnCode.NO_DATA.getMsg());
            returnEntity.setReturnPif(pif);
        }
        resp.setContentType("text/html;charset=utf-8");        //规范返回对象
        PrintWriter out = resp.getWriter();
        //返回json格式字符串
        out.print(JSON.toJSONString(returnEntity));           
        out.flush();
        out.close();
    }


    public void getoneuser(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("准备用户修改数据");
        //获取单个用户
        String uid =  req.getParameter("uid");     //获取id
        UserService us = new UserServiceImpl();
        User user = us.getOneUser(Integer.parseInt(uid));
        //放回数据放入了属性名为"myuser" , 内容是user对象
        req.setAttribute("myuser",user);
        //请求转发 通过request对象调用，把请求对象和响应对象与要跳转的地址共享
        getRequestDispatcher("/editUser.jsp").forward(req,resp);

    }
    public void mymodify(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("执行用户的修改");
        //执行修改语句
        //取参数保存
        /*auid: 8
            auname: 宇智波·刘能123
            aupwd: abc23
            auphone: 2222344444
            auremark: 舞王同伙*/
        req.setCharacterEncoding("utf-8");
        String auid = req.getParameter("euid");
        String auname = req.getParameter("euname");
        String aupwd = req.getParameter("eupwd");
        String auphone = req.getParameter("euphone");
        String auremark = req.getParameter("euremark");

        User user = new User(Integer.parseInt(auid), auname, aupwd, auphone, auremark);
        UserServiceImpl us = new UserServiceImpl();
        Boolean res = us.editUser(user);

        ReturnEntity returnEntity = new ReturnEntity();
        if(res){
            returnEntity.setReturnCode(ReturnCode.SUCCESS.getCode());
            returnEntity.setReturnMSG(ReturnCode.SUCCESS.getMsg());
        }else {
            returnEntity.setReturnCode(ReturnCode.NO_DATA.getCode());
            returnEntity.setReturnMSG(ReturnCode.NO_DATA.getMsg());
        }
        resp.setContentType("text/html;charset=utf-8");
        PrintWriter out = resp.getWriter();
        //返回json格式字符串
        out.print(JSON.toJSONString(returnEntity));
        out.flush();
        out.close();
    }

    public void mycreate(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("执行用户的创建");
        //执行insert语句

    }

    public void myremove(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("执行用户的删除");
        //执行删除语句
    }
}

dao 包

public interface UserDao {
    List<User> getUserByPage(Integer page,Integer pageSize,User queryUser);
    List<User> getAllUser(User queryUser);
    User getOneUser(Integer uid);
    Integer editUser(User editUser);
}



public class UserDaoImpl implements UserDao {
    @Override
    public Integer editUser(User editUser) {
        Connection conn = DBHelper.getConn();
        String sql = "update tb_user set user_name = ?,user_pwd = ?,user_phone = ?,user_remark = ?" +
                     "where user_id = ?";

        System.out.println(sql);
        User myuser = null;
        PreparedStatement psta = null;
        ResultSet rs = null;
        int resnum = 0;
        try {
            psta = conn.prepareStatement(sql);
            psta.setString(1,editUser.getUserName());
            psta.setString(2,editUser.getUserPwd());
            psta.setString(3,editUser.getUserPhone());
            psta.setString(4,editUser.getUserRemark());
            psta.setInt(5,editUser.getUserId());
            resnum = psta.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBHelper.CloseConn(conn,null,psta,rs);
        }
        return resnum;
    }

    @Override
    public User getOneUser(Integer uid) {
        Connection conn = DBHelper.getConn();
        String sql = "select tu.user_id,tu.user_name,tu.user_pwd,tu.user_phone,tu.user_remark from tb_user tu where tu.user_id = ?";
        System.out.println(sql);
        User myuser = null;
        PreparedStatement psta = null;
        ResultSet rs = null;
        try {
            psta = conn.prepareStatement(sql);
            psta.setInt(1,uid);
            rs = psta.executeQuery();
            while(rs.next()){
                Integer userId = rs.getInt("user_id");
                String userName = rs.getString("user_name");
                String userPwd = rs.getString("user_pwd");
                String userPhone = rs.getString("user_phone");
                String userRemark = rs.getString("user_remark");
                myuser = new User(userId, userName, userPwd, userPhone, userRemark);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBHelper.CloseConn(conn,null,psta,rs);
        }
        return myuser;
    }

    @Override
    public List<User> getUserByPage(Integer page,Integer pageSize,User queryUser) {
        Connection conn = DBHelper.getConn();
        String sql = "select tu.user_id,tu.user_name,tu.user_pwd,tu.user_phone,tu.user_remark from tb_user tu ";

      /*  "where" " tu.user_name = ?"
          "and" " tu.user_remark like ?"*/
        boolean isWhere = true;
        List params = new ArrayList();
        if(queryUser.getUserName()!=null&&!"".equals(queryUser.getUserName())){
            if(isWhere){
                sql+=" where ";
                isWhere = false;
            }else{
                sql+=" and ";
            }

            sql+=" tu.user_name = ? ";
            params.add(queryUser.getUserName());
        }
        if(queryUser.getUserRemark()!=null&&!"".equals(queryUser.getUserRemark())){
            if(isWhere){
                sql+=" where ";
                isWhere = false;
            }else{
                sql+=" and ";
            }
            sql+=" tu.user_remark like ? ";
            params.add("%"+queryUser.getUserRemark()+"%");
        }
        sql+="limit "+(page-1)*pageSize+","+pageSize;
        System.out.println(sql);
        List<User> lu = new ArrayList<User>();
        PreparedStatement psta = null;
        ResultSet rs = null;
        try {
            psta = conn.prepareStatement(sql);
            //循环传参
            //上面有两个条件可能同时使用 可能只使用其中一个 使得参数是一个变量
            //这样PreparedStatement操作不了变量  这时候可以将参数放进params数组里 有几个条件就放进去
            //到时候执行sql语句就从数组里取出来 这时候就间接是静态的
            for(int i = 0;i<params.size();i++){
                psta.setObject(i+1,params.get(i));
            }
            rs = psta.executeQuery();
            while(rs.next()){
                Integer userId = rs.getInt("user_id");
                String userName = rs.getString("user_name");
                String userPwd = rs.getString("user_pwd");
                String userPhone = rs.getString("user_phone");
                String userRemark = rs.getString("user_remark");
                User myuser = new User(userId, userName, userPwd, userPhone, userRemark);
                lu.add(myuser);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBHelper.CloseConn(conn,null,psta,rs);
        }
        return lu;
    }

    @Override
    public List<User> getAllUser(User queryUser) {
        Connection conn = DBHelper.getConn();
        String sql = "select tu.user_id,tu.user_name,tu.user_pwd,tu.user_phone,tu.user_remark from tb_user tu";
              /*  "where" " tu.user_name = ?"
          "and" " tu.user_remark like ?"*/
        boolean isWhere = true;
        List params = new ArrayList();
        if(queryUser.getUserName()!=null&&!"".equals(queryUser.getUserName())){
            if(isWhere){
                sql+=" where ";
                isWhere = false;
            }else{
                sql+=" and ";
            }
            sql+=" tu.user_name = ? ";
            params.add(queryUser.getUserName());
        }
        if(queryUser.getUserRemark()!=null&&!"".equals(queryUser.getUserRemark())){
            if(isWhere){
                sql+=" where ";
                isWhere = false;
            }else{
                sql+=" and ";
            }
            sql+=" tu.user_remark like ? ";
            params.add("%"+queryUser.getUserRemark()+"%");
        }
        List<User> lu = new ArrayList<User>();
        PreparedStatement psta = null;
        ResultSet rs = null;
        try {
            psta = conn.prepareStatement(sql);
            //循环传参
            for(int i = 0;i<params.size();i++){
                psta.setObject(i+1,params.get(i));
            }
            rs = psta.executeQuery();
            while(rs.next()){
                Integer userId = rs.getInt("user_id");
                String userName = rs.getString("user_name");
                String userPwd = rs.getString("user_pwd");
                String userPhone = rs.getString("user_phone");
                String userRemark = rs.getString("user_remark");
                User myuser = new User(userId, userName, userPwd, userPhone, userRemark);
                lu.add(myuser);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBHelper.CloseConn(conn,null,psta,rs);
        }
        return lu;
    }
}

service包

//service是业务层   操作dao数据访问层传来的数据
public interface UserService {
    List<User> getAllUser(Integer page,Integer pageSize,User queryUser);
    Integer getTotalPage(Integer pageSize,User queryUser);
    User getOneUser(Integer uid);
    Boolean editUser(User editUser);
}


public class UserServiceImpl implements UserService {

    @Override
    public Boolean editUser(User editUser) {
        UserDao ud = new UserDaoImpl();
        Integer resnum = ud.editUser(editUser);    
        Boolean flag = false;
        if(resnum>=1){
            flag = true;
        }
        return flag;
    }

    @Override
    public List<User> getAllUser(Integer page,Integer pageSize,User queryUser) {
        UserDao ud = new UserDaoImpl();
        return ud.getUserByPage(page,pageSize,queryUser);
    }

    @Override
    public Integer getTotalPage(Integer pageSize,User queryUser) {
        UserDao ud = new UserDaoImpl();
         List<User> lu = ud.getAllUser(queryUser);
        Integer totalNum = lu.size();
        Integer totalPage = 0;
        if(lu.size()!=0){
            totalPage = totalNum%pageSize>0?totalNum/pageSize+1:totalNum/pageSize;
        }

        //用总记录数 和每页显示记录数 算总页数
        // 总记录数与每页记录取余数 看是否有余数   有余数 totalNum/pageSize+1  没余数 totalNum/pageSize

        return totalPage;
    }


    @Override
    public User getOneUser(Integer uid) {
        UserDao ud = new UserDaoImpl();
        return ud.getOneUser(uid);
    }
}

entity包

public class PageInfo {

    private Integer page;
    private Integer pageSize;
    private Integer totalPage;

    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;
    }


    public PageInfo(Integer page, Integer pageSize, Integer totalPage) {
        this.page = page;
        this.pageSize = pageSize;
        this.totalPage = totalPage;
    }

    public PageInfo() {
    }
}

public enum ReturnCode {

    SUCCESS(10000,"操作成功"),
    UNAME_OK(10001,"用户名可用"),
    UNAME_DUPLICATE(20001,"用户名重复"),
    FAILED(20000,"操作失败"),
    NO_DATA(21000,"没有数据");

    Integer code;
    String msg;

    private ReturnCode(Integer code,String msg){
        this.code = code;
        this.msg = msg;
    }

    public Integer getCode() {
        return code;
    }

    public String getMsg() {
        return msg;
    }
}

public class ReturnEntity {
    //returnCode  returnMSG   returnData
    private Integer returnCode;
    private String returnMSG;
    private List returnData;
    private PageInfo returnPif;

    public PageInfo getReturnPif() {
        return returnPif;
    }

    public void setReturnPif(PageInfo returnPif) {
        this.returnPif = returnPif;
    }

    public Integer getReturnCode() {
        return returnCode;
    }

    public void setReturnCode(Integer returnCode) {
        this.returnCode = returnCode;
    }

    public String getReturnMSG() {
        return returnMSG;
    }

    public void setReturnMSG(String returnMSG) {
        this.returnMSG = returnMSG;
    }

    public List getReturnData() {
        return returnData;
    }

    public void setReturnData(List returnData) {
        this.returnData = returnData;
    }

    public ReturnEntity(Integer returnCode, String returnMSG, List returnData) {
        this.returnCode = returnCode;
        this.returnMSG = returnMSG;
        this.returnData = returnData;
    }

    public ReturnEntity() {

    }

    @Override
    public String toString() {
        return "ReturnEntity{" +
                "returnCode=" + returnCode +
                ", returnMSG='" + returnMSG + '\'' +
                ", returnData=" + returnData +
                ", returnPif=" + returnPif +
                '}';
    }
}

public class User {
    private Integer userId;
    private String userName;
    private String userPwd;
    private String userPhone;
    private String userRemark;

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getUserPwd() {
        return userPwd;
    }

    public void setUserPwd(String userPwd) {
        this.userPwd = userPwd;
    }

    public String getUserPhone() {
        return userPhone;
    }

    public void setUserPhone(String userPhone) {
        this.userPhone = userPhone;
    }

    public String getUserRemark() {
        return userRemark;
    }

    public void setUserRemark(String userRemark) {
        this.userRemark = userRemark;
    }

    public User(String userName, String userRemark) {
        this.userName = userName;
        this.userRemark = userRemark;
    }


    public User(Integer userId, String userName, String userPwd, String userPhone, String userRemark) {
        this.userId = userId;
        this.userName = userName;
        this.userPwd = userPwd;
        this.userPhone = userPhone;
        this.userRemark = userRemark;
    }

    public User() {
    }

    @Override
    public String toString() {
        return "User{" +
                "userId=" + userId +
                ", userName='" + userName + '\'' +
                ", userPwd='" + userPwd + '\'' +
                ", userPhone='" + userPhone + '\'' +
                ", userRemark='" + userRemark + '\'' +
                '}';
    }
}

util包

public class DBHelper {
	static String username;
	static String pwd;
	static String url;
	static String drivername;
	
	
	static {
		Properties p = new Properties();
		try {
			//程序运行时 不一定在你的工程目录的编译目录下
			p.load(DBHelper.class.getResourceAsStream("/jdbc.properties"));
			username = p.getProperty("jdbc.user");
			pwd = p.getProperty("jdbc.pass");
			url = p.getProperty("jdbc.url");
			drivername = p.getProperty("jdbc.driver");
			Class.forName(drivername);		
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static Connection getConn() {
		Connection con = null;
		
		try {
			con = DriverManager.getConnection(url, username, pwd);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}
	
	
	public static void CloseConn(Connection conn,Statement stat,PreparedStatement psta,ResultSet rs){		
			try {
				if(stat!=null)stat.close();
				if(psta!=null)psta.close();
				if(rs!=null)rs.close();
				if(conn!=null)conn.close();			
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}
}

src下 jdbc,protites

jdbc.user = root
jdbc.pass = root
jdbc.url = jdbc:mysql://127.0.0.1:3306/my_db?characterEncoding=utf8&useSSL=true
jdbc.driver = com.mysql.jdbc.Driver

我丶秦始皇丶打钱

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
10-分页

分页1.分页实现思路1.查询列表时默认显示固定数量的数据2.算出当前符合条件的所有数据的总页码数3.显示上一页下一页按钮4.每次点击按钮是判断是否是首尾页分页功能需要页面显示与数据查询同步进行。分页的功能需要由数据库语句体现，mysql数据库中分条显示数据语句：select uid,uname,upwd,unick_name,uage,uaddr from my_user limit 0 , 5如果page从1开始，那么这两个值的关系 (page-1
复制链接

扫一扫