mybatis常用查询技巧1

好久没有写csdn博客，在正文之前先扯蛋几句，很多时候坚持一件事情是很有价值同时也是很困难的事情，像我好像就停止更新csdn博客有些时间了，现在想想我的意志力还是没有达到那个程度，这次回归希望努力坚持。

1.介绍一下mybatis查询常用一些场景语句

使用场景：我们经常会遇到这样一种情况，比如我们做文章或者评论的时候会遇到每天只能点赞一次，此时，更具客户端ip作为独立条件，然后在更具时间进行判断，而数据库时间经常要存精确到时分秒的，所以我们可以用这样的一个语句解决这样一个查询，查询今天是否已经点过赞

	<select id="selectTodaySignScores" resultType="int">
		select scores from t_score_log 
			where (scoreName = '活动名' 或者 scoreId="活动id")
			and userId = #{userId}
			and year(createdTime)=year(now()) 
			and month(createdTime)=month(now()) 
			and day(createdTime)=day(now()) limit 1
	</select>

2.介绍一下mybatis查询中的复合查询

使用场景：我们要在页面上展示某个分类下的一些文章，同时需要显示文章和分类，这时候可以根须需要限制分类下文章的数量和不限制该分类下的文章数分为以下两种情况

（1）限制该分类下的文章数量

	<select id="selectAccurateThemeBannerAndTopicsNew" resultMap="getAccurateThemeBannerNewMap">
		select id,name,short_name as shortName,logo,shelve,del_flag as delFlag from docard_favor_attr where is_accurate_theme_banner_show=#{isAccurateThemeBannerShow}
		and parent_id=#{parentId} and shelve=#{shelve} and del_flag = #{DEL_FLAG_NORMAL} order by sort limit 3
	</select>
	<resultMap type="FavorAttr" id="getAccurateThemeBannerNewMap">
		<id property="id" column="id" />
		<result property="name" column="name"/>
		<result property="shortName" column="shortName" />
		<result property="logo" column="logo" />
		<result property="shelve" column="shelve"/>
		<result property="delFlag" column="delFlag"/>
		<collection property="topicList" select="selectAccurateThemeBannerTopic" column="{themeId = id, topicShelve = shelve,topicDelFlag=delFlag}">
		</collection>
	</resultMap>
	<select id="selectAccurateThemeBannerTopic" resultType="FavorTopic">
		select a.id as id,a.headline as headline,b.short_name as bankShortName from docard_favor_topic a,docard_favor_attr b where a.shelve=#{topicShelve}
		and a.del_flag=#{topicDelFlag} and a.theme_pop_position < 0 and a.theme_id=#{themeId} and a.bank_id=b.id order by publish_time desc limit 5
	</select>

（2）不限制该分类下的文章数量

	<resultMap type="FavorAttr" id="getAccurateThemeBannerMap">
		<id property="id" column="id"/>
		<result property="name" column="name"/>
		<result property="shortName" column="short_name"/>
		<result property="logo" column="logo"/>
		<collection property="topicList" javaType="FavorTopic">
			<id property="id" column="topic_id"/>
			<result property="headline" column="topic_headline"/>
			<result property="bankShortName" column="topic_bank_short_name"/>
		</collection>
	</resultMap>
	<select id="selectAccurateThemeBannerAndTopics" resultMap="getAccurateThemeBannerMap">
		select a.id as id,a.name as name,a.short_name as short_name,a.logo as logo,b.id as topic_id,
		b.headline as topic_headline,c.short_name as topic_bank_short_name 
		from docard_favor_attr a,docard_favor_topic b,docard_favor_attr c
		where a.is_accurate_theme_banner_show=#{isAccurateThemeBannerShow} and a.parent_id=#{parentId} and b.theme_id=a.id and c.id = b.bank_id 
		and b.shelve = #{shelve} and b.del_flag = #{DEL_FLAG_NORMAL} and b.theme_pop_position < 0
		order by b.publish_time desc
	</select>

希望以上两种mybatis提到的常用查询可以帮助到你们。还有就是最近还在学习mybatis常用的分页插件，等深入学习一些的时候，知道怎么更好分享的时候再拿出来与大家分享。希望学习技术，总结技术的心一直都在。加油！