fetchtype lazy
MyBatis动态sql
一:if,where,bind
if标签
where标签
bind标签
模糊查询应用示例
<!-- 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>
List<Staff> getStaff(String checktext);
@GetMapping("staff")
public CommonResult getStaff(String checktext){
List<Staff> list=dao.getStaff(checktext);
return CommonResult.success(list);
}
二:foreach
foreach标签
foreach标签添加数据示例
<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>
int addList(List<Staff> list);
@PostMapping("multiadd")
public String addList(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.addList(list);
return "success";
}
三:choose、when和otherwise标签
查询使用choose,when,otherwise示例
注意代码中判断字符串是否相等的两种写法写法
<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>
List<Staff> getStaffBySalary(String salarytext);
@GetMapping("staff/salary")
public CommonResult getStaffBySalary(String salarytext){
List<Staff> list=dao.getStaffBySalary(salarytext);
return CommonResult.success(list);
}
四:dao中接收多个参数
<update id="edit">
update staff set code=#{staff.code},name=#{staff.code} where id=#{id}
</update>
//如果dao中接收两个以上的参数,就要使用@Param注解给参数起别名
int edit(@Param("id")int id,@Param("staff")Staff staff);
resultMap
一对一查询或一对多查询需要指定映射方式 resultMap
colum对应数据库的字段,property对应实体类的属性
一对一关联查询
<resultMap id="staffAndDept" type="com.easy.bean.Staff">
<association column="dept_id" select="getStaffDept" property="dept">
</association>
</resultMap>
<select id="getStaffDept" resultType="com.easy.bean.Department">
select * from department where id=#{dept_id}
</select>
<select id="getStaffAndDept" resultMap="staffAndDept">
select * from staff
</select>
List<Staff> getStaffAndDept();
@GetMapping("staff/sad")
public CommonResult getStaffAndDept( ){
List<Staff> list=dao.getStaffAndDept();
return CommonResult.success(list);
}
一对多关联查询
<resultMap id="departmentAndStaff" type="com.easy.bean.Department">
<!-- <id column="id" property="deptid"/></id>-->
<!-- <result column="name" property="deptname"></result>-->
<result column="id" property="id"></result>
<collection property="staffList" column="id" select="getDeptStaff"></collection>
</resultMap>
<select id="getDeptStaff" resultType="com.easy.bean.Staff">
select * from staff where dept_id=#{id}
</select>
<select id="getDept" resultMap="departmentAndStaff">
select * from department
</select>
List<Department> getDept();
@GetMapping("dept")
public CommonResult getDept(){
List<Department> list=dao.getDept();
System.out.println("------------");
return CommonResult.success(list);
}
另外
MyBatis缓存
一级缓存
二级缓存
fetchType=“lazy“
懒加载,加载一个实体时,定义懒加载的属性不会马上从数据库中加载。开启延迟加载后,在真正使用数据的时候才发起级联查询,不用的时候不查询。