mybatis代码配置文件记录

<?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.team.gaoguangjin.jdbc.mybatis.mapper.StudentMapper">

    <!-- resultType="com.team.gaoguangjin.jdbc.mybatis.bean.ClassRoom" 或者 resultMap="ClassRoom" -->
    <resultMap id="classRoomMap" type="com.team.gaoguangjin.jdbc.mybatis.bean.ClassRoom">
        <id property="id" column="id"/>
        <result property="roomName" column="room_name"/>
        <result property="roomSize" column="room_size"/>
    </resultMap>

    <resultMap id="studentMap" type="com.team.gaoguangjin.jdbc.mybatis.bean.Student">
        <id property="id" column="id" />
        <result property="studentName" column="student_name" />
        <result property="studentSex" column="student_sex" />
        <!-- 级联查询1 
        <association property="room" column="room_id" resultMap="classRoomMap"/>
        -->
        <!-- 级联查询 2
        <association property="room" column="room_id" javaType="com.team.gaoguangjin.jdbc.mybatis.bean.ClassRoom" >
            <id property="id" column="id"/>
            <result property="roomName" column="room_name"/>
            <result property="roomSize" column="room_size"/>
        </association>
        -->
        <!-- 联合查询查询 3-->
        <association property="room" column="room_id" javaType="ClassRoom" select="selectClassRoomById"/>
    </resultMap>

    <resultMap id="subjectMap" type="com.team.gaoguangjin.jdbc.mybatis.bean.Subject">
        <id property="id" column="subject_id"/>
        <result property="subjectName" column="subject_name" />
        <result property="subjectTeacherName" column="subject_teacher_name" />
        <!-- 级联查询 List<Student> studentList -->
        <collection property="studentList"  resultMap="studentMap"/>
    </resultMap>



    <!--多对一查询 --> 
    <select id="selectStudentById" resultMap="studentMap" parameterType="String">
             select * from gao.tcstudent t1,gao.tc_classroom t2   where t1.room_id=t2.id and t1.id=#{id}
    </select>

    <!--classRoomMap对应的是上面声明的 resultMap的值 -->
    <select id="selectClassRoomById" parameterType="String" resultMap="classRoomMap">
        select *
     from gao.tc_classroom where id=#{id}
    </select>


    <!--使用resultType是对应在configuration里面配置的 typeAlias,必须要保证实体类和数据里面字段值一样 不然查不到值  -->
    <!-- 
    <select id="selectClassRoomById" resultType="ClassRoom" parameterType="String">
        select id,room_size as roomSize,room_name as roomName from gao.tc_classroom t1 where t1.id=#{id}
    </select>
     -->

     <!-- 一对多查询 不能用select * 记住cloumn 和实体类的映射关系 -->
     <select id="selectSubjectById" resultMap="subjectMap" parameterType="String">
                select a.id as subject_id,a.subject_name,a.subject_teacher_name,student_name,student_sex,room_id,b.id as id,d.room_name
       from gao.tcsubject        a,
            gao.tcstudent        b,
            gao.tcstudentsubject c,
            gao.tc_classroom d
      where a.id = c.subject_id
        and b.id = c.student_id
        and b.room_id=d.id
        and a.id =#{id} 
    </select>


