Ibatis commonly uses 16 SQL statements

Ibatis commonly used 16 SQL statements blog classification: • ibatis column (1) input parameter is a single value Xml Code 1. <delete id = "com. fashionfree. stat. accesslog. deleteMemberAccessLogsBefore "2. parameterClass = "long"> 3. delete from 4. memberAccessLog 5. where 6. accessTimestamp = # value #7. </delete> (2) the input parameter is an object. Xml Code 1. <insert id = "com. fashionfree. stat. accesslog. memberAccessLog. insert "2. parameterClass = "com. fashionfree. stat. accesslog. model. memberAccessLog> 3. insert Into MemberAccessLog 4. (5. accessLogId, memberId, clientIP, 6. httpMethod, actionId, requestURL, 7. accessTimestamp, extend1, extend2, 8. extend3 9 .) 10. values 11. (12. # accessLogId #, # memberId #, 13. # clientIP #, # httpMethod #, 14. # actionId #, # requestURL #, 15. # accessTimestamp #, # extend1 #, 16. # extend2 #, # extend3 #17 .) 18. </insert> (3) the input parameter is a java. util. hashMap Xml Code 1. <select id = "com. fashionfree. stat. acce Sslog. selectActionIdAndActionNumber "2. parameterClass = "hashMap" 3. resultMap = "getActionIdAndActionNumber"> 4. select 5. actionId, count (*) as count 6. from 7. memberAccessLog 8. where 9. memberId = # memberId # 10.and accessTimestamp> # start # 11.and accessTimestamp <= # end #12. group by actionId 13. </select> (4) the input parameter contains the array Xml Code 1. <insert id = "updateStatusBatch" parameterClass = "hashMap"> 2. update 3. question 4. set 5. status = # status #6. <dynamic prepend = "where questionId in"> 7. <isNotNull property = "actionIds"> 8. <iterate property = "actionIds" open = "(" close = ")" conjunction = ","> 9. # actionIds [] #10. </iterate> 11. </isNotNull> 12. </dynamic> 13. </insert> Description: actionIds is the name of the input array. Use the dynamic tag to avoid SQL statement syntax errors when the array is empty; use the isNotNull tag to avoid ibatis parsing errors when the array is null (5) passing parameters only contain one array Xml Code 1. <select id = "com.fashionfree.stat.accesslog.mo Del. statMemberAction. selectActionIdsOfModule "2. resultClass = "hashMap"> 3. select 4. moduleId, actionId 5. from 6. statMemberAction 7. <dynamic prepend = "where moduleId in"> 8. <iterate open = "(" close = ")" conjunction = ","> 9. # [] #10. </iterate> 11. </dynamic> 12. order by 13. moduleId 14. </select> Note: The select label does not contain parameterClass. In addition, the array can also be put into a hashMap, but additional overhead is added. It is not recommended to use (6) let ibatis parse the parameter directly into the string Xml Code 1. <select id = "c Om. fashionfree. stat. accesslog. selectSumDistinctCountOfAccessMemberNum "2. parameterClass = "hashMap" resultClass = "int"> 3. select 4. count (distinct memberId) 5. from 6. memberAccessLog 7. where 8. accessTimestamp >=# start # 9.and accessTimestamp <# end # 10.and actionId in $ actionIdString $11. </select> Note: This method has the risk of SQL injection. (7) pagedQuery Java code 1 is not recommended. <select id = "com. fashionfree. stat. accesslog. selectM EmberAccessLogBy "2. parameterClass = "hashMap" resultMap = "MemberAccessLogMap"> 3. <include refid = "selectAllSql"/> 4. <include refid = "whereSql"/> 5. <include refid = "pageSql"/> 6. </select> 7. <select id = "com. fashionfree. stat. accesslog. selectMemberAccessLogBy. count "8. parameterClass = "hashMap" resultClass = "int"> 9. <include refid = "countSql"/> 10. <include refid = "whereSql"/> 11. </select> 12. <SQL id = "selectils Ql "> 13. select 14. accessLogId, memberId, clientIP, 15. httpMethod, actionId, requestURL, 16. accessTimestamp, extend1, extend2, 17. extend3 18. from 19. memberAccessLog 20. </SQL> 21. <SQL id = "whereSql"> 22. accessTimestamp <= # accessTimestamp #23. </SQL> 24. <SQL id = "countSql"> 25. select 26. count (*) 27. from 28. memberAccessLog 29. </SQL> 30. <SQL id = "pageSql"> 31. <dynamic> 32. <isNotNull property = "startIndex"> 33. <isNotNull property = "pageSize"> 34. limit # startIndex #, # pageSize #35. </isNotNull> 36. </isNotNull> 37. </dynamic> 38. </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 looks for the name com. fashionfree. stat. accesslog. selectMemberAccessLogBy. mapped statement of Count for SQL query To obtain com. fashionfree. stat. accesslog. selectMemberAccessLogBy queries the number of records and then performs the required paged SQL query (com. fashionfree. stat. accesslog. selectMemberAccessLogBy). For details about the process, see the related code in the utils class (8). The SQL statement contains a greater than number and a smaller than number <1. write "greater than" and "smaller than" as:> <e. g.: Xml Code 1. <delete id = "com. fashionfree. stat. accesslog. deleteMemberAccessLogsBefore "parameterClass =" long "> 2. delete from 3. memberAccessLog 4. where 5. accessTimestamp <= # value #6. </delete> 2. place special characters in x In the CDATA area of ml: Xml Code 1. <delete id = "com. fashionfree. stat. accesslog. deleteMemberAccessLogsBefore" parameterClass = "long"> 2. <! [CDATA [3. delete from 4. memberAccessLog 5. where 6. accessTimestamp <= # value #7.]> 8. </delete> the first method is recommended, which is written as <and> (XML does not parse the content in CDATA, So If CDATA contains the dynamic tag, it will not work) (9) include and SQL labels combine common SQL statements to facilitate sharing: Xml Code 1. <SQL id = "selectBasicSql"> 2. select 3. samplingTimestamp, onlineNum, year, 4. month, week, day, hour 5. from 6. onlineMemberNum 7. </SQL> 8. <SQL id = "whereSqlBefore"> 9. where samplingTimestamp <= # sampling Timestamp #10. </SQL> 11. <select id = "com. fashionfree. accesslog. selectOnlineMemberNumsBeforeSamplingTimestamp "parameterClass =" hashmap "resultClass =" OnlineMemberNum "> 12. <include refid = "selectBasicSql"/> 13. <include refid = "whereSqlBefore"/> 14. </select> Note: SQL labels can only be referenced and cannot be used as mapped statement. The preceding example contains an SQL element named selectBasicSql. It is incorrect to try to use it as an SQL statement: sqlMapClient. queryForList ("selectBasicSql"); × (10) randomly select the recorded Xml Code 1. <SQL id = "randomSql"> 2. order by rand () LIMIT # number #3. </SQL> randomly select a number of records from the database (For MySQL only) (11). splice the fields in the SQL GROUP BY GROUP with Xml Code 1. <SQL id = "selectGroupBy> 2. SELECT 3. a. answererCategoryId,. answererId,. answererName, 4. a. questionCategoryId,. score,. answeredNum, 5. a. correctNum,. answerSe Conds,. createdTimestamp, 6. a. lastQuestionApprovedTimestamp,. lastModified, GROUP_CONCAT (q. categoryName) as categoryName 7. FROM 8. answererCategory a, QuestionCategory q 9. WHERE. questionCategoryId = q. questionCategoryId 10. group by. answerid 11. order by. answererCategoryId 12. </SQL> Note: The GROUP_CONCAT function (12) of MySQL is used in SQL to sort data IN the IN sequence. ① MySQL: Xml Code 1. <SQL id = "groupByInArea"> 2. select 3. moduleId, ModuleName, 4. status, lastModifierId, lastModifiedName, 5. lastModified 6. from 7. statModule 8. where 9. moduleId in (3, 5, 1) 10. order by 11. instr (', 3, 5, 1,' + ltrim (moduleId) + ',') 12. </SQL> ② SQLSERVER: Xml Code 1. <SQL id = "groupByInArea"> 2. select 3. moduleId, moduleName, 4. status, lastModifierId, lastModifiedName, 5. lastModified 6. from 7. statModule 8. where 9. moduleId in (3, 5, 1) 10. order by 11. chari Ndex (',' + ltrim (moduleId) + ', 3, 5, 1,') 12. </SQL> note: the query result returns MySQL: instr (str, substr) SQLSERVER: charindex (substr, str) returns the first occurrence location of the str substring, ltrim (str) returns the str string, and the leading (left) space character is deleted (13) resultMap maps the column values of the SQL query result set to the attribute values of Java Bean. Xml Code 1. <resultMap class = "java. util. hashMap "id =" getActionIdAndActionNumber "> 2. <result column = "actionId" property = "actionId" jdbcType = "BIGINT" javaType = "long"/> 3. <result column = "count" property = "count" jdbcType = "INT" javaType = "int"/> 4. </resultMap> using resultMap is called explicit result ing, which corresponds to resultClass (Inline result ing). The biggest advantage of using resultClass is that it is simple and convenient, and you do not need to display specified results, iBATIS determines Based on reflection. ResultMap can provide stricter configuration authentication by specifying jdbcType and javaType. (14) typeAlias Xml Code 1. <typeAlias alias = "MemberOnlineDuration" type = "com. fashionfree. stat. accesslog. model. memberOnlineDuration "/> 2. <typeAlias> allows you to define aliases to avoid repeated long names. (15) remap Xml Code 1. <select id = "testForRemap" parameterClass = "hashMap" resultClass = "hashMap" remapResults = "true"> 2. select 3. userId 4. <isEqual property = "tag" compareValue = "1"> 5 ., userName 6. </isEqual> 7. <isEqual property = "tag" compareValue = "2"> 8 ., userPassword 9. </isEqual> 10. from 11. userInfo 12. </select> in this example, different result sets are obtained based on the tag value of the parameter. If the remapResults = "true" attribute is not available, iBatis will cache the result set for the first query, and will not be executed the next time (it must still be in the process ). The execution result set ing uses the cached result set. Therefore, if the remapResult in the above example is the default false attribute, and a program is written as follows: Java code 1. hashMap <String, Integer> hashMap = new HashMap <String, Integer> (); 2. hashMap. put ("tag", 1); 3. sqlClient. queryForList ("testForRemap", hashMap); 4. hashMap. put ("tag", 2); 5. sqlClient. queryForList ("testForRemap", hashMap); the program reports an error when executing the last query statement, because iBATIS uses the result set for the first query, the result sets of the first and second operations are different: (userId, userName) and (userId, userPassword), which leads to an error. If the remapResults = "true" attribute is used, iBATIS will execute the result set ing every time a query is executed to avoid errors (there will be a large overhead at this time ). (16) The prepend attribute of the prepend dynamic label of the dynamic label is added before the result content as the prefix. When the result content of the label is empty, the prepend attribute does not work. When a dynamic tag contains the prepend attribute, the first prepend attribute of its nested sub-tag is ignored. Example: Xml Code 1. <SQL id = "whereSql"> 2. <dynamic prepend = "where"> 3. <isNotNull property = "userId" prepend = "BOGUS"> 4. userId = # userId #5. </isNotNull> 6. <isNotEmpty property = "userName" prepend = "and"> 7. userName = # userName #8. </isNotEmpty> 9. </dynamic> 10. </SQL> in this example, the dynamic tag contains two sub-tags <isNotNull> and <isNotEmpty>. According to the principles described above, if the <isNotNull> label does not have the prepend = "BOGUS" false attribute to remove dynamic, and in the <isNotEmpty> label will be ignored, SQL syntax errors may occur. Note: When the dynamic tag does not have the prepend attribute, the first prepend attribute of its subtags is not automatically ignored.