mybatis第二天

1> 课堂案例做一遍

2> 备份emp表，表明 emp_bak;

查询方法 List（String tabname,String firstname,String email,int max,int min）：传入表名，是从原表中获取数据还是从备份的表中获取数据

List（String tabname,String firstname,String email,int max,int min）
List(Map map);

3>用户登录信息和用户基本信息[一对一的关系]

用户登录，同时获取用户的基本信息

Users find(String name,String password);//获取用户信息以及用户的橘色信息

3>用户登录信息与角色信息[]

List find(int userid);//根据用户编号获取用户所有的角色信息

4> 查询员工信息如果是男，查询所再的部门，如果是女查询的job信息

：目录结构

![在这里插入图片描述](https://img-blog.csdnimg.cn/f5c15a559f99483eae0a1689b44ab036.png
案例一:查出全部员工信息的同时查出相关部门:
Dept类:

import lombok.Data;

import java.util.List;

@Data
public class Dept {
    private Integer departmentId;
    private String departmentName;
    private Integer managerId;
    private Integer locationId;
    private List<Emp> list;
}
1
2
3
4
5
6
7
8
9
10
11
12

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 com.cdcas.pojo.Emp;

import java.util.List;

public interface EmpMapper {
    List<Emp> findAllAndDept();
}
1
2
3
4
5
6
7

EmpMapper.xml:

<mapper namespace="com.cdcas.mapper.EmpMapper">
    <resultMap id="empMap" type="com.cdcas.pojo.Emp">
        <id column="employee_id" property="empid"/>
        <id column="first_name" property="empfirstname"/>
        <id column="last_name" property="emplastname"/>
        <id column="email" property="empemail"/>
        <id column="phone_number" property="empphone"/>
        <id column="job_id" property="empjob"/>
        <id column="salary" property="empsalary"/>
        <id column="manager_id" property="empmanager"/>
        <id column="department_id" property="deptid"/>
        <id column="hiredate" property="emphiredatey"/>
    resultMap>
    <resultMap id="empMap4" type="com.cdcas.pojo.Emp" extends="empMap">
        
        <association property="dept" javaType="com.cdcas.pojo.Dept">
            <id column="department_id" property="departmentId"/>
            <result column="department_name" property="departmentName"/>
            <result column="location_id" property="locationId"/>
            <result column="manager_id" property="managerId"/>
        association>
    resultMap>
    <select id="findAllAndDept" resultMap="empMap4">
        SELECT e.*, d.`department_name`, d.`manager_id`, d.`location_id`
        FROM employees e
                 LEFT JOIN departments d ON e.`department_id` = d.`department_id` select>
mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

第二种方式:只查询员工信息，然后根据查询到员工信息中部门编号再去查找他的部门信息
!!!—> 这里会有懒加载：会自动调用findById 这里的department_id为查出来的作为参数
EmpMapper.xml:

    <resultMap id="empMap3" type="com.cdcas.pojo.Emp" extends="empMap">
        
        <association property="dept" column="department_id"
                     select="com.cdcas.mapper.DeptMapper.findById"
                     fetchType="lazy"/>
    resultMap>
    <select id="findById" resultMap="empMap3">
        select *
        from employees
    select>
1
2
3
4
5
6
7
8
9
10

DeptMapper:中的findById会被调用:

import com.cdcas.pojo.Dept;

public interface DeptMapper {
    Dept findById(Integer id);
}

1
2
3
4
5
6

DeptMapper.xml:

    <select id="findById" resultType="com.cdcas.pojo.Dept">
        SELECT * FROM departments WHERE department_id=#{id}
    select>

1
2
3
4

案例2：查找部门的同时要找到部门下所有的员工信息
DeptMapper.xml：

<mapper namespace="com.cdcas.mapper.DeptMapper">
    <resultMap id="emp" type="com.cdcas.pojo.Emp">
        <id column="employee_id" property="empid"/>
        <id column="first_name" property="empfirstname"/>
        <id column="last_name" property="emplastname"/>
        <id column="email" property="empemail"/>
        <id column="phone_number" property="empphone"/>
        <id column="job_id" property="empjob"/>
        <id column="salary" property="empsalary"/>
        <id column="manager_id" property="empmanager"/>
        <id column="department_id" property="deptid"/>
        <id column="hiredate" property="emphiredatey"/>
    resultMap>
    <resultMap id="baseDeptMap" type="com.cdcas.pojo.Dept">
        <result column="department_id" property="departmentId"/>
        <result column="department_name" property="departmentName"/>
        <result column="location_id" property="locationId"/>
        <result column="manager_id" property="managerId"/>
    resultMap>
    <resultMap id="deptMap1" type="com.cdcas.pojo.Dept" extends="baseDeptMap">
        <collection property="list" resultMap="emp">
        collection>
    resultMap>


    <select id="findAll" resultMap="deptMap1">
        SELECT * FROM departments d LEFT JOIN employees e ON d.`department_id`=e.`department_id`
    select>
    <select id="findById" resultType="com.cdcas.pojo.Dept">
        SELECT * FROM departments WHERE department_id=#{id}
    select>
mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

第二种方法：只查询部门信息，然后再调用其他的方法找到部门下的人,N+1问题。
这里的+1就是查询所有部门的操作，与方法一不同的就是方法二先查出所有的部门，而不是同时查出每个部门下懂的员工
DeptMapper.xml:

DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cdcas.mapper.DeptMapper">
    <resultMap id="emp" type="com.cdcas.pojo.Emp">
        <id column="employee_id" property="empid"/>
        <id column="first_name" property="empfirstname"/>
        <id column="last_name" property="emplastname"/>
        <id column="email" property="empemail"/>
        <id column="phone_number" property="empphone"/>
        <id column="job_id" property="empjob"/>
        <id column="salary" property="empsalary"/>
        <id column="manager_id" property="empmanager"/>
        <id column="department_id" property="deptid"/>
        <id column="hiredate" property="emphiredatey"/>
    resultMap>
    <resultMap id="baseDeptMap" type="com.cdcas.pojo.Dept">
        <result column="department_id" property="departmentId"/>
        <result column="department_name" property="departmentName"/>
        <result column="location_id" property="locationId"/>
        <result column="manager_id" property="managerId"/>
    resultMap>
    <resultMap id="deptMap1" type="com.cdcas.pojo.Dept" extends="baseDeptMap">
        <collection property="list" resultMap="emp">
        collection>
    resultMap>
    <resultMap id="deptMap2" type="com.cdcas.pojo.Dept" extends="baseDeptMap">
        <collection property="list" select="com.cdcas.mapper.EmpMapper.findByDeptId"
                    column="department_id"
                    fetchType="eager"/>
    resultMap>


    <select id="findAll" resultMap="deptMap1">
        SELECT *
        FROM departments d
                 LEFT JOIN employees e ON d.`department_id` = e.`department_id`
    select>
    <select id="findById" resultType="com.cdcas.pojo.Dept">
        SELECT *
        FROM departments
        WHERE department_id = #{id}
    select>
    <select id="findDeptAndEmp" resultMap="deptMap2">
        select *from departments
    select>
mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

查询方法 List（String tabname,String firstname,String email,int max,int min）：传入表名，是从
原表中获取数据还是从备份的表中获取数据

传那个表的名字就是那个表

List(Map map);

2>用户登录信息和用户基本信息[一对一的关系]
用户登录，同时获取用户的基本信息
UserInFo:

import lombok.Data;

@Data
public class UserInFO {
    private Integer userId;
    private String  userName;
    private String  sex;
    private Integer age;
}

1
2
3
4
5
6
7
8
9
10

User

import lombok.Data;

@Data
public class User {
    private Integer userId;
    private String userName;
    private String userPwd;
    private String userStatus;
    private UserInFO userInFo;
}

1
2
3
4
5
6
7
8
9
10
11

Usermapper.xml:

DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cdcas.mapper.UserMapper">
    <resultMap id="user" type="com.cdcas.pojo.User">
        <id column="userid" property="userId"/>
        <result column="username" property="userName"/>
        <result column="userpwd" property="userPwd"/>
        <result column="user_status" property="userStatus"/>
        <association property="userInFo" javaType="com.cdcas.pojo.UserInFO">
            <id column="userid" property="userId"/>
            <result column="username" property="userName"/>
            <result column="sex" property="sex"/>
            <result column="age" property="age"/>
        association>
    resultMap>
    <select id="find" resultMap="user">
        select *
        from sys_users a
                 left join userinfo b on a.userid = b.userid
        where a.username = #{param1}
          and a.userpwd = #{param2}
    select>
mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

3>用户登录信息与角色信息
Role:

import lombok.Data;

import java.util.List;

@Data
public class Role {
    private Integer rid;
    private String rName;
    List<User> users;
}


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

重新修改一下我们的userMapper.xml：

最后的结果:

4> 查询员工信息如果是男，查询所再的部门，如果是女查询的job信息