SSM之Mybatis查询以及批量操作

来源：互联网发布：代理ip api python 编辑：程序博客网时间：2024/05/17 01:07

这篇文章是在我的SpringMVC前后端数据交互基础上把数据库环节打通，主要涉及mybatis的查询（传多个参数），批量删除，新增，修改，数据库是MySQL，注释很详细，直接看代码即可：

JSP

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><%String path = request.getContextPath();String basePath = request.getScheme() + "://"+ request.getServerName() + ":" + request.getServerPort()+ path + "/";%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head><title>mybatis测试页面</title><script type="text/javascript"src="${pageContext.request.contextPath}/static/jquery/jquery-1.9.1.min.js"></script><scriptsrc="${pageContext.request.contextPath}/static/bootstrap-3.3.5/js/bootstrap.min.js"></script><link rel="stylesheet"href="${pageContext.request.contextPath}/static/bootstrap-3.3.5/css/bootstrap-theme.css"></link><link rel="stylesheet"href="${pageContext.request.contextPath}/static/bootstrap-3.3.5/css/bootstrap.css"type="text/css"></link><link rel="stylesheet"href="${pageContext.request.contextPath}/static/bootstrap-3.3.5/css/bootstrap.min.css"type="text/css"></link><script type="text/javascript">$(document).ready(function() {//　});</script></head><body><br /><button type="button" onclick="test()">发送按钮</button><p></p><p>-----------1.条件(模糊)查询，2.一对一，一对多，多对多查询，2.批量删除，批量修改，批量新增，4.分页查询------------<p><p>--------------------------------------------------------------------------------------------------------------------------------------------------</p><p>查询结果集------：</p><p id="data"></p><p>---批量删除---</p><div><!-- 复选框，批量删除demo演示--><label class="checkbox-inline"> <input type="checkbox" name="deleteCheckBox"id="inlineCheckbox1" value="11"> 选项 1</label> <label class="checkbox-inline"> <input type="CheckBox" name="deleteCheckBox"id="inlineCheckbox2" value="22"> 选项 2</label> <label class="checkbox-inline"> <input type="CheckBox" name="deleteCheckBox"id="inlineCheckbox3" value="33"> 选项 3</div><!-- 创建表脚本 --><!--CREATE TABLE users (  id int(5) NOT NULL auto_increment,  name varchar(20)NOT NULL,  PRIMARY KEY  (`id`))charset utf8 collate utf8_general_ci;  --><script>function test() {//单个参数查询/* $.ajax({type : 'post',url : "${pageContext.request.contextPath}/mybatis/queryByAge",data:{age:"28"},dataType : "json",success : function(data) {console.log(data);//JSON.stringify(data)$("#data").text(JSON.stringify(data));},error : function() {alert("查询失败");}}); *///多个参数查询//1.用注解/* $.ajax({type : 'post',url : "${pageContext.request.contextPath}/mybatis/queryEmp",data:{queryAge:"28",queryNme:"李大嘴",deptno:"20"},dataType : "json",success : function(data) {console.log(data);$("#data").text(JSON.stringify(data));},error : function() {alert("查询失败");}}); *///2.用map/* $.ajax({type : 'post',url : "${pageContext.request.contextPath}/mybatis/queryEmpWithMapParam",data:{queryAge:"28",queryNme:"李大嘴",deptno:"20"},dataType : "json",success : function(data) {console.log(data);$("#data").text(JSON.stringify(data));},error : function() {alert("查询失败");}}); *///插入数据，主键自增/* var jsonObj = {"empAge" : "111","empName" : "卡丽熙","deptNo" : "12138"};$.ajax({type : 'post',url : '${pageContext.request.contextPath }/mybatis/addEmp',contentType : 'application/json;charset=utf-8',//指定为json类型，这个属性是配合注解@RequestBody使用的//数据格式是json串data : JSON.stringify(jsonObj),dataType : "json",success : function(data) {//返回json结果console.log(data)//插入成功打印数字 1 alert("成功");}}); *///批量删除数据/* var checkBoxArray=[];$("input[name='deleteCheckBox']:checked").each(function () {checkBoxArray.push(this.value)            });$.ajax({type : 'post',url : '${pageContext.request.contextPath }/mybatis/deleteBatch',traditional : true,//注意，必须要有个设置否则传递数组报400错误。默认为false深度序列化，在此改为truedata : {"array" : checkBoxArray},success : function(data) {//返回json结果console.log(data)//插入成功打印数字 1 alert("批量删除成功");},error : function() {alert("查询失败");}}); *///批量新增/* var empObjList=[];            var emp1={    "empAge" : "1111",    "empName" : "布兰",    "deptNo" : "1"    };            var emp2={    "empAge" : "2222",    "empName" : "艾丽娅",    "deptNo" : "2"    };            var emp3={    "empAge" : "3333",    "empName" : "罗伯",    "deptNo" : "3"    }             empObjList.push(emp1);            empObjList.push(emp2);            empObjList.push(emp3);$.ajax({type : 'post',url : '${pageContext.request.contextPath }/mybatis/addEmpByBatch',contentType : 'application/json;charset=utf-8',//指定为json类型//数据格式是json串,多个对象用[]包装data : JSON.stringify(empObjList),success : function(data) {console.log(data); alert("批量新增成功");},error : function() {alert("失败");}}); *///批量修改var empObjList=[];            var emp1={    "empAge" : "15",    "empName" : "布兰update",    "deptNo" : "1"    };            var emp2={    "empAge" : "19",    "empName" : "艾丽娅update",    "deptNo" : "2"    };            var emp3={    "empAge" : "26",    "empName" : "罗伯update",    "deptNo" : "3"    }             empObjList.push(emp1);            empObjList.push(emp2);            empObjList.push(emp3);$.ajax({type : 'post',url : '${pageContext.request.contextPath }/mybatis/updateEmpByBatch',contentType : 'application/json;charset=utf-8',//指定为json类型//数据格式是json串,多个对象用[]包装data : JSON.stringify(empObjList),success : function(data) {console.log(data.toString()); alert("批量修改成功");},error : function() {alert("失败");}});}</script></body></html>

controller

package com.wonders.controller;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.RequestBody;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.wonders.entity.Emp;import com.wonders.entity.User;import com.wonders.service.EmpService;import com.wonders.service.UserService;/** * AJAX+mybatis+mysql各种情况下查询 * 传数组批量 * 多个参数封装 （map @param） * @author Administrator * */@Controller@RequestMapping("/mybatis")public class MyBatisController {@Autowiredprivate EmpService empService;//http://127.0.0.1:8080/ssmmaven/mybatis/showView@RequestMapping("/showView")      public ModelAndView showView(){    ModelAndView mv = new ModelAndView();     mv.setViewName("mybatis");        return mv;      }////////////////////////////////////////////////////////////////////////////** * 单个参数查询 * @param age * @return */@RequestMapping("/queryByAge")public @ResponseBody List<Emp> queryByAge(String age){return empService.selectEmpByAge(age);}/////////////////////////////////////////////////////////////////////////////////** * 多个参数查询 * 1.注解 */@RequestMapping("/queryEmp")//public @ResponseBody Emp queryEmp(String queryAge, String queryNme,Integer deptno){Emp emp=empService.selectEmp(queryAge, queryNme, deptno);System.out.println("controller："+emp);return emp;}/** * 2.多个参数用map封装 */@RequestMapping("/queryEmpWithMapParam")//public @ResponseBody Emp queryEmpWithMapParam(String queryAge, String queryNme,Integer deptno){Map<String,Object> map=new HashMap<String,Object>();map.put("age", queryAge);map.put("name", queryNme);map.put("deptno", deptno);Emp emp=empService.queryEmpWithMapParam(map);System.out.println("controller："+emp);return emp;}/** * 插入数据 主键自增，更新操作同理 */@RequestMapping("/addEmp")//public @ResponseBody Integer addEmp(@RequestBody Emp emp){return empService.addEmp(emp);}/** * 批量删除 */@SuppressWarnings("null")@RequestMapping(value="/deleteBatch")public @ResponseBody Integer  deleteBatch(@RequestParam(value="array") Integer[] array){   if (array == null && array.length <= 0) {            return 0;        }return empService.deleteEmpBatch(array);    }/** * 批量新增 */@RequestMapping(value="/addEmpByBatch")public @ResponseBody Integer addEmpByBatch(@RequestBody ArrayList<Emp> emps){  return empService.addEmpByBatch(emps);    }/** * 批量更新  jdbc.properties文件添加----allowMultiQueries=true */@RequestMapping(value="/updateEmpByBatch")public @ResponseBody Integer updateEmpByBatch(@RequestBody ArrayList<Emp> emps){  return empService.updateEmpByBatch(emps);    }}

直接看mapper和mapper.xml

package com.wonders.dao;import java.util.List;import java.util.Map;import org.apache.ibatis.annotations.Param;import com.wonders.entity.Emp;public interface EmpMapper {//单个参数查询public List<Emp> selectEmpByAge(String age);//多个参数查询用@Param注解传参数public Emp selectEmp(@Param("age") String queryAge,@Param("name") String queryNme,@Param("deptno") Integer deptno);//多个参数查询用Map封装public Emp queryEmpWithMapParam(Map<String,Object> map);//插入数据主键自增public int addEmp(Emp emp);//checkBox批量删除public int deleteEmpBatch(Integer[] array);//批量新增public int addEmpByBatch(List<Emp> emps);//批量更新public int updateEmpByBatch(List<Emp> emps); }

<?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.wonders.dao.EmpMapper"><resultMap id="EmpResultMap" type="com.wonders.entity.Emp"><id column="ID" property="empId" jdbcType="INTEGER" /><result column="NAME" property="empName" jdbcType="CHAR" /><result column="AGE" property="empAge" jdbcType="CHAR" /><result column="DEPTNO" property="deptNo" jdbcType="INTEGER" /></resultMap><!-- 单个参数查询 --><select id="selectEmpByAge" parameterType="String" resultMap="EmpResultMap">SELECT * FROM EMP WHERE AGE = #{age}</select><!--多个参数查询@Param注解封装--><select id="selectEmp"  resultMap="EmpResultMap">SELECT * FROM EMP WHERE AGE = #{age,jdbcType=VARCHAR} AND NAME=#{name,jdbcType=VARCHAR}  AND DEPTNO=#{deptno,jdbcType=DECIMAL}</select><!--多个参数查询map封装，直接用key就可以取值，parameterType="map"不写也可以--><select id="queryEmpWithMapParam" parameterType="map" resultMap="EmpResultMap">SELECT * FROM EMP WHERE AGE = #{age,jdbcType=VARCHAR} AND NAME=#{name,jdbcType=VARCHAR}  AND DEPTNO=#{deptno,jdbcType=DECIMAL}</select><!--插入数据，主键自增  --><insert id="addEmp" parameterType="com.wonders.entity.Emp"useGeneratedKeys="true">insert into EMP(NAME,AGE,DEPTNO) values(#{empName},#{empAge},#{deptNo})</insert><!-- 批量删除 --><delete id="deleteEmpBatch" parameterType="Integer">        delete from EMP where         <foreach item="check_value" collection="array" open="ID in ("            separator="," close=")">            #{check_value}        </foreach>    </delete>    <!-- 批量新增-->    <insert id="addEmpByBatch" parameterType="java.util.List" useGeneratedKeys="true">    insert into EMP (NAME,AGE,DEPTNO)    values     <foreach collection="list" item="item" index="index" separator="," >        (#{item.empName},#{item.empAge},#{item.deptNo})    </foreach>    </insert>    <!-- 批量更新 -->    <update id="updateEmpByBatch"  parameterType="java.util.List">      <foreach collection="list" item="item" index="index" separator=";">       update EMP       <set >          <if test="item.empName != null" >            NAME = #{item.empName,jdbcType=VARCHAR},          </if>          <if test="item.empAge != null" >            AGE = #{item.empAge,jdbcType=VARCHAR},          </if>        </set>        where DEPTNO = #{item.deptNo,jdbcType=INTEGER}    </foreach>       </update></mapper>

数据库配置文件注意的地方：

jdbc_url=jdbc:mysql://localhost:3306/ssm?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true

阅读全文

2 0