1>动态sql练习关于员工的条件查询+分页
//firstname,lastname,email,salary,departid
List getPageData(EmpVo vo);//分页
int getCount(EmpVo vo);
List findByIds(List ids);
void save(List list);
void update(Employee employee);//set
缓存的练习
测试一级缓存,一级缓存失效的情况
测试二级缓存,了解二级缓存失效
很好的表达出一级缓存和二级缓存,以及代码正确的分析
Employeesvo类:
import lombok.Data;
@Data
public class Employeesvo {
private String firstName;
private String lastName;
private Integer minSalary;
private Integer maxSalary;
private String email;
private Integer departId;
}
Emp类:
import lombok.Data;
import java.sql.Date;
@Data
public class Emp {
private Integer empid;
private String empfirstname;
private String emplastname;
private String empemail;
private String empphone;
private String empjob;
private Double empsalary;
private Integer empmanager;
private Integer deptid;
private Date emphiredatey;
private Dept dept;
}
EmpMapper:
import java.util.List;
public interface EmpMapper {
List<Emp> findAllAndDept();
List<Emp> findById();
Emp findByDeptId(Integer id);
List<Emp> getPageData(Employeesvo vo);//分页
int getCount(Employeesvo vo);
List<Emp> findByIds(List<Integer> ids);
void save(List<Emp> list);
void update(Emp employee);//set
}
List getPageData(EmpVo vo):
<select id="getPageData" resultMap="empMap">
select * from employees
<where>
<if test="firstName!=null">
and first_name like #{firstName}
</if>
<if test="lastName!=null">
and last_name like #{firstName}
</if>
<if test="minSalary!=null">
and salary >=#{minSalary}
</if>
<if test="maxSalary!=null">
and salary <=#{maxSalary}
</if>
<if test="departId!=null">
and department_id=#{departId}
</if>
limit 1 ,2
</where>
</select>
int getCount(EmpVo vo);
<select id="getCount" resultType="java.lang.Integer">
select count(*) from employees
<where>
<if test="firstName!=null">
and first_name like #{firstName}
</if>
<if test="lastName!=null">
and last_name like #{firstName}
</if>
<if test="minSalary!=null">
and salary >=#{minSalary}
</if>
<if test="maxSalary!=null">
and salary <=#{maxSalary}
</if>
<if test="departId!=null">
and department_id=#{departId}
</if>
</where>
</select>
List findByIds(List ids);
<select id="findByIds" resultMap="empMap">
select *
from employees
where employee_id in
<foreach collection="ids" item="id" index="index" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
void save(List list);
<insert id="save">
insert into employees values
<foreach collection="list" item="items" index="index" separator=",">
(
null,
#{items.firstName},
#{items.lastName},#{items.email},#{items.phoneNumber},#{items.jobId},
#{items.salary},
#{items.commissionPct}
#{items.managerId}
#{items.departmentId}
#{items.hiredate}
)
</foreach>
</insert>
void update(Employee employee);//set
<update id="update">
update employees
<set>
<if test="firstName!=null and firstName!=' '">
first_name=#{empfirstname}
</if>
<if test="lastName!=null and lastName!=' '">
last_name=#{emplastname}
</if>
<if test="email!=null and email!=''">
email=#{empemail}
</if>
</set>
</update>
测试一级缓存,一级缓存失效的情况
一级缓存:同一个sqlsession同一个namespace(大条件)–在两个相同的操作之间有增删改的操作会缓存失效。同sqlsession不同namespace失效,同namespace不同SQL session失效。时间关系演示一个。