当前位置： 代码迷 >> SQL >> （转）ibatis惯用16条SQL语句
  详细解决方案

（转）ibatis惯用16条SQL语句

热度：76   发布时间：2016-05-05 11:53:03.0
（转）ibatis常用16条SQL语句

(1) 输入参数为单个值

Xml代码??收藏代码
  1. <delete?id="com.fashionfree.stat.accesslog.deleteMemberAccessLogsBefore"???
  2. parameterClass="long">???
  3. delete?from???
  4. MemberAccessLog???
  5. where???
  6. accessTimestamp?=?#value#???
  7. </delete>???

?
(2) 输入参数为一个对象

?

Xml代码??收藏代码
  1. <insert?id="com.fashionfree.stat.accesslog.MemberAccessLog.insert"???
  2. parameterClass="com.fashionfree.stat.accesslog.model.MemberAccessLog>???
  3. insert?into?MemberAccessLog???
  4. (???
  5. accessLogId,?memberId,?clientIP,???
  6. httpMethod,?actionId,?requestURL,???
  7. accessTimestamp,?extend1,?extend2,???
  8. extend3???
  9. )???
  10. values???
  11. (???
  12. #accessLogId#,?#memberId#,???
  13. #clientIP#,?#httpMethod#,???
  14. #actionId#,?#requestURL#,???
  15. #accessTimestamp#,?#extend1#,???
  16. #extend2#,?#extend3#???
  17. )???
  18. </insert>???

?

(3) 输入参数为一个java.util.HashMap?

Xml代码??收藏代码
  1. <select?id="com.fashionfree.stat.accesslog.selectActionIdAndActionNumber"???
  2. parameterClass="hashMap"???
  3. resultMap="getActionIdAndActionNumber">???
  4. select???
  5. actionId,?count(*)?as?count???
  6. from???
  7. MemberAccessLog???
  8. where???
  9. memberId?=?#memberId#???
  10. and?accessTimestamp?&gt;?#start#???
  11. and?accessTimestamp?&lt;=?#end#???
  12. group?by?actionId???
  13. </select>??

?

? (4) 输入参数中含有数组

???

Xml代码??收藏代码
  1. <insert?id="updateStatusBatch"?parameterClass="hashMap">???
  2. update???
  3. Question???
  4. set???
  5. status?=?#status#???
  6. <dynamic?prepend="where?questionId?in">???
  7. <isNotNull?property="actionIds">???
  8. <iterate?property="actionIds"?open="("?close=")"?conjunction=",">???
  9. #actionIds[]#???
  10. </iterate>???
  11. </isNotNull>???
  12. </dynamic>???
  13. </insert>???

?? 说明：actionIds为传入的数组的名字；?
???使用dynamic标签避免数组为空时导致sql语句语法出错；?
?? 使用isNotNull标签避免数组为null时ibatis解析出错

?

?? (5)传递参数只含有一个数组?
??

Xml代码??收藏代码
  1. <select?id="com.fashionfree.stat.accesslog.model.StatMemberAction.selectActionIdsOfModule"???
  2. resultClass="hashMap">???
  3. select???
  4. moduleId,?actionId???
  5. from???
  6. StatMemberAction???
  7. <dynamic?prepend="where?moduleId?in">???
  8. <iterate?open="("?close=")"?conjunction=",">???
  9. #[]#???
  10. </iterate>???
  11. </dynamic>???
  12. order?by???
  13. moduleId???
  14. </select>???

??? 说明：注意select的标签中没有parameterClass一项?
?????? 另：这里也可以把数组放进一个hashMap中，但增加额外开销，不建议使用

?

???(6)让ibatis把参数直接解析成字符串?
??

Xml代码??收藏代码
  1. <select?id="com.fashionfree.stat.accesslog.selectSumDistinctCountOfAccessMemberNum"???
  2. parameterClass="hashMap"?resultClass="int">???
  3. select???
  4. count(distinct?memberId)???
  5. from???
  6. MemberAccessLog???
  7. where???
  8. accessTimestamp?&gt;=?#start#???
  9. and?accessTimestamp?&lt;?#end#???
  10. and?actionId?in?$actionIdString$???
  11. </select>??

??? 说明：使用这种方法存在sql注入的风险，不推荐使用

?

????(7)分页查询 (pagedQuery)

???

