mybatis常用语法

namespace

namespace对应的是mapper.xml所属接口类的位置。

<mapper namespace="com.icbc.support.mapper.OperateLogMapper"> </mapper>

crud

crud 对应的是select、insert、update、delete标签。

<select></select>
<insert></insert>
<update></update>
<delete></delete>

sql重用

<sql id="userColumns">
 ${alias}.id,${alias}.username,${alias}.password
<sql>
<select id="selectUsers" resultType="map">
	select
	<include refid="(com.this.mapper xml的位置一般不写默认本xml).userColumns"><property name="alias" value="t1"></include>,
	<include refid="userColumns”><property name="alias" value="t2"></include>
	from some_table t1
	join some_table_t2 
<select>

trim

	<trim prefix="" suffix="" suffixOverrides="" PrefixOverrides="">
	<!--
    prefix:在trim标签内sql语句加上前缀。
    suffix:在trim标签内sql语句加上后缀。
    suffixOverrides:指定去处多余的后缀内容,如suffixOverrides="," 去处多余的,
    prefixOverrides:指定去除多余的前缀内容。
     -->
	<insert id="insert" parameterType="com.tortuousroad.groupon.cart.entity.Cart">  
        insert into cart  
        <trim prefix="(" suffix=")" suffixOverrides=",">  
            <if test="id != null">  
                id,  
            </if>  
            <if test="userId != null">  
                user_id,  
            </if>  
            <if test="dealId != null">  
                deal_id,  
            </if>  
            <if test="dealSkuId != null">  
                deal_sku_id,  
            </if>  
            <if test="count != null">  
                count,  
            </if>  
            <if test="createTime != null">  
                create_time,  
            </if>  
            <if test="updateTime != null">  
                update_time,  
            </if>  
        </trim>  

其他

<update id="methodName" parameterType="String/hashmap/int/Long" resultType="hashmap/com.xxxpackage.common.xxxEntity">
	use ${value}
	<if test="xxxFiled!=null and xxx!=null"> xxxFiled=#{xxxFiled}</if>
    <!-- 转义字符 -->
	<![CDATA[<=]]>	
</update>
<!--批量更新接口执行状态 -->
	<update id="xxx" parameterType="java.util.List">
		<foreach collection="list" item="item" index="index" separator=";">
			update xxxTable
			  <set>
			  	 xxx=#{item.xxx}
			  	 <if test="item.executeTime!=null and item.executeTime!=''">
			  	 	,executeTime=#{item.executeTime}
			  	 </if>	
			  </set>
			  where merid=#{item.merid} and interfaceId=#{item.transCode}
		</foreach>
    </update>

    <!--批量对接口配置加锁 -->
    <select id="queryInfoByMeridAndTransCode" parameterType="java.util.List" resultType="hashmap">
    	<foreach collection="list" item="item" index="index" separator=";">
    		select * from gc_transfer_sse_tbl where merid=#{item.merid} and interfaceId=#{item.transCode} FOR UPDATE
    	</foreach>
    </select> 	

    <resultMap type="com.xxxpackage.xxxEntity" id="xxx" autoMapping="true">
    	<id property="id" column="id">
        <result property="field1" column="filed1">		
        <result property="field2" column="filed2">		
        <result property="field3" column="filed3">
        <association property="repos" column="repositoryid" select="com.xxx.pass.ReposMapper.selectById"/>		
        <collection property="belongs" javaType="ArrayList" column="id" ofType="BelongToEntity" select="getUserBelong"/>
        <collection property="functions"  column="id" ofType="com.xxxpackage.entity" javaType="list" >
        	<result column="funcId" property="id"/>
        	<result column="functionCode" property="functionCode"/>
        </collection>	
        <collection property="roles" ofType="RoleEntity" column="{roleId=roleId,appId=selectAppId}" select="com.xxx.union.mapper.RoleMapper.getRoleWithFunctions" />
    </resultMap>


    <select id="getApplicationList" parameterType="hashmap" resultMap="xxx">
    </select>


    <insert id="insertAccount">
    	insert into scm_account_tbl
    	(
    	<foreach collection="namelist" item="item" index="index" separator=",">
           ${item}
    	</foreach>
    	)
    	values(
		<foreach collection="valueList" item="item" index="index" separator=",">
			#{item}
		</foreach>
    	)
    </insert>

    <insert id="updateAccount">
    	update scm_account_tbl set lastUpdateTime=sysdate()
    	<foreach collection="updateList" item="item" index="index" separator="">
    		,${item.filedName}=#{item.fieldValue}
    	</foreach>
    	where customerId=#{customerId}
    </insert>

    
    <select>
    	select 
    	<include refid="Base_Column_List">
    		from uisp_wechat2.scm_event_log
    		where id=#{id,jdbcType=INTEGER}
    </select>

    <insert id="insertSelective" parameterType="xxx.entity">
    	 <trim prefix="(" suffix=")" suffixOverrides=",">
    	 	<if test="content!-null">
    	 		#{content,jdbcType=VARCHAR}
    	 	</if>
    	 </trim>
    </insert> 

    <select id="queryAccount" resultType="com.espresso.commons.AccountEntity">
    	select id ,xxx,
    	<choose>
    		<when test="LongType!=null and LongType=='mobile'" >
    			xxx as xx,
    		</when>	
    		<otherwise>
    			xxx as xxxx,
    		</otherwise>
        </choose>
        aaa from n_user_tbl where 1=1
        <choose>
        	<when test="mobile !=null and mobile !=''">
        		AND mobile=#{mobile}
        	<when>
        	<otherwise>
        		AND userid=#{userid}
        	</otherwise>	
		</choose>        	

    </select>	

    <insert id="insertAccount" parameterType="com.espresso.commons.AccountEntity">
    	<selectKey resultType="long" keyProperty="userId">
    		SELECT LAST_INSERT_ID()
    	</selectKey>
    	...sql	
    </insert>