Mybatis高级应用

          Mybatis是一个半自动的框架。相对于hibernate全自动模式，mybatis为开发人员提供了更加灵活的对sql语句操作的控制能力，有利于dba对相关的sql操作进行优化，同时也方便开发者构建复杂的sql操作。以下是Mybatis的相关高级应用，以供参考。

         Mybatis的官方文档：http://mybatis.github.io/mybatis-3/zh/index.html

• 通过配置类进行构建SqlSessionFactory

             Mybatis允许通过xml或配置类构建SqlSessionFactory

        DataSource dataSource = BlogDataSourceFactory.getBlogDataSource();        TransactionFactory transactionFactory = new JdbcTransactionFactory();        Environment environment = new Environment("development", transactionFactory, dataSource);        Configuration configuration = new Configuration(environment);        configuration.addMapper(BlogMapper.class);        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);

              通过SqlSessionFactory获取SqlSession

        SqlSession session = sqlSessionFactory.openSession();        try {          BlogMapper mapper = session.getMapper(BlogMapper.class);          Blog blog = mapper.selectBlog(101);        } finally {          session.close();        }

• forEach应用

             forEach按照官方支持对单一数组和集合进行遍历，以下是对单一数组进行遍历的示例，集合参数其实是类似的

<delete id="deleteFile" parameterType="HashMap">deleteFrom tbl_FileWhere themeKey = #{themeKey}<trim suffixOverrides="and">  <if test="arrFileUrl != null and arrFileUrl != '' ">      And fileUrl in  <foreach item="item" index="index" collection="arrFileUrl"      open="(" separator="," close=")">        #{item}  </foreach>        </if></trim></delete>

              调用方法：

       HashMap<String, Object> argMap = new HashMap<String, Object>();              argMap.put("themeKey", themeKey);        /*arrFileUrl 是一个字符串数组*/             argMap.put("arrFileUrl", arrFileUrl);       deleteFile(HashMap<String, Object> argMap);

              foreach支持遍历list,map,array 三种类型，因此利用foreach以上特性实现批量插入数据功能。

    <insert id="addTrainRecordBatch" useGeneratedKeys="true" parameterType="java.util.List">          <selectKey resultType="long" keyProperty="id" order="AFTER">              SELECT              LAST_INSERT_ID()          </selectKey>          insert into t_train_record (add_time,emp_id,activity_id,flag)           values          <foreach collection="list" item="item" index="index" separator="," >              (#{item.addTime},#{item.empId},#{item.activityId},#{item.flag})          </foreach>      </insert>  

• collection应用

<resultMap id="Guide" type="Guide"><id column="spaguideKey" property="guideKey"/><result column="GUIDENAME" property="guideName"/><result column="GRADE" property="grade"/><result column="gradeName" property="gradeName"/><result column="SUBJECT" property="subject"/><result column="subjectName"         property="subjectName"/><result column="CREATECODE"         property="createCode"/><result column="realName" property="realName"/><result column="CREATETIME"         property="createTime"/><result column="ISVALID" property="isValid"/><result column="ISREVIEW" property="isReview"/><result column="CONTENT" property="content"/>                <collection  property="guideVideoList"  ofType="GuideVideo"><id column="spavideokey" property="videoKey"/><result column="videoName" property="videoName"/><result column="videoFileUrl" property="fileUrl"/>                        </collection>                 <collection  property="guideArchiveList" ofType="GuideArchive"><id column="SPAARCHIVESKEY" property="archiveKey"/><result column="archiveName" property="archiveName"/><result column="archiveFileUrl" property="fileUrl"/>                        </collection></resultMap>    <select id="getGuide" parameterType="Guide" resultMap="Guide">        Select             sg.SPAGUIDEKEY,            GUIDENAME,            CREATETIME,            CONTENT,            grade,            gradeName,            subject,            subjectName,            CREATECODE,            realName,                    spavideokey,            videoName,            videoFileName,            videoFileUrl,                      SPAARCHIVESKEY,            ARCHIVENAME,            archiveFileName,            archiveFileUrl         from vw_spa_guide sg        left join vw_spa_guide_video sgv on sgv.spaguidekey = sg.spaguidekey        left join vw_spa_guide_archive sga on sga.spaguidekey = sg.spaguidekey    </select>

• Mybatis 嵌套查询

    <resultMap type="User" id="userResultMap">        <id property="id" column="user_id"  />        <result property="userName" column="user_userName"  />        <result property="userAge" column="user_userAge"  />        <result property="userAddress" column="user_userAddress"  />    </resultMap>        <resultMap id="articleResultMap" type="Article">        <id property="id" column="article_id" />        <result property="title" column="article_title" />        <result property="content" column="article_content" />        <association property="user" javaType="User" resultMap="userResultMap"/>      </resultMap>        <select id="getUserArticles" parameterType="int" resultMap="articleResultMap">       select user.id user_id,user.userName user_userName,user.userAddress user_userAddress,       article.id article_id,article.title article_title,article.content article_content        from user,article        where user.id=article.userid and user.id=#{id}    </select>

        借用别人的例子来说明。

• mybatis 自定义主键
  

    <insert id="insertTest" parameterType="Test">         <selectKey  keyProperty="testKey" order="BEFORE" resultType="String">              SELECT  SEQ_TST_TEST.nextval               FROM DUAL         </selectKey>                     insert into TST_TEST(            TESTKEY,            TESTNAME        )values(            #{testKey},            #{testName}        )    </insert>

         mybatis 相对于oracle的自增长先进行查询读取下一个主键,oracle不支持useGeneratedKeys，然后再进行插入操作。相对于其他数据库可以采用如下方式

        <insert id="insertTest" parameterType="Test" useGeneratedKeys="true" keyProperty="testKey">              insert into Test(testKey, testName)              values(#{testKey},#{testName})          </insert>  

• mybatis复用语句块

        <!--定义可重用的SQL代码段-->          <sql id="multiplexSql">testKey, testName</sql>                    <select id="getTest" parameterType="int" resultType="hashmap">              select                 <include refid="multiplexSql"/>             from Blog             where id = #{testKey}          </select>  

• Mybatis执行函数，返回结果集

<select id="getSelfStatisticData" parameterType="HashMap" statementType="CALLABLE" >     {#{result,mode=OUT,jdbcType=CURSOR, resultMap=SelfStatisticData} = call PKG_RRT_SelfStatics.Fn_GetSelfStatData(#{userCode,jdbcType=VARCHAR,mode=IN})}    </select> 

                 Java调用的代码

        public interface SelfStatisticDataDao {     public List<SelfStatisticData> getSelfStatisticData(Map<String, Object> statMap);        }

                 statMap 中的键值对对应着Fn_GetSelfStatData()函数的参数，键名与参数名保持完全一致，区分大小写。
                 SelfStatisticData定义的实体保持与结果集的字段一致。

• Mybatis执行没有返回值的存储过程

    <select id="insertGuideIntegral" parameterType="HashMap" statementType="CALLABLE" >         {             call PKG_Center_Integral_guide.Pro_SyncGuideIntegral(                 #{userCode,jdbcType=VARCHAR,mode=IN},                 #{integralKey,jdbcType=VARCHAR,mode=IN},                 #{gradeKey,jdbcType=VARCHAR,mode=IN},                 #{subjectKey,jdbcType=VARCHAR,mode=IN}             )         }    </select>

•  Mybatis执行带有返回两个游标结果集和输出参数

     <resultMap type="IntegralResult" id="integralResult">           <result column="integralKey" property="integralKey"/>           <result column="integralName" property="integralName"/>       </resultMap>         <resultMap type="GuideIntegralResult" id="guideIntegralResult">           <result column="guideIntegralKey" property="guideIntegralKey"/>           <result column="guideIntegralName" property="guideIntegralName"/>       </resultMap>            <select id="get" parameterType="java.util.Map" statementType="CALLABLE"  resultMap="integralResult, guideIntegralResult">           {             call PKG_Center_Integral_guide.Pro_GuideIntegral(                   #{userCode,jdbcType=VARCHAR,mode=IN},                 #{subjectKey,jdbcType=VARCHAR,mode=IN},                 #{userName, mode=OUT, jdbcType=String}             )         }       </select> 

• Mybatis 支持结构体类型

      #{middleInitial, mode=OUT, jdbcType=STRUCT, jdbcTypeName=MY_TYPE, resultMap=departmentResultMap}

            MY_TYPE为数据库中自定义的结构体

• 定义输入输出小数点
  

       #{height,javaType=double,jdbcType=NUMERIC,numericScale=2}  

• 使用association进行复杂映射

        <resultMap type="Blog" id="Blog_result">                    <id column="id" property="id" />              <result column="title" property="title"/>                        <!-- 映射关联的对象 -->              <association property="author" javaType="Author">                  <id column="author_id" property="id"/>                  <result column="username" property="username"/>                  <result column="password" property="password"/>                  <result column="email" property="email"/>                  <result column="bio" property="bio"/>              </association>                     </resultMap>          <select id="selectBlog_by_id" parameterType="int" resultMap="Blog_result">              select                  b.id,                  b.title,                  b.author_id,                  a.id,                  a.username,                  a.password,                  a.email,                  a.bio              from  Blog b             left join Author a  on  b.author_id = a.id              where  b.id = #{id}          </select>

• 定义输入输出和指定游标结果集

    #{        department,          mode=OUT,          jdbcType=CURSOR,          javaType=ResultSet,          resultMap=departmentResultMap    }  

             mode属性允许你指定IN，OUT或INOUT参数。如果mode为OUT（或INOUT），而且jdbcType为CURSOR（也就是Oracle的REFCURSOR），你必须指定一个resultMap来映射结果集到参数类型。

• 参考资料

             http://blog.csdn.net/rootsuper/article/details/8542236