Mybatis高级应用

来源：互联网发布：淘宝怎样设置下架时间编辑：程序博客网时间：2024/05/18 21:06

转自：http://blog.csdn.net/zouqingfang/article/details/44782999

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

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

通过配置类进行构建SqlSessionFactory

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

[java] view plain copy
 print?
        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

[java] view plain copy
 print?
        SqlSession session = sqlSessionFactory.openSession();  
        try {  
          BlogMapper mapper = session.getMapper(BlogMapper.class);  
          Blog blog = mapper.selectBlog(101);  
        } finally {  
          session.close();  
        }  

forEach应用

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

[html] view plain copy
 print?
<delete id="deleteFile" parameterType="HashMap">  
    delete  
    From tbl_File  
    Where 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>  

调用方法：

[java] view plain copy
 print?
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以上特性实现批量插入数据功能。

[html] view plain copy
 print?
    <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应用

[html] view plain copy
 print?
    <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 嵌套查询

[html] view plain copy
 print?
<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 自定义主键

[html] view plain copy
 print?
<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，然后再进行插入操作。相对于其他数据库可以采用如下方式

[html] view plain copy
 print?
<insert id="insertTest" parameterType="Test" useGeneratedKeys="true" keyProperty="testKey">    
    insert into Test(testKey, testName)    
    values(#{testKey},#{testName})    
</insert>    

mybatis复用语句块

[html] view plain copy
 print?
<!--定义可重用的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执行函数，返回结果集

[html] view plain copy
 print?
<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调用的代码

[plain] view plain copy
 print?
public interface SelfStatisticDataDao {  
blic List<SelfStatisticData> getSelfStatisticData(Map<String, Object> statMap);  
}  

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

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

[html] view plain copy
 print?
<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执行带有返回两个游标结果集和输出参数

[html] view plain copy
 print?
<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 支持结构体类型

[html] view plain copy
 print?
#{middleInitial, mode=OUT, jdbcType=STRUCT, jdbcTypeName=MY_TYPE, resultMap=departmentResultMap}  

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

定义输入输出小数点

[plain] view plain copy
 print?
#{height,javaType=double,jdbcType=NUMERIC,numericScale=2}    

使用association进行复杂映射

[html] view plain copy
 print?
        <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>  

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

[html] view plain copy
 print?
#{  
    department,    
    mode=OUT,    
    jdbcType=CURSOR,    
    javaType=ResultSet,    
    resultMap=departmentResultMap  
}    

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

阅读全文

0 0