mybatis 的多表查询（22.07.07）

实体间的关系：关联关系（拥有 has、属于 belong）

OneToOne：一对一关系（Passenger--- Passport）

OneToMany：一对多关系（Employee --- Department）

ManyToMany：多对多关系（Student --- Subject）

 

 

 

OneToOne

<?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="com.qf.java2207.mapper.PassengerMapper">

    <resultMap id="passengerResultMap" type="passenger">
        <id property="id" column="id"></id>
        <result property="name" column="name"></result>
        <result property="sex" column="sex"></result>
        <result property="birthday" column="birthday"></result>
        <association property="passport" javaType="passport">
            <id property="id" column="passport_id"></id>
            <result property="nationality" column="nationality"></result>
            <result property="expire" column="expire"></result>
            <result property="passenger_id" column="passenger_id"></result>
        </association>
    </resultMap>

    <!--根据id乘客信息 同时把乘客的护照信息也查询出来-->
    <select id="selectPassengerById" resultMap="passengerResultMap">
        select t1.id,t1.name,t1.sex,t1.birthday,
               t2.id as passport_id,t2.nationality,t2.expire,t2.passenger_id
        from t_passenger t1, t_passport t2 where t1.id= t2.passenger_id and t1.id = #{id}

<!--         select t1.id,t1.name,t1.sex,t1.birthday,
                t2.id as passport_id,t2.nationality,t2.expire,t2.passenger_id
         from t_passenger t1 left join t_passport t2 on  t1.id= t2.passenger_id
         where t1.id = #{id}
-->

    </select>
</mapper>

OneTo Many

<?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="com.qf.java2207.mapper.DepartmentMapper">



    <resultMap id="departmentResultMap" type="department">
        <id property="id" column="id"></id>
        <result property="name" column="name"></result>
        <result property="location" column="location"></result>
        <collection property="employees" ofType="employee">
            <id property="id" column="emp_id"></id>
            <result property="name" column="emp_name"></result>
            <result property="salary" column="salary"></result>
            <result property="dept_id" column="dept_id"></result>
        </collection>
    </resultMap>


<!--    查询部门信息 顺道查询部门下的所有员工信息-->
    <select id="selectDepartmentById" resultMap="departmentResultMap">
        <!-- 别名（避免与d.id、d.name冲突）-->
        SELECT d.id , d.name , d.location , e.eid AS emp_id , e.name emp_name , e.salary , e.dept_id
        FROM t_departments d LEFT JOIN t_employees e
        ON d.id = e.dept_id
        WHERE d.id = #{id}
    </select>
</mapper>

ManyTo Many

 

<?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="com.qf.java2207.mapper.StudentMapper">

    <resultMap id="allStudentsMap" type="student">
        <id property="id" column="id"></id>
        <result property="name" column="name"></result>
        <result property="sex" column="sex"></result>
        <collection property="subjects" ofType="subject">
            <id property="id" column="sid"></id>
            <result property="name" column="sname"></result>
            <result property="grade" column="grade"></result>
        </collection>
    </resultMap>

    <select id="selectAllStudents" resultMap="allStudentsMap">
            SELECT s1.* , ss.* , s2.id as sid , s2.name as sname , s2.grade
            FROM t_students s1 LEFT JOIN t_stu_sub ss
            ON s1.id = ss.student_id <!-- 通过t_stu_sub表建立二者之间的关系 -->
            LEFT JOIN t_subjects s2
            ON ss.subject_id = s2.id
    </select>
</mapper>

mybatis-cofig.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">

<!--MyBatis配置-->
<configuration>

    <!--添加properties配置文件路径(外部配置、动态替换)-->
    <properties resource="db.properties" />

    <!--定义别名二选一-->
    <typeAliases>
        <!--定义类的别名-->
<!--        <typeAlias type="com.qf.java2207.mybatis.pojo.User" alias="user" />-->

        <!--自动扫描包，将原类名作为别名-->
        <package name="com.qf.java2207.pojo" />
    </typeAliases>


    <!--JDBC环境配置、选中默认环境-->
    <environments default="MySqlDB">
        <environment id="MySqlDB">
            <!--事务管理-->
            <transactionManager type="JDBC"></transactionManager>
            <!--连接池-->
            <dataSource type="org.apache.ibatis.datasource.pooled.PooledDataSourceFactory">
                <property name="driver" value="${driver}"/>
                <!-- &转义&amp; -->
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>


    </environments>

    <!--映射文件的配置-->
    <mappers>

        <package name="com.qf.mapper"/>
    </mappers>
</configuration>

关系总结:

一方，添加集合；多方，添加对象。

双方均可建立关系属性，建立关系属性后，对应的Mapper文件中需使用< ResultMap >完成多表映射。

持有对象关系属性，使用< association property="dept" javaType="department" >

持有集合关系属性，使用< collection property="emps" ofType="employee" >