spring+springmvc+jdbc （2）

增删改

• dao 层方法

	//添加方法
    int insertUser(User user);

    //修改方法
    int updateUser(User user);

    //删除方法
    int deleteUser(int id);

    //根据id 获取一条数据
    User findUser(int id);

• dao 层实现

@Override
    public int insertUser(User user) {
        String sql = "insert into user(uname,upwd,usex) value(?,?,?)";
        int num = BaseDao.executeUpdate(sql, new Object[]{user.getUname(), user.getUpwd(),user.getUsex()});
        return num;
    }

    @Override
    public int updateUser(User user) {
        String sql = "update user set uname=?,upwd=?,usex=? where uid=?";
        int num = BaseDao.executeUpdate(sql, new Object[]{user.getUname(), user.getUpwd(), user.getUsex(), user.getUid()});
        return num;
    }

    @Override
    public int deleteUser(int id) {
        String sql = "delete from user where uid=?";
        int num = BaseDao.executeUpdate(sql, new Object[]{id});
        return num;
    }

    @Override
    public User findUser(int id) {
        String sql = "select * from user where uid=?";
        return (User) BaseDao.executeQuery(sql,new Object[]{id},new BeanResultHandler(User.class));
    }

• service / service 实现

 	//添加方法
    int insertUser(User user);

    //修改方法
    int updateUser(User user);

    //删除方法
    int deleteUser(int id);

    //根据id 获取一条数据
    User findUser(int id);

	@Override
    public int insertUser(User user) {
        return userDao.insertUser(user);
    }

    @Override
    public int updateUser(User user) {
        return userDao.updateUser(user);
    }

    @Override
    public int deleteUser(int id) {
        return userDao.deleteUser(id);
    }

    @Override
    public User findUser(int id) {
        return userDao.findUser(id);
    }

• UserController

	/*去添加*/
    @RequestMapping("toAdd")
    public String toAdd(){

        return "addUser";
    }

    /*添加*/
    @RequestMapping("add")
    public String add(@RequestParam(value = "uname",required = false) String uname,
                      @RequestParam(value = "upwd",required = false) String upwd,
                      @RequestParam(value = "usex",required = false) String usex){
        //接下来
        User user = new User();
        user.setUname(uname);
        user.setUpwd(upwd);
        user.setUsex(Integer.parseInt(usex));
    /*    if (usex.equals("男")){
            user.setUsex(1);
        }else if (usex.equals("女")){
            user.setUsex(0);
        }*/
        int num = userService.insertUser(user);
        if (num > 0){
            //重定向打破首页
            return "redirect:toPage";
        }
        return "toAdd";
    }

    /*去修改*/
    @RequestMapping("toUpdate")
    public String toUpdate(@RequestParam(value = "uid",required = false) String uid,Model model){
        User user = userService.findUser(Integer.parseInt(uid));
        model.addAttribute("user",user);
        return "updateUser";
    }

    /*修改*/
    @RequestMapping("update")
    public String update(@RequestParam(value = "uname",required = false) String uname,
                         @RequestParam(value = "upwd",required = false) String upwd,
                         @RequestParam(value = "usex",required = false) String usex,
                         @RequestParam(value = "uid",required = false) String uid){
        User user = new User();
        user.setUname(uname);
        user.setUpwd(upwd);
        user.setUsex(Integer.parseInt(usex));
        user.setUid(Integer.parseInt(uid));

        int num = userService.updateUser(user);
        if (num > 0){
            //
            return "redirect:toPage";
        }
        return "toUpdate";
    }

    //删除
    /*ajax 请求是不需要 返回值的(因为 ajax 异步 不需要跳转)，一定要加上这个注解@ResponseBody*/
    @RequestMapping("delete")
    @ResponseBody
    public void delete(@RequestParam(value = "uid",required = false) String uid,
            HttpServletResponse response){
        int num = userService.deleteUser(Integer.parseInt(uid));


    }

• 分页 于 模糊查询

	//获取数据，第一个参数代表的是当前页，第二个参数代表页量
    List<User> selectAllUser(PageUtils pageUtils);

    //查询总记录数
    Integer selectUserCount(PageUtils pageUtils);

