iBatis和MyBatis SQL映射文件编码规范

1、iBatis和MyBatis SQL映射文件编码规范目录1XML注释注意事项21.1使用<!- ->注释，前后至少留出一个半角空格21.2避免注释出现在SQL语句中22防止SQL注入22.1变量引用的两种写法22.1.1$写法，存在SQL注入风险22.1.2#写法，数据库预编译，防止SQL注入32.2LIKE 操作符32.2.1$写法32.2.2#写法32.3IN 操作符42.3.1$写法42.3.2#写法42.4TOP 子句52.4.1$写法52.4.2#写法52.5ORDER BY 子句52.5.1$写法52.5.2#写法62.6GROUP BY 语句62.6.1$写法62.6

2、.2#写法63避免使用SELECT *查询物理表73.1避免使用SELECT * 查询物理表74避免WHERE/ON语句中列类型隐式转换74.1变量类型优先级应不高于数据库中的列类型74.2指定字符串变量的JDBC类型85避免WHERE/ON语句中对字段使用函数运算95.1日期类型字段95.1.1查询条件和日期字段的精确度一致95.1.2如果查询条件和日期字段的精确度不一致105.2字符串类型字段126指定别名（Alias）126.1建议使用AS关键字指定别名，而不是空格126.2建议子查询中应指定表的别名136.3建议避免使用a、b、c、d等无意义的别名137建议使用WITH (NOLOCK

3、)（涉及金融、财务、支付和会计成本等等的SQL语句不要使用该关键词）147.1建议SELECT语句使用WITH (NOLOCK) （关键业务除外）148建议指定表的架构名称148.1建议指定表的架构名称dbo141 XML注释注意事项1.1 使用<!- ->注释，前后至少留出一个半角空格SQL XML映射文件中使用<!- ->注释，其中注释内容不要紧挨着注释的起始标签和结束标签。注释内容距离起始标签和结束标签至少留出一个半角空格。<!- 获取学员信息 -><select id="getMemberInfo" resultClass=

4、"java.util.HashMap">select Uid, Memberid, FullName from Member with (nolock) where Memberid = #memberid:CHAR#</select>1.2 避免注释出现在SQL语句中<!- 获取学员信息 -><select id="getMemberInfo" resultClass="java.util.HashMap">select Uid, Memberid, FullName <!- 这里不要

5、出现注释 ->from Member with (nolock) <!- 这里不要出现注释 ->where Memberid = #memberid:CHAR# <!- 这里不要出现注释 -></select>2 防止SQL注入2.1 变量引用的两种写法对于ibatis/MyBatis变量引用方式可以使用 # 和 $ 两种写法，其中 # 写法会采用预编译方式，将转义交给了数据库，不会出现注入问题；如果采用 $ 写法，则相当于拼接字符串，会出现注入问题。禁止使用 $ 写法，请使用 # 写法或者枚举形式代替。如下以iBatis为例说明 $ 写法和 # 写法

6、。2.1.1 $写法，存在SQL注入风险引起SQL注入问题的 $ 写法示例：<!- 0. SQL注入写法：存在安全隐患 -><select id="getByIdCware" resultClass="com.cdeledu.courseware.cwareList.domain.Cware">SELECT cw.CwareID, cw.CwID, cw.CwareName, cw.InnerCwareIDFROM dbo.Cware AS cw WITH (NOLOCK)WHERE cw.CwareID = $cwareID$&

7、lt;/select>2.1.2 #写法，数据库预编译，防止SQL注入不会引起SQL注入问题的 # 写法示例：<!- 1. 预编译写法：防止SQL注入 -><select id="getByIdCware" resultClass="com.cdeledu.courseware.cwareList.domain.Cware">SELECT cw.CwareID, cw.CwID, cw.CwareName, cw.InnerCwareIDFROM dbo.Cware AS cw WITH (NOLOCK)WHERE cw.C

