多对一

SQL建表

CREATE TABLE	`teacher`(
	`id` int(10) not null,
	`name` VARCHAR(30) DEFAULT NULL,
	PRIMARY KEY(`id`)

)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT into teacher(`id`,`name`)VALUES(1,'老师');

CREATE TABLE `student`(
	`id` int(10) not null,
	`name` varchar(30) DEFAULT NULL,
	`tid` int(10) DEFAULT NULL,
	PRIMARY KEY(`id`),
	key `fktid`(`tid`),
	CONSTRAINT `fktid` FOREIGN KEY(`tid`) REFERENCES `teacher` (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

insert into `student`(`id`,`name`,`tid`)VALUES('1','小明','1')
insert into `student`(`id`,`name`,`tid`)VALUES('2','小红','1')
insert into `student`(`id`,`name`,`tid`)VALUES('3','小明','1')
insert into `student`(`id`,`name`,`tid`)VALUES('4','小明','1')
insert into `student`(`id`,`name`,`tid`)VALUES('5','小明','1')

1. 导入lombok

   <dependencies>
   <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
       <dependency>
           <groupId>org.projectlombok</groupId>
           <artifactId>lombok</artifactId>
           <version>1.18.10</version>
       </dependency>
   </dependencies>
   

2. 新建实体类Teacher Student

   @Data
   public class Student {
       private int id;
       private String name;
       //学生需要关联一个老师
       private Teacher teacher;
   }
   

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

3. 建立Mapper接口

   public interface TeacherMapper {
       @Select("select * from teacher where id = #{tid}")
       Teacher getTeacher(@Param("tid") int id);
   }
   

   package com.yu.dao;
   
   public interface StudentMapper {
   }
   

4. 建立Mapper.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.yu.dao.TeacherMapper">
   
   
   </mapper>
   

   <?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.yu.dao.StudentMapper">
   
   
   </mapper>
   

5. 在和新配置文件中绑定注册Mapper接口或者文件

   <mappers>
       <mapper class="com.yu.dao.TeacherMapper"></mapper>
       <mapper class="com.yu.dao.StudentMapper"></mapper>
   </mappers>
   

6. 测试

   public class MyTest {
       public static void main(String[] args) {
           SqlSession sqlSession = MybatisUtils.getSqlSession();
           TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
           Teacher teacher = mapper.getTeacher(1);
           System.out.println(teacher);
           sqlSession.close();
       }
   }
   

按照查询嵌套处理

<select id="getStudent" resultMap="StudentTeacher">
    select * from student
</select>

<resultMap id="StudentTeacher" type="Student">
    <result property="id" column="id"></result>
    <result property="name" column="name"></result>
    <!--
        复杂的属性单独处理
        对象:association
        集合:collection
     -->
    <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"></association>

</resultMap>
<select id="getTeacher" resultType="Teacher">
    select * from teacher where id = #{id}
</select>

按照结果嵌套处理

<select id="getStudent2" resultMap="StudentTeacher2">
    select s.id sid,s.name sname,t.name tname
    from student s,teacher t
    where s.tid = t.id;
</select>

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

多对一查询方式

• 子查询
• 联表查询