Mybatis-入门（九）

前言

我们在实际开发中是不仅仅针对一张表，而是两张或者多张表联动的操作，这里我们经常面对的关系就是一对一，一对多，多对一，多对多等等。

这里我们搭建环境，我们看看最常见的数据关系，学生和老师的关系

对于老师来说，一个老师拥有多个学生
对于学生而言，多个学生都有一个老师

create table teachers (
    `id` int(20) not null auto_increment,
    `name` varchar(64) not null,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into teachers (`name`) values ('燕子李三');

create table students (
    `id` int(20) not null auto_increment,
    `name` varchar(64) not null,
    `tid` int(20) not null,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into students (`name`, `tid`) values ('张三', 1), ('李四', 1), ('王五', 1), ('赵六', 1);

我们导入数据库，然后开始创建项目。

搭建新项目

到目前为之，我觉得学习就是一个重复的过程，不停的创建新的实验性项目，当然我心里也是很烦的。但是没有办法，发现只有动手之后我才能知道到底哪里有问题，我才能有针对性的记忆。这里我们建立一个 mybatis-06 创建好之后，我们在需要在 pom.xml 的 project 标签中加入以下代码：

第一步，`pom.xml` 加入相应的配置

<!-- 在build中配置resource，来防止我们资源导出失败的问题 -->
<build>
    <resources>
        <resource>
            <directory>src/main/resources</directory>
            <includes>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>true</filtering>
        </resource>

        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>true</filtering>
        </resource>
    </resources>
</build>

这个配置主要是为了相关的 XML 文件在编译之后，能被打包到对应的位置。

第二步：创建 `db.properties` `mybatis-config.xml` 配置类

db.properties 配置如下：

1	driver=com.mysql.jdbc.Driver
2	url=jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=utf-8&serverTimezone=Hongkong
3	username=root
4	password=

mybatis-config.xml 配置如下：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <properties resource="db.properties"/>

    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
</configuration>

第三步：创建 MybatisUtils 工具类

package com.yubulang.utils;

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 MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            String resource = "mybatis-config.xml";
            InputStream in = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession();
    }
}

到这里我们已经有了一个基础的架子。

一对多

对于老师而言，一个老师负责多个学生，我们来创建两个实体类：

创建实体类

com.yubulang.pojo.Teacher.java：

package com.yubulang.pojo;

public class Teacher {
    private int id;
    private String name;

    public Teacher() {
    }

    public Teacher(int id, String name) {
        this.id = id;
        this.name = name;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

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

com.yubulang.pojo.Student，注意这里我们要关联 Teacher，所以在 Student 的属性中 teacher 应该对应 Teacher 类：

package com.yubulang.pojo;

public class Student {
    private int id;
    private String name;
    private Teacher teacher;

    public Student() {
    }

    public Student(int id, String name, Teacher teacher) {
        this.id = id;
        this.name = name;
        this.teacher = teacher;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public Teacher getTeacher() {
        return teacher;
    }

    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }

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

创建实现接口

com.yubulang.dao.StudentMapper.java, 因为关联方式有两种:

package com.yubulang.dao;

import com.yubulang.pojo.Student;
import com.yubulang.pojo.Teacher;

import java.util.List;

public interface StudentMapper {

    // 第一种方式 根据结果描述隐射关系
    List<Student> getStudent();

    // 第二种方式
    List<Student> getStudentList();
}

有了实现接口，我们需要对应的 xml 与之匹配它的实现。我们把 xml 放到 resources/com/yubulang/dao 中，命名为 StudentMapper.xml：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.yubulang.dao.StudentMapper">
    <!-- 第一种方法，编写关联关系去查询 -->
    <select id="getStudent" resultMap="StudentTeacher">
        select * from students;
    </select>

    <resultMap id="StudentTeacher" type="Student">
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacherByTid"/>
    </resultMap>

    <select id="getTeacherByTid" resultType="Teacher">
        select * from teachers where id=#{id}
    </select>

    <!-- 第二种方法（相对简单）：根据查询结果来关联 -->
    <select id="getStudentList" resultMap="StudentTeacher2">
        select s.id sid, s.name sname, t.id tid, t.name tname from students s, teachers t where s.tid = t.id;
    </select>

    <resultMap id="StudentTeacher2" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>
</mapper>

测试类的干活

package com.yubulang.dao;

import com.yubulang.pojo.Student;
import com.yubulang.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class StudentMapperTest {
    @Test
    public void testGetStudent() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudent();
        for (Student student : studentList) {
            System.out.println(student);
        }

        sqlSession.close();
    }

    @Test
    public void testGetStudentList() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudentList();
        for (Student student : studentList) {
            System.out.println(student);
        }

        sqlSession.close();
    }
}

多对一

对于学生而言，多个学生跟着一个老师，我们来创建两个实体类：

创建实体类

com.yubulang.pojo.Teacher:

package com.yubulang.pojo;

import java.util.List;

public class Teacher {
    private int id;
    private String name;