8、wareID = #cwareID#</select>2.2 LIKE 操作符2.2.1 $写法<!- 0. SQL注入写法：存在安全隐患 -><select id="like0" resultClass="com.cdeledu.courseware.cwareList.domain.Cware"parameterClass="java.util.Map">SELECT cw.CwareID, cw.CwID, cw.CwareName, cw.InnerCwareIDFROM dbo.Cware

9、 AS cw WITH (NOLOCK)WHERE cw.CwareName like $cwareName$</select>2.2.2 #写法<!- 1. 预编译写法：使用字符串连接符+号 -><select id="like1" resultClass="com.cdeledu.courseware.cwareList.domain.Cware"parameterClass="java.util.Map">SELECT cw.CwareID, cw.CwID, cw.CwareName, cw

10、.InnerCwareIDFROM dbo.Cware AS cw WITH (NOLOCK)WHERE cw.CwareName like '%' + #cwareName:VARCHAR# + '%'</select><!- 2. 预编译写法：在JAVA程序中预先拼接前后的%符号 -><select id="like2" resultClass="com.cdeledu.courseware.cwareList.domain.Cware"parameterClass="java.

11、util.Map">SELECT cw.CwareID, cw.CwID, cw.CwareName, cw.InnerCwareIDFROM dbo.Cware AS cw WITH (NOLOCK)WHERE cw.CwareName like #cwareName:VARCHAR#</select>2.3 IN 操作符2.3.1 $写法<!- 0. SQL注入写法：存在安全隐患 -><select id="in0" resultClass="com.cdeledu.courseware.cwareList.dom

12、ain.Cware"parameterClass="java.lang.String">SELECT cw.CwareID, cw.CwID, cw.CwareName, cw.InnerCwareIDFROM dbo.Cware AS cw WITH (NOLOCK)WHERE cw.CwareID IN $value$</select>2.3.2 #写法iBatis中，使用<iterate>标签遍历，其中property属性参数可以是数组Array、列表List。<!- 1. 预编译写法：适用于一个或多个参数 ->&l

13、t;select id="in1" resultClass="com.cdeledu.courseware.cwareList.domain.Cware"parameterClass="java.util.Map">SELECT cw.CwareID, cw.CwID, cw.CwareName, cw.InnerCwareIDFROM dbo.Cware AS cw WITH (NOLOCK)WHERE cw.CwareID IN<iterate open="(" close=")"

14、; conjunction="," property="cwareIDs">#cwareIDs#</iterate></select><!- 2. 预编译写法：如果只有一个参数 -><select id="in2" resultClass="com.cdeledu.courseware.cwareList.domain.Cware">SELECT cw.CwareID, cw.CwID, cw.CwareName, cw.InnerCwareIDFROM dbo

15、.Cware AS cw WITH (NOLOCK)WHERE cw.CwareID IN<iterate open="(" close=")" conjunction=",">#</iterate></select>2.4 TOP 子句2.4.1 $写法<!- 0. SQL注入写法：存在安全隐患 -><select id="top0" resultClass="com.cdeledu.courseware.cwareList.domain.Cware

16、">SELECT TOP $value$ cw.CwareID, cw.CwID, cw.CwareName, cw.InnerCwareIDFROM dbo.Cware AS cw WITH (NOLOCK)</select>2.4.2 #写法<!- 1. 预编译写法：TOP后的参数必须使用小括号，并且必须是整数（无法自动转换数据类型） -><select id="top1" resultClass="com.cdeledu.courseware.cwareList.domain.Cware">SEL

