(1) 输入参数为单个值
- <delete id="com.fashionfree.stat.accesslog.deleteMemberAccessLogsBefore"
- parameterClass="long">
- delete from
- MemberAccessLog
- where
- accessTimestamp = #value#
- </delete>
(2) 输入参数为一个对象
- <insert id="com.fashionfree.stat.accesslog.MemberAccessLog.insert"
- parameterClass="com.fashionfree.stat.accesslog.model.MemberAccessLog>
- insert into MemberAccessLog
- (
- accessLogId, memberId, clientIP,
- httpMethod, actionId, requestURL,
- accessTimestamp, extend1, extend2,
- extend3
- )
- values
- (
- #accessLogId#, #memberId#,
- #clientIP#, #httpMethod#,
- #actionId#, #requestURL#,
- #accessTimestamp#, #extend1#,
- #extend2#, #extend3#
- )
- </insert>
(3) 输入参数为一个java.util.HashMap
- <select id="com.fashionfree.stat.accesslog.selectActionIdAndActionNumber"
- parameterClass="hashMap"
- resultMap="getActionIdAndActionNumber">
- select
- actionId, count(*) as count
- from
- MemberAccessLog
- where
- memberId = #memberId#
- and accessTimestamp > #start#
- and accessTimestamp <= #end#
- group by actionId
- </select>
(4) 输入参数中含有数组
- <insert id="updateStatusBatch" parameterClass="hashMap">
- update
- Question
- set
- status = #status#
- <dynamic prepend="where questionId in">
- <isNotNull property="actionIds">
- <iterate property="actionIds" open="(" close=")" conjunction=",">
- #actionIds[]#
- </iterate>
- </isNotNull>
- </dynamic>
- </insert>
说明:actionIds为传入的数组的名字;
使用dynamic标签避免数组为空时导致sql语句语法出错;
使用isNotNull标签避免数组为null时ibatis解析出错
(5)传递参数只含有一个数组
- <select id="com.fashionfree.stat.accesslog.model.StatMemberAction.selectActionIdsOfModule"
- resultClass="hashMap">
- select
- moduleId, actionId
- from
- StatMemberAction
- <dynamic prepend="where moduleId in">
- <iterate open="(" close=")" conjunction=",">
- #[]#
- </iterate>
- </dynamic>
- order by
- moduleId
- </select>
说明:注意select的标签中没有parameterClass一项
另:这里也可以把数组放进一个hashMap中,但增加额外开销,不建议使用
(6)让ibatis把参数直接解析成字符串
- <select id="com.fashionfree.stat.accesslog.selectSumDistinctCountOfAccessMemberNum"
- parameterClass="hashMap" resultClass="int">
- select
- count(distinct memberId)
- from
- MemberAccessLog
- where
- accessTimestamp >= #start#
- and accessTimestamp < #end#
- and actionId in $actionIdString$
- </select>
说明:使用这种方法存在sql注入的风险,不推荐使用
(7)分页查询 (pagedQuery)
- <select id="com.fashionfree.stat.accesslog.selectMemberAccessLogBy"
- parameterClass="hashMap" resultMap="MemberAccessLogMap">
- <include refid="selectAllSql"/>
- <include refid="whereSql"/>
- <include refid="pageSql"/>
- </select>
- <select id="com.fashionfree.stat.accesslog.selectMemberAccessLogBy.Count"
- parameterClass="hashMap" resultClass="int">
- <include refid="countSql"/>
- <include refid="whereSql"/>
- </select>
- <sql id="selectAllSql">
- select
- accessLogId, memberId, clientIP,
- httpMethod, actionId, requestURL,
- accessTimestamp, extend1, extend2,
- extend3
- from
- MemberAccessLog
- </sql>
- <sql id="whereSql">
- accessTimestamp <= #accessTimestamp#
- </sql>
- <sql id="countSql">
- select
- count(*)
- from
- MemberAccessLog
- </sql>
- <sql id="pageSql">
- <dynamic>
- <isNotNull property="startIndex">
- <isNotNull property="pageSize">
- limit #startIndex# , #pageSize#
- </isNotNull>
- </isNotNull>
- </dynamic>
- </sql>
说明:本例中,代码应为:
HashMap hashMap = new HashMap();
hashMap.put(“accessTimestamp”, someValue);
pagedQuery(“com.fashionfree.stat.accesslog.selectMemberAccessLogBy”, hashMap);
pagedQuery方法首先去查找名为com.fashionfree.stat.accesslog.selectMemberAccessLogBy.Count 的mapped statement来进行sql查询,从而得到com.fashionfree.stat.accesslog.selectMemberAccessLogBy查询的记录个数,
再进行所需的paged sql查询(com.fashionfree.stat.accesslog.selectMemberAccessLogBy),具体过程参见utils类中的相关代码
(8)sql语句中含有大于号>、小于号<
1. 将大于号、小于号写为: > < 如:
- <delete id="com.fashionfree.stat.accesslog.deleteMemberAccessLogsBefore" parameterClass="long">
- delete from
- MemberAccessLog
- where
- accessTimestamp <= #value#
- </delete>
2. 将特殊字符放在xml的CDATA区内:
- <delete id="com.fashionfree.stat.accesslog.deleteMemberAccessLogsBefore" parameterClass="long">
- <![CDATA[
- delete from
- MemberAccessLog
- where
- accessTimestamp <= #value#
- ]]>
- </delete>
推荐使用第一种方式,写为< 和 > (XML不对CDATA里的内容进行解析,因此如果CDATA中含有dynamic标签,将不起作用)
(9)include和sql标签
将常用的sql语句整理在一起,便于共用:
- <sql id="selectBasicSql">
- select
- samplingTimestamp,onlineNum,year,
- month,week,day,hour
- from
- OnlineMemberNum
- </sql>
- <sql id="whereSqlBefore">
- where samplingTimestamp <= #samplingTimestamp#
- </sql>
- <select id="com.fashionfree.accesslog.selectOnlineMemberNumsBeforeSamplingTimestamp" parameterClass="hashmap" resultClass="OnlineMemberNum">
- <include refid="selectBasicSql" />
- <include refid="whereSqlBefore" />
- </select>
注意:sql标签只能用于被引用,不能当作mapped statement。如上例中有名为selectBasicSql的sql元素,试图使用其作为sql语句执行是错误的:
sqlMapClient.queryForList(“selectBasicSql”); ×
(10)随机选取记录
- <sql id=”randomSql”>
- ORDER BY rand() LIMIT #number#
- </sql>
从数据库中随机选取number条记录(只适用于MySQL)
(11)将SQL GROUP BY分组中的字段拼接
- <sql id=”selectGroupBy>
- SELECT
- a.answererCategoryId, a.answererId, a.answererName,
- a.questionCategoryId, a.score, a.answeredNum,
- a.correctNum, a.answerSeconds, a.createdTimestamp,
- a.lastQuestionApprovedTimestamp, a.lastModified, GROUP_CONCAT(q.categoryName) as categoryName
- FROM
- AnswererCategory a, QuestionCategory q
- WHERE a.questionCategoryId = q.questionCategoryId
- GROUP BY a.answererId
- ORDER BY a.answererCategoryId
- </sql>
注:SQL中使用了MySQL的GROUP_CONCAT函数
(12) 按照IN里面的顺序进行排序
①MySQL:
- <sql id=”groupByInArea”>
- select
- moduleId, moduleName,
- status, lastModifierId, lastModifiedName,
- lastModified
- from
- StatModule
- where
- moduleId in (3, 5, 1)
- order by
- instr(',3,5,1,' , ','+ltrim(moduleId)+',')
- </sql>
②SQLSERVER:
- <sql id=”groupByInArea”>
- select
- moduleId, moduleName,
- status, lastModifierId, lastModifiedName,
- lastModified
- from
- StatModule
- where
- moduleId in (3, 5, 1)
- order by
- charindex(','+ltrim(moduleId)+',' , ',3,5,1,')
- </sql>
说明:查询结果将按照moduleId在in列表中的顺序(3, 5, 1)来返回
MySQL : instr(str, substr)
SQLSERVER: charindex(substr, str)
返回字符串str 中子字符串的第一个出现位置
ltrim(str)
返回字符串str, 其引导(左面的)空格字符被删除
(13) resultMap
resultMap负责将SQL查询结果集的列值映射成Java Bean的属性值。
- <resultMap class="java.util.HashMap" id="getActionIdAndActionNumber">
- <result column="actionId" property="actionId" jdbcType="BIGINT" javaType="long"/>
- <result column="count" property="count" jdbcType="INT" javaType="int"/>
- </resultMap>
使用resultMap称为显式结果映射,与之对应的是resultClass(内联结果映射),使用resultClass的最大好处便是简单、方便,不需显示指定结果,由iBATIS根据反射来确定自行决定。而resultMap则可以通过指定jdbcType和javaType,提供更严格的配置认证。
(14) typeAlias
- <typeAlias alias="MemberOnlineDuration" type="com.fashionfree.stat.accesslog.model.MemberOnlineDuration" />
- <typeAlias>允许你定义别名,避免重复输入过长的名字。
(15) remap
- <select id="testForRemap" parameterClass="hashMap" resultClass="hashMap" remapResults="true">
- select
- userId
- <isEqual property="tag" compareValue="1">
- , userName
- </isEqual>
- <isEqual property="tag" compareValue="2">
- , userPassword
- </isEqual>
- from
- UserInfo
- </select>
此例中,根据参数tag值的不同,会获得不同的结果集,如果没有remapResults="true"属性,iBatis会将第一次查询时的结果集缓存,下次再执行时(必须还是该进程中)不会再执行结果集映射,而是会使用缓存的结果集。
因此,如果上面的例子中remapResult为默认的false属性,而有一段程序这样书写:
- HashMap<String, Integer> hashMap = new HashMap<String, Integer>();
- hashMap.put("tag", 1);
- sqlClient.queryForList("testForRemap", hashMap);
- hashMap.put("tag", 2);
- sqlClient.queryForList("testForRemap", hashMap);
则程序会在执行最后一句的query查询时报错,原因就是iBATIS使用了第一次查询时的结果集,而前后两次的结果集是不同的:(userId, userName)和(userId, userPassword),所以导致出错。如果使用了remapResults="true"这一属性,iBATIS会在每次执行查询时都执行结果集映射,从而避免错误的发生(此时会有较大的开销)。
(16) dynamic标签的prepend
dynamic标签的prepend属性作为前缀添加到结果内容前面,当标签的结果内容为空时,prepend属性将不起作用。
当dynamic标签中存在prepend属性时,将会把其嵌套子标签的第一个prepend属性忽略。例如:
- <sql id="whereSql">
- <dynamic prepend="where ">
- <isNotNull property="userId" prepend="BOGUS">
- userId = #userId#
- </isNotNull>
- <isNotEmpty property="userName" prepend="and ">
- userName = #userName#
- </isNotEmpty>
- </dynamic>
- </sql>
此例中,dynamic标签中含有两个子标签<isNotNull>和<isNotEmpty>。根据前面叙述的原则,如果<isNotNull>标签中没有prepend="BOGUS" 这一假的属性来让dynamic去掉的话,<isNotEmpty>标签中的and就会被忽略,会造成sql语法错误。
注意:当dynamic标签没有prepend属性时,不会自动忽略其子标签的第一个prepend属性。