IBATIS常用的16种SQL语句写法
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Xml 代码 1. <delete id="com.fashionfree.stat.accesslog.deleteMemberAccessLogsBefore" paramete rClass="long"> 2. 3. 4. delete from MemberAccessLog where
5. 6.
10. </iterate> 11. </dynamic> 12. order by 13. moduleId 14. </select>
说明：注意 select 的标签中没有 parameterClass 一项 另：这里也可以把数组放进一个 hashMap 中，但增加额外开销，不建议使用 (6)让 ibatis 把参数直接解析成字符串
Xml 代码
1.
<select id="com.fashionfree.stat.accesslog.model.StatMemberAction.selectActionIdsO fModule"
2. 3. 4. 5. 6. 7. 8. 9.
resultClass="hashMap"> select moduleId, wenku.baidu.comctionId from StatMemberAction <dynamic prepend="where moduleId in"> <iterate open="(" close=")" conjunction=","> #[]#
10. </iterate> 11. </isNotNull> 12. </dynamic> 13. </insert>
说明：actionIds 为传入的数组的名字； 使用 dynamic 标签避免数组为空时导致 sql 语句语法出错； 使用 isNotNull 标签避免数组为 null 时 ibatis 解析出错 (5)传递参数只含有一个数组
6. 7. 8. 9.
OnlineMemberNum </sql> <sql id="whereSqlBefore"> where samplingTimestamp <= #samplingTimestamp#
10. </sql> 11. <select id="com.fashionfree.accesslog.selectOnlineMemberNumsBeforeSamplingTimes tamp" parameterClass="hashmap" resultClass="OnlineMemberNum"> 12. <include refid="selectBasicSql" /> 13. <include refid="whereSqlBefore" /> 14. </select>
Xml 代码 1. <select id="com.fashionfree.stat.accesslog.selectSumDistinctCountOfAccessMemberN um" 2. parameterClass="hashMap" resultClass="int">
3. 4. 5. 6. 7. 8. 9.
10. and accessTimestamp > #start# 11. and accessTimestamp <= #end# 12. group by actionId
13. </select>
(4) 输入参数中含有数组
Xml 代码 1. 2. 3. 4. 5. 6. 7. 8. 9. <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[]#
(3) 输入参数为一个 java.util.HashMap
Xml 代码 1. 2. 3. 4. 5. 6. 7. 8. 9. <select id="com.fashionfree.stat.accesslog.selectActionIdAndActionNumber" parameterClass="hashMap" resultMap="getActionIdAndActionNumber"> select actionId, count(*) as count from MemberAccessLog where memberId = #memberId#
(1) 输入参数为单个值
Xml 代码 1. 2. 3. 4. 5. 6. 7. <delete id="com.fashionfree.stat.accesslog.deleteMemberAccessLogsBefore" parameterClass="long"> delete from MemberAccessLog where accessTimestamp = #value# </delete>
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 <= #accessTimestamp# 23. </sql> 24. <sql id="countSql"> 25. select 26. count(*) 27. from 28. MemberAccessLog 29. </sql> 30. <sql id="pageSql"> 31. <dynamic>
10. values
11. ( 12. #accessLogId#, #memberId#, 13. #clientIP#, #httpMethod#, 14. #actionId#, #requestURL#, 15. #accessTimestamp#, #extend1#, 16. #extend2#, #extend3# 17. ) 18. </insert>
select count(distinct memberId) from MemberAccessLog where accessTimestamp >= #start# and accessTimestamp < #end#
10. and actionId in $actionIdString$ 11. </select>
accessTimestamp <= #value# </delete>
2. 将特殊字符放在 xml 的 CDATA 区内：
Xml 代码 1. <delete id="com.fashionfree.stat.accesslog.deleteMemberAccessLogsBefore" paramete rClass="long"> 2. 3. 4. 5. 6. 7. 8. <![CDATA[ delete from MemberAccessLog where accessTimestamp <= #value# ]]> </delete>
推荐使用第一种方式，写为< 和 > (XML 不对 CDATA 里的内容进行解析，因此如 果 CDATA 中含有 dynamic 标签，将不起作用) (9)include 和 sql 标签 将常用的 sql 语句整理在一起，便于共用：
Xml 代码 1. 2. 3. 4. 5. <sql id="selectBasicSql"> select samplingTimestamp,onlineNum,year, month,week,day,hour from
说明：本例中，代码应为： 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. 将大于号、小于号写为： > < 如：
说明：使用这种方法存在 sql 注入的风险，不推荐使用 (7)分页查询 (pagedQuery)
Java 代码 1. 2. 3. 4. 5. 6. 7. 8. 9. <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"/>
注意：sql 标签只能用于被引用，不能当作 mapped statement。如上例中有名为 selectBasicSql 的 sql 元素，试图使用其作为 sql 语句执行是错误的: sqlMapClient.queryForList(“selectBasicSql”); × (10)随机选取记录
(2) 输入参数为一个对象
Xml 代码 1. 2. 3. 4. 5. 6. 7. 8. 9. <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 )
32. <isNotNull property="startIndex"> 33. <isNotNull property="pageSize"> 34. limit #startIndex# , #pageSize# 35. </isNotNull> 36. </isNotNull> 37. </dynamic> 38. </sql>
5. 6.
10. </iterate> 11. </dynamic> 12. order by 13. moduleId 14. </select>
说明：注意 select 的标签中没有 parameterClass 一项 另：这里也可以把数组放进一个 hashMap 中，但增加额外开销，不建议使用 (6)让 ibatis 把参数直接解析成字符串
Xml 代码
1.
<select id="com.fashionfree.stat.accesslog.model.StatMemberAction.selectActionIdsO fModule"
2. 3. 4. 5. 6. 7. 8. 9.
resultClass="hashMap"> select moduleId, wenku.baidu.comctionId from StatMemberAction <dynamic prepend="where moduleId in"> <iterate open="(" close=")" conjunction=","> #[]#
10. </iterate> 11. </isNotNull> 12. </dynamic> 13. </insert>
说明：actionIds 为传入的数组的名字； 使用 dynamic 标签避免数组为空时导致 sql 语句语法出错； 使用 isNotNull 标签避免数组为 null 时 ibatis 解析出错 (5)传递参数只含有一个数组
6. 7. 8. 9.
OnlineMemberNum </sql> <sql id="whereSqlBefore"> where samplingTimestamp <= #samplingTimestamp#
10. </sql> 11. <select id="com.fashionfree.accesslog.selectOnlineMemberNumsBeforeSamplingTimes tamp" parameterClass="hashmap" resultClass="OnlineMemberNum"> 12. <include refid="selectBasicSql" /> 13. <include refid="whereSqlBefore" /> 14. </select>
Xml 代码 1. <select id="com.fashionfree.stat.accesslog.selectSumDistinctCountOfAccessMemberN um" 2. parameterClass="hashMap" resultClass="int">
3. 4. 5. 6. 7. 8. 9.
10. and accessTimestamp > #start# 11. and accessTimestamp <= #end# 12. group by actionId
13. </select>
(4) 输入参数中含有数组
Xml 代码 1. 2. 3. 4. 5. 6. 7. 8. 9. <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[]#
(3) 输入参数为一个 java.util.HashMap
Xml 代码 1. 2. 3. 4. 5. 6. 7. 8. 9. <select id="com.fashionfree.stat.accesslog.selectActionIdAndActionNumber" parameterClass="hashMap" resultMap="getActionIdAndActionNumber"> select actionId, count(*) as count from MemberAccessLog where memberId = #memberId#
(1) 输入参数为单个值
Xml 代码 1. 2. 3. 4. 5. 6. 7. <delete id="com.fashionfree.stat.accesslog.deleteMemberAccessLogsBefore" parameterClass="long"> delete from MemberAccessLog where accessTimestamp = #value# </delete>
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 <= #accessTimestamp# 23. </sql> 24. <sql id="countSql"> 25. select 26. count(*) 27. from 28. MemberAccessLog 29. </sql> 30. <sql id="pageSql"> 31. <dynamic>
10. values
11. ( 12. #accessLogId#, #memberId#, 13. #clientIP#, #httpMethod#, 14. #actionId#, #requestURL#, 15. #accessTimestamp#, #extend1#, 16. #extend2#, #extend3# 17. ) 18. </insert>
select count(distinct memberId) from MemberAccessLog where accessTimestamp >= #start# and accessTimestamp < #end#
10. and actionId in $actionIdString$ 11. </select>
accessTimestamp <= #value# </delete>
2. 将特殊字符放在 xml 的 CDATA 区内：
Xml 代码 1. <delete id="com.fashionfree.stat.accesslog.deleteMemberAccessLogsBefore" paramete rClass="long"> 2. 3. 4. 5. 6. 7. 8. <![CDATA[ delete from MemberAccessLog where accessTimestamp <= #value# ]]> </delete>
推荐使用第一种方式，写为< 和 > (XML 不对 CDATA 里的内容进行解析，因此如 果 CDATA 中含有 dynamic 标签，将不起作用) (9)include 和 sql 标签 将常用的 sql 语句整理在一起，便于共用：
Xml 代码 1. 2. 3. 4. 5. <sql id="selectBasicSql"> select samplingTimestamp,onlineNum,year, month,week,day,hour from
说明：本例中，代码应为： 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. 将大于号、小于号写为： > < 如：
说明：使用这种方法存在 sql 注入的风险，不推荐使用 (7)分页查询 (pagedQuery)
Java 代码 1. 2. 3. 4. 5. 6. 7. 8. 9. <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"/>
注意：sql 标签只能用于被引用，不能当作 mapped statement。如上例中有名为 selectBasicSql 的 sql 元素，试图使用其作为 sql 语句执行是错误的: sqlMapClient.queryForList(“selectBasicSql”); × (10)随机选取记录
(2) 输入参数为一个对象
Xml 代码 1. 2. 3. 4. 5. 6. 7. 8. 9. <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 )
32. <isNotNull property="startIndex"> 33. <isNotNull property="pageSize"> 34. limit #startIndex# , #pageSize# 35. </isNotNull> 36. </isNotNull> 37. </dynamic> 38. </sql>