Introduction to iBatis:
iBatis is an open source project of apache and an O/R Mapping solution. The biggest feature of iBatis is that it is compact and quick to get started. If you don't need too many complex functions, iBatis is the simplest solution that can meet your requirements and be flexible enough. Now iBatis has been renamed Mybatis.
The official website is: http://www.mybatis.org/
1. The input parameter is a single value
<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. The input parameter is an object
<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. The input parameter is a 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. The input parameters contain arrays
<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>Description: actionIds is the name of the passed array; use dynamic tag to avoid sql statement syntax errors when the array is empty; use isNotNull tag to avoid ibatis parsing errors when the array is null
5. Passing parameters contains only one array
<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>Note: Note that there is no parameterClass item in the select tag
Also: You can also put the array into a hashMap here, but it adds extra overhead and is not recommended to use it.
6. Let ibatis parse the parameters directly into strings
<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>
Note: There is a risk of SQL injection when using this method, and it is not recommended to use it.
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="countSql"> select count(*) 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>
Note: In this example, the code should be:
HashMap hashMap = new HashMap(); hashMap.put("accessTimestamp", someValue); pagedQuery("com.fashionfree.stat.accesslog.selectMemberAccessLogBy", hashMap);The pagedQuery method first searches for a mapped statement named com.fashionfree.stat.accesslog.selectMemberAccessLogBy.Count to perform sql query, thereby obtaining the number of records of com.fashionfree.stat.accesslog.selectMemberAccessLogBy query, and then performs the required paged sql query (com.fashionfree.stat.accesslog.selectMemberAccessLogBy). For the specific process, please refer to the relevant code in the utils class.
8. The sql statement contains greater than the sign> and less than the sign< 1. Write the greater than the sign and less than the sign as: > < For example:
<delete id="com.fashionfree.stat.accesslog.deleteMemberAccessLogsBefore" parameterClass="long"> delete from MemberAccessLog where accessTimestamp <= #value# </delete> Xml code<delete id="com.fashionfree.stat.accesslog.deleteMemberAccessLogsBefore" parameterClass="long"> delete from MemberAccessLog where accessTimestamp <= #value# </delete>
Place special characters in the CDATA area of xml:
<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>
It is recommended to use the first method, write it as < and > (XML does not parse the content in CDATA, so if the CDATA contains dynamic tags, it will not work)
9. Include and SQL tags organize common SQL statements together for easy sharing:
<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>
Note: SQL tags can only be used for reference and cannot be regarded as mapped statements. As in the above example, there is a SQL element named selectBasicSql, and it is wrong to try to use it as a SQL statement to execute:
sqlMapClient.queryForList("selectBasicSql"); ×10. Randomly select records
<sql id=”randomSql”> ORDER BY rand() LIMIT #number# </sql>
Randomly select number records from the database (only for MySQL)
11. Stitch fields in SQL GROUP BY grouping
<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>
Note: MySQL's GROUP_CONCAT function is used in SQL
12. Sort in the order in 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>Description: The query results will be returned in the order of moduleId in the in list (3, 5, 1).
MySQL: instr(str, substr)
SQLSERVER: charindex(substr, str) Returns the first occurrence position of the substring in the string str ltrim(str) Returns the string str, its boot (left) space character is deleted
13.resultMap resultMap is responsible for mapping column values of SQL query result sets into attribute values of Java Beans
<resultMap id="getActionIdAndActionNumber"> <result column="actionId" property="actionId" jdbcType="BIGINT" javaType="long"/> <result column="count" property="count" jdbcType="INT" javaType="int"/> </resultMap> Xml code<resultMap id="getActionIdAndActionNumber"> <result column="actionId" property="actionId" jdbcType="BIGINT" javaType="long"/> <result column="count" property="count" jdbcType="INT" javaType="int"/> </resultMap>
The use of resultMap is called an explicit result mapping, which corresponds to resultClass (inline result mapping). The biggest advantage of using resultClass is that it is simple and convenient. It does not require the specified result to be displayed. iBATIS determines the decision based on reflection. resultMap can provide stricter configuration authentication by specifying jdbcType and javaType.
14.typeAlias
<typeAlias alias="MemberOnlineDuration" type="com.fashionfree.stat.accesslog.model.MemberOnlineDuration" /> <typeAlias>
Allow you to define alias to avoid repeated input of excessively long names
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>
In this example, different result sets will be obtained according to the value of the parameter tag. If there is no remapResults="true" attribute, iBatis will cache the result set during the first query. The result set mapping will not be performed the next time (it must still be in the process), but the cached result set will be used.
Therefore, if in the above example remapResult is the default false property, and a program is written like this:
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 code
HashMap<String, Integer> hashMap = new HashMap<String, Integer>(); hashMap.put("tag", 1); sqlClient.queryForList("testForRemap", hashMap); hashMap.put("tag", 2); sqlClient.queryForList("testForRemap", hashMap);The program will report an error when executing the last query query. The reason is that iBATIS uses the result set of the first query, and the result sets of the first and last two times are different: (userId, userName) and (userId, userPassword), which leads to an error. If the remapResults="true" property is used, iBATIS will perform the result set mapping every time the query is executed, thereby avoiding errors (there will be a lot of overhead).
16. The prepend attribute of the dynamic tag is added as a prefix to the result content. When the result content of the label is empty, the prepend attribute will not work.
When the prepend attribute exists in the dynamic tag, the first prepend attribute of its nested subtitles will be ignored. For example:
<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>
In this example, the dynamic tag contains two sub-labels <isNotNull> and <isNotEmpty>. According to the principles described above, if the false attribute prepend="BOGUS" is not in the <isNotNull> tag to remove dynamic, and the and in the <isNotEmpty> tag will be ignored, causing SQL syntax errors.
Note: When a dynamic tag does not have a prepend attribute, the first prepend attribute of its child tag is not automatically ignored.
The above are 16 SQL statements that iBatis is used to introduce to you. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to Wulin.com website!