场景:数据库三张表,article, tag, article_tag_relation。
要求通过标签(tag)进行文章的查询,三个查询条件:交,并,非。ComplexQuery:List<Long> andTagIds; List<Long> orTagIds; List<Long> notTagIds;
sqlmap的select如下,通过sql搞定,不过性能堪忧,以后有机会用一些其他的工具进行优化吧。
<select id="listArticleIdsByQuery" resultClass="java.lang.Long" parameterClass="ComplexQuery">
select distinct(article_id) from article_tag_relation where 1 = 1
<dynamic>
<isNotEmpty prepend="and" property="andTagIds">
<iterate conjunction="and" property="andTagIds">
article_id in (select distinct(article_id) from article_tag_relation where tag_id = #andTagIds[]#)
</iterate>
</isNotEmpty>
<isNotEmpty prepend="and" property="notTagIds">
<iterate conjunction="and" property="notTagIds">
article_id not in (select distinct(article_id) from article_tag_relation where tag_id = #notTagIds[]#)
</iterate>
</isNotEmpty>
<isNotEmpty prepend="and" property="orTagIds">
<iterate conjunction="," open="tag_id in (" close=")" property="orTagIds">
#orTagIds[]#
</iterate>
</isNotEmpty>
</dynamic>
limit #startRow#,#pageSize#
</select>