Ibatis 16 SQL statements used in _java

IBatis Introduction:

IBatis is an open source project in Apache, an O/R Mapping solution, IBatis the biggest feature is small, quick to start. If you don't need too many complex features, IBatis is the simplest solution to meet your requirements and be flexible enough, and now the IBatis has been renamed MyBatis.

The official website is: http://www.mybatis.org/

1. 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. 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 (#access logid#, #memberId #, #clientIP #, #httpMethod #, #actionId #, #requestURL #, #accessTimestamp #, #extend1 #, #extend2 #, #exte nd3#) </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 #, #m emberid#, #clientIP #, #httpMethod #, #actionId #, #requestURL #, #accessTimestamp #, #extend1 #, #extend2 #, #extend3 #) & Lt;/insert> 

3. 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 parameter contains an array

<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 array passed in; The SQL statement syntax error is caused by using the dynamic label to prevent the array from being empty; Ibatis Parse error using Isnotnull label to avoid null array

5. Pass parameters contain 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: Notice that there is no parameterclass in the tag of the Select

Another: You can also put the array into a hashmap, but add extra overhead, do not recommend the use of

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 using this method, and it is not recommended to use

7. Paging Query (Pagedquery)

<select id= "Com.fashionfree.stat.accesslog.selectMemberAccessLogBy" parameterclass= "HashMap" 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"/> &  
lt;/select> <sql id= "Selectallsql" > select Accesslogid, MemberID, ClientIP, HttpMethod, ActionId, RequestURL, Accesstimestamp, Extend1, Extend2, extend3 from Memberaccesslog </sql> <sql id= "Wheresql" > Accesstimes Tamp <= #accessTimestamp # </sql> <sql id= ' countsql ' > select COUNT (*) from Memberaccesslog &LT;/SQL&G 
T <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 "/> </se lect> <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 looks up the mapped statement named Com.fashionfree.stat.accesslog.selectMemberAccessLogBy.Count for SQL queries, To get the number of com.fashionfree.stat.accesslog.selectMemberAccessLogBy queries, and then do the required paged SQL query ( Com.fashionfree.stat.accesslog.selectMemberAccessLogBy), see the related code in the Utils class

The 8.sql statement contains the greater than >, less than number < 1. Write the greater-than, less-than number as: > < such as:

<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>

The first method is recommended, written as < and > (XML does not parse content in CDATA, so if 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 <= #samplingTimesta mp# </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 to be referenced and not as mapped statement. As in the previous example, an SQL element named Selectbasicsql, an attempt to use it as an SQL statement is an error:

Sqlmapclient.queryforlist ("Selectbasicsql"); X

10. Randomly Selected records

<sql id= "Randomsql" > Order by 
rand () LIMIT #number # 
</sql>

Randomly select number records from a database (for MySQL only)

11. Concatenation of fields in SQL Group by group

<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 order in inside

①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 to (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 are returned according to the order of ModuleID in the In list (3, 5, 1)

Mysql:instr (str, substr)

Sqlserver:charindex (substr, str) returns the first occurrence of a string str neutron string LTrim (str) returns the string str, whose boot (left) space character is deleted

13.resultMap Resultmap is responsible for mapping the column values of the SQL query result set to the property values of the 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> 
XML code 
<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 >

Using Resultmap is called an explicit result mapping, which corresponds to the resultclass (inline result mapping), the greatest benefit of using resultclass is simplicity, convenience, no need to display the specified result, and ibatis based on reflection to determine its own decision. However, Resultmap can provide more stringent configuration authentication by specifying Jdbctype and Javatype.

14.typeAlias

<typealias alias= "memberonlineduration" type= "Com.fashionfree.stat.accesslog.model.MemberOnlineDuration"/> 
<typeAlias>

Allows you to define aliases and avoid repeated input of 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" True "> 
select 
userId 
<isequal property=" tag "comparevalue=" 1 "> 
, UserName 
</ isequal> 
<isequal property= "tag" comparevalue= "2" > 
, userpassword 
</isEqual> 
From 
UserInfo 
</select>

In this case, depending on the parameter tag value, a different result set is obtained, and if there is no remapresults= "true" property, Ibatis will cache the result set at the first query, and the result set mapping will not be performed the next time (must still be in the process). Instead, the cached result set is used.

Therefore, if the above example Remapresult to the default false attribute, a section of the program writes as follows:

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);

Then the program will query the times in the last sentence, because Ibatis uses the result set for the first query, and the two-time result set is different: (UserId, UserName) and (UserId, UserPassword), causing an error. If you use the attribute remapresults= "true", Ibatis performs a result set mapping every time the query is executed, thereby avoiding the occurrence of the error (there is a higher cost).

The Prepend property of the prepend dynamic label for the 16.dynamic label is added to the result content as a prefix, and the Prepend property will not work when the label's result content is empty.

When the Prepend property exists in the dynamic label, the first prepend property of its nested label is 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 label contains two child tags <isNotNull> and <isNotEmpty>. According to the principle described above, if the <isNotNull> label does not have the false attribute prepend= "BOGUS" for dynamic removal, the and in the,<isnotempty> tag is ignored, resulting in SQL syntax errors.

Note: The first prepend property of its child labels is not automatically ignored when the dynamic label has no prepend property.

The above is a small set to introduce the Ibatis used by the 16 of SQL statements, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!