JavaEE之Mybatis关系映射

JavaEE之Mybatis关系映射

• 创建封装工具类
• 一对一（旅客与护照）

• 创建数据表
• 创建相应实体类
• 创建DAO接口
• 创建Mapper文件
• 注册Mapper文件
• 测试

• 一对多（部门与职员）

• 创建数据表
• 创建相应实体类
• 创建DAO接口
• 创建Mapper文件
• 注册Mapper文件
• 测试

• 多对多（学生与科目）

• 创建数据表
• 创建相应实体类
• 创建DAO接口
• 创建Mapper文件
• 注册Mapper文件
• 测试

创建封装工具类

MybatisUtil.java

package com.edu.util;

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 java.io.IOException;
import java.io.InputStream;

/**
 * 1.加载配置
 * 2.创建sqlSessionFactory
 * 3.创建Session
 * 4.事务的管理
 * 5.mapper获取
 */
public class MybatisUtil {
    private static SqlSessionFactory sqlSessionFactory;
    //创建ThreadLocal绑定当前线程中的sqlSession对象
    private static final ThreadLocal<SqlSession> tl = new ThreadLocal<SqlSession>();
    static {//加载配置信息,并构建session工厂
        //1. 加载配置文件
        try {
            InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
            sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
    public static SqlSession openSession(){

        SqlSession sqlSession = tl.get();
        if(sqlSession == null){
            sqlSession = sqlSessionFactory.openSession();
            tl.set(sqlSession);
        }
        return sqlSession;
    }
    //释放连接（释放当前线程中的SqlSession）
    private static void closeSession(){
        SqlSession sqlSession = tl.get();
        sqlSession.close();
        //tl.remove();
    }

    //提交事务（提交当前线程中的SqlSession所管理的事务）
    public static void commit(){
        SqlSession sqlSession = openSession();
        sqlSession.commit();
        closeSession();
    }

    //回滚事务（回滚当前线程中的SqlSession所管理的事务）
    public static void rollback(){
        SqlSession sqlSession = openSession();
        sqlSession.rollback();
        closeSession();
    }
    //获得接口实现类对象
    public static <T extends Object> T getMapper(Class<T> clazz){
        SqlSession sqlSession = openSession();
        return sqlSession.getMapper(clazz);
    }
}

一对一（旅客与护照）

创建数据表

create table t_passengers(
                             id  int primary key auto_increment,
                             name  varchar(50),
                             sex varchar(1),
                             birthday date
)default charset =utf8;
create table t_passports(
                            id   int primary key auto_increment,
                            nationality  varchar(50),
                            expire       date,
                            passenger_id int unique,
                            foreign key (passenger_id) references t_passengers (id)
)default charset =utf8;

insert into t_passengers values(null, 'Lily',0,'2018-11-11');
insert into t_passengers values(null, 'Tom',1,'2019-12-12');

insert into t_passports values(null, 'china','2030-12-12',1);
insert into t_passports values(null, 'America','2035-12-12',2);

创建相应实体类

Passenger.java

package com.edu.entity;

import java.util.Date;

public class Passenger {
    private Integer id;
    private String name;
    private Boolean sex;
    private Date birthday;

    //存储旅客的护照信息； 关系属性
    private Passport passport;

    public Passenger(){}
    public Passenger(Integer id, String name, Boolean sex, Date birthday) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.birthday = birthday;
    }

    public Passport getPassport() {
        return passport;
    }

    @Override
    public String toString() {
        return "Passenger{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex=" + sex +
                ", birthday=" + birthday +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Boolean getSex() {
        return sex;
    }

    public void setSex(Boolean sex) {
        this.sex = sex;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
}

Passport.java

package com.edu.entity;

import java.util.Date;

public class Passport {
    private Integer id;
    private String nationality;
    private Date expire;

    //存储旅客信息； 关系属性
    private Passenger passenger;

    public Passport(){}
    public Passport(Integer id, String nationality, Date expire) {
        this.id = id;
        this.nationality = nationality;
        this.expire = expire;
    }

    @Override
    public String toString() {
        return "Passport{" +
                "id=" + id +
                ", nationality='" + nationality + '\'' +
                ", expire=" + expire +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getNationality() {
        return nationality;
    }

    public void setNationality(String nationality) {
        this.nationality = nationality;
    }

    public Date getExpire() {
        return expire;
    }

    public void setExpire(Date expire) {
        this.expire = expire;
    }
}

创建DAO接口

PassengerDAO.java

package com.edu.dao;

import com.edu.entity.Passenger;
import org.apache.ibatis.annotations.Param;

public interface PassengerDAO {
    //通过旅客的id，查询旅客信息及其护照信息；关联查询；级联查询
    Passenger queryPassengerById(@Param("id") Integer id);
}

创建Mapper文件

PassengerDAOMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapperg 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.edu.dao.PassengerDAO">
    <resultMap  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>

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

    </resultMap>

    <!--查询旅客及其护照信息-->
    <select  resultMap="passenger_passport">
         select t_passengers.id, t_passengers.name, t_passengers.sex, t_passengers.birthday,
                t_passports.id passid, t_passports.nationality, t_passports.expire
         from t_passengers join t_passports
         on t_passengers.id= t_passports.passenger_id
         where t_passengers.id=#{id}
    </select>
</mapper>

注册Mapper文件

<mapper resource="PassengerDAOMapper.xml"/>

测试

MybatisUtilTest.java

在import com.edu.dao.PassengerDAO;
import com.edu.dao.UserDAO;
import com.edu.entity.Passenger;
import com.edu.entity.User;
import com.edu.util.MybatisUtil;
import org.apache.ibatis.annotations.Mapper;
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 javax.jws.soap.SOAPBinding;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MybatisUtilTest {
    public static void main(String[] args) throws IOException {
        PassengerDAO mapper =MybatisUtil.getMapper(PassengerDAO.class);
        Passenger passenger= mapper.queryPassengerById(1);
        System.out.println("=====================");
        System.out.println(passenger);
        System.out.println(passenger.getPassport());
    }
}

一对多（部门与职员）

创建数据表

create table t_departments(
                              id int primary key auto_increment,
                              name varchar(50),
                              location varchar(100)
)default charset =utf8;

create table t_employees(
                            id int primary key auto_increment,
                            name varchar(50),
                            salary double,
                            dept_id int,
                            foreign key (dept_id) references t_departments(id)
)default charset =utf8;

insert into t_departments values(1,"教学部",'深圳'),(2,"研发部",'广州');
insert into t_employees values(1,"Tom",10000.5,1),(2,"Lily",20000.5,1),
                              (3,"Freeman",90000.5,2),(4,"Lihua",8000.5,2);

创建相应实体类

Department.java

package com.edu.entity;

import java.util.List;

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

    private List<Employee> employees;

    public Department(){}
    public Department(Integer id, String name, String location) {
        this.id = id;
        this.name = name;
        this.location = location;
    }

    @Override
    public String toString() {
        return "Department{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", location='" + location + '\'' +
                '}';
    }

    public List<Employee> getEmployees() {
        return employees;
    }

    public void setEmployees(List<Employee> employees) {
        this.employees = employees;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getLocation() {
        return location;
    }

    public void setLocation(String location) {
        this.location = location;
    }
}

Employee.java

package com.edu.entity;

import java.util.List;

public class Employee {
    private Integer id;
    private  String name;
    private  double salary;

    private Department department;

    public Employee(){}
    public Employee(Integer id, String name, double salary) {
        this.id = id;
        this.name = name;
        this.salary = salary;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", salary='" + salary + '\'' +
                '}';
    }

    public Department getDepartment() {
        return department;
    }

    public void setDepartment(Department department) {
        this.department = department;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public double getSalary() {
        return salary;
    }

    public void setSalary(double salary) {
        this.salary = salary;
    }
}

创建DAO接口

DepartmentDAO.java

package com.edu.dao;

import com.edu.entity.Department;
import org.apache.ibatis.annotations.Param;

public interface DepartmentDAO {
    Department queryDepartmentById(@Param("id")Integer id);
}

创建Mapper文件

DepartmentDAOMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapperg 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.edu.dao.DepartmentDAO">
    <resultMap  type="Department">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <result column="location" property="location"></result>

        <!--emp_id  emp_name salary emplyees-->
        <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  resultMap="dept_emp">
select t_departments.id, t_departments.name,t_departments.location,
      t_employees.id emp_id,t_employees.name emp_name,t_employees.salary
from t_departments join t_employees
on t_departments.id=t_employees.dept_id
where t_departments.id=#{id}
	</select>
</mapper>

注册Mapper文件

<mapper resource="DepartmentDAOMapper.xml"/>

测试

在MybatisUtilTest.java中，添加一对多测试代码

//一对多
DepartmentDAO mapper =MybatisUtil.getMapper(DepartmentDAO.class);
Department department= mapper.queryDepartmentById(1);
System.out.println(department);
List<Employee> employees= department.getEmployees();
for(Employee employee:employees){
    System.out.println(employee);
}

多对多（学生与科目）

创建数据表

create table t_students(
                           id int primary key auto_increment,
                           name varchar(50),
                           sex varchar(1)
)default charset=utf8;

create table t_subjects(
                           id int primary key auto_increment,
                           name varchar(50),
                           grade int
)default charset =utf8;

create table t_stu_sub(
                          student_id int,
                          subject_id int,
                          foreign key (student_id) references t_students(id),
                          foreign key (subject_id) references t_subjects(id),
                          primary key(student_id,subject_id)
)default charset =utf8;
insert into t_students values(1,"Tom",1),(2,"Lily",0);
insert into t_subjects values(1001,"JAVAEE",1),(1002,"Python",2);
insert into t_stu_sub value(1,1001),(1,1002),(2,1001),(2,1002);

创建相应实体类

Student.java

package com.edu.entity;

import java.util.Date;
import java.util.List;

public class Student {
    private Integer id;
    private String name;
    private Boolean sex;

    private List<Subject> subjects;

    public Student(){}
    public Student(Integer id, String name, Boolean sex) {
        this.id = id;
        this.name = name;
        this.sex = sex;
    }

    public List<Subject> getSubject() {
        return subjects;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex=" + sex +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Boolean getSex() {
        return sex;
    }

    public void setSex(Boolean sex) {
        this.sex = sex;
    }
}

Subject.java

package com.edu.entity;

import java.util.List;

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


    private List<Student> students;

    public Subject(){}
    public Subject(Integer id, String name,Integer grade) {
        this.id = id;
        this.name = name;
        this.grade = grade;
    }

    public List<Student>  getStudent() {
        return students;
    }

    @Override
    public String toString() {
        return "Subject{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", grade=" + grade +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getGrade() {
        return grade;
    }
    public void setSex(Integer grade) {
        this.grade = grade;
    }
}

创建DAO接口

StudentDAO.java

package com.edu.dao;

import com.edu.entity.Student;
import org.apache.ibatis.annotations.Param;

public interface StudentDAO {
    Student queryStudentById(@Param("id")Integer id);
}

SubjectDAO.java

package com.edu.dao;

import com.edu.entity.Subject;
import org.apache.ibatis.annotations.Param;

public interface SubjectDAO {
    Subject querySubjectById(@Param("id")Integer id);
}

创建Mapper文件

StudentDAOMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapperg 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.edu.dao.StudentDAO">
    <resultMap  type="Student">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <result column="sex" property="sex"></result>

        <!--emp_id  emp_name salary emplyees-->
        <collection property="subjects" ofType="Subject">
            <id column="sub_id" property="id"></id>
            <result column="sub_name" property="name"></result>
            <result column="grade" property="grade"></result>
        </collection>
    </resultMap>
    <select  resultMap="stu_sub">
select t_students.id, t_students.name,t_students.sex,
       t_subjects.id sub_id,t_subjects.name sub_name,t_subjects.grade
from t_students,t_stu_sub,t_subjects
where t_students.id=t_stu_sub.student_id
 and t_stu_sub.subject_id=t_subjects.id and t_students.id=#{id}
	</select>
</mapper>

SubjectDAOMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapperg 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.edu.dao.SubjectDAO">
    <resultMap  type="Subject">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <result column="grade" property="grade"></result>

        <!--emp_id  emp_name salary emplyees-->
        <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  resultMap="sub_stu">
select t_subjects.id, t_subjects.name,t_subjects.grade,
       t_students.id stu_id,t_students.name stu_name,t_students.sex
from t_students,t_stu_sub,t_subjects
where t_subjects.id=t_stu_sub.subject_id
  and t_stu_sub.student_id=t_students.id and t_subjects.id=#{id}
	</select>
</mapper>

注册Mapper文件

<mapper resource="StudentDAOMapper.xml"/>
<mapper resource="SubjectDAOMapper.xml"/>

测试

在MybatisUtilTest.java中，添加多对多测试代码

//多对多
StudentDAO Studentmapper =MybatisUtil.getMapper(StudentDAO.class);
Student student= Studentmapper.queryStudentById(1);
System.out.println("=====================");
System.out.println(student);
List<Subject>  subjects= student.getSubject();
for(Subject sub:subjects){
    System.out.println(sub);
}
SubjectDAO Subjectmapper =MybatisUtil.getMapper(SubjectDAO.class);
Subject subject= Subjectmapper.querySubjectById(1001);
 System.out.println("=====================");
 System.out.println(subject);
 List<Student>  students= subject.getStudent();
 for(Student stu:students){
     System.out.println(stu);
 }