Mybatis-多表操作

目录

多表模型分类(回顾)：

一对一的数据准备

一对一的功能实现

一对多的数据准备

一对多的功能实现

多对多的数据准备

多对多的功能实现

多表模型分类(回顾)：

一对一的数据准备

CREATE TABLE person(
	id INT PRIMARY KEY auto_increment,
	name VARCHAr(30),
	age INT
);
INSERT INTO person VALUES (NULL,'张三',23),(NULL,'李四',24),(NULL,'王五',25);

CREATE TABLE card(
	id INT PRIMARY KEY auto_increment,
	number VARCHAr(30),
	pid INT,
	CONSTRAINT kk FOREIGN KEY (pid) REFERENCES person(id)
);
INSERT INTO card VALUES (NULL,'12345',1),(NULL,'23456',2),(NULL,'34567',3);

package demo4.bean;

public class Person {
    private Integer id;  //主键id
    private String name; //人的姓名
    private Integer age; //人的年龄

    public Person() {
    }

    public Person(Integer id, String name, Integer age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    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 getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }
@Override
    public String toString() {
        return "Person{" +
                "id=" + id +
                ", name='" + name + ''' +
                ", age=" + age +
                '}';
    }
}

package demo4.bean;

public class Card {
    private Integer id;  //主键id
    private String number;  //身份证号
    private Person p;  //所属人的对象

    public Card() {
    }

    public Card(Integer id, String number, Person p) {
        this.id = id;
        this.number = number;
        this.p = p;
    }

    public Integer getId() {
        return id;
    }

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

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }

    public Person getP() {
        return p;
    }

    public void setP(Person p) {
        this.p = p;
    }
@Override
    public String toString() {
        return "Card{" +
                "id=" + id +
                ", number='" + number + ''' +
                ", p=" + p +
                '}';
    }
}

一对一的功能实现

    
    
        
        
        
        
        
            
            
            
        
    
    
        SELECT c.id cid,number,pid,name,age FROM card c,person p WHERe c.pid=p.id
    

public interface OneToOneMapper {
    //查询全部
    public abstract List selectAll();
}

public class Test01 {
    @Test
    public void selectAll() throws Exception{
        //1.加载核心配置文件
        InputStream is = Resources.getResourceAsStream("MybatisConfig.xml");
        //2.获取SqlSession工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        //3.通过工厂对象获取SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        //4.获取StudentMapper接口的实现类对象
        OneToOneMapper mapper = sqlSession.getMapper(OneToOneMapper.class);
        //5.调用实现类的方法，接收结果
        List list = mapper.selectAll();
        //6.处理结果
        for(Card c : list){
            System.out.println(c);
        }
        //7.释放资源
        sqlSession.close();
        is.close();
    }
}

一对多的数据准备

CREATE TABLE classes(
	id INT PRIMARY KEY auto_increment,
	name VARCHAr(30)
);
INSERT INTO classes VALUES (NULL,'一班'),(NULL,'二班');

CREATE TABLE student(
	id INT PRIMARY KEY auto_increment,
	name VARCHAr(30),
	age INT,
	cid INT,
	CONSTRAINT kkk FOREIGN KEY (cid) REFERENCES classes(id)
);
INSERT INTO student VALUES (NULL,'张三',23,1),(NULL,'李四',24,1),(NULL,'王五',25,2),(NULL,'赵六',26,2);

public class Classes {
    private Integer id; //主键id
    private String name; //班级名称
    private List students;//班级中所有学生对象

    public Classes() {
    }

    public Classes(Integer id, String name, List students) {
        this.id = id;
        this.name = name;
        this.students = students;
    }

    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 List getStudents() {
        return students;
    }

    public void setStudents(List students) {
        this.students = students;
    }

