SSM框架CRUD操作&批量删除&批量追加数据（Oracle&MySQL数据库）

短命女 2022-06-08 05:26 259阅读 0赞

SSM框架CRUD操作Demo  
1、SSM开发环境搭建详见下一章微博  
2、定义起始页，基本的超链接完成请求，详见如图

由于时间关系，本人将在本博客中陆续推出高级框架阶段的系列教程，现已经发布如下几篇，如果对大家有帮助也请各位给点鼓励吧 ，比如关注微博、顶一下或者给点评论，谢谢！

[http://blog.csdn.net/dl0246/article/details/77996811][http_blog.csdn.net_dl0246_article_details_77996811] SSM框架CRUD操作&批量删除&批量追加数据（Oracle&MySQL数据库）  
[http://blog.csdn.net/dl0246/article/details/77966611][http_blog.csdn.net_dl0246_article_details_77966611] 诸多设计模式中，最为常见的三种设计模式-单例模式，代理模式，工厂模式  
[http://blog.csdn.net/dl0246/article/details/78251319][http_blog.csdn.net_dl0246_article_details_78251319] SpringMVC入门及系列教程（一）-SpringMVC环境搭建及入门案例（详细）  
[http://blog.csdn.net/dl0246/article/details/78259612][http_blog.csdn.net_dl0246_article_details_78259612] SpringMVC入门及系列教程（二）-SpringMVC请求周期及响应处理（初级）（详细）  
[http://blog.csdn.net/dl0246/article/details/78274343][http_blog.csdn.net_dl0246_article_details_78274343] SpringMVC入门及系列教程（三）-请求的映射（详细）  
[http://blog.csdn.net/dl0246/article/details/78286685][http_blog.csdn.net_dl0246_article_details_78286685] SpringMVC入门及系列教程（四）-SpringMVC在控制器中获取请求中的参数值(详细)  
[http://blog.csdn.net/dl0246/article/details/78390587][http_blog.csdn.net_dl0246_article_details_78390587] SpringMVC入门及系列教程（五）-响应处理&响应参数处理&SpringMVC作用域对象&SpringMVC原生Servlet对象

1、SSM开发环境搭建详见下一章微博  
2、定义起始页，基本的超链接完成请求，详见如图

<fieldset>
            <legend>SSM CRUD  数据库：MySQL，请切换连接MySQL参数</legend>
            <a href="user/queryAllUser.do">查看所有员工信息</a>
        </fieldset>

![这里写图片描述][SouthEast]

3、编写对对应控制器Handler

@Controller
    @RequestMapping("/user")
    public class UserAction { 
        @Autowired
        private IUserService userService;
        @RequestMapping("/queryAllUser")
        public String queryAllUser(Map<String,Object> requests){
            requests.put("users",userService.queryAllUser());
            return "/users";
        }
    }

4、创建service

@Service
    @Transactional
    public class IUserServiceImpl implements IUserService { 
        @Resource
        private IUserDao userDao;
    
        public List<User> queryAllUser(){
            return userDao.queryAllUser();
        }
    }

5、创建对应的Dao

public interface IUserDao {
        public List<User> queryAllUser();
    }

6、创建MyBaits对应的SQL文

响应的画面效果如图：  
![这里写图片描述][SouthEast 1]

7、下面来看看页面效果的实现代码及前对对批量删除的前端代码，代码中有注释

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
    <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
    <%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <link rel="stylesheet" type="text/css" href="${pageContext.request.contextPath}/css/users.css">
    <link rel="stylesheet" type="text/css" href="${pageContext.request.contextPath}/css/users_hover.css">
    <script type="text/javascript" src="${pageContext.request.contextPath}/js/jquery-2.1.1.js"></script>
    <script type="text/javascript"> $(document).ready(function(){ //鼠标移动到行变色,单独建立css类hover //tr:gt(0):表示获取大于 tr index 为0 的所有tr，即不包括表头 /* $("#customers tbody tr:odd").addClass("odd"); $("#customers tbody tr:even").addClass("even"); */ var flag=false; $("#customers tr:gt(0)").hover( function () { if($(this).prop("class")=="alt"){ flag=true; } $(this).removeClass("alt"); $(this).addClass("hover"); }, function () { if(flag){ $(this).addClass("alt"); } $(this).removeClass("hover"); flag=false; }); $("#sal").click(function(){ /* 返回了rquest域中users这个List集合的长度（User的个数） */ var max="${fn:length(requestScope.users) }"; for(var i=0;i<max;i++){ if(this.checked){ $("#selecters"+i).prop("checked",true); } else $("#selecters"+i).prop("`checked",false); } }); $("#del").click(function(){ var url="deleteBatchById.do?ids="; var max="${fn:length(requestScope.users) }"; var ids=""; /* 循环遍历所有的复选框 */ for(var i=0;i<max;i++){ if($("#selecters"+i).prop("checked")){ //若这个复选框被选中状态，那么结果是true 否则是false、 ids+=$("#selecters"+i).prop("value")+",";//若复选框被选中状态，那么将这个复选框中的value值获取后保存在ids变量zhong } } ids=ids.substr(0,ids.length-1); /* alert(ids); */ /* 通过jQuery的get方法发起异步请求（ajax）请求，是以get方式发起的请求 第一个参数：ajax请求的url是多少 第二个参数是回调函数：回调函数中的第一个参数是服务器返回来的数据，第二个参数保存了ajaxget请求执行结果 */ $.get(url+ids,function(data,status){ //url=deleteBatchById.do?ids=3,66,332,11 /* 将id属性值是customers的table元素中的tr的行数大于0 就是除了表头之外的所有行 remove(); */ $("#customers tr:gt(0)").remove(); var newRow=""; /* [{"userid":"aa","password":"aa","username":"aa","phone":"aa","birthday":"2010-09-09","score":200,"typename":"aaa","question":"aaa","answer":"aaa"}, {"userid":"bb","password":"aa","username":"aa","phone":"aa","birthday":"2010-09-09","score":200,"typename":"aaa","question":"aaa","answer":"aaa"}, {"userid":"cc","password":"aa","username":"aa","phone":"aa","birthday":"2010-09-09","score":200,"typename":"aaa","question":"aaa","answer":"aaa"}, {"userid":"dd","password":"aa","username":"aa","phone":"aa","birthday":"2010-09-09","score":200,"typename":"aaa","question":"aaa","answer":"aaa"} ] */ for(var i=0;i<data.length;i++){ //data<---List<User> [{},{},{}] var id=data[i].userid; var username=data[i].username; var phone=data[i].phone; var birthday=data[i].birthday; var score=data[i].score; var typename=data[i].typename; var password=data[i].password; var question=data[i].question; var answer=data[i].answer; //alert(id+" "+username+" "+phone+" "+birthday); if(i%2==0){ newRow="<tr><td>"+ "<input type='checkbox' id='selecters"+i+"' name='selecters' value='"+id+"'>" +"</td><td>"+id+"</td><td>"+ "<a href='abc'>"+ username+ "</a>"+ "</td><td>"+phone+"</td><td>"+password+"</td><td>"+typename+"</td></tr>"; } else{ newRow="<tr class='alt'><td>"+ "<input type='checkbox' id='selecters"+i+"' name='selecters' value='"+id+"'>" +"</td><td>"+id+"</td><td>"+username+"</td><td>"+phone+"</td><td>"+password+"</td><td>"+typename+"</td></tr>"; } $("#customers tr:last").after(newRow); } /* 将整个页面进行重新加载，说白了就是整个页面刷新一次 */ //window.location.reload();// reload body }); }); $("#delBody").click(function () { //删除指定行（第二行） // $("#table3 tr:gt(0):eq(1)").remove(); /* 删除其它行，比如第二行之外的所有行： $("#table3 tr:gt(0):not(:eq(1))").remove(); */ $("#customers tr:gt(0)").remove(); }); }); </script>
    <title>Insert title here</title>
    </head>
    <body>
        <fieldset>
            <legend>jQuery操作table</legend>
            <a href="#" id="delBody">删除其他行</a>
        </fieldset>
        
        <c:if test="${not empty requestScope.user }">
                <fieldset>
                    <legend>修改【${requestScope.user.username }】的数据</legend>
                    <form action="${pageContext.request.contextPath}/user/doUpdate.do">
                    <div>
                        Userid:<input type="text" name="userid" value="${requestScope.user.userid }">
                    </div>
                    <div>
                        username:<input type="text" name="username" value="${requestScope.user.username}">
                    </div>
                    <div>
                        password:<input type="text" name="password" value="${requestScope.user.password }">
                    </div>
                    <div>
                        <input type="submit" value="修改密码"/>
                    </div>
                </form>
                </fieldset>
        </c:if>
        <c:if test="${empty requestScope.users }">
            <fieldset>
                <legend>暂时没有数据</legend>
            </fieldset>
        </c:if>
        <c:if test="${not empty requestScope.users }">
            <fieldset>
            <legend>所有员工信息如下</legend>
            <table id="customers">
                <tr>
                    <th>
                        <input type="checkbox" id="sal" > 全选
                        <a href="#" id="del">删除</a>
                    </th>
                    <th>用户编号</th>
                    <th>用户姓名</th>
                    <th>用户电话</th>
                    <th>登录密码</th>
                    <th>会员类型</th>
                    <th>操作</th>
                </tr>
                <c:forEach items="${requestScope.users }" var="user" varStatus="stus">
                    <c:if test="${stus.count%2 eq 0 }">
                        <tr>
                            <td>
                                <input type="checkbox" id="selecters${stus.index}" name="selecters" value="${user.userid }">
                            </td>
                            <td>${user.userid }</td>
                            <td>
                                <a href="${pageContext.request.contextPath }/user/update.do?userid=${user.userid }">${user.username }</a>
                            </td>
                            <td>${user.phone }</td>
                            <td>${user.password }</td>
                            <td>${user.typename }</td>
                            <td>
                                <a href="${pageContext.request.contextPath }/user/deleteUser.do?userid=${user.userid }">删除</a>
                            </td>
                        </tr>
                    </c:if>
                    <c:if test="${stus.count%2 eq 1 }">
                        <tr class="alt">
                            <td>
                                <input type="checkbox" id="selecters${stus.index}" name="selecters" value="${user.userid }">
                            </td>
                            <td>${user.userid }</td>
                            <td>
                                <a href="${pageContext.request.contextPath }/user/update.do?userid=${user.userid }">${user.username }</a>
                            </td>
                            <td>${user.phone }</td>
                            <td>${user.password }</td>
                            <td>${user.typename }</td>
                            <td>
                                <a href="${pageContext.request.contextPath }/user/deleteUser.do?userid=${user.userid }">删除</a>
                            </td>
                        </tr>
                    </c:if>
                </c:forEach>
            </table>
    
        </fieldset>
        </c:if>
        <fieldset>
            <legend>添加数据</legend>
            <div>
                <form action="${pageContext.request.contextPath}/user/addUser.do">
                    <div>
                        Userid:<input type="text" name="userid" >
                    </div>
                    <div>
                        username:<input type="text" name="username" >
                    </div>
                    <div>
                        password:<input type="text" name="password" >
                    </div>
                    <div>
                        <input type="submit" value="添加"/>
                    </div>
                </form>
            </div>
        </fieldset>
    
    </body>
    </html>

8、下面来看看批量删除对应的控制器代码，因为整个CRUD操作后端代码中最主要的就是控制器Handler及MyBatis的映射文件了，所以这里把对应的代码全部贴出，以供参考

package com.rock.trainner.action;
    
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.LinkedHashMap;
    import java.util.List;
    import java.util.Map;
    
    import javax.servlet.http.HttpServletResponse;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.ModelAttribute;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestParam;
    import org.springframework.web.bind.annotation.ResponseBody;
    import org.springframework.web.servlet.ModelAndView;
    
    import com.rock.trainner.entities.User;
    import com.rock.trainner.services.IUserService;
    
    @Controller
    @RequestMapping("/user")
    public class UserAction { 
        @Autowired
        private IUserService userService;
        @RequestMapping("/queryAllUser")
        public String queryAllUser(Map<String,Object> requests){
            requests.put("users",userService.queryAllUser());
            return "/users";
        }
        @RequestMapping("/addUser")
        public ModelAndView addUser(User user){
            System.out.println("UserAction.addUser() is running.........");
            userService.addUser(user);
            /** * 注意：在SpringMVC中 由控制器产生响应的方式有如下三种： * 1、action返回一个String， prefix+retrunValue+suffix ----》转发 * 2、action返回一个ModelAndView * 3、若action方法想直接转发或重定向当前请求的话，可以通过在返回的字符串前加上forword: | redirect: * return "forword:users.jsp" * ModelAndView modelAndView=new ModelAndView("redirect:abc.jsp"); * * 若在一个action方法中将请求继续提交给另一个action方法，那么无需指定一级目录（Action类上的那个@RequestMapping） */
            ModelAndView  modelAndView=new ModelAndView("redirect:queryAllUser.do");
            return modelAndView;
        }
    
        @RequestMapping("/deleteUser")
        public String deleteUser(@RequestParam("userid") String userid){
            System.out.println("UserAction.deleteUser() is running......"+userid );
            int a=userService.deleteUser(userid);
            return "redirect:queryAllUser.do";
        }
        /*@ModelAttribute public void update_init(@RequestParam("userid") String userid,Map<String,Object> map){ User user=userService.queryById(userid); map.put("user",user); }*/
        @RequestMapping("/update")
        public String update(User user,Map<String,Object> map){
            System.out.println("UserAction.update() is running......"+user );
            map.put("user",userService.queryById(user.getUserid()));
            return "users";
        }
        @RequestMapping("/doUpdate")
        public String doUpdate(@ModelAttribute("user") User user){
            System.out.println("UserAction.doUpdate() is running......"+user );
    
            int a=userService.updateUser(user);
            return "redirect:queryAllUser.do";
        }
    // 若希望SpringMVC的Handler相应回来一个Json数据话，我们可以将这些数据存放在List集合中，
    // SpringMVC的jar包中提供了专门将List集合数据转化成json格式的对象
        @ResponseBody
        @RequestMapping("/deleteBatchById")
        public List<User> deleteBatchById(@RequestParam(value="ids") String ids,HttpServletResponse response){
            System.out.println("UserAction.deleteBatchById()............................."+ids);
            int a=userService.deleteBatchById(ids);
            return userService.queryAllUser();
        }
    
        @ResponseBody
        @RequestMapping("/jsonTest")
        public List<User> jsonTest(){
            List<User> all=new ArrayList<User>();
            Map<String,User> maps=new HashMap<String,User>();
            User user1=new User("aa","aa","aa","aa","2010-09-09",200,"aaa","aaa","aaa");
            User user2=new User("bb","aa","aa","aa","2010-09-09",200,"aaa","aaa","aaa");
            User user3=new User("cc","aa","aa","aa","2010-09-09",200,"aaa","aaa","aaa");
            User user4=new User("dd","aa","aa","aa","2010-09-09",200,"aaa","aaa","aaa");
            maps.put("aa",user1);
            maps.put("bb",user2);
            maps.put("cc",user3);
            maps.put("dd",user4);
            all.add(user1);
            all.add(user2);
            all.add(user3);
            all.add(user4);
    
            return all;
        }
    
        @ResponseBody
        @RequestMapping("/jsonTestMap")
        public Map<String,User> jsonTestMap(){
            List<User> all=new ArrayList<User>();
            Map<String,User> maps=new LinkedHashMap<String,User>();
            User user1=new User("aa","aa","aa","aa","2010-09-09",200,"aaa","aaa","aaa");
            User user2=new User("bb","aa","aa","aa","2010-09-09",200,"aaa","aaa","aaa");
            User user3=new User("cc","aa","aa","aa","2010-09-09",200,"aaa","aaa","aaa");
            User user4=new User("dd","aa","aa","aa","2010-09-09",200,"aaa","aaa","aaa");
            maps.put("aa",user1);
            maps.put("bb",user2);
            maps.put("cc",user3);
            maps.put("dd",user4);
            all.add(user1);
            all.add(user2);
            all.add(user3);
            all.add(user4);
    
            return maps;
        }
    
    }

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.rock.trainner.dao.IUserDao">
        <sql id="userLists">
            userid,username,phone,birthday,score,typename,password,question,answer
        </sql>
        <select id="queryAllUser" resultType="User">
            select <include refid="userLists"/> from user
        </select>
        <select id="queryById" parameterType="String" resultType="User">
            select <include refid="userLists"/> from user where userid=#{userid}
        </select>
        <insert id="addUser" parameterType="User">
            insert into user(userid,password,username) values(#{userid},#{password},#{username})        
        </insert>
        <delete id="deleteUser" parameterType="String">
            delete from user where userid=#{id}
        </delete>
        <update id="updateUser" parameterType="User">
            update user set password=#{password} where userid=#{userid}
        </update>
        
        <delete id="deleteBatchById" parameterType="String">
            delete from user where userid in
            <foreach collection="array" item="ids" separator="," open="(" close=")">
                #{ids}
            </foreach>
        </delete>
    </mapper>

9、针对Oracle和MySQL数据库的批量追加功能稍后微博奉上，或者直接联系我。

--------------------

10、关于SSM的批量插入数据的功能实现，今天有时间整理如下，首先我们预设实验场景，即在完成数据的批量删除的同时将被删除的数据存于备份表中，因为Oracle和MySQL的批量存储的SQL文不同，所以这里分开进行阐述。  
1）、SSM框架完成批量保存-MySQL  
①，MySQL表结构如下，user是用户表，而user\_bak是user的备份表，其结构与user表一致，通过如下语句复制而来，代码及表结构如下：  
![这里写图片描述][SouthEast 2]

②、在删除备询数据时将删除数据存于user\_bak表中  
批量删除页面效果  
![这里写图片描述][SouthEast 3]

当选中7号和8号用户后，单击删除按钮开始批量删除，详情见本篇博客前部分。这里要补充是完成批量插入功能的实现。因为实验场景，所以这里采用常规手段，现将被删除的用户信息从数据库中检索出来然后再将数据保存到备份表中

![这里写图片描述][SouthEast 4]

userDao的saveBatchUser\_MySQL代码如下

public int saveBatchUser_MySQL(List<User> users);

MyBatis配置MySQL批量存储的SQL文如下  
![这里写图片描述][SouthEast 5]

2）、SSM框架完成批量保存-Oracle  
其实Oracle和MySQL批量存储功能的差别，从代码角度看，主要的差别就是SQL语句不同而已  
MyBatis配置Oracle批量存储的SQL文如下  
![这里写图片描述][SouthEast 6]

数据库效果如下  
![这里写图片描述][SouthEast 7]

至此：SSM框架CRUD操作&批量删除&批量追加数据（Oracle&MySQL数据库）文章的所有功能完毕，欢迎分享交流

[http_blog.csdn.net_dl0246_article_details_77996811]: http://blog.csdn.net/dl0246/article/details/77996811
[http_blog.csdn.net_dl0246_article_details_77966611]: http://blog.csdn.net/dl0246/article/details/77966611
[http_blog.csdn.net_dl0246_article_details_78251319]: http://blog.csdn.net/dl0246/article/details/78251319
[http_blog.csdn.net_dl0246_article_details_78259612]: http://blog.csdn.net/dl0246/article/details/78259612
[http_blog.csdn.net_dl0246_article_details_78274343]: http://blog.csdn.net/dl0246/article/details/78274343
[http_blog.csdn.net_dl0246_article_details_78286685]: http://blog.csdn.net/dl0246/article/details/78286685
[http_blog.csdn.net_dl0246_article_details_78390587]: http://blog.csdn.net/dl0246/article/details/78390587
[SouthEast]: /images/20220608/1d18aee470554c26a5547f741c57abbf.png
[SouthEast 1]: /images/20220608/b040cff45cad4b04a412fe4a525a5bef.png
[SouthEast 2]: /images/20220608/c13f63f49a5a4bcbb4f8485adb80f612.png
[SouthEast 3]: /images/20220608/c4913cbcd89e4ce091c4cfc75682a992.png
[SouthEast 4]: /images/20220608/f9aa17e2e1e54cdcbee46397e525f990.png
[SouthEast 5]: /images/20220608/b0cbce64bb1a4d15a76bfc7e6bcc8504.png
[SouthEast 6]: /images/20220608/c54f770ab7ba40e29d3d27aa1c39b286.png
[SouthEast 7]: /images/20220608/5ad5f768ef9d4e6b90a07ea9e800a472.png