java基础-day48-Mybatis02

image.png

一、ORM映射【重点】

---

1.1 MyBatis自动ORM失效

MyBatis只能自动维护库表”列名“与”属性名“相同时的一一对应关系，二者不同时，无法自动ORM。

自动ORM失效
image.png

1.2 方案一：列的别名

在SQL中使用 as 为查询字段添加列别名，以匹配属性名。

<mapper namespace="com.qf.mybatis.part2.orm.ManagerDao">
    <select id="selectManagerByIdAndPwd" resultType="com.qf.mybatis.part2.orm.Manager">
        SELECT mgr_id AS id , mgr_name AS username , mgr_pwd AS password
        FROM t_managers
        WHERE mgr_id = #{id} AND mgr_pwd = #{pwd}
    </select>
</mapper>

1.3 方案二：结果映射（ResultMap - 查询结果的封装规则）

通过< resultMap id="" type="" >映射，匹配列名与属性名。

<mapper namespace="com.qf.mybatis.part2.orm.ManagerDao">

    <!--定义resultMap标签-->
    <resultMap id="managerResultMap" type="com.qf.mybatis.part2.orm.Manager">
        <!--关联主键与列名-->
        <id property="id" column="mgr_id" />
      
        <!--关联属性与列名-->
        <result property="username" column="mgr_name" />
        <result property="password" column="mgr_pwd" />
    </resultMap>
  
     <!--使用resultMap作为ORM映射依据-->
    <select id="selectAllManagers" resultMap="managerResultMap">
        SELECT mgr_id , mgr_name , mgr_pwd
        FROM t_managers
    </select>
</mapper>

二、MyBatis处理关联关系-多表连接【重点】

---

实体间的关系：关联关系（拥有 has、属于 belong）

• OneToOne：一对一关系（Passenger--- Passport）

• OneToMany：一对多关系（Employee --- Department）

• ManyToMany：多对多关系（Student --- Subject）

Table建立外键关系
image.png

Entity添加关系属性
image.png

Mapper中将属性与列名对应
image.png

2.1 OneToOne

1.导入依赖，把之前的db.properties，log4j.properties，mybatis-config.xml拷贝过来并稍作适当修改

<dependencies>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.4</version>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.21</version>
    </dependency>

    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.12</version>
    </dependency>

    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.13</version>
    </dependency>

    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.17</version>
    </dependency>
</dependencies>

2.创建旅客表以及护照表

//旅客表
CREATE TABLE t_passenger(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(50),
    sex VARCHAR(1),
    birthday DATE
)DEFAULT CHARSET =utf8;
 
INSERT INTO t_passenger VALUES(NULL,'zhansan','f','2020-11-11');
INSERT INTO t_passenger VALUES(NULL,'lucy','m','2020-12-12');

//护照表
CREATE TABLE t_passport(
    id INT PRIMARY KEY AUTO_INCREMENT,
    nationality VARCHAR(50),
    expire DATE,
    passenger_id INT UNIQUE
)DEFAULT CHARSET =utf8;


INSERT INTO t_passport VALUES(NULL,'China','2030-11-11',1);
INSERT INTO t_passport VALUES(NULL,'America','2030-12-12',2);

3.创建对应的实体类

package com.qf.pojo;

import lombok.Data;

import java.util.Date;

//旅客表
@Data
public class Passenger {

    private Integer id;
    private String name;
    private String sex;
    private Date birthday;

    private Passport passport;//存储对应的护照信息，关系属性
}

---------------------------------------------------------

package com.qf.pojo;

import lombok.Data;

import java.util.Date;

//护照表
@Data
public class Passport {

    private Integer id;
    private String nationality;
    private Date expire;

}

4.创建Dao

package com.qf.dao;

import com.qf.pojo.Passenger;

public interface PassengerDao {

    //通过旅客id查询旅客信息以及护照信息（关联查询）
    public Passenger findById(Integer id);
}

5.创建PassengerDao.xml配置文件

<?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.qf.dao.PassengerDao">

    <resultMap id="passengerMap" type="Passenger">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <result column="sex" property="sex"></result>
        <result column="birthday" property="birthday"></result>

        <!-- 描述 passportId nationality expire 和  passport 映射规则 -->
        <association property="passport" javaType="Passport">
            <id column="passportId" property="id"></id>
            <result column="nationality" property="nationality"></result>
            <result column="expire" property="expire"/>
        </association>
    </resultMap>

    <!-- 查询旅客及其护照 -->
    <select id="findById" resultMap="passengerMap">
          SELECT p1.id,p1.name,p1.sex,p1.birthday,
          p2.id passportId,p2.nationality,p2.expire
          FROM t_passenger p1 INNER JOIN t_passport p2
          ON p1.id = p2.passenger_id
          WHERE p1.id=#{id};
    </select>

