mybatis 多表联查,支持分页

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/qq_24192465/article/details/79970598

Mapper

    <select id="queryShopList" resultMap="shopMap">
		SELECT
		shop_id,
		owner_id,
		area_id,
		shop_category_id,
		parent_category_id,
		shop_name,
		shop_desc,
		shop_addr,
		phone,
		shop_img,
		longitude,
		latitude,
		priority,
		create_time,
		last_edit_time,
		enable_status,
		advice
		FROM
		tb_shop
		<where>
			<if test="shopCondition.shopId!=null">
				and shop_id = #{shopCondition.shopId}
			</if>
			<if
				test="shopCondition.shopCategory!=null
				 and shopCondition.shopCategory.shopCategoryId!=null">
				and shop_category_id =
				#{shopCondition.shopCategory.shopCategoryId}
			</if>
			<if
				test="shopCondition.parentCategory!=null
				 and shopCondition.parentCategory.shopCategoryId!=null">
				and parent_category_id =
				#{shopCondition.parentCategory.shopCategoryId}
			</if>
			<if
				test="shopCondition.area!=null
				 and shopCondition.area.areaId!=null">
				and area_id =
				#{shopCondition.area.areaId}
			</if>
			<!-- 写like语句的时候 一般都会写成 like '% %' 在mybatis里面写就是应该是 like '%${name} %' 而不是 
				'%#{name} %' ${name} 是不带单引号的,而#{name} 是带单引号的 -->
			<if test="shopCondition.shopName!=null">
				and shop_name like '%${shopCondition.shopName}%'
			</if>
			<if test="shopCondition.enableStatus!=null">
				and enable_status = #{shopCondition.enableStatus}
			</if>
		</where>
		ORDER BY
		priority DESC
		LIMIT #{rowIndex},#{pageSize};
	</select>
	<select id="queryShopCount" resultType="int">
		SELECT
		count(1)
		FROM
		tb_shop
		<where>
			<if test="shopCondition.shopId!=null">
				and shop_id = #{shopCondition.shopId}
			</if>
			<if
				test="shopCondition.shopCategory!=null
				 and shopCondition.shopCategory.shopCategoryId!=null">
				and shop_category_id =
				#{shopCondition.shopCategory.shopCategoryId}
			</if>
			<if
				test="shopCondition.parentCategory!=null
				 and shopCondition.parentCategory.shopCategoryId!=null">
				and parent_category_id =
				#{shopCondition.parentCategory.shopCategoryId}
			</if>
			<if
				test="shopCondition.area!=null
				 and shopCondition.area.areaId!=null">
				and area_id =
				#{shopCondition.area.areaId}
			</if>
			<!-- 写like语句的时候 一般都会写成 like '% %' 在mybatis里面写就是应该是 like '%${name} %' 而不是 
				'%#{name} %' ${name} 是不带单引号的,而#{name} 是带单引号的 -->
			<if test="shopCondition.shopName!=null">
				and shop_name like '%${shopCondition.shopName}%'
			</if>
			<if test="shopCondition.enableStatus!=null">
				and enable_status = #{shopCondition.enableStatus}
			</if>
		</where>
	</select>


Dao

	/**
	 * 分页查询店铺,可输入的条件有:店铺名(模糊),店铺状态,店铺Id,店铺类别,区域ID
	 * 
	 * @param shopCondition
	 * @param rowIndex
	 * @param pageSize
	 * @return
	 */
	List<Shop> queryShopList(@Param("shopCondition") Shop shopCondition,
			@Param("rowIndex") int rowIndex, @Param("pageSize") int pageSize);

	/**
	 * 返回queryShopList总数
	 * 
	 * @param shopCondition
	 * @return
	 */
	int queryShopCount(@Param("shopCondition") Shop shopCondition);

阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页