    @Override
    public String toString() {
        return "Classes{" +
                "id=" + id +
                ", name='" + name + ''' +
                ", students=" + students +
                '}';
    }
}

public class Student {
    private Integer id; //主键id
    private String name;//学生姓名
    private Integer age;//学生年龄

    public Student() {
    }

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

    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 getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

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

一对多的功能实现

    
        
        
        
        
            
            
            
        
    
    
        SELECT c.id cid,c.name cname,s.id sid,s.name sname,s.age sage FROM classes c,student s WHERe c.id=s.cid
    
 
 

        
        
        
    
 
再创建一个包用于存放对应的映射接口以及测试类
 
public interface OneToManyMapper {
    //查询全部
    public abstract List selectAll();
}
 
 
@Test
    public void selectAll() throws Exception {
        //1.加载核心配置文件
        InputStream is = Resources.getResourceAsStream("MybatisConfig.xml");
        //2.获取SqlSession工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        //3.通过工厂对象获取SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        //4.获取OneToManyMapper接口的实现类对象
        OneToManyMapper mapper = sqlSession.getMapper(OneToManyMapper.class);
        //5.调用实现类的方法，接收结果
        List classes = mapper.selectAll();
        //6.处理结果
        for(Classes cls : classes){
            System.out.println(cls.getId()+","+cls.getName());
            List students = cls.getStudents();
            for(Student student : students){
                System.out.println("t"+student);
            }
        }
        //7.释放资源
        sqlSession.close();
        is.close();
    }
 
 
多对多的数据准备 
多对多模型：学生和课程，一个学生可以选择多门课程，一个课程也可以被多个学生所选择
环境准备(沿用上次的student表略微改动)
 
CREATE TABLE course(
	id INT PRIMARY KEY auto_increment,
	name VARCHAr(30)
);
INSERT INTO course VALUES (NULL,'语文'),(NULL,'数学');

CREATE TABLE stu_cr(
 id INT PRIMARY KEY auto_increment,
 sid INT,
 cid INT,
 CONSTRAINT kk_1 FOREIGN KEY (sid) REFERENCES student(id),
 CONSTRAINT kk_2 FOREIGN KEY (cid) REFERENCES course(id)
);
INSERT INTO stu_cr VALUES (NULL,1,1),(NULL,1,2),(NULL,2,1),(NULL,2,2);
 
 
 
 
public class Course {
    private Integer id;  //主键id
    private String name; //课程名称

    public Course() {
    }

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

    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;
    }

    @Override
    public String toString() {
        return "Course{" +
                "id=" + id +
                ", name='" + name + ''' +
                '}';
    }
}
 
 
public class Student {
    private Integer id; //主键id
    private String name;//学生姓名
    private Integer age;//学生年龄
    private List courses;//学生所选择的课程集合

    public Student() {
    }

    public Student(Integer id, String name, Integer age, List courses) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.courses = courses;
    }

    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 getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public List getCourses() {
        return courses;
    }

    public void setCourses(List courses) {
        this.courses = courses;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + ''' +
                ", age=" + age +
                ", courses=" + courses +
                '}';
    }
}
 
 
多对多的功能实现 
：
配置字段和对象属性的映射关系标签
id属性：唯一标识
type属性：实体对象类型
：
配置主键映射关系标签
property属性：实体对象变量名称
：
配置非主键映射关系标签
column属性：表中字段名称
property属性：实体对象变量名称
：
配置被包含集合对象的映射关系标签
property属性：被包含集合对象的变量名
ofType属性：集合中保存的对象数据类型
创建一个包，用于存放对应映射配置文件
 




    
        
        
        
        
            
            
        
    
    
        SELECT sc.sid,s.name sname,s.age sage,sc.cid,c.name cname FROM student s,course c,stu_cr sc WHERe sc.sid=s.id AND sc.cid=c.id
    

public interface ManyToManyMapper {
    //查询全部
    public abstract List selectAll();
}

@Test
    public void selectAll() throws Exception {
        //1.加载核心配置文件
        InputStream is = Resources.getResourceAsStream("MybatisConfig.xml");
        //2.获取SqlSession工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        //3.通过工厂对象获取SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        //4.获取ManyToManyMapper接口的实现类对象
        ManyToManyMapper mapper = sqlSession.getMapper(ManyToManyMapper.class);
        //5.调用实现类的方法，接收结果
        List students = mapper.selectAll();
        //6.处理结果
        for (Student student : students) {
            System.out.println(student.getId()+","+student.getName()+","+student.getAge());
            List courses = student.getCourses();
            for(Course course : courses){
                System.out.println("t"+course);
            }
        }
        //7.释放资源
        sqlSession.close();
        is.close();
    }