MyBatis快速入门（二）（隔壁王大爷都学会了！）

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

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

    
        SELECT mgr_id AS id , mgr_name AS username , mgr_pwd AS password
        FROM t_managers
        WHERe mgr_id = #{id} AND mgr_pwd = #{pwd}
    

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

​
    
    
        
        
      
        
        
        
    
  
     
    
        SELECT mgr_id , mgr_name , mgr_pwd
        FROM t_managers
    
 
 
二、MyBatis处理关联关系-多表连接【重点】 

 
 
实体间的关系：关联关系（拥有 has、属于 belong）
 
 
 
OneToOne：一对一关系（Passenger --- Passport）：一个旅客只有一个护照，一个护照只对应一个旅客
 
 
OneToMany：一对多关系（Department --- Employee）：一个部门有多个员工，一个员工只属于一个部门
 
 
ManyToMany：多对多关系（Student --- Subject）：一个学生学习多门课程，一个课程被多个学生学习
 
 
Table建立外键关系

 
Entity添加关系属性

Mapper中将属性与列名对应

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

    
        org.mybatis
        mybatis
        3.5.4
    
​
    
        mysql
        mysql-connector-java
        8.0.21
    
​
    
        org.projectlombok
        lombok
        1.18.12
    
​
    
        junit
        junit
        4.13
    
​
    
        log4j
        log4j
        1.2.17
    
 
 
 
 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.coffee.entity;

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.coffee.entity;

import lombok.Data;

import java.util.Date;

@Data
public class Passport {
    private Integer id;
    private String nationality;
    private Date expire;
    private Integer passengerId;
}
 
 
 4.创建Dao 
 
package com.coffee.dao;

import com.coffee.entity.Passenger;

public interface PassengerDao {
    Passenger findById(Integer id);
}
 
 
 
 5.创建PassengerDao.xml 和 mybatis-config.xml配置文件 
 





    
    
        
        
        
        
        
        
            
            
            
        
    
    
    
          SELECT p1.id,p1.name,p1.sex,p1.birthday,
          p2.id passport_id,p2.nationality,p2.expire
          FROM t_passenger p1 INNER JOIN t_passport p2
          ON p1.id = p2.passenger_id
          WHERe p1.id=#{id};
    

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

6.测试

 @org.junit.Test
    public void find() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream resourceAsStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        PassengerDao mapper = sqlSession.getMapper(PassengerDao.class);
        Passenger byId = mapper.findById(2);
        System.out.println(byId);
        sqlSession.close();
        resourceAsStream.close();
    }

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.qianwz.entity;

import lombok.Data;

import java.util.List;

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

    List employees;
}

package com.qianwz.entity;

import lombok.Data;
@Data
public class Employee {
    private Integer id;
    private String name;
    private String salary;

    private List departments;
}

3.创建Dao

package com.qianwz.dao;

import com.qianwz.entity.Department;


public interface DepartmentDao {
    Department findById(Integer id);
}

package com.qianwz.dao;

import com.qianwz.entity.Employee;

public interface EmployeeDao {
    Employee findById(Integer id);
}

4.创建EmployeeDao.xml配置文件

    
    
        
        
        
        
            
            
            
        
    
    
          select e.id,e.name,salary,d.id deptId,d.name deptName,location
          from t_employee e
          inner join t_department d
          on e.dept_id=d.id
          where e.id = #{id}
    

 
 
 5.创建DepartmentDao.xml配置文件 
 





    
    
        
        
        
      
        
            
            
            
        
    
   
    
          select d.id,d.name,location,e.id employeeId,e.name employeeName,salary
          from t_department d
          inner join t_employee e
          on d.id = e.dept_id
          where d.id = #{id}
    

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

6.测试

import com.qianwz.dao.DepartmentDao;
import com.qianwz.dao.EmployeeDao;
import com.qianwz.entity.Department;
import com.qianwz.entity.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 java.io.IOException;
import java.io.InputStream;

public class Test {
    @org.junit.Test
    public void test() throws IOException {
        String resourrce = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resourrce);
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = build.openSession();
        DepartmentDao mapper = sqlSession.getMapper(DepartmentDao.class);
        Department byId = mapper.findById(1);
        System.out.println(byId);
        sqlSession.close();
        inputStream.close();
    }
    @org.junit.Test
    public void test2() throws IOException {
        String resourrce = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resourrce);
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = build.openSession();
        EmployeeDao mapper = sqlSession.getMapper(EmployeeDao.class);
        Employee dById = mapper.findById(1);
        System.out.println(dById);
        sqlSession.close();
        inputStream.close();
    }
}

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.qwz.entity;

import lombok.Data;

import java.util.List;
@Data
public class Student {
    private Integer id;
    private String name;
    private String sex;

    private List subjects;
}

package com.qwz.entity;

import lombok.Data;

import java.util.List;

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

    private List students;
}

3.创建Dao

package com.qwz.dao;

import com.qwz.entity.Student;

import java.util.List;

public interface StudentDao {
    List findStudentSubject();
}

package com.qwz.dao;

import com.qwz.entity.Subject;

import java.util.List;

public interface SubjectDao {
    List findSubjectStudent();
}

4.创建StudentDao.xml

    
    
        
        
        
        
        
            
            
            
        
    
    
    
         select s.id,s.name,s.sex,j.id jId,j.name jName,grade
         from t_student s
         inner join t_stu_sub t
         on t.student_id = s.id
         inner join t_subject j
         on j.id = t.subject_id
    

5.创建mybatis-config.xml

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

6.测试

import com.qwz.dao.StudentDao;
import com.qwz.entity.Student;
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;
import java.util.List;

public class Test {
    @org.junit.Test
    public void test() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream  inputStream= Resources.getResourceAsStream(resource);
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory build = sqlSessionFactoryBuilder.build(inputStream);
        SqlSession sqlSession = build.openSession();
        StudentDao mapper = sqlSession.getMapper(StudentDao.class);
        List studentSubject = mapper.findStudentSubject();
        for (Student student : studentSubject) {
            System.out.println(student);
        }
        sqlSession.close();
        inputStream.close();
    }
}

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

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

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

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