山西高速公路,钓上亲亲老哥,8l9980
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语句，希望对大家有所帮助
如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复
浅析我对 String、StringBuilder、StringBuffer 的理解
使用IDEA搭建SSM框架的详细教程(spring + springMVC +MyBatis)
Springboot整合freemarker 404问题解决方案
引入mybatis-plus报 Invalid bound statement错误问题的解决方法
网友评论