iBatis习惯用的16条SQL语句
ibatis 简介:
ibatis 是apache 的一个开源项目,一个o/r mapping 解决方案,ibatis 最大的特点就是小巧,上手很快。如果不需要太多复杂的功能,ibatis 是能够满足你的要求又足够灵活的最简单的解决方案,现在的ibatis 已经改名为mybatis 了。
官网为:
1.输入参数为单个值
<delete id="com.fashionfree.stat.accesslog.deletememberaccesslogsbefore" parameterclass="long"> delete from memberaccesslog where accesstimestamp = #value# </delete> <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> <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> <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> <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 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> <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> <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> xml代码 <delete id="com.fashionfree.stat.accesslog.deletememberaccesslogsbefore" parameterclass="long"> delete from memberaccesslog where accesstimestamp <= #value# </delete>
将特殊字符放在xml的cdata区内:
<delete id="com.fashionfree.stat.accesslog.deletememberaccesslogsbefore" parameterclass="long"> <![cdata[ delete from memberaccesslog where accesstimestamp <= #value# ]]> </delete> <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 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 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> <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> <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> xml代码 <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> <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);
java代码
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> <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属性。
以上所述是小编给大家介绍的ibatis习惯用的16条sql语句,希望对大家有所帮助
上一篇: Struts2学习笔记(9)-Result配置全局结果集
下一篇: JavaWeb文件上传入门教程