Mybatis第三天

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;
}

1
2
3
4
5
6
7
8
9
10
11
12
13

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;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

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
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

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>
1
2
3
4
5
6
7
8
9

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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14

测试一级缓存，一级缓存失效的情况
一级缓存：同一个sqlsession同一个namespace(大条件)–在两个相同的操作之间有增删改的操作会缓存失效。同sqlsession不同namespace失效，同namespace不同SQL session失效。时间关系演示一个。