</mapper>

• 注意：指定“一方”关系时（对象），使用< association javaType="" >

6.测试

package com.qf.demo;

import com.qf.dao.PassengerDao;
import com.qf.pojo.Passenger;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.InputStream;

public class MybatisTest {

    @Test
    public void testFindById()throws Exception{

        //准备环境
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession sqlSession = sqlSessionFactory.openSession();
        //-------------------------------------------------------------

        PassengerDao passengerDao = sqlSession.getMapper(PassengerDao.class);

        Passenger passenger = passengerDao.findById(1);

        System.out.println(passenger);

        //-------------------------------------------------------------
        sqlSession.close();
        inputStream.close();
    }
}

2.2 OneToMany

1.创建表

CREATE TABLE t_department(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(50),
    location VARCHAR(100)
)DEFAULT CHARSET =utf8;

INSERT INTO t_department VALUES(1,"教学部","北京"),(2,"研发部","上海");

CREATE TABLE t_employee(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(50),
    salary DOUBLE,
    dept_id INT
)DEFAULT CHARSET =utf8;

INSERT INTO t_employee VALUES(1,"jack",1000.5,1);
INSERT INTO t_employee VALUES(2,"rose",2000.5,1);
INSERT INTO t_employee VALUES(3,"张三",3000.5,2);
INSERT INTO t_employee VALUES(4,"李四",4000.5,2);

2.创建实体类

package com.qf.pojo;

import lombok.Data;

@Data
public class Employee {
    private Integer id;
    private String name;
    private Double salary;

    // 员工从属的部门信息
    private Department department;

}

-----------------------------------------
    
package com.qf.pojo;

import lombok.Data;

import java.util.List;

@Data
public class Department {
    private Integer id;
    private String name;
    private String location;

    // 存储部门中所有员工信息
    private List<Employee> employees;

}

3.创建Dao

package com.qf.dao;

import com.qf.pojo.Employee;
import org.apache.ibatis.annotations.Param;

public interface EmployeeDao {

    // 查询员工信息 并且 查到对应的部门信息
    Employee findEmployeeById(@Param("id") Integer id);
}

---------------------------------------------------------
    
package com.qf.dao;

import com.qf.pojo.Department;
import org.apache.ibatis.annotations.Param;

public interface DepartmentDao {

    // 查询部门，及其所有员工信息
    Department findDepartmentById(@Param("id") Integer id);
}

4.创建EmployeeDao.xml配置文件

<?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.qf.dao.EmployeeDao">

    <resultMap id="empMap" type="Employee">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <result column="salary" property="salary"></result>

        <association property="department" javaType="Department">
            <id column="deptId" property="id"></id>
            <result column="deptName" property="name"></result>
            <result column="location" property="location"></result>
        </association>
    </resultMap>

    <select id="findEmployeeById" resultMap="empMap">
        select e.id,e.name,e.salary,
               d.id deptId ,d.name deptName,d.location
        from t_employee e join t_department d
        on d.id = e.dept_id
        where e.id=#{id}
    </select>

</mapper>

5.创建DepartmentDao.xml配置文件

<?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.qf.dao.DepartmentDao">

    <resultMap id="deptMap" type="Department">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <result column="location" property="location"></result>

        <collection property="employees" ofType="Employee">
            <id column="emp_id" property="id"></id>
            <result column="emp_name" property="name"></result>
            <result column="salary" property="salary"></result>
        </collection>
    </resultMap>

    <select id="findDepartmentById" resultMap="deptMap">
        select d.id ,d.name,d.location,
               e.id emp_id,e.name emp_name,e.salary
        from t_department d join t_employee e
        on d.id = e.dept_id
        where d.id=#{id}
    </select>

</mapper>

• 注意：指定“多方”关系时（集合），使用< collection ofType="" >

6.测试

package com.qf.demo;

import com.qf.dao.DepartmentDao;
import com.qf.dao.EmployeeDao;
import com.qf.pojo.Department;
import com.qf.pojo.Employee;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.InputStream;

public class MybatisTest {

