Mybatis中实现多表查询（多对一）
目录
Mybatis项目结构
首先建立数据库表
student
teacher
然后创建实体类
student
teacher
dao层
mapper
teat
Mybatis项目结构
mybatis-project
|—— src/main/java
| |—— com.dao
| |—— com.entity
|—— src/main/resources/mapper
|—— src/test首先建立数据库表
student
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;-- Table structure for student
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (`id` int(11) NOT NULL AUTO_INCREMENT,`Sname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,`sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,`age` int(11) DEFAULT NULL,`t_id` int(11) DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;-- Records of student
INSERT INTO `student` VALUES (1, '张三', '男', 18, 1);
INSERT INTO `student` VALUES (2, '李四', '女', 18, 1);
INSERT INTO `student` VALUES (3, '王五', '男', 18, 1);
INSERT INTO `student` VALUES (4, '小白', '女', 18, 1);
INSERT INTO `student` VALUES (5, '小黑', '男', 18, 1);
INSERT INTO `student` VALUES (6, '小红', '女', 20, 2);
INSERT INTO `student` VALUES (7, '小李', '男', 20, 2);
INSERT INTO `student` VALUES (8, '小张', '女', 20, 2);
INSERT INTO `student` VALUES (9, '小赵', '男', 20, 2);
INSERT INTO `student` VALUES (10, '小王', '女', 20, 2);SET FOREIGN_KEY_CHECKS = 1;teacher
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;-- Table structure for teacher
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (`id` int(11) NOT NULL AUTO_INCREMENT,`Tname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;-- Records of teacher
INSERT INTO `teacher` VALUES (1, '张老师');
INSERT INTO `teacher` VALUES (2, '李老师');SET FOREIGN_KEY_CHECKS = 1;然后创建实体类
student
package com.qcby.entity;import java.util.List;public class Student {private Integer id;private String Sname;private String sex;private Integer age;private Integer t_id;private Teacher teacher;@Overridepublic String toString() {return "Student{" +"id=" + id +", Sname='" + Sname + '\'' +", sex='" + sex + '\'' +", age=" + age +", t_id=" + t_id +", teacher=" + teacher +'}';}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getSname() {return Sname;}public void setSname(String sname) {Sname = sname;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public Integer getAge() {return age;}public void setAge(Integer age) {this.age = age;}public Integer getT_id() {return t_id;}public void setT_id(Integer t_id) {this.t_id = t_id;}public Teacher getTeacher() {return teacher;}public void setTeacher(Teacher teacher) {this.teacher = teacher;}
}teacher
package com.qcby.entity;import java.util.List;public class Teacher {private Integer id;private String Tname;public Teacher() {}public Teacher(Integer id, String tname) {this.id = id;Tname = tname;}@Overridepublic String toString() {return "Teacher{" +"id=" + id +", Tname='" + Tname + '\'' +'}';}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getTname() {return Tname;}public void setTname(String tname) {Tname = tname;}
}dao层
package com.qcby.dao;import com.qcby.entity.Student;
import com.qcby.entity.StudentTeacher;import java.util.List;public interface StudentDao {List<StudentTeacher> findStudentTeacher();List<Student> findStudentTeacher1();List<Student> findStudentTeacher2();
}mapper
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qcby.dao.StudentDao"><!--查询每个学生对应的老师--><select id="findStudentTeacher" resultType="com.qcby.entity.StudentTeacher">select student.*,teacher.Tname from student left join teacher on student.t_id = teacher.id;</select><!--关系映射--><!--查询每个学生对应的老师 （映射型的出参）--><select id="findStudentTeacher1" resultMap="StudentTeacher">select student.*,teacher.Tname from student left join teacher on student.t_id = teacher.id;</select><resultMap id="StudentTeacher" type="com.qcby.entity.Student"><!--property是实体类中的变量 column是表当中的字段--><id property="id" column="id"/><result property="Sname" column="Sname"/><result property="sex" column="sex"/><result property="age" column="age"/><result property="t_id" column="t_id"/><!--复杂的属性单独处理 对象--><association property="teacher" javaType="com.qcby.entity.Teacher"><result property="Tname" column="Tname"/></association></resultMap><!--多表查询；分布实现--><!--select * from student--> <!--将第一个查询出来的t_id字段的值写入到第二个sql当中--><!--select * from teacher where id = #{t_id}--><select id="findStudentTeacher2" resultMap="StudentTeacher2">select * from student</select><resultMap id="StudentTeacher2" type="com.qcby.entity.Student"><id property="id" column="id"/><result property="Sname" column="Sname"/><result property="sex" column="sex"/><result property="age" column="age"/><result property="t_id" column="t_id"/><!-- column:传值的作用 select:sql方法调用--><association property="teacher" javaType="com.qcby.entity.Teacher" column="t_id" select="getTeacher"/></resultMap><select id="getTeacher" resultType="com.qcby.entity.Teacher">select * from teacher where id = #{t_id}</select>
</mapper>teat
import com.qcby.dao.StudentDao;
import com.qcby.entity.Student;
import com.qcby.entity.StudentTeacher;
import com.qcby.entity.User;
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.After;
import org.junit.Before;
import org.junit.Test;import java.io.IOException;
import java.io.InputStream;
import java.sql.Date;
import java.util.Arrays;
import java.util.List;
public class StudentTest {private InputStream in = null;private SqlSession session = null;private StudentDao mapper = null;@Before //前置通知, 在方法执行之前执行public void init() throws IOException {//加载主配置文件，目的是为了构建SqlSessionFactory对象in = Resources.getResourceAsStream("SqlMapConfig.xml");//创建SqlSessionFactory对象SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);//通过SqlSessionFactory工厂对象创建SqlSesssion对象session = factory.openSession();//通过Session创建UserDao接口代理对象mapper = session.getMapper(StudentDao.class);}@After //@After: 后置通知, 在方法执行之后执行 。public void destory() throws IOException {//释放资源session.close();in.close();}@Testpublic void findStudentTeacher(){List<StudentTeacher> studentTeachers = mapper.findStudentTeacher();for (StudentTeacher studentteacher : studentTeachers) {System.out.println(studentteacher.toString());}}@Testpublic void findStudentTeacher1(){List<Student> studentTeachers = mapper.findStudentTeacher1();for (Student studentteacher : studentTeachers) {System.out.println(studentteacher.toString());}}@Testpublic void findStudentTeacher2(){List<Student> studentTeachers = mapper.findStudentTeacher2();for (Student studentteacher : studentTeachers) {System.out.println(studentteacher.toString());}}
}