Mybatis学习笔记（五）：多对一和一对多

Mybatis多对一和一对多

一、多对一

• 概述

  即多个对象对应一个对象，例如多个学生对应一个老师

• association — 联系 ，关联 多个人可以关联一个人。

• collection — 集合 一个人有一个集合，包含多个人。

• 发现是多对一业务情况，我们需要使用association 标签进行关联

例如，现在有两张表，student表和teacher表，两张表之间用tid和id进行关联。现需要查询学生id，name，还有老师的name，因此需要连表查询

pojo实体类

package org.westos.pojo;

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

package org.westos.pojo;

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

dao层接口：

import org.westos.pojo.Student;
import java.util.List;

public interface StudentDao {

    //获得全部学生的信息以及对应的老师【方法一】
    List<Student> getStudents();

    //获得全部学生的信息以及对应的老师【方法二】
    List<Student> getStudentsTwo();
}

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

<!--namespace不能写别名-->
<mapper namespace="org.westos.dao.StudentDao">

    <!--遇到问题：学生类中关联老师： 多个学生对应一个老师 -->
    <!--<select id="getStudents" resultType="Student">-->
    <!--select s.id,s.name,t.name from mybatis.student as s,mybatis.teacher as t-->
    <!--where s.tid = t.id-->
    <!--</select>-->

    <!--解决问题方式一:按查询结果嵌套处理，模拟数据库思想；-->
    <select id="getStudents" resultMap="StudentTeacher">
        select * from mybatis.student
    </select>

    <resultMap id="StudentTeacher" type="Student">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <!--属性和字段对应  ， 类和表对应  ， 对象和记录
        关联一个字段
        需求：拿到老师这个类的属性

            association ： 关联，多对一
            column ： 数据库对应的列名
            property ： 对应属性名
            javaType ： 多对一字段对应的Java类型
            select ： 关联一个语句
        -->
        <association column="tid" property="teacher" javaType="Teacher" select="getTeacher"/>
    </resultMap>

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




    <!-- 解决方式二：一个resultMap解决 ， 模拟面向对象的思想-->
    <select id="getStudentsTwo" resultMap="StudentTeacher2">
        select s.id,s.name,t.id as tid,t.name as tname
        from mybatis.student as s, mybatis.teacher as t
        where s.tid = t.id
    </select>

    <!--设置结果集映射ResultMap -->
    <resultMap id="StudentTeacher2" type="Student">
        <id property="id" column="id"/>
        <result property="name" column="name"/>

        <!--直接关联一个老师-->
        <association property="teacher" javaType="Teacher">
            <id property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>
</mapper>

测试类：

package org.westos.dao;


import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import org.westos.pojo.Student;
import org.westos.utils.MyBatisUtils;

import java.util.List;

public class StudentDaoTest {
    @Test
    public void getStudents(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

        StudentDao mapper = sqlSession.getMapper(StudentDao.class);

        List<Student> students = mapper.getStudents();

        for (Student student : students) {
            System.out.println("学生姓名："+student.getName()+"\t老师姓名："+student.getTeacher().getName());
        }

    }

    @Test
    public void getStudentsTwo(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

        StudentDao mapper = sqlSession.getMapper(StudentDao.class);

        List<Student> students = mapper.getStudentsTwo();

        for (Student student : students) {
            System.out.println("学生姓名："+student.getName()+"\t老师姓名："+student.getTeacher().getName()
            +student.getTeacher().getId());
        }

    }
}

两种方式都能达到想要的效果，查询结果如下：

二、一对多

pojo类：

package org.westos.pojo;

import java.util.List;

public class Teacher {
    private int id;
    private String name;
    private List<Student> students;


    public Teacher() {
    }

    public Teacher(int id, String name, List<Student> students) {
        this.id = id;
        this.name = name;
        this.students = students;
    }

    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 List<Student> getStudents() {
        return students;
    }

    public void setStudents(List<Student> students) {
        this.students = students;
    }

    @Override
    public String toString() {
        return "Teacher{" +
                "id=" + id +
                ", name='" + name + '\'' +"\n"+
                ", students=" + students +"\n"+
                '}';
    }
}

dao层接口：

package org.westos.dao;

import org.westos.pojo.Teacher;

public interface TeacherDao {
    Teacher getTeacher(int id);

    Teacher getTeacher2(int id);
}

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="org.westos.dao.TeacherDao">

    <!--数据库思想-->
    <select id="getTeacher" resultMap="TeacherStudent">
        select * from mybatis.teacher where id = #{id}
    </select>

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

    <select id="student" resultType="Student">
        select * from mybatis.student s,mybatis.teacher t where s.tid=t.id
    </select>


    <!--面向对象思想-->
    <select id="getTeacher2" resultMap="TeacherStudent2">
        select s.name sname,s.id sid,t.id tid, t.name tname
        from mybatis.student as s,mybatis.teacher as t
        where s.tid = t.id and t.id = #{id}
    </select>
    <resultMap id="TeacherStudent2" type="Teacher">
        <result property="name" column="tname"/>
        <collection property="students" ofType="Student">
            <id property="id" column="sid"/>
            <result property="name" column="sname"/>
        </collection>
    </resultMap>

</mapper>

测试代码：

package org.westos.dao;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import org.westos.pojo.Teacher;
import org.westos.utils.MyBatisUtils;

public class TestTeacherMapper {
    @Test
    public void test(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        TeacherDao mapper = sqlSession.getMapper(TeacherDao.class);
        Teacher teacher = mapper.getTeacher(1);
        System.out.println(teacher);
    }

    @Test
    public void test2(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        TeacherDao mapper = sqlSession.getMapper(TeacherDao.class);
        Teacher teacher2 = mapper.getTeacher2(1);
        System.out.println(teacher2);
    }
}

运行结果：

三、动态SQL

动态SQL就是指根据不同查询条件，生成不同的SQL语句，在 Mybatis之前的版本中，有很多元素需要花时间了解。Mybatis3 大大精简了元素种类，现在只需学习原来一半的元素便可。Mybatis采用功能强大的基于 OGNL 的表达式来淘汰其它大部分元素。

if

参数test：里面的表达式如果为ture则执行，否则不执行，且test为必填属性

案例：通过多个条件（可能为一个，也可能为两个）查询获取值

    <select id="getBillList" resultType="Bill">
        select b.*,p.proName from smbms.smbms_bill b,smbms.smbms_provider p
        where b.providerId=p.id
        <if test="providerName!=null">and p.proName like CONCAT('%',#{providerName}'%')</if>
        <if test="providerId!=null">and b.providerId=#{providerId}</if>
        order by b.id asc limit #{currentPage},#{pageSize}
    </select>

上面案例中两个第一个if表示如果providerName不为空则进行其后面的模糊查询，第二个if表示如果providerName不为空则执行其后的为providerId赋值操作