【Mybatis学习笔记】系列之二：Mybatis双项一对多关联

条件：Group和Student是一对多关系

一：GroupMapper.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="com.company.dao.IGroupDAO"> 

    <resultMap type="Group" id="groupResultMap" >  
        <id column="g_id" property="id"/>  
        <result column="g_name" property="name"/>  
        <result column="g_position" property="position"/>  
    </resultMap>  
    <sql id="getALl">select * from</sql>  
      
    <!-- 意图想通过获得组和组中的所有student,此处相当于one2many -->  
    <resultMap type="Group" id="getGroupAndStudents">  
        <id column="g_id" property="id"/>  
        <result column="g_name" property="name"/>  
        <result column="g_position" property="position"/>  
        <collection property="students" ofType="Student" column="group_id"><!-- 注意此处的group_id是student表的外键 -->  
            <id column="id" property="id"/>  
            <result column="name" property="name"/>  
            <result column="birth" property="birth"/>  
        </collection>  
    </resultMap>  
      
    <select id="getById" parameterType="int" resultMap="getGroupAndStudents">  
        select g.g_id,g.g_name,g.g_position,s.id,s.name,s.birth ,s.group_id  
        from g_group g  
        left join student s on g.g_id = s.group_id  
        where g.g_id = #{id}  
    </select>  
  
    <select id="getByIdResultMap" parameterType="_int" resultMap="groupResultMap">  
        select g_id ,g_name, g_position  from g_group where g_id=#{id}  
    </select>  
    <delete id="deleteById" parameterType="_int" timeout="1000">  
        delete from g_group where g_id=#{id}  
    </delete>  
    <insert id="add" parameterType="Group">  
        insert into g_group(g_id, g_name, g_position)   
        values(#{id}, #{name}, #{position})  
    </insert>  
      
</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="com.company.dao.IStudentDAO">  
      
    <!-- mybatis缓存 -->  
    <cache eviction="LRU" flushInterval="600000" size="1024" readOnly="false" />  
      
    <!-- sql标签用来定义一些可以被重用的sql语句或字段或片段等 -->  
    <sql id="studentColumns">select id,name,birth from student</sql>  
      
    <!-- 此处获得多对一的关系 ,但就单条记录而言却是一对一的关系，所以一对一的写法跟此相同-->  
    <resultMap type="Student" id="getStudentAndGroup" >  
        <id column="id" property="id"/>  
        <result column="name" property="name"/>  
        <result column="birth" property="birth"/>  
        <association property="group" column="group_id" javaType="Group">  
            <id column="g_id" property="id"/>  
            <result column="g_name" property="name"/>  
            <result column="g_position" property="position"/>  
        </association>  
    </resultMap>  
    <select id="many2one" resultMap="getStudentAndGroup" parameterType="int" >  
        select s.id,s.name,s.birth,s.group_id,g.g_id,g.g_name,g.g_position   
        from student s   
        left join g_group g on s.group_id = g.g_id  
        where s.id = #{id}  
    </select>  
      
      
    <!-- 意图是获得一个学生，并且获得该学生所属的组，跟上面的意思差不多 ,用association的select属性-->  
    <!-- 于上面的相比个人感觉上面的效率要高些，因为上面只有一条sql语句 -->  
    <resultMap type="Student" id="getStudentAndGroupUseSelectMap">  
        <id column="id" property="id"/>  
        <result column="name" property="name"/>  
        <result column="birth" property="birth"/>  
        <association property="group" column="group_id" javaType="Group" select="selectGroup" />  
    </resultMap>  
    <select id="getStudentAndGroupUseSelect" resultMap="getStudentAndGroupUseSelectMap" parameterType="int">  
        select *   
        from student   
        where id = #{id}  
    </select>  
    <select id="selectGroup" resultType="Group" parameterType="int" flushCache="false" useCache="true"><!-- 此处实用缓存 -->  
        select g_id as id, g_name as name, g_position as position   
        from g_group   
        where g_id = #{id}  
    </select>  
  
    <!-- 动态sql语句 的测试dynamic sql-->      
    <select id="getStudentBySomeCondition" parameterType="Student" resultType="Student">  
        select *  
        from student  
        <where>  
            <if test="id != null">  
                id>2  
            </if>  
            <if test="name != null">  
                and name like '%g%'  
            </if>  
        </where>  
    </select>  
      
    <!-- MyBatis调用存储过程 -->  
    <resultMap type="Student" id="studentMap">  
        <id column="id" property="id"/>  
        <result column="name" property="name"/>  
        <result column="birth" property="birth"/>  
    </resultMap>  
    <select id="getAllUser" statementType="CALLABLE" >  
        {call get_all_student(#{students ,mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=studentMap} )}  
    </select>  
      
      
    <!-- MyBatis向student表中插入一条数据 -->  
    <insert id="add" parameterType="Student" keyColumn="id">  
        <selectKey keyProperty="id" order="BEFORE" resultType="int">   
            select stu_id_sequence.nextval from dual  
        </selectKey>  
        insert into student(id,name,birth) values(#{id},#{name},#{birth})  
    </insert>  
      
    <!-- 根据id获得学生的信息 -->  
    <select id="getById" parameterType="int" resultType="Student">  
        <include refid="studentColumns"/> where id=#{id}  
    </select>  
      
    <!-- 此处的实现方法是一个分页的原型,请查看IStudentDAOImpl.java中的调用方法 -->  
    <select id="getAllStudent" resultMap="studentMap">  
        <include refid="studentColumns"/> order by id<!--此处是引用了上面预定义好的sql语句-->  
    </select>  
      
      
</mapper>