Java代码??收藏代码
  1. <select?id="com.fashionfree.stat.accesslog.selectMemberAccessLogBy"???
  2. parameterClass="hashMap"?resultMap="MemberAccessLogMap">???
  3. <include?refid="selectAllSql"/>???
  4. <include?refid="whereSql"/>???
  5. <include?refid="pageSql"/>???
  6. </select>???
  7. <select?id="com.fashionfree.stat.accesslog.selectMemberAccessLogBy.Count"???
  8. parameterClass="hashMap"?resultClass="int">???
  9. <include?refid="countSql"/>???
  10. <include?refid="whereSql"/>???
  11. </select>???
  12. <sql?id="selectAllSql">???
  13. select???
  14. accessLogId,?memberId,?clientIP,???
  15. httpMethod,?actionId,?requestURL,???
  16. accessTimestamp,?extend1,?extend2,???
  17. extend3???
  18. from???
  19. MemberAccessLog???
  20. </sql>???
  21. <sql?id="whereSql">???
  22. accessTimestamp?&lt;=?#accessTimestamp#???
  23. </sql>???
  24. <sql?id="countSql">???
  25. select???
  26. count(*)???
  27. from???
  28. MemberAccessLog???
  29. </sql>???
  30. <sql?id="pageSql">???
  31. <dynamic>???
  32. <isNotNull?property="startIndex">???
  33. <isNotNull?property="pageSize">???
  34. limit?#startIndex#?,?#pageSize#???
  35. </isNotNull>???
  36. </isNotNull>???
  37. </dynamic>???
  38. </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. 将大于号、小于号写为： &gt; &lt; 如：?

Xml代码??收藏代码
  1. <delete?id="com.fashionfree.stat.accesslog.deleteMemberAccessLogsBefore"?parameterClass="long">???
  2. delete?from???
  3. MemberAccessLog???
  4. where???
  5. accessTimestamp?&lt;=?#value#???
  6. </delete>???

?

?? ?2. 将特殊字符放在xml的CDATA区内：?

Xml代码??收藏代码
  1. <delete?id="com.fashionfree.stat.accesslog.deleteMemberAccessLogsBefore"?parameterClass="long">???
  2. <![CDATA[??
  3. delete?from??
  4. MemberAccessLog??
  5. where??
  6. accessTimestamp?<=?#value#??
  7. ]]>???
  8. </delete>???

?? 推荐使用第一种方式，写为&lt; 和 &gt; (XML不对CDATA里的内容进行解析，因此如果CDATA中含有dynamic标签，将不起作用)?

(9)include和sql标签?
?? 将常用的sql语句整理在一起，便于共用：?

Xml代码??收藏代码
  1. <sql?id="selectBasicSql">???
  2. select???
  3. samplingTimestamp,onlineNum,year,???
  4. month,week,day,hour???
  5. from???
  6. OnlineMemberNum???
  7. </sql>???
  8. <sql?id="whereSqlBefore">???
  9. where?samplingTimestamp?&lt;=?#samplingTimestamp#???
  10. </sql>???
  11. <select?id="com.fashionfree.accesslog.selectOnlineMemberNumsBeforeSamplingTimestamp"?parameterClass="hashmap"?resultClass="OnlineMemberNum">???
  12. <include?refid="selectBasicSql"?/>???
  13. <include?refid="whereSqlBefore"?/>???
  14. </select>???

? ? 注意：sql标签只能用于被引用，不能当作mapped statement。如上例中有名为selectBasicSql的sql元素，试图使用其作为sql语句执行是错误的:?
??? sqlMapClient.queryForList(“selectBasicSql”); ×

(10)随机选取记录

Xml代码??收藏代码
  1. <sql?id=”randomSql”>???
  2. ORDER?BY?rand()?LIMIT?#number#???
  3. </sql>???

????从数据库中随机选取number条记录（只适用于MySQL）

?

(11)将SQL GROUP BY分组中的字段拼接

Xml代码??收藏代码
  1. <sql?id=”selectGroupBy>???
  2. SELECT???
  3. a.answererCategoryId,?a.answererId,?a.answererName,???
  4. a.questionCategoryId,?a.score,?a.answeredNum,???
  5. a.correctNum,?a.answerSeconds,?a.createdTimestamp,???
  6. a.lastQuestionApprovedTimestamp,?a.lastModified,?GROUP_CONCAT(q.categoryName)?as?categoryName???
  7. FROM???
  8. AnswererCategory?a,?QuestionCategory?q???
  9. WHERE?a.questionCategoryId?=?q.questionCategoryId???
  10. GROUP?BY?a.answererId???
  11. ORDER?BY?a.answererCategoryId???
  12. </sql>??

??? 注：SQL中使用了MySQL的GROUP_CONCAT函数

(12) 按照IN里面的顺序进行排序

????①MySQL:?

Xml代码??收藏代码
  1. <sql?id=”groupByInArea”>???
  2. select???
  3. moduleId,?moduleName,???
  4. status,?lastModifierId,?lastModifiedName,???
  5. lastModified???
  6. from???
  7. StatModule???
  8. where???
  9. moduleId?in?(3,?5,?1)???
  10. order?by???
  11. instr(',3,5,1,'?,?','+ltrim(moduleId)+',')???
  12. </sql>???

