jsp 条件查询、列表分页

2018/05/11 16:40
阅读数 6

条件查询

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>

 

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部