17、ECT TOP (#value#) cw.CwareID, cw.CwID, cw.CwareName, cw.InnerCwareIDFROM dbo.Cware AS cw WITH (NOLOCK)</select>2.5 ORDER BY 子句2.5.1 $写法<!- 0. SQL注入写法：存在安全隐患 -><select id="orderBy0" resultClass="com.cdeledu.courseware.cwareList.domain.Cware">SELECT TOP (100) Cwar

18、eID, CwID, CwareName, InnerCwareIDFROM dbo.Cware WITH (NOLOCK)ORDER BY $orderBy$</select>2.5.2 #写法<!- 1. 预编译写法：枚举有效的列名称 -><select id="orderBy1" resultClass="com.cdeledu.courseware.cwareList.domain.Cware"parameterClass="java.util.Map">SELECT TOP (100) C

19、wareID, CwID, CwareName, InnerCwareIDFROM dbo.Cware WITH (NOLOCK)<isEqual property="orderBy" compareValue="CwareID">ORDER BY CwareID</isEqual><isEqual property="orderBy" compareValue="CwID">ORDER BY CwID</isEqual><isEqual property=&q

20、uot;orderBy" compareValue="CwareName">ORDER BY CwareName</isEqual><isEqual property="orderBy" compareValue="InnerCwareID">ORDER BY InnerCwareID</isEqual></select>2.6 GROUP BY 语句2.6.1 $写法同ORDER BY子句2.6.2 #写法同ORDER BY子句3 避免使用SELECT *查询物理表3.

21、1 避免使用SELECT * 查询物理表避免使用SELECT * 查询物理表，应该只查询业务需要的字段。如果确实需要查询物理表的所有字段，则应该在SELECT语句中列出所有字段。示例问题SQL语句：SELECT top 1 * FROM MEMBER WHERE memberid = #memberid#上述SQL语句可改写成：- 只列出业务需要的字段SELECT TOP (1) Memberid, FullName, Uid FROM dbo.Member WITH (NOLOCK) WHERE Uid = #uid#4 避免WHERE/ON语句中列类型隐式转换4.1 变量类型优先级应不高于

22、数据库中的列类型变量类型尽量与数据库中列类型保存一致，至少变量类型优先级应不高于数据库中的列类型。避免因变量类型优先级较高而使列类型产生隐式转换，进而严重影响索引的使用。SQL Server 显示和隐式类型转换：SQL Server 数据类型优先级 (Transact-SQL) :SQL Server、JDBC 和 Java 编程语言数据类型之间的默认映射：(1) 示例一：如下SQL语句中，列类型的优先级低于变量类型，执行计划中显示对列Memberid使用了CONVERT_IMPLICIT() 函数操作，因此影响索引的使用。虽然 SQL Server 2008 优化了隐式数据类型转换的处理，但

23、是，还是应该避免这种情况的出现。GO - Memberid列类型char的优先级低于P0变量类型nvarchardeclare P0 nvarchar(4000) = N'cdeljishu007'select Uid, Memberid, FullName from Member where Memberid = P0;GO(2) 示例二：如下SQL语句中，列类型的优先级高于变量类型，执行计划中显示对变量P0使用了CONVERT_IMPLICIT() 函数操作，但是对列Memberid没有影响，因此不会影响索引的使用。GO- Uid列类型int的优先级高于P0变量类型nvar

24、chardeclare P0 nvarchar(4000) = N'42226790'select Uid, Memberid, FullName from Member where Uid = P0;GO4.2 指定字符串变量的JDBC类型指定字符串变量的JDBC类型，尤其是数据库字段类型为CHAR、VARCHAR的字符串变量。示例1：iBatis中指定变量courseID的JDBC类型：<select id="getByIdCourse" parameterClass="java.lang.String" resultClass

25、="java.util.HashMap">SELECT Courseid, Title, SelCourseTitle, HomeTitle, MobileTitle,SubjectID, ClassID, Price, CloseDate, StudyDay, DownLoadHour,CanUseCard, EnjoyDiscount, EduSubjectID, BasicUnit, CanPay, DispDefault,appShowStatus, mobileclass, CrossSubject, SupportMobile, ContinueCours

26、eIDFROM dbo.Course WITH (NOLOCK)WHERE Courseid = #courseID:CHAR#</select>示例2：MyBatis中指定变量userName 的JDBC类型：<select id="countExistOpmnUser" resultType="java.lang.Integer" parameterType="java.lang.String">select count(*)from dbo.OPMN_USER with (nolock)where use

27、rName = #userName,jdbcType=VARCHAR</select>5 避免WHERE/ON语句中对字段使用函数运算避免对WHERE语句中的字段进行函数运算，尤其对于日期类型的字段使用函数操作。不要为了书写或者理解方便而编写损害数据库查询性能的SQL语句。5.1 日期类型字段避免对于日期类型的字段使用函数操作，应将对日期字段的函数操作改写成对参数变量的函数操作，或者改写成区间查询。日期类型的查询条件分为如下两种情况：(1) 如果查询条件和日期字段的精确度一致（即查询条件是整日期或整时整点，并且字段存储也是整日期或整时整点的记录），可以使用等号连接字段和查询条件。(

28、2) 如果查询条件和日期字段的精确度不一致。可以使用区间查询（即日期字段大于等于起始时间，小于结束时间）。5.1.1 查询条件和日期字段的精确度一致示例问题SQL语句：<!- 查询指定学员，历史的学习明细信息 -><select id="getStudyHistoryDetail" parameterClass="java.util.HashMap" resultClass="java.util.HashMap"> select cu.UserID as uid,cw.CwareID,cw.CwareName+