</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="com.team.gaoguangjin.jdbc.mybatis.mapper.ClassRoomMapper">
    <!-- classroom有三个字段 -->
    <sql id="columns">
     id as "id",
     room_name as "roomName",
     room_size as "roomSize"
    </sql>

    <!-- 定义column到实体的映射关系 -->
    <resultMap id="classRoomResult" type="com.team.gaoguangjin.jdbc.mybatis.bean.ClassRoom" >
        <id column="id" property="id"/>
        <result column="room_name" property="roomName" />
        <result column="room_size" property="roomSize"/>
    </resultMap>

    <!-- 第一种方式  带include 、parameterType 和resultType resulttype对应的是config里面配置的typeAlias-->
    <select id="get1" parameterType="String" resultType="ClassRoom">
        select 
    <include refid="columns"/>
     from gao.tc_classroom where id=#{id}
    </select>

    <!-- 第二种方式 没有配置column到bean的映射关系，所以room_name和 room_size的值为空-->
    <select id="get2" parameterType="String" resultType="ClassRoom">
        select *
     from gao.tc_classroom where id=#{id}
    </select>

    <!-- 第三种方式，resultMap  没有带参数 parameterType-->
    <select id="get3" resultMap="classRoomResult" >
        select *
     from gao.tc_classroom where id=#{id}
    </select>

    <!-- 插入 有时候 入参数parameterType可以不用写的哎 -->
    <insert id="insert1" >
        <selectKey keyProperty="id" resultType="java.lang.String" order="BEFORE"  statementType="STATEMENT">
            select youdao.seq_human.nextval from dual
        </selectKey>
    insert into  gao.tc_classroom 
        (id,room_name,room_size)
    values (#{id},#{roomName},#{roomSize})
    </insert>
</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="com.thinkgem.jeesite.modules.gao.dao.TestAoliaoDao">

    <sql id="testAoliaoColumns">
        a.id AS "id",
        a.card_id AS "cardId",
        a.name AS "name",
        a.sex AS "sex",
        a.birth_date AS "birthDate",
        a.create_by AS "createBy.id",
        a.create_date AS "createDate",
        a.update_by AS "updateBy.id",
        a.update_date AS "updateDate",
        a.remarks AS "remarks",
        a.del_flag AS "delFlag"
    </sql>

    <sql id="testAoliaoJoins">
    </sql>

    <select id="get" resultType="TestAoliao">
        SELECT 
            <include refid="testAoliaoColumns"/>
        FROM clear.test_aoliao a
        <include refid="testAoliaoJoins"/>
        WHERE a.id = #{id}
    </select>

    <select id="findList" resultType="TestAoliao">
        SELECT 
            <include refid="testAoliaoColumns"/>
        FROM clear.test_aoliao a
        <include refid="testAoliaoJoins"/>
        <where>
            a.del_flag = #{DEL_FLAG_NORMAL}
            <if test="name != null and name != ''">
                AND a.name LIKE 
                    <if test="dbName == 'oracle'">'%'||#{name}||'%'</if>
                    <if test="dbName == 'mssql'">'%'+#{name}+'%'</if>
                    <if test="dbName == 'mysql'">concat('%',#{name},'%')</if>
            </if>
        </where>
        <choose>
            <when test="page !=null and page.orderBy != null and page.orderBy != ''">
                ORDER BY ${page.orderBy}
            </when>
            <otherwise>
                ORDER BY a.update_date DESC
            </otherwise>
        </choose>
    </select>

    <select id="findAllList" resultType="TestAoliao">
        SELECT 
            <include refid="testAoliaoColumns"/>
        FROM clear.test_aoliao a
        <include refid="testAoliaoJoins"/>
        <where>
            a.del_flag = #{DEL_FLAG_NORMAL}
        </where>        
        <choose>
            <when test="page !=null and page.orderBy != null and page.orderBy != ''">
                ORDER BY ${page.orderBy}
            </when>
            <otherwise>
                ORDER BY a.update_date DESC
            </otherwise>
        </choose>
    </select>

    <insert id="insert">
        INSERT INTO clear.test_aoliao(
            id,
            card_id,
            name,
            sex,
            birth_date,
            create_by,
            create_date,
            update_by,
            update_date,
            remarks,
            del_flag
        ) VALUES (
            #{id},
            #{cardId},
            #{name},
            #{sex},
            #{birthDate},
            #{createBy.id},
            #{createDate},
            #{updateBy.id},
            #{updateDate},
            #{remarks},
            #{delFlag}
        )
    </insert>

    <update id="update">
        UPDATE clear.test_aoliao SET    
            card_id = #{cardId},
            name = #{name},
            sex = #{sex},
            birth_date = #{birthDate},
            update_by = #{updateBy.id},
            update_date = #{updateDate},
            remarks = #{remarks}
        WHERE id = #{id}
    </update>

    <update id="delete">
            DELETE FROM clear.test_aoliao
        WHERE id = #{id}
    </update>

</mapper>