Mybatis第三次课总结

19大数据2班
赖晓桐

一、利用Mybatis实现关联查询
1、首先是创建三个表：教师表、班级表和学生表并并分别插入数据。

2、创建与数据库表对应的实体类：Teacher、User、Student并分别创建getter and setter和overwrite方法。

3、在resources/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="net.lxt.mybatis.mapper.ClazzMapper">
    <!--三表关联查询--> 
    <select id="findById" parameterType="int" resultMap="clazzResultMap">
        SELECT * FROM t_teacher INNER JOIN t_class on t_class.teacher_id = t_teacher.t_id
        INNER JOIN t_student ON t_class.c_id = t_student.class_id WHERE c_id = #{id};
    </select>

    <!--定义班级结果映射-->
    <resultMap id="clazzResultMap" type="Clazz">
        <result property="id" column="c_id"/>
        <result property="name" column="c_name"/>
        <!--一对一-->
        <association property="teacher"  javaType="Teacher" column="teacher_id">
            <result property="id" column="t_id"/>
            <result property="name" column="t_name"/>
        </association>
        <!--一对多-->
        <collection property="students" ofType="Student">
            <result property="id" column="s_id"/>
            <result property="name" column="s_name"/>
            <result property="gender" column="s_gender"/>
            <result property="age" column="s_age"/>
            <association property="clazz" javaType="Clazz" column="class_id">
                <result property="name" column="c_name"/>
            </association>
        </collection>
    </resultMap>

    <!--通过嵌套查询实现一对一关联-->
    <select id="findAll" resultMap="clazzResultMap2">
        select * from t_class;
    </select>

    <!--定义班级结果映射-->
    <resultMap id="clazzResultMap2" type="Clazz">
        <result property="id" column="c_id"/>
        <result property="name" column="c_name"/>
        <association property="teacher" column="teacher_id" javaType="Teacher" select="getTeacher"/>
    </resultMap>

    <select id="getTeacher" resultType="Teacher">
        SELECT t_id id,t_name name from t_teacher where t_id = #{id};
    </select>


</mapper>

4、修改Mybatis配置文件，在mybatis-config.xml中配置班级、教室和学生实体类别名以及班级映射器配置文件。

5、定义班级映射接口ClassMapper

6、创建测试类TestClazzMapper

package net.lxt.mybatis.mapper;

import net.lxt.mybatis.bean.Clazz;
import net.lxt.mybatis.bean.Student;
import net.lxt.mybatis.bean.User;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.Reader;
import java.util.List;

public class TestClazzMapper {
    private SqlSession sqlSession;//sql会话
    private ClazzMapper clazzMapper;//班级映射器

    @Before
    public void init() {
        try {
            //读取MyBatis配置文件
            Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
            //基于MyBatis配置文件构建SQL会话
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
            //利用SQL会话工厂获取SQL会话
            sqlSession = factory.openSession();
            //提示用户SQL会话创建成功
            System.out.println("sqlSession对象创建成功。");
            clazzMapper = sqlSession.getMapper(ClazzMapper.class);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void testFindById() {
        int id = 1;
        Clazz clazz = clazzMapper.findById(id);
        System.out.println(clazz);

        System.out.println("班级编号[" + id + "]的学生：");
        List<Student> students = clazz.getStudents();
        for (Student student : students) {
            System.out.println(student);
        }


    }

    @Test
    public void testFindAll() {
        List<Clazz> clazzes = clazzMapper.findAll();

        for (Clazz clazz : clazzes) {
            System.out.println(clazz);
        }

    }
    @After

    public void destroy(){
        //关闭SQL会话
        sqlSession.close();
        //提示用户SQL会话对象关闭
        System.out.println("sqlSession对象已关闭。");
    }
}

1)testFindById运行结果：

2)testFindAll运行结果：

二、利用Mybatis实现条件查询
1、在resources/mapper目录中创建学生映射器配置文件：StudentMapper.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 = "net.lxt.mybatis.mapper.StudentMapper">
    <select id="findByCondition" parameterType="java.util.Map" resultMap="studentResultMap">
        select * from t_student
        <trim prefix="where" prefixOverrides="and | or">
            <if test="name != null">
                s_name like concat(#{name},"%")
            </if>
            <if test="gender != null">
                and s_gender = #{gender}
            </if>
            <if test="age != null">
                and s_age =#{age}
            </if>
        </trim>
    </select>

    <resultMap id="studentResultMap" type="Student">
        <result column="s_id" property="id"/>
        <result column="s_name" property="name"/>
        <result column="s_gender" property="gender"/>
        <result column="s_age" property="age"/>
        <association property="clazz" column="class_id" javaType="Clazz" select="getClazz"/>
    </resultMap>

    <select id="getClazz" resultType="Clazz">
        select c_id id,c_name name from t_class where c_id = #{id};
    </select>
</mapper>

2、在MyBatis配置文件里注册学生映射器配置文件。

3、创建学生映射器接口：StudentMapper

4、创建测试类TestStudentMapper

package net.lxt.mybatis.mapper;

import net.lxt.mybatis.bean.Clazz;
import net.lxt.mybatis.bean.Student;
import net.lxt.mybatis.bean.User;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.Reader;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class TestStudentMapper {
    private SqlSession sqlSession;//sql会话
    private StudentMapper studentMapper;//班级映射器

    @Before
    public void init() {
        try {
            //读取MyBatis配置文件
            Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
            //基于MyBatis配置文件构建SQL会话
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
            //利用SQL会话工厂获取SQL会话
            sqlSession = factory.openSession();
            //提示用户SQL会话创建成功
            System.out.println("sqlSession对象创建成功。");
            studentMapper = sqlSession.getMapper(StudentMapper.class);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    @Test
    public void testFindByCondition(){
        Map<String,Object> condition = new HashMap<String, Object>();
        condition.put("gender","女");
        List<Student> students = studentMapper.findByCondition(condition);
        for (Student student : students) {
            System.out.println(student);
        }

    }
    @After

    public void destroy(){
        //关闭SQL会话
        sqlSession.close();
        //提示用户SQL会话对象关闭
        System.out.println("sqlSession对象已关闭。");
    }
}

运行testFindByCondition()方法，查找性别为女的记录：

三、遇到的问题
1、没有获取对应的Mapper

报错为空指针异常，问题出现在TestClazzMapper中少了一句：clazzMapper =sqlSession.getMapper(ClazzMapper.class);
映射器无法通过命名空间和方法名称找到对应的SQL，就无法发送给数据库执行后返回结果。

2、代码错误
在错误提示中明显可以看到是SQL语句出错，在StudentMapper.xml中发现SQL语句最后多了一个分号。

多了一个分号导致SQL语句出错，删掉分号就可以了。