综合练习（增删查改）

目录

添加

思路

删除

思路

普通删除

删除选中

修改

思路

模糊查询

思路

分页查询

思路

代码

PageBean

UserDaoImpl

UserServiceImpl

Servlet

---

添加

思路

 

删除

思路

普通删除

 

删除选中

 

修改

思路

 

模糊查询

思路

 

分页查询

思路

 

代码

PageBean

public class PageBean<T> {
    private int totalCount; // 总记录数
    private int totalPage ; // 总页码
    private List<T> list ; // 每页的数据
    private int currentPage ; //当前页码
    private int rows;//每页显示的记录数

    public int getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(int totalCount) {
        this.totalCount = totalCount;
    }

    public int getTotalPage() {
        return totalPage;
    }

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

    public List<T> getList() {
        return list;
    }

    public void setList(List<T> list) {
        this.list = list;
    }

    public int getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
    }

    public int getRows() {
        return rows;
    }

    public void setRows(int rows) {
        this.rows = rows;
    }

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

UserDaoImpl

public class UserDaoImpl implements UserDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    @Override
    public List<User> findall() {
        //使用数据库
        String sql = "select * from user";
        List<User> users = template.query(sql, new BeanPropertyRowMapper<User>(User.class));
        return users;
    }

    //登录
    @Override
    public User findUserByUsernameAndPassword(String username, String password) {
        try {
            String sql = "select * from user where username = ? and password = ?";
            User user = template.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), username, password);
            return user;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }

    }

    @Override
    public void add(User user) {
        String sql = "insert into user values(null,?,?,?,?,?,?,null,null)";
        template.update(sql, user.getName(), user.getGender(), user.getAge(), user.getAddress(), user.getQq(), user.getEmail());
    }

    @Override
    public void delete(int id) {
        String sql = "delete from user where id=?";
        template.update(sql, id);
    }

    @Override
    public User findById(int id) {
        String sql = "select * from user where id=?";
        return template.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), id);
    }

    @Override
    public void update(User user) {
        String sql = "update user set name=?,gender=?,age=?,address=?,qq=?,email=? where id=?";
        template.update(sql, user.getName(), user.getGender(), user.getAge(), user.getAddress(), user.getQq(), user.getEmail(), user.getId());
    }

    @Override
    public int findTotalCount(Map<String, String[]> condition) {
        //1.定义模板初始化sql
        String sql = "select count(*) from user where 1 = 1 ";
        StringBuilder sb = new StringBuilder(sql);
        //2.遍历map
        Set<String> keySet = condition.keySet();
        //定义参数的集合
        List<Object> params = new ArrayList<Object>();
        for (String key : keySet) {
            //排除分页条件参数
            if("currentPage".equals(key) || "rows".equals(key)){
                continue;
            }
            //获取value
            String value = condition.get(key)[0];
            //判断value是否有值
            if(value != null && !"".equals(value)){
                //有值
                sb.append(" and "+key+" like ? ");
                params.add("%"+value+"%");//？条件的值
            }
        }
        return template.queryForObject(sb.toString(),Integer.class,params.toArray());
    }

    @Override
    public List<User> findByPage(int start, int rows, Map<String, String[]> condition) {
        String sql = "select * from user where 1 = 1 ";
        StringBuilder sb = new StringBuilder(sql);
        //2.遍历map
        Set<String> keySet = condition.keySet();
        //定义参数的集合
        List<Object> params = new ArrayList<Object>();
        for (String key : keySet) {

            //排除分页条件参数
            if("currentPage".equals(key) || "rows".equals(key)){
                continue;
            }

            //获取value
            String value = condition.get(key)[0];
            //判断value是否有值
            if(value != null && !"".equals(value)){
                //有值
                sb.append(" and "+key+" like ? ");
                params.add("%"+value+"%");//？条件的值
            }
        }

        //添加分页查询
        sb.append(" limit ?,? ");
        //添加分页查询参数值
        params.add(start);
        params.add(rows);
        System.out.println(start+" "+rows);
        sql = sb.toString();
        System.out.println(sql);
        return template.query(sql,new BeanPropertyRowMapper<User>(User.class),params.toArray());
    }
}

UserServiceImpl

public class UserServiceImpl implements UserService {
    private UserDao dao = new UserDaoImpl();

    @Override
    public List<User> findall() {
        return dao.findall();
    }

    @Override
    public User login(User user) {
        return dao.findUserByUsernameAndPassword(user.getUsername(), user.getPassword());
    }

