iBatis动态sql 博客分类: ibatis ibatis动态sql
一、
ibatis中拼接sql,先要转换为字符串,否则乱码
CONCAT(CAST(if(count(1)=0,0,count(if(t3.ine='E',1,null))/count(1))*100 AS char(10)),'%')
二、
(1) 输入参数为单个值
- <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>
(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>
(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>
说明: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的标签中没有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>
说明:使用这种方法存在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>
说明:本例中,代码应为:
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>
2. 将特殊字符放在xml的CDATA区内:
- <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标签只能用于被引用,不能当作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中使用了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>
②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>
说明:查询结果将按照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>
使用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>
此例中,根据参数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);
则程序会在执行最后一句的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>
此例中,dynamic标签中含有两个子标签<isNotNull>和<isNotEmpty>。根据前面叙述的原则,如果<isNotNull>标签中没有prepend="BOGUS" 这一假的属性来让dynamic去掉的话,<isNotEmpty>标签中的and就会被忽略,会造成sql语法错误。
注意:当dynamic标签没有prepend属性时,不会自动忽略其子标签的第一个prepend属性。
三、
iBatis解决sql注入
(1) ibatis xml配置:下面的写法只是简单的转义 namelike '%$name$%'
(2) 这时会导致sql注入问题,比如参数name传进一个单引号“'”,生成的sql语句会是:name like '%'%'
(3) 解决方法是利用字符串连接的方式来构成sql语句 name like '%'||'#name#'||'%'
(4) 这样参数都会经过预编译,就不会发生sql注入问题了。
(5) #与$区别:
#xxx# 代表xxx是属性值,map里面的key或者是你的pojo对象里面的属性,ibatis会自动在它的外面加上引号,表现在sql语句是这样的 where xxx = 'xxx' ;
$xxx$ 则是把xxx作为字符串拼接到你的sql语句中, 比如 order by topicId , 语句这样写 ... order by #xxx#,ibatis 就会把他翻译成 order by 'topicId' (这样就会报错) 语句这样写 ... order by $xxx$ibatis 就会把他翻译成 order by topicId
Sql代码
SELECT * FROM user WHERE username like '%' || #username# || '%'
其实上面的语句是针对Oracle 的,对于不同数据字符串连接符不一样。现列举mysql和SQLServer如下:
Mysql:
SQLServer:
-----------------------------------------------------------------------------------------------------------------------------
关于数据库字符串连接符简单列举我使用过的一些数据库如下:
Oracle | SQLServer | Mysql | DB2 |
|| 或 CONCAT() | + | CONCAT() | || 或 CONCAT() |
推荐阅读
-
sql server、db2、oracle 存储过程动态sql语句示例 博客分类: 程序日志 ORACLE、DB2、SQL Server数据库存储过程动态SQL
-
Oracle数据库之PLSQL绑定变量 博客分类: Oracle 动态SQL绑定变量静态SQLPLSQL
-
mysql数据库存储过程游标循环,提前退出 博客分类: mysql 存储过程游标循环失败动态sql增加字段
-
iBatis动态sql 博客分类: ibatis ibatis动态sql
-
mybatis 动态sql 博客分类: myibatis myibatis动态sql
-
Mybatis foreach用法 博客分类: Mybatis MyBatisforeachsql动态sql
-
Spring中使用HSQLDB测试ibatis的dao 博客分类: Spring DAOiBATISHSQLDBSpringCMS
-
ibatis中prepend的小用法 博客分类: iBATIS iBATISXMLCMSSQL
-
ibatis源码 博客分类: ibatis源码 ibatis
-
spring 3.0 +ibatIS2.0 两个数据源的实现 博客分类: spring spring3ibatIS2两个数据源