项目

模糊查询

dao层

package com.offcn.dao;

import com.offcn.entity.User;

import java.util.List;

public interface UserDao {
    //查询全部信息
    List<User> selectAll();
    //查询用户名来查询
    User selectByName(String uname);
    //根据Id查询
    User selectByUid(int uid);
    //根据Id修改
    int updateByUid(User user);

    //分页
    //查询总记录数  根据用户名、开始时间、结束时间模糊查询
    Integer selectCount(String uname,String startTime,String endTime);
    //查询出分页 集合:当前页数  页量    根据用户名、开始时间、结束时间模糊查询
    List<User> selectAll(String uname,String startTime,String endTime,Integer currentPageNo,Integer pageSize);

    //添加信息
    int addUser(User user);

    //根据Id 进行删除
    //int deleteByUid(int uid);

    //批量删除
    int deleteByUid(String ids);
}

dao实现层

//查询总记录数 根据用户名、开始时间、结束时间模糊查询
    @Override
    public Integer selectCount(String uname,String startTime,String endTime) {
        //使用可变字符来动态接收sql语句
        StringBuffer sb = new StringBuffer("select count(1) from user where 1=1 ");
        //使用集合来动态改变参数的个数
        List<Object> userList = new ArrayList<Object>();
        if (uname != null && uname != ""){
            sb.append(" and uname like ?");
            userList.add("%"+uname+"%");
        }
        if (startTime != null && startTime != ""){
            sb.append(" and udate > ? ");
            userList.add(startTime);
        }
        if (endTime != null && endTime != ""){
            sb.append(" and udate < ?");
            userList.add(endTime);
        }
        Long count = null;
        try {
            count = (Long)qr.query(sb.toString(),new ScalarHandler(),userList.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return Integer.parseInt(count+"");
    }

    @Override
    public List<User> selectAll(String uname,String startTime,String endTime,Integer currentPageNo, Integer pageSize) {
        //使用可变字符来动态接收sql语句
        StringBuffer sb = new StringBuffer("select * from user where 1=1 ");
        //使用集合来动态改变参数的个数
        List<Object> userList = new ArrayList<Object>();
        if (uname != null && uname != ""){
            sb.append(" and uname like ?");
            userList.add("%"+uname+"%");
        }
        if (startTime != null && startTime != ""){
            sb.append(" and udate > ? ");
            userList.add(startTime);
        }
        if (endTime != null && endTime != ""){
            sb.append(" and udate < ?");
            userList.add(endTime);
        }
        //设置时间排序
        sb.append(" order by uid desc");
        sb.append(" limit ?,? ");
        userList.add((currentPageNo-1)*pageSize);
        userList.add(pageSize);

        List<User> list = null;
        try {
            list = qr.query(sb.toString(),new BeanListHandler<User>(User.class),userList.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

service 层

//分页
    //查询总记录数
    Integer selectCount(String uname,String startTime,String endTime);
    //查询出分页 集合:当前页数  页量
    List<User> selectAll(String uname,String startTime,String endTime,Integer currentPageNo,Integer pageSize);

service 实现层

@Override
    public Integer selectCount(String uname,String startTime,String endTime) {

        return userDao.selectCount(uname, startTime, endTime);
    }

    @Override
    public List<User> selectAll(String uname,String startTime,String endTime,Integer currentPageNo, Integer pageSize) {
        return userDao.selectAll(uname,startTime,endTime,currentPageNo,pageSize);
    }

servlet

//获取前端传来的三个数据
            String uname = req.getParameter("uname");
            String startTime = req.getParameter("startTime");
            String endTime = req.getParameter("endTime");

            //查询时设置回显  设置到作用域里
            req.setAttribute("uname",uname);
            req.setAttribute("startTime",startTime);
            req.setAttribute("endTime",endTime);


            //获取当前页量
            String pageSizestr = req.getParameter("pageSize");
            Integer pageSize;
            if (pageSizestr == null || pageSizestr == ""){
                pageSize = 3;
            }else {
                pageSize = Integer.parseInt(pageSizestr);
            }
            //获取总记录数
            Integer totalPageCount = userService.selectCount(uname, startTime, endTime);
            //获取总页数
            Integer totalPageSize = totalPageCount % pageSize == 0 ? totalPageCount / pageSize : totalPageCount / pageSize + 1;
            //获取当前页数
            String currentPageNostr = req.getParameter("currentPageNo");
            Integer currentPageNo;
            if (currentPageNostr == null || currentPageNostr == ""){
                currentPageNo = 1;
            }else {
                currentPageNo = Integer.parseInt(currentPageNostr);
                if (currentPageNo <= 1){
                    currentPageNo = 1;
                }
                if (currentPageNo >= totalPageSize){
                    currentPageNo = totalPageSize;
                }
            }
            //获取当前页数集合
            List<User> userList = userService.selectAll(uname, startTime, endTime,currentPageNo,pageSize);
            //把工具类填满
            Utils<User> userUtils = new Utils<User>();
            userUtils.setCurrentPageNo(currentPageNo);
            userUtils.setPageSize(pageSize);
            userUtils.setTotalPageCount(totalPageCount);
            userUtils.setTotalPageSize(totalPageSize);
            userUtils.setuList(userList);
            //把工具类存到作用域下
            req.setAttribute("userUtils",userUtils);
            //转发到响应的页面
            req.getRequestDispatcher("select.jsp").forward(req,resp);

前台页面(设置查询回显)

<form  style="margin-left: 120px" method="post" action="userServlet">
    <p>查询：&nbsp;
        姓名<input type="text" name="uname" value="${uname}">
        开始时间<input type="date" name="startTime" value="${startTime}">
        结束时间<input type="date" name="endTime" value="${endTime}">
        <input type="submit" value="搜索">
    </p>
</form>