jsp 条件查询、列表分页

2021-09-11 20:30:06

条件查询

dao

    //根据搜索条件筛选数据

    public List<User> GetUserBySearch(String userName, String sex) throws SQLException {

        String sql = "select * from User where 1=1 ";

        List<String> params = new ArrayList<String>();

        if (userName != null && userName != "") {

            sql += " and UserName like ?";

            params.add("%"+userName+"%");

        }

        if (sex != null && sex != "") {

            sql += " and sex = ?";

            params.add(sex);

        }

        QueryRunner qr = new QueryRunner(DBUtil.GetDataSource());

        List<User> users = qr.query(sql, new BeanListHandler<User>(User.class), params.toArray());

        return users;

    }

service

   public List<User> GetUserBySearch(String userName,String sex) throws SQLException {

        UserDao dao = new UserDao();

        return dao.GetUserBySearch(userName,sex);

    }

userList servlet

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        String userName = request.getParameter("userName");

        String sex = request.getParameter("sex");

        UserService service = new UserService();

        List<User> userList = null;

        try {

            //userList = service.GetAllService();

            userList = service.GetUserBySearch(userName,sex);

        } catch (SQLException e) {

            e.printStackTrace();

        }

        request.setAttribute("userName",userName);

        request.setAttribute("sex",sex);

        request.setAttribute("userList", userList);

        request.getRequestDispatcher("userList.jsp").forward(request,response);

    }

userList.jsp

<%@ page import="com.david.domain.User" %>

<%@ page import="java.util.List" %>

<%@ page contentType="text/html;charset=UTF-8" language="java" %>

<html>

<head>

    <title>用户列表</title>

</head>

<body>

<div>

    按用户名称搜索：<input id="txtUserName" value="${userName}"> &nbsp;

    按用户性别查找：<select id="selSex">

    <option value="">全部</option>

    <option value="男">男</option>

    <option value="女">女</option>

</select> &nbsp;

<button type="button" onclick="search()">搜索</button>

</div>

<script>

    //设置默认值

    window.onload = function (){

        for(var i = 0;i<document.getElementById("selSex").options.length;i++){

            var option = document.getElementById("selSex").options[i];

            if(option.value == "${sex}"){

                option.selected = true;

            }

        }

    }

    function search(){

        var userName = document.getElementById("txtUserName").value;

        var sex = "";

        for(var i = 0;i<document.getElementById("selSex").options.length;i++){

            if(document.getElementById("selSex").options[i].selected){

                sex = document.getElementById("selSex").options[i].value;

            }

        }

        location.href='userList?userName='+userName+"&sex="+sex;

    }

</script>

<table>

    <tr>

        <th>用户ID</th>

        <th>用户名</th>

        <th>用户密码</th>

        <th>用户年龄</th>

        <th>用户性别</th>

        <th>操作</th>

    </tr>

    <%

        if (request.getAttribute("userList") != null) {

    %>

    <%for (User u : (List<User>) request.getAttribute("userList")) {%>

    <tr>

        <td><%=u.getUserId()%>

        </td>

        <td><%=u.getUserName()%>

        </td>

        <td><%=u.getPassWord()%>

        </td>

        <td><%=u.getAge()%>

        </td>

        <td><%=u.getSex()%>

        </td>

        <td>

            <a href="GetUserById?userId=<%=u.getUserId()%>">修改</a>

            <a href="DeleteUser?userId=<%=u.getUserId()%>">删除</a>

        </td>

    </tr>

    <%}%>

    <%}%>

</table>

<a href="AddUser.jsp">添加用户</a>

</body>

</html>

列表分页

在domain中创建pageBean实体

package com.david.domain;

import java.util.List;

public class PageBean<T> {

    //当前页

    private int curPage;

    //当前显示条数

    private int pageSize;

    //总页数

    private int totalPage;

    //总条数

    private int totalCount;

    //展示的数据

    private List<T> Data;

    public int getCurPage() {

        return curPage;

    }

    public int getPageSize() {

        return pageSize;

    }

    public int getTotalPage() {

        return totalPage;

    }

    public int getTotalCount() {

        return totalCount;

    }

    public List<T> getData() {

        return Data;

    }

    public void setCurPage(int curPage) {

        this.curPage = curPage;

    }

    public void setPageSize(int pageSize) {

        this.pageSize = pageSize;

    }

    public void setTotalPage(int totalPage) {

        this.totalPage = totalPage;

    }

    public void setTotalCount(int totalCount) {

        this.totalCount = totalCount;

    }

    public void setData(List<T> data) {

        Data = data;

    }

}

在dao中创建分页方法

//获取总条数

    public int GetUserCount(String userName, String sex) throws SQLException {

        String sql = "select count(*) from User where 1=1 ";

        List<String> params = new ArrayList<String>();

        if (userName != null && userName != "") {

            sql += " and UserName like ?";

            params.add("%" + userName + "%");

        }

        if (sex != null && sex != "") {

            sql += " and sex = ?";

            params.add(sex);

        }

        QueryRunner qr = new QueryRunner(DBUtil.GetDataSource());

        long count = (long)qr.query(sql,new ScalarHandler(),params.toArray());

        return (int)count;

    }

    public List<User> GetUserListForPageBean(String userName, String sex, int page, int pageSize) throws SQLException {

        String sql = "select * from User where 1=1 ";

        List<Object> params = new ArrayList<Object>();

        if (userName != null && userName != "") {

            sql += " and UserName like ?";

            params.add("%" + userName + "%");

        }

        if (sex != null && sex != "") {

            sql += " and sex = ?";

            params.add(sex);

        }

        sql += " limit ?,?";

        page = (page - 1) * pageSize;

        params.add(page);

        params.add(pageSize);

        QueryRunner qr = new QueryRunner(DBUtil.GetDataSource());

        List<User> users = qr.query(sql, new BeanListHandler<User>(User.class), params.toArray());

        return users;

    }

