MyBatis Mapper File Example

Reprint: http://blog.csdn.net/ppby2002/article/details/20611737

<?xml version= "1.0" encoding= "UTF-8"?>
<! DOCTYPE Mapper Public "-//mybatis.org//dtd mapper 3.0//en"
"Http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace= "Com.lee.UserMapper" >


<!--return a single object--
<select id= "Selectname" resulttype= "Com.lee.User" >
<! [cdata[
Select User_name UserName from user_table where user_id = #{userid}
]]>
</select>


<!--return result format map< field name, field value >-->
<select id= "Selectbyname" resulttype= "HashMap" parametertype= "string" >
<! [cdata[
SELECT * from user_table where user_name=#{username}
]]>
</select>

<!--call the stored procedure--
<select id= "Selectuserfromstoreprocedure" statementtype= "callable" >
<! [cdata[
{Call My_pack.my_proc (
#{userid,mode=in,jdbctype=varchar,javatype=string},
#{username,mode=out,jdbctype=float,javatype=string})}
]]>
</select>


<!--return list<user>-->
<select id= "Selectusers" resulttype= "Com.lee.User" >
<! [cdata[
Select user_id userId, user_name userName from user_table
]]>
</select>

<!--reuse Sql-->
<sql id= "subquery" >
<! [cdata[
With My_sub_query as (
Select ' Lee ' as user_name, ' 1 ' as user_id from dual
Union select ' Lee1 ', ' 2 ' from dual
)
]]>
</sql>

<!--dynamic sql-->
<sql id= "Selectother" >
<include refid= "subquery"/>
<! [cdata[
SELECT t.other_id Otherid, T.other_name othername, T.other_flag otherflag from Othertable t
INNER JOIN my_sub_query mapper on mapper.user_id = t.user_id
]]>
<if test= "Filterflag==true" >
<! [cdata[
and T.other_flag = ' Y '
]]>
</if>
<!--
Another if segment
<if test= "Flag1==true" >
...
</if>
-
<!--
Using the Choose Statement, FLAG1 is a parameter passed from the Mapper method, such as a @Param ("Flag1") String Flag1
<choose>
<when test= "Flag1 = = ' Y '" >
T1.FLAG1 as Flag
from table1 t1
</when>
<otherwise>
T2.FLAG2 as Flag
From Table2 T2
</otherwise>
</choose>
-
</sql>

<!--return Numbers--
<select id= "SelectCount" resulttype= "Java.lang.Long" >
<! [cdata[
SELECT Count (*) from user_table
]]>
</select>

<!--map parameter, format map< parameter name, parameter value >-->
<select id= "Selectuser" parametertype= "Java.util.HashMap" resulttype= "Com.lee.User" >
<! [cdata[
SELECT user_id userId, user_name userName from user_table
where User_id=#{userid} and user_name = #{username}
]]>
</select>
</mapper>


--------------------------------------------------The purpose of this split line is to display the Java object example below------------------------------------------------ --
public class User {
private int userId;
Private String UserName;
Public String GetUserId () {return userId}
public void Setuserid (int userId) {This.userid=userid}
Public String GetUserName () {return userName}
public void Setusername (String userName) {this.username=username}
}
--------------------------------------------------the function of this divider is to display the Mapper object example below---------------------------------------------- ----
@Repository
Public interface Usermapper {
Public User Selectname (@Param ("userid") String userId);
Public list<map<string,object>> Selectbyname (@Param ("UserName") String userName);
<!--map< field name, field value >-->
public void Selectuserfromstoreprocedure (map<string,object> Map);
Public list<user> selectusers ();
Public OtherUser selectother ();
public int selectcount ();
Public User Selectuser (map<string,object> Map);
}
--------------------------------------------------The purpose of this split line is to show some other configuration examples--------------------------------------------------
<!--configuring queries with mappings Sql-->
<resultmap id= "UserMap" type= "Com.lee.User" >
<result column= "user_id" property= "UserId"/>
<result column= "user_name" property= "UserName"/>
</resultMap>
<select id= "Selectname" resultmap= "UserMap" >
<! [cdata[
Select user_name from user_table where user_id = #{userid}
]]>
</select>


<!--reuse mapping configuration and connect to other result sets query--
<resultmap id= "Otherusermap" type= "Com.lee.OtherUser" extends= "UserMap" >
<!--multiple query conditions separated by commas, such as userid=user_id,username=user_name-->
<collection property= "Owneditems" select= "Selectitems" column= "userid=user_id"/>
</resultMap>
<select id= "Selectitems" resulttype= "Com.lee.UserItem" >
SELECT * from user_item_table WHERE user_id = #{userid}
</select>


public class OtherUser extends User {
Private list<useritem> Owneditems;
Public list<useritem> Getowneditems () {return owneditems}
public void Setowneditems (list<useritem> userId) {This.owneditems=owneditems}
}
--------------------------------------------------The purpose of this split line is to show another example of reusing a subquery configuration----------------------------------------------- ---
<mapper namespace= "Mapper.namespace" >
<sql id= "SelectTable1" >
<! [cdata[
Select F1, F2, F3 from table1 where 1=1
]]>
</sql>
<select id= "getstandardagents" resultmap= "Standardagent" >
<include refid= "Mapper.namespace.selectTable1"/>
<! [cdata[
and f1 = ' abc '
]]>
</select>
</mapper>
--------------------------------------------------The function of this split line is to show insert/update/ Delete Configuration Example--------------------------------------------------
<!--generate user_id from the Oracle sequence, Jdbctype=varchar is used to insert null--
<insert id= "Insertuser" parametertype= "Com.lee.User" >
<selectkey keyproperty= "user_id" resulttype= "string" order= "before" >
Select Db_seq.nextval as user_id from dual
</selectKey>
INSERT into
User_table (
USER_ID,
USER_NAME,
) VALUES (
#{user_id},
#{user_name,jdbctype=varchar}
)
</insert>


<update id= "UpdateUser" parametertype= "Com.lee.User" >
UPDATE user_table
SET user_name = #{username,jdbctype=varchar},
WHERE
user_id = #{userid}
</update>


<delete id= "DeleteUser" parametertype= "Com.lee.User" >
DELETE user_table WHERE user_id = #{userid}
</delete>

MyBatis Mapper File Example