    @Override
    public void addUser(User user) {
        dao.add(user);
    }

    @Override
    public void deleteUser(String id) {
        dao.delete(Integer.parseInt(id));
    }

    @Override
    public User findUserById(String id) {
        return dao.findById(Integer.parseInt(id));
    }

    @Override
    public void updateUserById(User user) {
        dao.update(user);
    }

    @Override
    public void delSelectedUser(String[] ids) {
        for (String i : ids) {
            dao.delete(Integer.parseInt(i));
        }
    }

    @Override
    public PageBean<User> findUserByPage(String currentpage1, String rows1, Map<String, String[]> condition) {
        int currentPage = Integer.parseInt(currentpage1);
        int rows = Integer.parseInt(rows1);
        if(currentPage <=0) {
            currentPage = 1;
        }
        //设置空的对象
        PageBean<User> pb = new PageBean<>();
        pb.setRows(rows);
        //总记录数
        int totalCount = dao.findTotalCount(condition);
        pb.setTotalCount(totalCount);
        //总页码
        int totalpage = totalCount % rows == 0 ? totalCount / rows : (totalCount / rows) + 1;
        pb.setTotalPage(totalpage);
        if(currentPage >=totalpage) {
            currentPage = totalpage;
        }
        System.out.println("totalpage:"+totalpage);
        //设置参数
        pb.setCurrentPage(currentPage);
        //调用dao查询List集合，计算开始的索引记录
        int start = (currentPage - 1) * rows;
        if (start<=0){
            start = 0;
        }
        List<User> list = dao.findByPage(start,rows,condition);
        for (User u:list){
            System.out.println(u);
        }
        pb.setList(list);
        return pb;
    }
}

Servlet

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//AddUserServlet
        //设置编码
        request.setCharacterEncoding("UTF-8");
        //获取参数
        Map<String, String[]> map = request.getParameterMap();
        //封装User
        User user = new User();
        try {
            BeanUtils .populate(user,map);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        //调用Service保存
        UserService service = new UserServiceImpl();
        service.addUser(user);
        //跳转到userListServlet
        response.sendRedirect(request.getContextPath()+"/findUserByPageServlet");

//DelSelectedServlet
 //获取所有id
        String[] ids = request.getParameterValues("uid");
        //调用service删除
        UserService service = new UserServiceImpl();
        service.delSelectedUser(ids);

        //跳转查询所有Servlet
        response.sendRedirect(request.getContextPath()+"/findUserByPageServlet");

//DelUserServlet
//获取id
        String id = request.getParameter("id");
        //调用service删除
        UserService service = new UserServiceImpl();
        service.deleteUser(id);
        //跳转到查询所有Servelet
        response.sendRedirect(request.getContextPath()+"/findUserByPageServlet");

//FindUserByPageServlet
request.setCharacterEncoding("utf-8");
        //获取参数
        String currentPage = request.getParameter("currentPage");//当前页码
        String rows = request.getParameter("rows");//每页显示条数
        System.out.println("currentPage:"+currentPage+"rows:"+rows);
        if(currentPage == null || "".equals(currentPage)){
            currentPage = "1";
        }
        if(rows == null || "".equals(rows)){
            rows = "5";
        }
        //获取条件查询参数
        Map<String, String[]> condition = request.getParameterMap();

        //调用service
        UserService service = new UserServiceImpl();
        PageBean<User> pb = service.findUserByPage(currentPage,rows,condition);
        request.setAttribute("pb",pb);
        request.setAttribute("condition",condition);
//        转发到list.jsp
        request.getRequestDispatcher("/list.jsp").forward(request,response);

//FindUserServlet
String id = request.getParameter("id");
        UserService service = new UserServiceImpl();
        User user = service.findUserById(id);
//        将user存入request
        request.setAttribute("user",user);
        request.getRequestDispatcher("/update.jsp").forward(request,response);

//UpdateUserServlet
request.setCharacterEncoding("utf-8");
        Map<String, String[]> map = request.getParameterMap();
        User user = new User();
        try {
            BeanUtils.populate(user,map);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        UserService service = new UserServiceImpl();
         service.updateUserById(user);
        response.sendRedirect(request.getContextPath()+"/findUserByPageServlet");


//UserListServlet
//调用UserService完成查询
        UserService service = new UserServiceImpl();
        List<User> users = service.findall();
        //存入request域
        request.setAttribute("users",users);
        //转发到list.jsp
        request.getRequestDispatcher("/list.jsp").forward(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request, response);
    }
}