service

public PageBean<User> GetUserListForPageBean(String userName, String sex, int page, int pageSize) throws SQLException {

        UserDao dao = new UserDao();

        PageBean pageBean = new PageBean();

        pageBean.setCurPage(page);

        pageBean.setPageSize(pageSize);

        int totalCount = dao.GetUserCount(userName, sex);

        pageBean.setTotalCount(totalCount);

        int totalPage = (int) Math.ceil(1.0 * totalCount / pageSize);

        pageBean.setTotalPage(totalPage);

        List<User> data = dao.GetUserListForPageBean(userName,sex,page,pageSize);

        pageBean.setData(data);

        return pageBean;

    }

UserListForPage servlet

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        String userName = request.getParameter("userName");

        String sex = request.getParameter("sex");

        String page = request.getParameter("page");

        int curPage = 1;

        if(page != null && page != ""){

            curPage = Integer.parseInt(page);

        }

        int pageSize = 5;

        UserService service = new UserService();

        PageBean<User> pageBean = null;

        try {

            pageBean = service.GetUserListForPageBean(userName,sex,curPage,pageSize);

        } catch (SQLException e) {

            e.printStackTrace();

        }

        request.setAttribute("userName",userName);

        request.setAttribute("sex",sex);

        request.setAttribute("pageBean", pageBean);

        request.getRequestDispatcher("userListForPage.jsp").forward(request,response);

    }

web.xml

    <servlet>

        <servlet-name>UserListForPage</servlet-name>

        <servlet-class>com.david.web.UserListForPage</servlet-class>

    </servlet>

    <servlet-mapping>

        <servlet-name>UserListForPage</servlet-name>

        <url-pattern>/userListForPage</url-pattern>

    </servlet-mapping>

userListForPage.jsp

<%@ page import="com.david.domain.User" %>

<%@ page import="java.util.List" %>

<%@ page import="com.david.domain.PageBean" %>

<%@ page contentType="text/html;charset=UTF-8" language="java" %>

<html>

<head>

    <title>用户列表</title>

</head>

<body>

<div>

    按用户名称搜索：<input id="txtUserName" value="${userName}"> &nbsp;

    按用户性别查找：<select id="selSex">

    <option value="">全部</option>

    <option value="男">男</option>

    <option value="女">女</option>

</select> &nbsp;

    <button type="button" onclick="search()">搜索</button>

    <a href="AddUser.jsp">添加用户</a>

</div>

<script>

    //设置默认值

    window.onload = function (){

        for(var i = 0;i<document.getElementById("selSex").options.length;i++){

            var option = document.getElementById("selSex").options[i];

            if(option.value == "${sex}"){

                option.selected = true;

            }

        }

    }

    function search(){

        var userName = document.getElementById("txtUserName").value;

        var sex = "";

        for(var i = 0;i<document.getElementById("selSex").options.length;i++){

            if(document.getElementById("selSex").options[i].selected){

                sex = document.getElementById("selSex").options[i].value;

            }

        }

        location.href='userList?userName='+userName+"&sex="+sex;

    }

</script>

<table>

    <tr>

        <th>用户ID</th>

        <th>用户名</th>

        <th>用户密码</th>

        <th>用户年龄</th>

        <th>用户性别</th>

        <th>操作</th>

    </tr>

    <%

        PageBean pageBean = (PageBean)request.getAttribute("pageBean");

        if (pageBean != null && pageBean.getData() != null) {

    %>

    <%for (User u : (List<User>)pageBean.getData()) {%>

    <tr>

        <td><%=u.getUserId()%>

        </td>

        <td><%=u.getUserName()%>

        </td>

        <td><%=u.getPassWord()%>

        </td>

        <td><%=u.getAge()%>

        </td>

        <td><%=u.getSex()%>

        </td>

        <td>

            <a href="GetUserById?userId=<%=u.getUserId()%>">修改</a>

            <a href="DeleteUser?userId=<%=u.getUserId()%>">删除</a>

        </td>

    </tr>

    <%}%>

    <%}%>

</table>

<div class="page">

    <%if(pageBean.getCurPage() != 1){%>

    <a href="?page=1">首页</a>

    <a href="?page=<%=pageBean.getCurPage()-1%>">上一页</a>

    <%}%>

    <%for(int i = 1;i<=pageBean.getTotalPage();i++){

        %>

    <%if(pageBean.getCurPage() == i){%>

    <a href="?page=<%=i%>" class="currentPage"><%=i%></a>

    <%}else{%>

    <a href="?page=<%=i%>"><%=i%></a>

    <%}%>

    <%}%>

    <%if(pageBean.getCurPage() != pageBean.getTotalPage()){%>

    <a href="?page=<%=pageBean.getCurPage()+1%>">下一页</a>

    <a href="?page=<%=pageBean.getTotalPage()%>">尾页</a>

    <%}%>

     共<%=pageBean.getTotalCount()%>条数据，<%=pageBean.getTotalPage()%>页。

</div>

<style>

    .page a{

        font-size:12px;

        text-decoration: none;

        color:#ccc;

    }

    .page .currentPage{

        color:#000;

        font-size:20px;

    }

</style>

</body>

</html>

码农公寓

相关文章