ibatis常用16条SQL语句(转)
来源:互联网 发布:qq群 淘宝客机器人 编辑:程序博客网 时间:2024/05/18 00:18
(1)输入参数为单个值
- <</span>delete
id="com.fashionfree.stat.accesslog.deleteMemberAccessLogsBe fore" - parameterClass="long">
- delete
from - MemberAccessLog
- where
- accessTimestamp
= #value# - </</span>delete>
(2) 输入参数为一个对象
- <</span>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# - )
- </</span>insert>
(3) 输入参数为一个java.util.HashMap
- <</span>select
id="com.fashionfree.stat.accesslog.selectActionIdAndActionN umber" - parameterClass="hashMap"
- resultMap="getActionIdAndActionNumb
er" > - select
- actionId,
count(*) as count - from
- MemberAccessLog
- where
- memberId
= #memberId# - and
accessTimestamp > #start# - and
accessTimestamp <= #end# - group
by actionId - </</span>select>
- <</span>insert
id="updateStatusBatch" parameterClass="hashMap"> - update
- Question
- set
- status
= #status# - <</span>dynamic
prepend="where questionId >in" - <</span>isNotNull
property="actionIds"> - <</span>iterate
property="actionIds" open="(" close=")" conjunction=","> - #actionIds[]#
- </</span>iterate>
- </</span>isNotNull>
- </</span>dynamic>
- </</span>insert>
- <</span>select
id="com.fashionfree.stat.accesslog.model.StatMemberAction.selectActionIdsOfModule" - resultClass="hashMap">
- select
- moduleId,
actionId - from
- StatMemberAction
- <</span>dynamic
prepend="where moduleId >in" - <</span>iterate
open="(" close=")" conjunction=","> - #[]#
- </</span>iterate>
- </</span>dynamic>
- order
by - moduleId
- </</span>select>
- <</span>select
id="com.fashionfree.stat.accesslog.selectSumDistinctCountOf AccessMemberNum" - parameterClass="hashMap"
resultClass="int"> - select
- count(distinct
memberId) - from
- MemberAccessLog
- where
- accessTimestamp
>= #start# - and
accessTimestamp < #end# - and
actionId in $actionIdString$ - </</span>select>
- "com.fashionfree.stat.accesslog.selectMemberAccessLogBy"
- parameterClass="hashMap"
resultMap="MemberAccessLogMap"> - "selectAllSql"/>
- "whereSql"/>
- "pageSql"/>
-
- "com.fashionfree.stat.accesslog.selectMemberAccessLogBy.Count"
- parameterClass="hashMap"
resultClass="int"> - "countSql"/>
- "whereSql"/>
-
- "selectAllSql">
- select
- accessLogId,
memberId, clientIP, - httpMethod,
actionId, requestURL, - accessTimestamp,
extend1, extend2, - extend3
- from
- MemberAccessLog
-
- "whereSql">
- accessTimestamp
<= #accessTimestamp# -
- "countSql">
- select
- count(*)
- from
- MemberAccessLog
-
- "pageSql">
-
- "startIndex">
- "pageSize">
- limit
#startIndex# , #pageSize# -
-
-
-
再进行所需的pagedsql查询(com.fashionfree.stat.accesslog.selectMemberAccessLogBy),具体过程参见utils类中的相关代码
(8)sql语句中含有大于号>、小于号<</span>
- <</span>delete
id="com.fashionfree.stat.accesslog.deleteMemberAccessLogsBe fore" parameterClass="long"> - delete
from - MemberAccessLog
- where
- accessTimestamp
<= #value# - </</span>delete>
- <</span>delete
id="com.fashionfree.stat.accesslog.deleteMemberAccessLogsBe fore" parameterClass="long"> -
- delete
from - MemberAccessLog
- where
- accessTimestamp
<= #value# -
- </</span>delete>
(9)include和sql标签
- <</span>sql
id="selectBasicSql"> - select
- samplingTimestamp,onlineNum,year,
- month,week,day,hour
- from
- OnlineMemberNum
- </</span>sql>
- <</span>sql
id="whereSqlBefore"> - where
samplingTimestamp <= #samplingTimestamp# - </</span>sql>
- <</span>select
id="com.fashionfree.accesslog.selectOnlineMemberNumsBe foreSamplingTimestamp" parameterClass="hashmap" resultClass="OnlineMemberNum"> - <</span>include
refid="selectBasicSql" /> - <</span>include
refid="whereSqlBefore" /> - </</span>select>
(10)随机选取记录
- <</span>sql
id=”randomSql”> - ORDER
BY rand() LIMIT #number# - </</span>sql>
(11)将SQL GROUPBY分组中的字段拼接
- <</span>sql
id=”selectGroupBy> - SELECT
- a.answererCategoryId,
a.answererId, a.answererName, - a.questionCategoryId,
a.score, a.answeredNum, - a.correctNum,
a.answerSeconds, a.createdTimestamp, - a.lastQuestionApprovedTime
stamp, 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 - </</span>sql>
(12)按照IN里面的顺序进行排序
- <</span>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)+',') - </</span>sql>
②SQLSERVER:
- <</span>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,') - </</span>sql>
(13)resultMap
- <</span>resultMap
class="java.util.HashMap" id="getActionIdAndActionNumb er" > - <</span>result
column="actionId" property="actionId" jdbcType="BIGINT" javaType="long"/> - <</span>result
column="count" property="count" jdbcType="INT" javaType="int"/> - </</span>resultMap>
(14)typeAlias
- <</span>typeAlias
alias="MemberOnlineDuration" type="com.fashionfree.stat.accesslog.model.MemberOnlineDuration" /> - <</span>typeAlias>允许你定义别名,避免重复输入过长的名字。
(15)remap
- <</span>select
id="testForRemap" parameterClass="hashMap" resultClass="hashMap" remapResults="true"> - select
- userId
- <</span>isEqual
property="tag" compareValue="1"> - ,
userName - </</span>isEqual>
- <</span>isEqual
property="tag" compareValue="2"> - ,
userPassword - </</span>isEqual>
- from
- UserInfo
- </</span>select>
因此,如果上面的例子中remapResult为默认的false属性,而有一段程序这样书写:
- HashMap
hashMap = new HashMap(); - hashMap.put("tag",
1); - sqlClient.queryForList("testForRemap",
hashMap); - hashMap.put("tag",
2); - sqlClient.queryForList("testForRemap",
hashMap);
(16)dynamic标签的prepend
当dynamic标签中存在prepend属性时,将会把其嵌套子标签的第一个prepend属性忽略。例如:
- <</span>sql
id="whereSql"> - <</span>dynamic
prepend="where " > - <</span>isNotNull
property="userId" prepend="BOGUS"> - userId
= #userId# - </</span>isNotNull>
- <</span>isNotEmpty
property="userName" prepend="and " > - userName
= #userName# - </</span>isNotEmpty>
- </</span>dynamic>
- </</span>sql>
此例中,dynamic标签中含有两个子标签和。根据前面叙述的原则,如果标签中没有prepend="BOGUS"这一假的属性来让dynamic去掉的话,标签中的and就会被忽略,会造成sql语法错误。
转自:
http://javacrazyer.iteye.com/blog/1135561
转载请说明转载地址.
- ibatis常用16条SQL语句(转)
- ibatis常用16条SQL语句
- ibatis常用16条SQL语句
- ibatis常用16条SQL语句
- ibatis常用16条SQL语句
- ibatis常用16条SQL语句
- ibatis常用16条SQL语句
- ibatis常用16条SQL语句
- ibatis常用16条SQL语句
- ibatis常用sql语句
- 50条常用SQL语句(转)
- 50条常用sql语句
- 50条常用sql语句
- 五十条常用SQL语句
- 50条常用SQL语句
- 50条常用SQL语句
- 常用的50条SQL语句
- 【精】50条常用SQL语句 ---100%
- 转:一款好看的日期选择器
- 关于Oracle不能用127.0.0.1和local…
- Ibatis中insert用法(转)
- Struts2整合Spring方法及原…
- gcc 编译优化选项
- ibatis常用16条SQL语句(转)
- ibatis-dynamic的用法
- iBatis整理——iBatis批处理实现(Sp…
- ibatis解决sql注入问题
- cmd模式下的adb命令不能用
- android访问网络和SD卡必须配置
- SELECT @@IDENTITY as&n…
- struts2+freemarker 生成静态…
- Spring管理hibernate