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">
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>
调用方法：
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
wala-wo
