mybatis多对一处理

mybatis多对一处理

1.环境搭建

• 多个学生，对应一个老师
• 对于学生而言，关联。。多个学生，关联一个老师【多对一】
• 对于老师而言，集合，一个老师，有很多老师【一对多】

1. 建表

   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,'jy');
   
   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');
   INSERT INTO `student`(`id`,`name`,`tid`)VALUES('6','小白','1');
   

2. 建实体类teacher/student，用lombok插件、

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

3. 建立Mapper接口

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

4. 建立Mapper.xml文件

   <?xml version="1.0" encoding="UTF-8" ?>
   <!DOCTYPE mapper
           PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
           "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
   <mapper namespace="com.jy.dao.StudentMapper">
   </mapper>
   

   <?xml version="1.0" encoding="UTF-8" ?>
   <!DOCTYPE mapper
           PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
           "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
   <mapper namespace="com.jy.dao.TeacherMapper">
   </mapper>
   

5. 在核心配置文件中绑定注册的接口或者文件【方式很多，随机选】

   <mapper class="com.jy.dao.TeacherMapper"/>
   <mapper resource="com/jy/dao/TeacherMapper.xml"/>
   <package name="com.jy.dao"/>
   

6. 测试

   public class MyTest {
       @Test
       public void test() {
           SqlSession sqlSession = MybatisUtils.getSqlSession();
           TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
           int teacherById = mapper.getTeacherById(1);
           System.out.println(teacherById);
           sqlSession.close();
       }
   }
   

   测试成功就证明环境搭建成功

2.按照查询嵌套处理（子查询）

思路：

1. 查询所有的学生信息
2. 根据查询出来的学生的tid，寻找对应的老师。（子查询）

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jy.dao.StudentMapper">

    <select id="getStudent" resultMap="StudentTeacher">
        select * from student
    </select>
    
    <resultMap id="StudentTeacher" type="Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <!--复杂的属性，需要单独进行处理。 对象：association	集合：collection-->
        <association property="teacher" column="tid"
                     select="getTeachers" javaType="teacher"/>
    </resultMap>

    <select id="getTeachers" resultType="teacher">
        select * from teacher
    </select>

</mapper>

3.按照结果集嵌套处理（联表查询）

思路：

1. 查询所有的学生信息
2. 根据查询出来的学生的tid，寻找对应的老师。（联表查询）

<!--2 按照结果嵌套处理-->
<select id="getStudent" resultMap="StudentTeacher">
    select s.id sid,s.name sname,t.name tname
    from student s,teacher t
    where s.tid = t.id
</select>
<resultMap id="StudentTeacher" type="student">
    <result column="sid" property="id"/>
    <result column="sname" property="name"/>
    <association property="teacher" javaType="teacher">
        <result column="tname" property="name"/>
    </association>
</resultMap>

测试：

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

结果：

4.附其他部分

• 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">
    </properties>
    <!--日志处理-->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

    <!--别名-->
    <typeAliases>
        <package name="com.jy.pojo"/>
    </typeAliases>

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


    <mappers>
      <package name="com.jy.dao"/>
    </mappers>
</configuration>

• db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=UTF-8
username=root
password=061699

• MybatisUtils

package com.jy.utils;

//可当模板
public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;
    static {
        try {
            //使用Mybatis的第一步，获取sqlSessionFactory对象
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //既然有了 SqlSessionFactory，顾名思义，我们可以从中获得 SqlSession 的实例。
    // SqlSession 提供了在数据库执行 org.apache.ibatis.jdbc.SQL 命令所需的所有方法。
    //你可以通过 SqlSession 实例来直接执行已映射的 SQL 语句
    public static SqlSession getSqlSession() {
        //加上true就代表是自动提交事务
        return sqlSessionFactory.openSession(true);
    }
}

• 接口

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

    List<Teacher> getTeachers();
}
public interface StudentMapper {
    List<Student> getStudent();
}