@Override
    public List<User> selectAllUser(PageUtils pageUtils) {
        StringBuffer sb = new StringBuffer("select * from user where 1=1");
        //定义一个占位符的集合
        List<Object> list = new ArrayList<Object>();
        //首先判断是否需要查询
        if (EmptyUtils.isNotEmpty(pageUtils.getQueryWord())){
            //拼接sql语句
            sb.append(" and uname like ?");
            list.add( "%"+ pageUtils.getQueryWord() +"%");
        }
        //排序


        //分页查询
        if(EmptyUtils.isNotEmpty(pageUtils.getCurrentPageNo())){
            sb.append(" limit ?,?");
            //给占位符赋值
            list.add((pageUtils.getCurrentPageNo()-1)*pageUtils.getPageSize());
            list.add(pageUtils.getPageSize());
        }
        return (List<User>) BaseDao.executeQuery(sb.toString(), list.toArray(), new BeanListResultHandler(User.class));
    }

    @Override
    public Integer selectUserCount(PageUtils pageUtils) {
        StringBuffer sb = new StringBuffer("select count(1) from user where 1=1");
        //定义一个占位符的集合
        List<Object> list = new ArrayList<Object>();
        //首先判断是否需要查询
        if (EmptyUtils.isNotEmpty(pageUtils.getQueryWord())){
            //拼接sql语句
            sb.append(" and uname like ? ");
            list.add( "%"+ pageUtils.getQueryWord() +"%");
        }
        List<User> list1 = (List<User>) BaseDao.executeQuery(sb.toString(), list.toArray(), new ArrayResultHandler());
        String count = list1.get(0)+"";
        return Integer.parseInt(count);
    }

• 主页面代码显示 以及 分页 ，删除运用了ajax

<body>
    <div class="box">
        <a href="toAdd">增加</a>
        <form action="toPage" method="post" class="box2">
            <input type="text" name="queryWord">
            <input type="submit" value="确认">
        </form>

    </div>
    <table border="1" cellspacing="0" cellpadding="0" width="400" align="center">
        <tr id="box">
            <th>编号</th><th>姓名</th><th>密码</th><th>性别</th><th colspan="2">功能</th>
        </tr>
        <c:forEach var="user" items="${pageUtils.userList}">
        <tr>
            <td>${user.uid}</td>
            <td>${user.uname}</td>
            <td>${user.upwd}</td>
            <c:choose>
                <c:when test="${user.usex == 1}">
                    <td>男</td>
                </c:when>
                <c:otherwise>
                    <td>女</td>
                </c:otherwise>
            </c:choose>
            <td>
                <a href="toUpdate?uid=${user.uid}">修改</a>
            </td>
            <td>
                <a href="javascript:;" onclick="showInfo(this,${user.uid});" >删除</a>
            </td>
        </tr>
        </c:forEach>
    </table>

    <div style="width: 300px;margin: 10px 0 0 650px;">

    <c:choose>
        <c:when test="${pageUtils.currentPageNo>1}">
            <a href="toPage?currentPageNo=1&queryWord=${pageUtils.queryWord}">首页</a>
            <a href="toPage?currentPageNo=${pageUtils.currentPageNo-1}&queryWord=${pageUtils.queryWord}">上一页</a>
        </c:when>
        <c:otherwise>
            <span style="color:#999">首页</span>
            <span style="color:#999">上一页</span>
        </c:otherwise>
    </c:choose>
    [${pageUtils.currentPageNo}/${pageUtils.totalPageSize}]
    <c:choose>
        <c:when test="${pageUtils.currentPageNo < pageUtils.totalPageSize}">
            <a href="toPage?currentPageNo=${pageUtils.currentPageNo+1}&queryWord=${pageUtils.queryWord}">下一页</a>
            <a href="toPage?currentPageNo=${pageUtils.totalPageSize}&queryWord=${pageUtils.queryWord}">末页</a>
        </c:when>
        <c:otherwise>
            <span style="color:#999">下一页</span>
            <span style="color:#999">末页</span>
        </c:otherwise>
    </c:choose>
    </div>
    <script type="text/javascript">
        function showInfo(a,id) {
            if (confirm("确认删除此条信息")) {
                $.post("${pageContext.request.contextPath}/userController/delete",{"uid":id},function () {
                    $(a).parents("tr").remove();
                })
            }
        }

    </script>

</body>

注意：传值和路径问题；