这篇文章是在我的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>
<script
src="${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" οnclick="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深度序列化,在此改为true
data : {
"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 {
@Autowired
private 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