Mybatis学习总结（三）

mybatis的级联查询

一.准备环境

1.1创建班级，学生，老师表。其中班级和老师是一对一关系，班级和学生是一对多关系。sql语句如下：

CREATE TABLE teacher(t_id INT(10),t_name VARCHAR(32),t_age INT(10));
CREATE TABLE class(c_id INT(10),c_name VARCHAR(32),teacher_id INT(10));
CREATE TABLE student(s_id INT(10) PRIMARY KEY AUTO_INCREMENT ,s_name VARCHAR(32),s_age INT(10),class_id INT(10));
INSERT INTO class(c_id,c_name,teacher_id)VALUES (1,'classOne',1),(2,'classTwo',2);
INSERT INTO teacher(t_id,t_name,t_age)VALUES (1,'zhangsan',31),(2,'lisi',43);
INSERT INTO student(s_id,s_name,class_id)VALUES (1,'zhaoliu',16,1),(2,'wangwu',17,1),(3,'qianqi',15,2);
ALTER TABLE class ADD CONSTRAINT fk_class FOREIGN KEY class(teacher_id) REFERENCES teacher(t_id);
ALTER TABLE student ADD CONSTRAINT fk_student FOREIGN KEY student(class_id) REFERENCES class(c_id);

1.2创建实体类，class类：

import java.util.List;

public class Class {

    private int id;
    private String name;
    private Teacher teacher;
    private List<Student> students;

    }

student类：

public class Student {

    private int id;
    private String name;
    private int age;
   }

teacher类:

public class Teacher {

    private int id;
    private String name;
    private int age;
    }

二.开始开发

2.1mybatis的配置文件conf.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>

    <environments default="mybatisDemo">
        <environment id="mybatisDemo">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="username" value="root" />
                <property name="password" value="" />
                <property name="url" value="jdbc:mysql:///test" />
            </dataSource>
        </environment>
    </environments>
    <!-- 注册classMapper.xml文件 -->
    <mappers>
        <mapper resource="mapper/classMapper.xml" />
    </mappers>
</configuration>

2.2.mabatis的实体类和表的映射文件classMapper.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="mapper.classMapper">

   <!--通过<resultMap>映射实体类属性名和表的字段名对应关系 -->
    <resultMap type="entity.Class" id="classResultMap">
        <id property="id" column="c_id" />
        <result property="name" column="c_name" />
        <!-- association标签解决一对一映射关系 -->
        <association property="teacher" column="teacher_id"
            javaType="entity.Teacher">
            <id property="id" column="t_id" />
            <result property="name" column="t_name" />
            <result property="age" column="t_age" />
        </association>
        <!-- collection标签解决一对多映射关系，ofType指定students集合中的对象类型 -->
        <collection property="students" ofType="entity.Student">
            <id property="id" column="s_id" />
            <result property="name" column="s_name" />
            <result property="age" column="s_age" />
        </collection>
    </resultMap>

    <!-- 根据班级id查询班级，班级老师，和班级学生 -->
    <select id="selectClass" parameterType="int" resultMap="classResultMap">
        select * from class c,teacher t,student s where c.teacher_id=t.t_id and
        c.c_id=s.class_id and c.c_id=#{id}
    </select>
</mapper>

三.测试

package test;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import entity.User;
public class MybatisTest {
    @Test
    public void selectClass() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        entity.Class class1 = sqlSession.selectOne("mapper.classMapper.selectClass", 1);
        sqlSession.close();
        System.out.println(class1);
    }
}

四.查询结果