iBATIS常用Sql语句版本1.0文档修订记录版本编号或者更改记录编号*变化状态简要说明（变更内容和变更范围）日期变更人审核日期审核人1.0 新建2009-4-17 谷涛1.1 增加2009-4-28 谷涛*变化状态：A——增加，M——修改，D——删除(1) 输入参数为单个值<delete id="com.fashionfree.stat.accesslog.deleteMemberAccessLogsBefore"parameterClass="long">delete fromMemberAccessLogwhere#value#<=accessTimestamp</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#,#extend3##extend2#,)</insert>(3) 输入参数为一个java.util.HashMap<select id="com.fashionfree.stat.accesslog.selectActionIdAndActionNumber"parameterClass="hashMap"resultMap="getActionIdAndActionNumber">selectactionId, count(*) as countfromMemberAccessLogwhere#memberId#=memberId>#start#accessTimestampand<=#end#accessTimestampandbyactionIdgroup</select>(4) 输入参数中含有数组<insert id="updateStatusBatch" parameterClass="hashMap">updateQuestionsetstatus = #status#<dynamic prepend="where questionId in">property="actionIds"><isNotNullconjunction=",">close=")"<iterateopen="("property="actionIds"#actionIds[]#</iterate></isNotNull></dynamic></insert>说明：actionIds为传入的数组的名字；使用dynamic标签避免数组为空时导致sql语句语法出错；使用isNotNull标签避免数组为null时ibatis解析出错(5)传递参数只含有一个数组<select id="com.fashionfree.stat.accesslog.model.StatMemberAction.selectActionIdsOfModule"resultClass="hashMap">selectmoduleId, actionIdfromStatMemberAction<dynamic prepend="where moduleId in"><iterate open="(" close=")" conjunction=",">#[]#</iterate></dynamic>order bymoduleId</select>说明；注意select的标签中没有parameterClass一项另：这里也可以把数组放进一个hashMap中，但增加额外开销，不建议使用(6)让ibatis把参数直接解析成字符串<select id="com.fashionfree.stat.accesslog.selectSumDistinctCountOfAccessMemberNum"parameterClass="hashMap" resultClass="int">selectcount(distinct memberId)fromMemberAccessLogwhereaccessTimestamp >= #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">selectclientIP,memberId,accessLogId,httpMethod, actionId, requestURL,accessTimestamp, extend1, extend2,extend3fromMemberAccessLog</sql><sql id="whereSql">accessTimestamp <= #accessTimestamp#</sql><sql id="countSql">selectcount(*)fromMemberAccessLog</sql><sql id="pageSql"><dynamic><isNotNullproperty="startIndex">property="pageSize"><isNotNull#pageSize#,#startIndex#limit</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 fromMemberAccessLogwhere<= #value#accessTimestamp</delete>2.将特殊字符放在xml的CDATA区内：<delete id="com.fashionfree.stat.accesslog.deleteMemberAccessLogsBefore"parameterClass="long"><![CDATA[delete fromMemberAccessLogwhere<= #value#accessTimestamp]]></delete>推荐使用第一种方式，写为< 和 > (XML不对CDATA里的内容进行解析，因此如果CDATA中含有dynamic标签，将不起作用)(9)include和sql标签将常用的sql语句整理在一起，便于共用：<sql id="selectBasicSql">selectsamplingTimestamp,onlineNum,year,month,week,day,hourfromOnlineMemberNum</sql><sql id="whereSqlBefore">where samplingTimestamp <= #samplingTimestamp#</sql><selectid="com.fashionfree.accesslog.selectOnlineMemberNumsBeforeSamplingTimestamp"parameterClass="hashmap" resultClass="OnlineMemberNum"><include refid="selectBasicSql" /><include refid="whereSqlBefore" /></select>注意：sql标签只能用于被引用，不能当作mapped statement。