一、修改数据
1.当修改数据时,会先判断该属性是否存在或者是否为空串
<update id="editStaffItem">
update staff
<set>
<if test='name!=null and name!=""'>
name=#{name},
</if>
<if test="salary!=null">
salary=#{salary},
</if>
</set>
<where>
id=#{id}
</where>
</update>
2.在Dao层声明该方法
int editStaffItem(Staff staff);
3.在Controller类中,编写代码来接收http请求
@PutMapping("staffitem")
public String editStaffItem(Staff staff) {
dao.editStaffItem(staff);
return "success";
}
二、模糊查询
1.在Dao层声明方法
List<Staff> getStaff(String checktext);
将查询的结果放在一个List中
2.在Mapper文件中编写数据库操作
<!-- resultType查出来的结果中的每一行都要映射成该类型的对象 -->
<select id="getStaff" resultType="com.easy.bean.Staff">
select * from staff
<!-- 根据参数不同组合出不同的SQL语句 动态SQL语句 标签-->
<where>
<!-- 编写条件语句 如果where标签中有内容 会自动添加where关键字 -->
<if test="checktext !=null and checktext !=''">
<!-- 重新定义参数内容 -->
<bind value="'%'+checktext+'%'" name="liketext"></bind>
name like #{liketext}
</if>
</where>
</select>
3.控制类
@GetMapping("staff")
public CommonResult getStraff(String checktext) {
List<Staff> list=dao.getStaff(checktext);
return CommonResult.success(list);
}
三、根据工资的等级来获取职员的信息
1.Dao层
List<Staff> getStaffBySalary(String salarytext);
2.Mapper文件
<select id="getStaffBySalary" resultType="com.easy.bean.Staff">
select * from staff
<where>
<!-- 参数名 salarytext -->
<choose>
<when test='salarytext == "低"'>
salary <= 5000
</when>
<when test='salarytext == "中"'>
salary >5000 and salary<=8000
</when>
<otherwise>
salary>8000
</otherwise>
</choose>
</where>
</select>
resultType表示输出的对象类型
3.控制类
@GetMapping("staff/salary")
public CommonResult getStaffBySalary(String salarytext) {
List<Staff> list=dao.getStaffBySalary(salarytext);
return CommonResult.success(list);
}
四、一次添加多组数据
1.Dao
int addList(List<Staff> list);
2.Mapper文件
<insert id="addList" >
insert into staff(code,name,salary,username,userpass)
values
<foreach collection="list" item="it" separator=",">
(#{it.code},#{it.name},#{it.salary},#{it.username},#{it.userpass})
</foreach>
</insert>
3.控制类
@PostMapping("staff")
public String addStaff(Staff staff) {
staff=new Staff();
staff.setCode("10001");
staff.setName("李思思");
staff.setSalary(new BigDecimal(2000));
staff.setUsername("lisisi");
staff.setUserpass("123123");
List list=new ArrayList();
list.add(staff);
staff=new Staff();
staff.setCode("10002");
staff.setName("小甜甜");
staff.setSalary(new BigDecimal(2000));
staff.setUsername("牛夫人");
staff.setUserpass("123123");
list.add(staff);
// dao.addStaff(staff);
dao.addList(list);
return "success";
}
五、Dao中接收多个参数
如果Dao中接收两个以上的参数就要使用@Param注解给参数起名字
1.Dao
int edit(@Param("id") int id,@Param("staff") Staff staff);
2.Mapper
<update id="edit">
upadate staff code=#{staff.code},name=#{staff.name}set where id=#{id}
</update>
六、多张表的映射
查询Staff表时也会显示Department表的部门信息
1.在Staff类中添加Department类的属性
private Department dep;
public Department getDep() {
return dep;
}
public void setDep(Department dep) {
this.dep = dep;
}
2.Dao
List<Staff> getStaffAndDep();
3.Mapper
<!-- 一对一或一对多查询需要制定映射方式 resultMap -->
<resultMap id="staffAndDep" type="com.easy.bean.Staff">
<association column="dep_id" select="getStaffDep" property="dep" ></association>
</resultMap>
<select id="getStaffDep" resultType="com.easy.bean.Department">
select * from department where id=#{dep_id}
</select>
<select id="getStaffAndDep" resultMap="staffAndDep" >
select * from staff
</select>
resultMap来定制映射的方式
type定义返回的对象是Staff类,通过dep_id来查询,将查询的内容放在dep列中,查询方法是select中指向的getStaffDep。
一对一用association
七、示例
查询部门时能显示内部职员的信息,一对多
1.Department
package com.easy.bean;
import java.io.Serializable;
import java.util.List;
public class Department implements Serializable {
private int id;
private String code;
private String name;
private List<Staff> staffList;
public List<Staff> getStaffList() {
return staffList;
}
public void setStaffList(List<Staff> staffList) {
this.staffList = staffList;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
2.Dao
package com.easy.dao;
import com.easy.bean.Department;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface IDepartmentDao {
int addDepartment(Department department);
int delDepartment(int id);
int editDepartment(Department department);
List<Department> getDep();
}
3.Mapper
<?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.easy.dao.IDepartmentDao">
<cache eviction="FIFO" flushInterval="60000" size="512" readOnly="true" />
<insert id="addDepartment">
insert into department(code,name)
value(#{code},#{name})
</insert>
<delete id="delDepartment">
delete from department where id=#{id}
</delete>
<update id="editDepartment">
update department set name=#{name} where id=#{id}
</update>
<resultMap id="departmentAndStaff" type="com.easy.bean.Department">
<!-- <id property="id" column="id"></id>-->
<!-- <result column="name" property="name"></result>-->
<result column="id" property="id"></result>
<collection fetchType="lazy" property="staffList" column="id" select="getDepStaff"></collection>
</resultMap>
<select id="getDepStaff" resultType="com.easy.bean.Staff">
select *from staff where dep_id=#{id}
</select>
<select id="getDep" resultMap="departmentAndStaff">
select * from department
</select>
</mapper>
4.控制类
package com.easy.controller;
import com.easy.bean.Department;
import com.easy.common.CommonResult;
import com.easy.dao.IDepartmentDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.*;
import java.util.List;
@RestController
public class DepartmentController {
@Autowired
IDepartmentDao dao;
@PostMapping("department")
public String addDepartment(Department department){
dao.addDepartment(department);
return "添加成功";
}
@DeleteMapping("department/{id}")
public String delDepartment(@PathVariable int id){
dao.delDepartment(id);
return "删除成功";
}
@PutMapping("department")
public String editDepartment(Department department){
dao.editDepartment(department);
return "修改成功";
}
@GetMapping ("dep")
// @Transactional
public CommonResult getDep(){
List<Department> list =dao.getDep();
// System.out.println("---------");
// list =dao.getDep();
return CommonResult.success(list);
}
}
八、缓存
运行一次,就会加载一次
1.可以通过在Mapper文件中添加
<cache eviction="FIFO" flushInterval="60000" size="512" readOnly="true"
然后将在映射方式中加上fetchType属性
<collection fetchType="lazy" property="staffList" column="id" select="getDepStaff"></collection>
只有在系统调用时才会加载
2.@Transactional
在需要缓存的方法上加上@Transactional注解,并在启动类上加上@EnableTransactionManagement