Java分页查询


阅读数: 次   

Java分页查询，可自行调整页数

💖💛💙💚👩‍💻

一.首先需要创建一个BaseDao

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
package Dao;

import javax.naming.InitialContext;
import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class BaseDao {

    static Connection con = null;
    static PreparedStatement pst = null;
    static ResultSet rs = null;

    static DataSource source = null;
    static {
        try {
            InitialContext init = new InitialContext();
            source = (DataSource)init.lookup("java:comp/env/jdbc/test");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static Connection getConnection(){
        try {
            con = source.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

    private static void setParam(PreparedStatement pst,Object... objects){
        if(objects != null){
            for(int i = 0;i<objects.length;i++){
                try {
                    pst.setObject(i+1,objects[i]);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private static void Close(Connection con,PreparedStatement pst,ResultSet rs){
        try{
            if(rs != null)rs.close();
            if(pst != null)pst.close();
            if(con != null)con.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public static int update(String sql,Object... objects){
        con = getConnection();
        try {
            pst = con.prepareStatement(sql);
            setParam(pst,objects);
            return pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Close(con,pst,rs);
        }
        return 0;
    }

    public static List<Map<String,Object>> query(String sql,Object... object){
        List<Map<String,Object>> list = new ArrayList<>();
        con = getConnection();
        try {
            pst = con.prepareStatement(sql);
            setParam(pst,object);
            rs = pst.executeQuery();
            ResultSetMetaData rst = rs.getMetaData();
            while(rs.next()){
                Map<String,Object> map = new HashMap<>();
                for(int i=0;i<rst.getColumnCount();i++){
                    map.put(rst.getColumnName(i+1),rs.getObject(i+1));
                }
                list.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Close(con,pst,rs);
        }
        return list;
    }
}

这是运用 连接池 连接数据库的，所以要创建一个context.xml文件

img

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?xml version="1.0" encoding="UTF-8"?>

<Context>

     <Resource
             name="jdbc/test"
             auth="Application"
             type="javax.sql.DataSource"
             maxActive="100"
             maxIdle="30"
             maxWait="10000"
             username="root"
             password="admin"
             driverClassName="com.mysql.cj.jdbc.Driver"
             url="jdbc:mysql://localhost:3306/java_web?useSSL=false&amp;serverTimezone=UTC"/>
</Context>

username：连接数据库的账号

password：链接数据库的密码

二.添加一个分页需要的实体类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
package Entity;

import java.util.List;
import java.util.Map;

public class PageBean {
    private int curPage;//当前页
    private int totalPage;//总页数
    private List<Map<String,Object>> list;

    public PageBean(int curPage, int totalPage, List<Map<String, Object>> list) {
        this.curPage = curPage;
        this.totalPage = totalPage;
        this.list = list;
    }

    public PageBean() {
    }

    public int getCurPage() {
        return curPage;
    }

    public void setCurPage(int curPage) {
        this.curPage = curPage;
    }

    public int getTotalPage() {
        return totalPage;
    }

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

    public List<Map<String, Object>> getList() {
        return list;
    }

    public void setList(List<Map<String, Object>> list) {
        this.list = list;
    }

    @Override
    public String toString() {
        return "PageBean{" +
                "curPage=" + curPage +
                ", totalPage=" + totalPage +
                ", list=" + list +
                '}';
    }
}

三.创建一个分页需要的服务层

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
package service;

import Dao.ClientDao;
import Dao.ClientImpl;
import Entity.PageBean;
import Tool.PageController;

public class UserService {
    //获取PageBean对象
    public static PageBean getPageBean(int curPage,int pageSize){
        PageBean pageBean = new PageBean();
        //当前页数
        pageBean.setCurPage(curPage);
        //总页数
        ClientDao client = new ClientImpl();
        int totalCount  = client.queryAll().size();
        //有多少页
        int totalPage = PageController.getTotalPage(totalCount,pageSize);
        pageBean.setTotalPage(totalPage);
        //当前页查询数据
        pageBean.setList(client.query(curPage,pageSize));
        return pageBean;
    }
}

四.创建一个通用的工具类

src

1
2
3
4
5
6
7
8
9
10
11
12
13
package Tool;

public class PageController {
    //判断总行数除以每页行数是否有余数
    public static  int getTotalPage(int count,int pageSize){
        if(count%pageSize == 0){
            return count/pageSize;
        }else{
            //如果有余，则多加一页
            return count/pageSize+1;
        }
    }
}

五.编写一个servlet控制层

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
package servlet;

import Entity.PageBean;
import com.alibaba.fastjson.JSON;
import service.UserService;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

@WebServlet(name = "InfoServlet",urlPatterns = "/info")
public class InfoServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html;charset=utf-8");
        request.setCharacterEncoding("utf-8");
        int curPage = 0;//第几页
        int pageSize = 5;//一页默认有几个
        //获取修改后一页的行数
        if(request.getParameter("down")!=null){
            pageSize = Integer.parseInt(request.getParameter("down"));
        }
        //获取请求页数
        String curPageStr = request.getParameter("curPage");
        if(curPageStr == null){
            curPageStr = "1";
        }
        //转换为整数
        curPage = Integer.parseInt(curPageStr);
        //调用服务层方法，整合分页实体类
        PageBean pageBean = UserService.getPageBean(curPage,pageSize);
        //将该实体类转化为JSON格式，转发给前端服务
        response.getWriter().print(JSON.toJSONString(pageBean));
    }
}

六.最后前端的jsp如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
<%--
  Created by IntelliJ IDEA.
  User: 腾飞
  Date: 2019/11/22
  Time: 16:33
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
  <head>
    <title>分页显示</title>
    <script type="text/javascript" src="js/jquery1.7.2.js"></script>
  </head>
  <body>
    <table border="1" cellspacing="0">
      <thead>
      <tr>
        <th>用户编号</th>
        <th>用户姓名</th>
        <th>用户性别</th>
        <th>用户电话</th>
      </tr>
      </thead>
      <tbody id="hq">
      </tbody>
    </table>
  </body>
  <script>
      $(
          function () {
              queryPage(1,5)
          }
      )
      var xuan;
      function queryPage(page,down) {
          $.ajax(
              {
                  url:"${pageContext.request.contextPath}/info",
                  type:"get",
                  data:{curPage:page,down:down},
                  dataType:"json",
                  success:function (msg) {
                      var list = msg.list;
                      if(list.length>0){
                          $("#hq").empty();
                          for(var i=0;i<list.length;i++){
                              var row = list[i];//取出一行数据
                              //row.name  row.pwd
                              var str = "<tr>"
                              str+="<td>";
                              str+=row.id;
                              str+="</td>";

                              str+="<td>";
                              str+=row.name;
                              str+="</td>";

                              str+="<td>";
                              str+=row.sex;
                              str+="</td>";

                              str+="<td>";
                              str+=row.number;
                              str+="</td>";

                              str+="</tr>";
                              $("#hq").append(str);
                          }

                          var last = "<tr>"
                          last+="<td colspan='4'>"

                          //选择每页显示行数
                          last+="一页<select id='down'>"
                          if(xuan==5){
                              last+="<option value='5' selected>5</option>"
                          }else{
                              last+="<option value='5'>5</option>"
                          }
                          if(xuan==10){
                              last+="<option value='10' selected>10</option>"
                          }else{
                              last+="<option value='10'>10</option>"
                          }
                          if(xuan==15){
                              last+="<option value='15' selected>15</option>"
                          }else{
                              last+="<option value='15'>15</option>"
                          }
                              last+="<select>行"

                          //当前第几页
                          last+="第"+msg.curPage+"页/共"+msg.totalPage+"页||"

                          //首页
                          last+="<a href='javascript:void(0)' onclick='queryPage(1)'>首页</a>||"

                          //上一页
                          if(msg.curPage>1){
                              last+="<a href='javascript:void(0)' onclick='queryPage("+(msg.curPage-1)+")'>上一页</a>||";
                          }
                          //下一页
                          if(msg.curPage<msg.totalPage){
                              last+="<a href='javascript:void(0)' onclick='queryPage("+(msg.curPage+1)+")'>下一页</a>||";
                          }

                          //末页
                          last+="<a href='javascript:void(0)' onclick='queryPage("+(msg.totalPage)+")'>末页</a>";

                          last+="</td>"
                          last+="</tr>";
                          $("#hq").append(last);
                      }
                      $("#down").change(function (){
                          xuan = $("#down option:selected").val();
                          //修改选中值
                          // $(this).find('option[value='+$("#down option:selected").val()+']').attr("selected",true);
                          //重新加载事件
                          queryPage(1,$("#down option:selected").val());
                      })

                  }
              }
          )
      }
  </script>
</html>

七.总结

  • 下拉框（select）的响应时间是.change
  • 怎样让下拉框选中的值变为默认值?
    • 因为下拉框会在queryPage方法加载后刷新
    • 所以只好创建一个全局变量，记录
赏

感谢支持!

微信

扫一扫，分享到微信

微信分享二维码

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true

正因为没有翅膀，<br>人们才会寻找飞翔的方法。