一、MyBatis 中数据关联查询 —— 使用 association 完成 多对关系的映射

1.1 修改数据库

针对数据库的数据格式要进行修改

给 student 表增加如下外键关系
image.png
image.png

  1. SET FOREIGN_KEY_CHECKS=0;
  3. -- ----------------------------
  4. -- Table structure for `clazz`
  5. -- ----------------------------
  6. DROP TABLE IF EXISTS `clazz`;
  7. CREATE TABLE `clazz` (
  8.   `cno` int(11) NOT NULL AUTO_INCREMENT,
  9.   `cname` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  10.   PRIMARY KEY (`cno`)
  11. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
  13. -- ----------------------------
  14. -- Records of clazz
  15. -- ----------------------------
  16. INSERT INTO `clazz` VALUES ('1', '班级1');
  17. INSERT INTO `clazz` VALUES ('2', '班级2');
  18. INSERT INTO `clazz` VALUES ('3', '班级3');
  20. -- ----------------------------
  21. -- Table structure for `student`
  22. -- ----------------------------
  23. DROP TABLE IF EXISTS `student`;
  24. CREATE TABLE `student` (
  25.   `id` tinyint(5) NOT NULL AUTO_INCREMENT,
  26.   `sname` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  27.   `gender` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  28.   `age` tinyint(3) NOT NULL,
  29.   `cno` int(11) DEFAULT NULL,
  30.   PRIMARY KEY (`id`),
  31.   KEY `student_ibfk_1` (`cno`),
  32.   CONSTRAINT `student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `clazz` (`cno`) ON DELETE NO ACTION ON UPDATE NO ACTION
  33. ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;
  35. -- ----------------------------
  36. -- Records of student
  37. -- ----------------------------
  38. INSERT INTO `student` VALUES ('1', '张', '女', '10', '1');
  39. INSERT INTO `student` VALUES ('2', 'coco', '女', '10', '2');
  40. INSERT INTO `student` VALUES ('6', '小张同学', '女', '18', '3');
  41. INSERT INTO `student` VALUES ('7', '小李', '女', '23', '1');
  42. INSERT INTO `student` VALUES ('8', '小T', '男', '19', '2');

1.2 实体类修改

  1. // mybatis 中数据库的信息通过 setter 方法和 ognl 表达式即可完成注入，
  2. public class Student {
  3.     private Integer id;
  4.     private String name;
  5.     private String gender;
  6.     private int age;
  7.     private Clazz clazz;
  8.      // getter  setter 省略，toString 方法省略   
  9. }
  11. // 班级类
  12. public class Clazz {
  13.     private int cno;
  14.     private String cname;
  15.      // getter 和 setter 省略，toString 省略   
  16. }

1.3 编写接口完成级联查询

public interface StudentMapper {    

    // 根据 ID 查询 （使用级联查询）
    public Clazz selectClazzById(int id);

    public Student selectStudentById(int id);   

}

1.4 studentMapper 配置文件修改

<?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="cn.gorit.mapper.StudentMapper">

    <select id="selectClazzById" parameterType="int" resultType="cn.gorit.entity.Clazz">
        select * from clazz where cno = #{cno}
    </select>

    <!-- 当数据库表的表名称和实体类的属性名称不对应的时候需要声明 -->
    <resultMap id="stuMap" type="cn.gorit.entity.Student">
        <id column="id" property="id"/>
        <result column="sname" property="name"/>
        <result column="gender" property="gender"/>
        <result column="age" property="age"/>
        <!-- 方式二：使用级联查询 使用了 额外的嵌套查询-->
        <association property="clazz" column="cno" javaType="cn.gorit.entity.Clazz" select="selectClazzById"/>
    </resultMap>

    <!-- 方式二 -->
    <select id="selectStudentById" parameterType="int" resultMap="stuMap">
        select * from student where id = #{id}
    </select>

</mapper>

1.5 运行测试

 public class Test {
    public static void main(String[] args) throws IOException {
        // 加载配置文件
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);

        // 创建能执行映射文件中的 SqlSession
        SqlSession session = sessionFactory.openSession();
        // 获取映射，自动创建 mapper 接口的实现，直接调用数据库的方法
        StudentMapper mapper = session.getMapper(StudentMapper.class);
        // 级联查询
        Student student =  mapper.selectStudentById(1);
        System.out.println(student.toString());
     }
 }

我们可以看到，分开执行了两次
image.png

二、使用子查询简化查询步骤

2.1 studentMapper 修改

    <!-- 当数据库表的表名称和实体类的属性名称不对应的时候需要声明 -->
    <resultMap id="stuMap" type="cn.gorit.entity.Student">
        <id column="id" property="id"/>
        <result column="sname" property="name"/>
        <result column="gender" property="gender"/>
        <result column="age" property="age"/>

        <!--方式三： -->
        <association property="clazz"  javaType="cn.gorit.entity.Clazz">
            <id column="cno" property="cno"/>
            <result column="cname" property="cname"/>
        </association>
    </resultMap>

            <!-- 方式三 使用 map 映射 -->
    <select id="selectStudentById" parameterType="int" resultMap="stuMap">
        select * from student s,clazz c where s.cno = c.cno and s.id = #{id}
    </select>

2.2 运行测试

上述配置不变，然后运行即可

image.png

三、动态 SQL

3.1 实体类修改

// 单表
public class Student1 {
    private Integer id;
    private String sname;
    private String gender;
    private int age;
    private Integer cno;
     // getter setter 省略
}

3.2 接口编写

    // 动态 SQL 测试
    List<Student1> selectStudentByGenderAndCno(@Param("gender") String gender, @Param("cno") Integer cno);

    // 动态 SQL
    List<Student1> selectGenderAndName(@Param("gender") String gender,@Param("sname") String sname);

    // 动态 SQL 修改
    public void updateStudentWithNameAndGenderAndAgeById(@Param("sname") String sname,@Param("gender") String gender,@Param("age") Integer age,@Param("id") Integer id);

3.3 studentMapper 编写

<!-- 动态 SQL -->
    <select id="selectStudentByGenderAndCno" resultType="cn.gorit.entity.Student1">
        select * from student where 1=1
        <choose>
            <when test="gender != null and cno != null">
                AND gender = #{gender} AND cno = #{cno}
            </when>
            <when test="gender != null">
                AND gender = #{gender}
            </when>
            <otherwise>
                AND cno = #{cno}
            </otherwise>
        </choose>
    </select>

    <select id="selectGenderAndName" resultType="cn.gorit.entity.Student1">
        <include refid="s1"/>
            <!-- where 会自动插入 where 子句 -->
            <where>
                <if test="gender != null and sname != null">
                    gender = #{gender} AND  sname like #{sname}
                 </if>
                <if test="sname != null">
                   and sname like #{sname}
                </if>
                <if test="gender != null">
                   and gender = #{gender}
                </if>
            </where>
    </select>

    <!-- 动态 SQL -->
    <update id="updateStudentWithNameAndGenderAndAgeById">
        update student
        <set>
            <if test="sname!=null">
                sname = #{sname},
            </if>
            <if test="age!=null">
                age = #{age},
            </if>
            <if test="gender!=null">
                gender = #{gender},
            </if>
        </set>
            where id = #{id}
    </update>

3.4 测试类

        // 动态 SQL  choose when otherwise
//        List<Student1> list = mapper.selectStudentByGenderAndCno("男",2);
//        List<Student1> list = mapper.selectStudentByGenderAndCno("男",null);
//        for (Student1 s:list) {
//            System.out.println(s.toString());
//        }

        // 动态 SQL  where if
//        List<Student1> list = mapper.selectGenderAndName("男",null);
//        for (Student1 s:list) {
//            System.out.println(s.toString());
//        }

        // 动语 SQL update set
//        mapper.updateStudentWithNameAndGenderAndAgeById("小A","女",20, 17);
//        session.commit();
        System.out.println(mapper.selectStudentById(17));