29、' '+cc.CwareClassName as studyContent, cr.TeacherName,cu.studyTime from( select UserID,CwareID,SUM(StudyTime) as studyTime from Cware_TimeDay_User with (nolock) where UserID = #uid# and convert(varchar(10), StudyDay, 120) = #studyDate# group by UserID,CwareID )cu inner join Cware cw with (no

30、lock) on cu.CwareID=cw.CwareID inner join Cware_Class cc with (nolock) on cw.CwareClassID = cc.cwareClassID inner join Cware_Teacher cr with (nolock) on cw.TeacherID = cr.TeacherID order by cw.CwareID</select>上述SQL语句可改写成：<!- 查询指定学员，历史的学习明细信息 -><select id="getStudyHistoryDetail&qu

31、ot; parameterClass="java.util.HashMap" resultClass="java.util.HashMap"> select cu.UserID as uid,cw.CwareID,cw.CwareName+' '+cc.CwareClassName as studyContent, cr.TeacherName,cu.studyTime from( select UserID,CwareID,SUM(StudyTime) as studyTime from Cware_TimeDay_User wi

32、th (nolock) where UserID = #uid# and StudyDay = #studyDate# group by UserID,CwareID )cu inner join Cware cw with (nolock) on cu.CwareID=cw.CwareID inner join Cware_Class cc with (nolock) on cw.CwareClassID = cc.cwareClassID inner join Cware_Teacher cr with (nolock) on cw.TeacherID = cr.TeacherID ord

33、er by cw.CwareID</select>5.1.2 如果查询条件和日期字段的精确度不一致示例问题SQL语句：<!- 查询指定学员，今天的学习明细信息 -><select id="getStudyTodayDetail" parameterClass="java.util.HashMap" resultClass="java.util.HashMap"> select ct.UserID as uid,cw.CwareID,cw.CwareName+' '+cc.CwareC

34、lassName as studyContent, cr.TeacherName,ct.studyTime from ( select UserID,CwareID,SUM(ThisStudyTime) as studyTime from Cware_TimeKcjy with (nolock) where userID = #uid# and convert(varchar(10), StudyDate, 120) = #studyDate# group by UserID,CwareID )ct inner join Cware cw with (nolock) on ct.CwareID

35、=cw.CwareID inner join Cware_Class cc with (nolock) on cw.CwareClassID = cc.cwareClassID inner join Cware_Teacher cr with (nolock) on cw.TeacherID = cr.TeacherID</select>上述SQL语句可改写成：<!- 查询指定学员，今天的学习明细信息 -><select id="getStudyTodayDetail" parameterClass="java.util.HashMa

36、p" resultClass="java.util.HashMap"> select ct.UserID as uid,cw.CwareID,cw.CwareName+' '+cc.CwareClassName as studyContent, cr.TeacherName,ct.studyTime from ( select UserID,CwareID,SUM(ThisStudyTime) as studyTime from Cware_TimeKcjy with (nolock) where userID = #uid# <!CD

37、ATA and StudyDate >= DATEADD(DD, 0, DATEDIFF(DD, 0, #studyDate#) and StudyDate < DATEADD(DD, 1, DATEDIFF(DD, 0, #studyDate#) > group by UserID,CwareID )ct inner join Cware cw with (nolock) on ct.CwareID=cw.CwareID inner join Cware_Class cc with (nolock) on cw.CwareClassID = cc.cwareClassID

38、inner join Cware_Teacher cr with (nolock) on cw.TeacherID = cr.TeacherID</select>5.2 字符串类型字段避免WHERE/ON语句中对字符串类型的字段使用CAST()、RTRIM()、LEN()、LEFT()、CHARINDEX()等函数运算。示例问题SQL语句：select SmallListID,SUM(ct.ThisStudyTime) as ThisStudyTime,MAX(StudyDate) as StudyDatefrom Cware_TimeKcjy ct with (nolock) l

39、eft join Cware_Kcjy_Published cp with (nolock) on ct.jyID=rtrim(cp.jyID)left join Cware_InnerListVideo cv with (nolock) on cp.InnerCwareID=cv.InnerCwareID and cp.VideoID = cv.VideoIDwhere ct.UserID=#userID# and ct.CwareID=#cwareID#group by cv.SmallListID上述SQL语句可改写成：select SmallListID, SUM(ct.ThisStu

40、dyTime) as ThisStudyTime, MAX(StudyDate) as StudyDatefrom dbo.Cware_TimeKcjy as ct with (nolock)left join dbo.Cware_Kcjy_Published as cp with (nolock) on ct.jyID = cp.jyIDleft join dbo.Cware_InnerListVideo as cv with (nolock) on cp.InnerCwareID=cv.InnerCwareID and cp.VideoID = cv.VideoIDwhere ct.Use

41、rID = #userID# and ct.CwareID = #cwareID#group by cv.SmallListID6 指定别名（Alias）6.1 建议使用AS关键字指定别名，而不是空格如果需要为字段、表或者表表达式指定别名（Alias），应使用AS关键字，而不是空格。示例SQL语句：SELECT cw.CwareID CwareID, cw.CwID CwID, cw.CwareName CwareNameFROM dbo.Cware cw WITH (NOLOCK)WHERE cw.CwareID = #cwareID#上述SQL语句可改写成：SELECT cw.CwareI

42、D AS CwareID, cw.CwID AS CwID, cw.CwareName AS CwareNameFROM dbo.Cware AS cw WITH (NOLOCK)WHERE cw.CwareID = #cwareID#6.2 建议子查询中应指定表的别名子查询（包括相关子查询和不相关子查询）中应尽量指定表的别名。示例SQL语句：SELECT ci.InnerCwareID, ci.InnerCwareName, ci.CwareSubjectIDFROM Cware_Inner AS ci WITH (NOLOCK)WHERE ci.InnerCwareID = (SELECT cw.InnerCwareID FROM Cware AS cw WITH (NOLOCK) WHERE cw.CwareID = #cwareID#)6.3 建议避免使用a、b、c、d等无意义的别名避免为表指定a、b、c、d等这种无意义的别名，建议使用表名称的字母缩写形式指定别名。示例问题SQL语句：SELECT a.CwareID, a.CwID, a.CwareName, a.CwareClassID, a