Mybatis-Study09-多对一

根本就是解决属性名和字段名不一致的问题

测试环境搭建

• 数据库

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

• 包的结构
  
• dao层的StudentMapper和TeacherMapper接口

StudentMapper接口

public interface StudentMapper {
}

TeacherMapper接口

public interface TeacherMapper {
//测试数据库是否连接成功所用的方法
    @Select("select * from teacher where id=#{tid}")
    Teacher getteacher(@Param("tid") int id);
}

• pojo层Student和Teacher实体类

Student类

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 +
                '}';
    }
}

Teacher类

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 + '\'' +
                '}';
    }
}

• utils层的MybatisUtils工具类

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 (Exception e){
            e.printStackTrace();
        }
    }
    public static SqlSession getSqlSession(){
        //设置为true目的是自动提交事务，不需要在设置commit
        return sqlSessionFactory.openSession(true);
    }
}

• resources目录下的配置文件

db.properties

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

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>

    <environments default="mytest">
        <environment id="mytest">
            <transactionManager type="JDBC"></transactionManager>
            <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>
        <mapper class="com.wang.dao.TeacherMapper"></mapper>
        <mapper class="com.wang.dao.StudentMapper"></mapper>
    </mappers>

</configuration>

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.wang.dao.StudentMapper">

</mapper>

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.wang.dao.TeacherMapper">
</mapper>

• 测试类

public class MyTest {
//测试数据库是否连接成功
    @Test
    public void test(){
        SqlSession sqlSession= MybatisUtils.getSqlSession();
        TeacherMapper mapper=sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher=mapper.getteacher(1);
        System.out.println(teacher);
        sqlSession.close();
    }
}

多对一

就相当于我们数据库多表查询

在我们的Student类中有个属性是Teacher,我们需要即查出学生信息又要查出跟这个学生有关联的老师信息

在我们的数据库中对于上述这种情况，我们有两种方式进行查询

• 子查询
• 联表查询

我们的Mybatis中也和数据库中的差不了多少

方式一：按照查询嵌套处理

• StudentMapper接口中

 //方式一：按照查询嵌套处理
    List<Student> getStudent1();

• StudentMapper.xml文件

  <!--方式一：按照查询嵌套处理  相当于数据库中的子查询

        查出teacher表中的数据，然后查出student表中的数据，进行嵌套处理
    -->
    <select id="getStudent1" resultMap="st">
        select * from student
    </select>

    <resultMap id="st" type="Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <!--属性是一个对象使用association  是集合则使用collection -->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"></association>
    </resultMap>

    <select id="getTeacher" resultType="Teacher">
        select * from teacher
    </select>

• 测试类

   //多对一  ：方式一测试
    @Test
    public void test1(){
        SqlSession sqlSession= MybatisUtils.getSqlSession();
        StudentMapper mapper=sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList=mapper.getStudent1();
        for(Student student:studentList){
            System.out.println(student);
        }
        sqlSession.close();
    }

• 结果
  

方式二：按照结果嵌套处理

• StudentMapper接口中

 //方式二：按照结果嵌套处理
    List<Student> getStudent2();

• StuderntMapper.xml文件

<!--方式二：按照结果嵌套处理  数据库中的联表查询-->

    <select id="getStudent2" resultMap="st2">
        select s.id sid,s.name sname,t.name tname from student s,teacher t where s.tid=t.id
    </select>
    <resultMap id="st2" type="Student">
        <!--如果sql语句中规定了别名，我们就要使用别名，否则会报错-->
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <!--对象：association  集合：collection-->
        <association property="teacher" javaType="Teacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>

• 测试类

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

• 结果