    @Test
    public void findEmployeeById()throws Exception{

        //准备环境
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession sqlSession = sqlSessionFactory.openSession();
        //-------------------------------------------------------------

        EmployeeDao employeeDao = sqlSession.getMapper(EmployeeDao.class);

        Employee employee = employeeDao.findEmployeeById(1);

        System.out.println(employee);
        //-------------------------------------------------------------
        sqlSession.close();
        inputStream.close();
    }

    @Test
    public void findDeptById()throws Exception{

        //准备环境
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession sqlSession = sqlSessionFactory.openSession();
        //-------------------------------------------------------------

        DepartmentDao departmentDao = sqlSession.getMapper(DepartmentDao.class);

        Department department = departmentDao.findDepartmentById(1);

        System.out.println(department);
        //-------------------------------------------------------------
        sqlSession.close();
        inputStream.close();
    }
}

2.3 ManyToMany

建立第三张关系表
image.png

1.创建表

CREATE TABLE t_student(

    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(50),
    sex VARCHAR(1)

)DEFAULT CHARSET = utf8;

INSERT INTO t_student VALUES(1,'jack','m');
INSERT INTO t_student VALUES(2,'rose','f');


CREATE TABLE t_subject(

    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(50),
    grade INT

)DEFAULT CHARSET = utf8;

INSERT INTO t_subject VALUES(1001,'JavaSE',1);
INSERT INTO t_subject VALUES(1002,'JavaEE',2);

CREATE TABLE t_stu_sub(

    student_id INT,
    subject_id INT

)DEFAULT CHARSET = utf8;

INSERT INTO t_stu_sub VALUES(1,1001);
INSERT INTO t_stu_sub VALUES(1,1002);
INSERT INTO t_stu_sub VALUES(2,1001);
INSERT INTO t_stu_sub VALUES(2,1002);

2.创建实体类

package com.qf.pojo;

import lombok.Data;

import java.util.List;

@Data
public class Student {
    private Integer id;
    private String name;
    private String sex;

    private List<Subject> subjects;
   
}

-----------------------------------------

package com.qf.pojo;

import lombok.Data;

import java.util.List;

@Data
public class Subject {
    private Integer id;
    private String name;
    private Integer grade;

    private List<Student> students;

}

3.创建Dao

package com.qf.dao;

public interface StudentDao {
    
}

---------------------------------------------

package com.qf.dao;

import com.qf.pojo.Subject;
import org.apache.ibatis.annotations.Param;

public interface SubjectDao {

    public Subject findSubjectById(@Param("id") Integer id);
}

4.创建StudentDao.xml

<?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.qf.dao.StudentDao">

</mapper>

5.创建SubjectDao.xml

<?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.qf.dao.SubjectDao">

    <resultMap id="subjectMap" type="Subject">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <result column="grade" property="grade"></result>

        <collection property="students" ofType="Student">
            <id column="stu_id" property="id"></id>
            <result column="stu_name" property="name"></result>
            <result column="sex" property="sex"></result>
        </collection>
    </resultMap>

    <select id="findSubjectById" resultMap="subjectMap">

    SELECT t_subject.id,t_subject.name,t_subject.grade,
                   t_student.id stu_id,t_student.name stu_name,t_student.sex
            FROM t_subject JOIN t_stu_sub
            ON t_subject.id = t_stu_sub.subject_id
            JOIN t_student
            ON t_stu_sub.student_id = t_student.id
            WHERE t_subject.id=#{id}
    </select>

</mapper>

• 注意：指定“多方”关系时（集合），使用< collection ofType="" >

6.测试

package com.qf.demo;

import com.qf.dao.SubjectDao;
import com.qf.pojo.Subject;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.InputStream;

public class MybatisTest {

    @Test
    public void findById()throws Exception{

        //准备环境
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession sqlSession = sqlSessionFactory.openSession();
        //-------------------------------------------------------------

        SubjectDao  subjectDao= sqlSession.getMapper(SubjectDao.class);

        Subject subject = subjectDao.findSubjectById(1001);

        System.out.println(subject);
        //-------------------------------------------------------------
        sqlSession.close();
        inputStream.close();
    }

}

2.4 关系总结

一方，添加集合；多方，添加对象。

双方均可建立关系属性，建立关系属性后，对应的Mapper文件中需使用< ResultMap >完成多表映射。

持有对象关系属性，使用< association property="dept" javaType="department" >

持有集合关系属性，使用< collection property="emps" ofType="employee" >