    // 一个老师有多个学生
    private List<Student> students;

    public Teacher() {
    }

    public Teacher(int id, String name, List<Student> students) {
        this.id = id;
        this.name = name;
        this.students = students;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

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

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

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

com.yubulang.pojo.Student:

package com.yubulang.pojo;

public class Student {
    private int id;
    private String name;
    private int tid;

    public Student() {
    }

    public Student(int id, String name, int tid) {
        this.id = id;
        this.name = name;
        this.tid = tid;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public int getTid() {
        return tid;
    }

    public void setTid(int tid) {
        this.tid = tid;
    }

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

创建实现接口

com.yubulang.dao.TeacherMapper:

package com.yubulang.dao;

import com.yubulang.pojo.Teacher;
import org.apache.ibatis.annotations.Param;

public interface TeacherMapper {
    Teacher getTeacherById(@Param("tid") int id);

    Teacher getTeacherByIdAnother(@Param("tid") int id);
}

有了实现接口，我们需要对应的 xml 与之匹配它的实现。我们把 xml 放到 resources/com/yubulang/dao 中，命名为 TeacherMapper.xml：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.yubulang.dao.TeacherMapper">
    <!--第一种方式（复杂度在sql）-->
    <select id="getTeacherById" resultMap="TeacherStudent">
        select t.id tid, t.name tname, s.id sid, s.name sname
        from teachers t,
             students s
        where t.id = s.tid
          and t.id = #{tid};
    </select>

    <resultMap id="TeacherStudent" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="students" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>

    <!--第二种方式复杂度在凭借-->
    <select id="getTeacherByIdAnother" resultMap="TeacherStudentAnother">
        select id, name
        from teachers
        where id = #{tid}
    </select>

    <resultMap id="TeacherStudentAnother" type="Teacher">
        <result property="id" column="id"/>
        <collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTid" column="id">
            <result property="id" column="id"/>
            <result property="name" column="name"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>

    <select id="getStudentByTid" resultType="Student">
        select *
        from students
        where tid = #{tid}
    </select>
</mapper>

测试代码的干货

package com.yubulang.dao;

import com.yubulang.pojo.Teacher;
import com.yubulang.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class TeacherMapperTest {
    @Test
    public void testGetTeacherById() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacherById = mapper.getTeacherById(1);

        System.out.println(teacherById);

        sqlSession.close();
    }

    @Test
    public void testGetTeacherByIdAnother() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacherById = mapper.getTeacherByIdAnother(1);

        System.out.println(teacherById);

        sqlSession.close();
    }
}

总结

比之前更发杂了点，但是这些关系是我们做项目时常用的一些数组组成方式。有个基础，后面复杂的都不在话下。

1	package com.yubulang.pojo;
2
3	public class Teacher {
4	private int id;
5	private String name;
6
7	public Teacher() {
8	}
9
10	public Teacher(int id, String name) {
11	this.id = id;
12	this.name = name;
13	}
14
15	public int getId() {
16	return id;
17	}
18
19	public void setId(int id) {
20	this.id = id;
21	}
22
23	public String getName() {
24	return name;
25	}
26
27	public void setName(String name) {
28	this.name = name;
29	}
30
31	@Override
32	public String toString() {
33	return "Teacher{" +
34	"id=" + id +
35	", name='" + name + '\'' +
36	'}';
37	}
38	}

1	package com.yubulang.pojo;
2
3	public class Student {
4	private int id;
5	private String name;
6	private Teacher teacher;
7
8	public Student() {
9	}
10
11	public Student(int id, String name, Teacher teacher) {
12	this.id = id;
13	this.name = name;
14	this.teacher = teacher;
15	}
16
17	public int getId() {
18	return id;
19	}
20
21	public void setId(int id) {
22	this.id = id;
23	}
24
25	public String getName() {
26	return name;
27	}
28
29	public void setName(String name) {
30	this.name = name;
31	}
32
33	public Teacher getTeacher() {
34	return teacher;
35	}
36
37	public void setTeacher(Teacher teacher) {
38	this.teacher = teacher;
39	}
40
41	@Override
42	public String toString() {
43	return "Student{" +
44	"id=" + id +
45	", name='" + name + '\'' +
46	", teacher=" + teacher +
47	'}';
48	}
49	}

1	package com.yubulang.pojo;
2
3	import java.util.List;
4
5	public class Teacher {
6	private int id;
7	private String name;
8
9	// 一个老师有多个学生
10	private List<Student> students;
11
12	public Teacher() {
13	}
14
15	public Teacher(int id, String name, List<Student> students) {
16	this.id = id;
17	this.name = name;
18	this.students = students;
19	}
20
21	public int getId() {
22	return id;
23	}
24
25	public void setId(int id) {
26	this.id = id;
27	}
28
29	public String getName() {
30	return name;
31	}
32
33	public void setName(String name) {
34	this.name = name;
35	}
36
37	public List<Student> getStudents() {
38	return students;
39	}
40
41	public void setStudents(List<Student> students) {
42	this.students = students;
43	}
44
45	@Override
46	public String toString() {
47	return "Teacher{" +
48	"id=" + id +
49	", name='" + name + '\'' +
50	", students=" + students +
51	'}';
52	}
53	}

1	package com.yubulang.pojo;
2
3	public class Student {
4	private int id;
5	private String name;
6	private int tid;
7
8	public Student() {
9	}
10
11	public Student(int id, String name, int tid) {
12	this.id = id;
13	this.name = name;
14	this.tid = tid;
15	}
16
17	public int getId() {
18	return id;
19	}
20
21	public void setId(int id) {
22	this.id = id;
23	}
24
25	public String getName() {
26	return name;
27	}
28
29	public void setName(String name) {
30	this.name = name;
31	}
32
33	public int getTid() {
34	return tid;
35	}
36
37	public void setTid(int tid) {
38	this.tid = tid;
39	}
40
41	@Override
42	public String toString() {
43	return "Student{" +
44	"id=" + id +
45	", name='" + name + '\'' +
46	", tid=" + tid +
47	'}';
48	}
49	}

1	create table teachers (
2	`id` int(20) not null auto_increment,
3	`name` varchar(64) not null,
4	PRIMARY KEY (`id`)
5	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
6
7	insert into teachers (`name`) values ('燕子李三');
8
9	create table students (
10	`id` int(20) not null auto_increment,
11	`name` varchar(64) not null,
12	`tid` int(20) not null,
13	PRIMARY KEY (`id`)
14	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
15
16	insert into students (`name`, `tid`) values ('张三', 1), ('李四', 1), ('王五', 1), ('赵六', 1);

1	<!-- 在build中配置resource，来防止我们资源导出失败的问题 -->
2	<build>
3	<resources>
4	<resource>
5	<directory>src/main/resources</directory>
6	<includes>
7	<include>*/.properties</include>
8	<include>*/.xml</include>
9	</includes>
10	<filtering>true</filtering>
11	</resource>
12
13	<resource>
14	<directory>src/main/java</directory>
15	<includes>
16	<include>*/.properties</include>
17	<include>*/.xml</include>
18	</includes>
19	<filtering>true</filtering>
20	</resource>
21	</resources>
22	</build>

1	<?xml version="1.0" encoding="UTF-8"?>
2	<!DOCTYPE configuration
3	PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
4	"http://mybatis.org/dtd/mybatis-3-config.dtd">
5
6	<configuration>
7	<properties resource="db.properties"/>
8
9	<settings>
10	<setting name="logImpl" value="STDOUT_LOGGING"/>
11	</settings>
12
13	<environments default="development">
14	<environment id="development">
15	<transactionManager type="JDBC"/>
16	<dataSource type="POOLED">
17	<property name="driver" value="${driver}"/>
18	<property name="url" value="${url}"/>
19	<property name="username" value="${username}"/>
20	<property name="password" value="${password}"/>
21	</dataSource>
22	</environment>
23	</environments>
24	</configuration>

1	package com.yubulang.utils;
2
3	import org.apache.ibatis.io.Resources;
4	import org.apache.ibatis.session.SqlSession;
5	import org.apache.ibatis.session.SqlSessionFactory;
6	import org.apache.ibatis.session.SqlSessionFactoryBuilder;
7
8	import java.io.IOException;
9	import java.io.InputStream;
10
11	public class MybatisUtils {
12	private static SqlSessionFactory sqlSessionFactory;
13
14	static {
15	try {
16	String resource = "mybatis-config.xml";
17	InputStream in = Resources.getResourceAsStream(resource);
18	sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
19	} catch (IOException e) {
20	e.printStackTrace();
21	}
22	}
23
24	public static SqlSession getSqlSession() {
25	return sqlSessionFactory.openSession();
26	}
27	}

1	package com.yubulang.dao;
2
3	import com.yubulang.pojo.Student;
4	import com.yubulang.pojo.Teacher;
5
6	import java.util.List;
7
8	public interface StudentMapper {
9
10	// 第一种方式根据结果描述隐射关系
11	List<Student> getStudent();
12
13	// 第二种方式
14	List<Student> getStudentList();
15	}

1	<?xml version="1.0" encoding="UTF-8"?>
2	<!DOCTYPE mapper
3	PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
4	"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
5
6	<mapper namespace="com.yubulang.dao.StudentMapper">
7	<!-- 第一种方法，编写关联关系去查询 -->
8	<select id="getStudent" resultMap="StudentTeacher">
9	select * from students;
10	</select>
11
12	<resultMap id="StudentTeacher" type="Student">
13	<association property="teacher" column="tid" javaType="Teacher" select="getTeacherByTid"/>
14	</resultMap>
15
16	<select id="getTeacherByTid" resultType="Teacher">
17	select * from teachers where id=#{id}
18	</select>
19
20	<!-- 第二种方法（相对简单）：根据查询结果来关联 -->
21	<select id="getStudentList" resultMap="StudentTeacher2">
22	select s.id sid, s.name sname, t.id tid, t.name tname from students s, teachers t where s.tid = t.id;
23	</select>
24
25	<resultMap id="StudentTeacher2" type="Student">
26	<result property="id" column="sid"/>
27	<result property="name" column="sname"/>
28	<association property="teacher" javaType="Teacher">
29	<result property="id" column="tid"/>
30	<result property="name" column="tname"/>
31	</association>
32	</resultMap>
33	</mapper>

1	package com.yubulang.dao;
2
3	import com.yubulang.pojo.Student;
4	import com.yubulang.utils.MybatisUtils;
5	import org.apache.ibatis.session.SqlSession;
6	import org.junit.Test;
7
8	import java.util.List;
9
10	public class StudentMapperTest {
11	@Test
12	public void testGetStudent() {
13	SqlSession sqlSession = MybatisUtils.getSqlSession();
14	StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
15	List<Student> studentList = mapper.getStudent();
16	for (Student student : studentList) {
17	System.out.println(student);
18	}
19
20	sqlSession.close();
21	}
22
23	@Test
24	public void testGetStudentList() {
25	SqlSession sqlSession = MybatisUtils.getSqlSession();
26	StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
27	List<Student> studentList = mapper.getStudentList();
28	for (Student student : studentList) {
29	System.out.println(student);
30	}
31
32	sqlSession.close();
33	}
34	}

1	package com.yubulang.dao;
2
3	import com.yubulang.pojo.Teacher;
4	import org.apache.ibatis.annotations.Param;
5
6	public interface TeacherMapper {
7	Teacher getTeacherById(@Param("tid") int id);
8
9	Teacher getTeacherByIdAnother(@Param("tid") int id);
10	}

前言

搭建新项目

第一步，pom.xml 加入相应的配置

第二步：创建 db.properties mybatis-config.xml 配置类

第三步：创建 MybatisUtils 工具类

一对多

创建实体类

创建实现接口

测试类的干活

多对一

创建实体类

创建实现接口

测试代码的干货

总结

第一步，`pom.xml` 加入相应的配置

第二步：创建 `db.properties` `mybatis-config.xml` 配置类