mybatis 复杂查询

1、定义sql语句，减少我们经常手写的字段

例子：     select id,name,age from user

可以写成  <sql id="col"> id,name,age</sql>,select <include refid="col" /> from user

这样以后如果用到上面的字段或者修改col中某个字段，我们只需要修改col就好，不需要每条sql语句都修改。

 

2、条件判断

<select id="findList" resultType="java.util.Map" parameterType="java.util.Map">
	  SELECT job_id,job_name,job_status,job_cron,job_class,job_method,init_status FROM qurta_setting
	  <where>
			<if test="job_name != null and job_name != ''">
			     AND job_name like  CONCAT('%',#{job_name}, '%')
			</if> 
	  </where>
	  <choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
            <otherwise>
				ORDER BY job_id DESC
			</otherwise>
		</choose>
	</select>

3、复杂的map查询，结构为Map<String,List<String>>

additional  就是上面map的名字，

<sql id="get_account_id" >
		<if test="additional != null">
			<foreach collection="additional" index="key"  item="val" open=" and " separator=" and ">
			     <if test="key == '不包含'">
	                 <foreach collection="val"  item="_param" separator="and" open="(" close=")" >  
		                 <if test="key == '不包含'">
		                     locate('${_param}',c.account_name)=0 
		                 </if>
	                 </foreach>  
	             </if>
	             <if test="key != '不包含'">
	                 <foreach collection="val"  item="_param" separator="or" open="(" close=")" >  
		                 <if test="key == '包含'">
		                     locate('${_param}',c.account_name)>0 
		                 </if>
		                 <if test="key == '等于'">
		                     c.account_name='${_param}' 
		                 </if>
		                 <if test="key == '开始是'">
		                     locate('${_param}',c.account_name)=1 
		                 </if>
		                 <if test="key == '结尾是'">
		                     locate(reverse('${_param}'), reverse(c.account_name) )=1 
		                 </if>
		             </foreach>
	             </if>
		    </foreach>
		</if>
	  </sql>