?? ?

②SQLSERVER:

Xml代码??收藏代码
  1. <sql?id=”groupByInArea”>???
  2. select???
  3. moduleId,?moduleName,???
  4. status,?lastModifierId,?lastModifiedName,???
  5. lastModified???
  6. from???
  7. StatModule???
  8. where???
  9. moduleId?in?(3,?5,?1)???
  10. order?by???
  11. charindex(','+ltrim(moduleId)+','?,?',3,5,1,')???
  12. </sql>??

??? 说明：查询结果将按照moduleId在in列表中的顺序(3, 5, 1)来返回?
??? MySQL : instr(str, substr)?
??? SQLSERVER: charindex(substr, str)?
????返回字符串str 中子字符串的第一个出现位置?
??? ltrim(str)?
????返回字符串str, 其引导(左面的)空格字符被删除

(13) resultMap?
????resultMap负责将SQL查询结果集的列值映射成Java Bean的属性值。

Xml代码??收藏代码
  1. <resultMap?class="java.util.HashMap"?id="getActionIdAndActionNumber">???
  2. <result?column="actionId"?property="actionId"?jdbcType="BIGINT"?javaType="long"/>???
  3. <result?column="count"?property="count"?jdbcType="INT"?javaType="int"/>???
  4. </resultMap>???

?? 使用resultMap称为显式结果映射，与之对应的是resultClass(内联结果映射)，使用resultClass的最大好处便是简单、方便，不需显示指定结果，由iBATIS根据反射来确定自行决定。而resultMap则可以通过指定jdbcType和javaType，提供更严格的配置认证。


(14) typeAlias

Xml代码??收藏代码
  1. <typeAlias?alias="MemberOnlineDuration"?type="com.fashionfree.stat.accesslog.model.MemberOnlineDuration"?/>???
  2. <typeAlias>允许你定义别名，避免重复输入过长的名字。??

?

(15) remap

Xml代码??收藏代码
  1. <select?id="testForRemap"?parameterClass="hashMap"?resultClass="hashMap"?remapResults="true">???
  2. select???
  3. userId???
  4. <isEqual?property="tag"?compareValue="1">???
  5. ,?userName???
  6. </isEqual>???
  7. <isEqual?property="tag"?compareValue="2">???
  8. ,?userPassword???
  9. </isEqual>???
  10. from???
  11. UserInfo???
  12. </select>???

??此例中，根据参数tag值的不同，会获得不同的结果集，如果没有remapResults="true"属性，iBatis会将第一次查询时的结果集缓存，下次再执行时（必须还是该进程中）不会再执行结果集映射，而是会使用缓存的结果集。?
因此，如果上面的例子中remapResult为默认的false属性，而有一段程序这样书写：?

Java代码??收藏代码
  1. HashMap<String,?Integer>?hashMap?=?new?HashMap<String,?Integer>();???
  2. hashMap.put("tag",?1);???
  3. sqlClient.queryForList("testForRemap",?hashMap);???
  4. hashMap.put("tag",?2);???
  5. sqlClient.queryForList("testForRemap",?hashMap);???

?则程序会在执行最后一句的query查询时报错，原因就是iBATIS使用了第一次查询时的结果集，而前后两次的结果集是不同的:(userId, userName)和(userId, userPassword)，所以导致出错。如果使用了remapResults="true"这一属性，iBATIS会在每次执行查询时都执行结果集映射，从而避免错误的发生（此时会有较大的开销）。?

(16) dynamic标签的prepend

??dynamic标签的prepend属性作为前缀添加到结果内容前面，当标签的结果内容为空时，prepend属性将不起作用。?
当dynamic标签中存在prepend属性时，将会把其嵌套子标签的第一个prepend属性忽略。例如：

Xml代码??收藏代码
  1. <sql?id="whereSql">???
  2. <dynamic?prepend="where?">???
  3. <isNotNull?property="userId"?prepend="BOGUS">???
  4. userId?=?#userId#???
  5. </isNotNull>???
  6. <isNotEmpty?property="userName"?prepend="and?">???
  7. userName?=?#userName#???
  8. </isNotEmpty>???
  9. </dynamic>???
  10. </sql>??

??

此例中，dynamic标签中含有两个子标签<isNotNull>和<isNotEmpty>。根据前面叙述的原则，如果<isNotNull>标签中没有prepend="BOGUS" 这一假的属性来让dynamic去掉的话，<isNotEmpty>标签中的and就会被忽略，会造成sql语法错误。?
?? 注意：当dynamic标签没有prepend属性时，不会自动忽略其子标签的第一个prepend属性。?

  相关解决方案

代码迷 - 您身边的软件异常，代码异常，编程异常助手（发现，解决，分享）
Copyright © 2009-2013 DAIMAMI.COM. All rights reserved.