Mybatis-03(多对一的表查询，一对多的表查询)

多对一的表查询：

①数据库

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');

②创建maven项目，配置Mybatis-config.xml

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="admin"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper class="dao.TeacherMapper"/>
        <mapper resource="dao/StudentMapper.xml"/>
    </mappers>
</configuration>

③编写工具类MybatisUtils

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

//SqlSessionFacory
public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;
    static{
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static SqlSession getSqlSession(){
        return  sqlSessionFactory.openSession(true);
    }


}

④编写实体类，Student和Teacher

package pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private int id;
    private String name;
    private Teacher teacher;
}

package pojo;


import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
    private int id;
    private String name;
}

⑤这时候发现忘了添加mybatis（本地文件中自己添加）和lombok依赖了

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>

        </dependency>

⑥编写StudentMapper接口

package dao;

import pojo.Student;

import java.util.List;

public interface StudentMapper {
    //查询所有的学生信息，以及对应的老师信息
    public List<Student> getStudent();
}

⑦编写StudentMapper.xml，有两种查询方法，一种是按照查询嵌套处理，另一种是按照结果嵌套处理。

1）按照查询嵌套处理

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

<mapper namespace="dao.StudentMapper">


    <select id="getStudent" resultMap="StudentTeacher">
        select * from mybatis.student;
    </select>
    <resultMap id="StudentTeacher" type="pojo.Student">
        <id property="id" column="id"/>
        <id property="name" column="name"/>
        <!--复杂的属性，我们需要单独处理 对象： association 集合： collection -->
        <association property="teacher" column="tid" javaType="pojo.Teacher" select="getT"/>
    </resultMap>
    <select id="getT" resultType="pojo.Teacher">
        select * from mybatis.teacher where id=#{id};
    </select>



</mapper>

2)按照结果嵌套处理

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

<mapper namespace="dao.StudentMapper">


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

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

</mapper>

⑧在mybatis-config.xml中添加映射（②中已经添加）

⑨编写测试类

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

项目结构：

TeacherMapper不用管，没有用到

一对多的查询：

前五步都一样

⑥编写TeacherMapper

package dao;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import pojo.Teacher;

import java.util.List;

public interface TeacherMapper {
//    List<Teacher> getTeacher();
    //获取指定老师下的所有学生及老师的信息
    Teacher getTeacher(@Param("tid") int id);
}

⑦编写TeacherMapper.xml

1）按照结果嵌套处理

<?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="dao.TeacherMapper">

    <select id="getTeacher" resultMap="TS">
        select s.id sid, s.name sname, t.name tname,t.id tid
        from mybatis.student s,mybatis.teacher t
        where s.tid = t.id and t.id = #{tid}
    </select>
    <resultMap id="TS" type="pojo.Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="students" ofType="pojo.Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>
</mapper>

2）按照查询嵌套处理

<select id="getTeacher2" resultMap="TeacherStudent2">
    select * from mybatis.teacher where id = #{tid}
</select>

<resultMap id="TeacherStudent2" type="Teacher">
    <collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id"/>
</resultMap>

<select id="getStudentByTeacherId" resultType="Student">
    select * from mybatis.student where tid = #{tid}
</select>

⑧⑨跟之前的一样。

需要注意的点是：

关联 - association 【多对一】
集合 - collection 【一对多】
javaType & ofType
1. JavaType 用来指定实体类中属性的类型
2. ofType 用来指定映射到List或者集合中的 pojo类型，泛型中的约束类型！

posted @ 2022-03-20 23:43 塵暘阅读(43) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

塵暘

Mybatis-03(多对一的表查询，一对多的表查询)

公告