Mybatis中xml常用标签及代码块

Mybatis xml常用代码块

映射返回对象:

	<resultMap type="com.test.security.pojo.TbArticle" id="getTbArticle">
		<id property="aId" column="a_id" />
        <result property="title" column="title" />
	</resultMap>
1对1：
	<resultMap id="orderUserResultMap" type="com.cloud.mybaits.pojo.OrdersCustomer">
        <!--property：实体类属性;column：orders表的主键-->
        <id property="id" column="id"></id>
        <result property="number" column="number"></result>
        <result property="createtime" column="createtime"></result>
        <association property="user" javaType="com.cloud.mybaits.pojo.User">
            <id property="id" column="userId"></id>
            <result property="username" column="username"></result>
            <result property="address" column="address"></result>
        </association>
    </resultMap>
    
1对多:
	<resultMap type="com.test.security.pojo.OrderInfo" id="OrderInfoMape">
		<id property="oId" column="o_id" />
        <result property="goodsId" column="goods_id" />
        <result property="uId" column="u_id" />  
        <result property="salenum" column="salenum" />
        
        <collection property="tbAddress" ofType="com.test.security.pojo.TbAddress">
            <id property="addressId" column="address_id" />
	        <result property="userAddid" column="user_addid" /> 
        </collection>
	</resultMap>

传入一个参数：

Mapper接口中方法的定义：
	List<TbArticle> getArticle(@Param("msg")Integer msg);
xml:
    <select id="getArticle" parameterType="java.lang.Integer" resultMap="getTbArticle" >		
		SELECT 	*		 
			FROM 
			litchi_sys.tb_article 
		<where>
			<if test="msg != null and msg != ''">
				ac_anumber = #{msg,jdbcType=INTEGER}
			</if>
		</where>	
	</select>

传入两个参数：

Mapper接口中方法的定义：
	List<TbArticle> getArticleByCate(Integer acAnumber, Integer page);
xml:
	<select id="getArticleByCate"  resultMap="getTbArticle" >		
		SELECT 	*		 
			FROM 
			litchi_sys.tb_article 
				where  ac_anumber = #{arg0} limit #{arg1},5
	</select>

传入对象跟散的参数：

Mapper接口中方法的定义：
	List<OrderInfo> onLoadOrder(@Param("tbOrder")TbOrder tbOrder,@Param("page")Integer page,@Param("pagesize")Integer pagesize);
xml:
  	<select id="onLoadOrder"  resultMap="OrderInfoMape" >						
		SELECT *
			FROM 
			tb_order tor JOIN tb_goods tg ON tor.goods_id = tg.g_id) o1
			ON ta.address_id = o1.ads_id   where  o1.ispay =#{tbOrder.ispay}
			<if test="tbOrder.uId != null">
				and o1.u_id =#{tbOrder.uId}
			</if>
			<if test="tbOrder.ishandle != null and tbOrder.ishandle !=-1">
				and o1.ishandle =#{tbOrder.ishandle}
			</if>
			<if test="tbOrder.isreceive != null and tbOrder.isreceive !=-1">
				and o1.isreceive =#{tbOrder.isreceive}
			</if>
			order by o1.ordertime DESC limit #{page},#{pagesize}
	</select>

传入对象：

Mapper接口中方法的定义：
	List<OrderInfo> onLoadShopCart(TbOrder tbOrder);
xml:
    <select id="onLoadShopCart" parameterType="com.test.security.pojo.TbOrder" resultMap="OrderInfoMape" >						
		SELECT *
			FROM 
	tb_order tor JOIN tb_goods tg ON tor.goods_id = tg.g_id WHERE u_id=#{uId} AND ordertime=#{ordertime}) o1
	ON ta.address_id = o1.ads_id
	</select>

插入后得到数据返回的id：

Mapper接口中方法的定义：
	Integer addShopCarReturnId(TbOrder tbOrder);
xml:
	<insert id="addShopCarReturnId" parameterType="com.test.security.pojo.TbOrder"  useGeneratedKeys="true" keyProperty="oId">    
		INSERT INTO litchi_sys.tb_order 
			(goods_id, u_id, weight,ordertime)
			VALUES
			(#{goodsId},#{uId},#{weight},#{ordertime});
  	</insert>

传入list数组：

Mapper接口中方法的定义：
	List<OrderInfo> onLoadOrderInfo(List<Integer> cds);	
xml:
	<select id="onLoadOrderInfo" parameterType="java.util.List" resultMap="OrderInfoMape" >						
		SELECT *
			FROM 
		tb_order tor JOIN tb_goods tg ON tor.goods_id = tg.g_id where o_id in
			<foreach collection="list" index="index" item="item" open="("
				separator="," close=")">
				#{item}
			</foreach>
	</select>

假设查询person表，参数类型为XXXVo，XXXVo中包含一个List对象，保存了状态列表，此时可以参考如下查询

	<select id="queryXXX" parameterType="XXXVo" resultMap="XXXResult">
		select *
		from person
		 WHERE 1=1
		<if test="statusFilter != null and statusFilter.size() > 0">
            and status in
            <foreach collection="statusFilter" item="statusId" index="i" open="(" close=")" separator=",">
                #{statusId}
            </foreach>
        </if>
		 ORDER BY DEPTID
	</select>