2021年10月8日

 

001

Author：Exchanges

Version：9.0.2

一、ORM映射【重点】1.1 MyBatis自动ORM失效1.2 方案一：列的别名1.3 方案二：结果映射（ResultMap - 查询结果的封装规则）二、MyBatis处理关联关系-多表连接【重点】2.1 OneToOne2.2 OneToMany2.3 ManyToMany2.4 关系总结

一、ORM映射【重点】

1.1 MyBatis自动ORM失效

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

自动ORM失效
007

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：一对多关系（Department --- Employee）：一个部门有多个员工，一个员工只属于一个部门

  • ManyToMany：多对多关系（Student --- Subject）：一个学生学习多门课程，一个课程被多个学生学习

Table建立外键关系
008
Entity添加关系属性
009_2
Mapper中将属性与列名对应
010

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(1,'zhansan','f','2020-11-11');
INSERT INTO t_passenger VALUES(2,'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(10001,'China','2030-11-11',1);
INSERT INTO t_passport VALUES(10002,'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>
​
    <!-- 查询旅客及其护照，由于两个表都有名为id的列，需要在查询时区分，否则相同列名的值会被覆盖 -->
    <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>

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>

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

建立第三张关系表
011_2

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>

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" >



posted @ 2021-10-08 19:25  张三疯321  阅读(50)  评论(0)  编辑  收藏  举报