Mybatis动态sql语句及模糊查询札记

1 单条件模糊查询

• sql中字符串拼接模糊匹配

<select id="getComponentByName" parameterType="String" resultMap="component">
	SELECT * FROM dmp_component WHERE name like CONCAT(CONCAT('%',#{name}),'%')
</select>

• 忽略大小写匹配

<select id="getComponentByName" parameterType="String" resultMap="component">
	SELECT * FROM dmp_component where upper(code_name) like CONCAT(CONCAT(‘%’,upper(#{codeName})),’%’)
</select>

这里稍微岔开话题，Mysql默认的字符检索策略：utf8_general_ci，表示不区分大小写；此情况下，当用户认证系统中若查询字段是关键字时，大小写可能都能登录成功，如下语句返回的结果也是一样。

select * from user where username = 'admin' and password = 'admin';
select * from user where username = 'Admin' and password = 'admin';
select * from user where username = 'ADMIN' and password = 'admin';

因此我们在创建表时，应设置表的collate属性为utf8_general_cs或者utf8_bin；如果已经创建表，则直接修改字段的Collation属性为utf8_general_cs或者utf8_bin。utf8_general_cs表示区分大小写，utf8_bin表示二进制比较，同样也区分大小写 。（Mysql5.6.10版本不支持utf8_genral_cs）

2 多条件查询动态sql语句

多条件查询的要点是判断查询条件是否为空，再拼接sql语句。在mybatis中提供了if标签和where 标签。两种标签的用法如下：

• if标签
  mybatis 中的if标签有些类似于EL表达式的使用，test中可以直接写入类中的属性或者key值。

<select id="selectComponentByNameAndType" resultMap="component"> 
	SELECT 
	* 
	FROM 
	dmp_component 
	WHERE 1=1 
	<if test="name != null and name != ''"> 
		and name = #{name} 
	</if> 
	<if test="type != null and type != ''"> 
		and type = #{type} 
	</if> 
</select> 

• where标签
  where标签 替换了上述代码中的 WHERE 1=1 。 mybatis中的where 标签会判断标签内是否有内容， 如果有内容则自动生成where 并把 where 后面的第一个and 和一个空格，or和一个空格 去掉。

<select id="selectFIleByNameAndType" resultMap="HdfsFile">
   SELECT 
   * 
   FROM
   dmp_hdfs_file
   <where>
	   <if test="name != null and name != ''">
	   	and name = #{name}
	   </if>
	   <if test="type != null and type != ''">
	   	and type= #{type}
	   </if>
   </where>
  </select>

• choose ， when 和 otherwise 标签
  当所有条件不满足时，执行otherwise标签的内容。

<select id="selectComponentCat" resultMap="componentCategory">
   SELECT 
   * 
   FROM
   dmp_component_category
   <where>
   <choose>
    <when test="category_id != null and category_id != ''">
       and category_id = #{categoryId}
    </when>
    <when test="name != null and name != ''">
       and name = #{name}
    </when>
    <otherwise>
      category = #{category}
    </otherwise>
   </choose>
   </where>
  </select>

choose跟 JAVA 中的if-else（也有说switch）效果差不多，是按照条件的顺序进行匹配，当 when 中有条件满足的时候，就会跳出 choose，即所有的 when 和 otherwise 条件中，只有一个会输出，当所有的when条件都不满足时，输出 otherwise 中的内容。所以上述语句的意思非常简单。

• set标签
  set标签常用于update操作，并且会自动过滤其他无关字段

 <update id="updateComponent" >
  UPDATE 
  dmp_component
  <set>
    <if test="codeName != null and codeName != ''">
       codeName = #{codeName }
    </if>
    <if test="platform != null and platform != ''">
       , platform = #{platform}
    </if>
  </set>
  WHERE id = #{id} 
  </update>

• foreach标签
  foreach 用于处理数组或者list集合，下面是一个批量更新List的例子

<update id="updateModel">
    <foreach separator=";" collection="list" item="meta" index="index">
        update dmp_model
        <set>
           expriment_id=#{meta.exprimentId}
        </set>
           where id =#{meta.id}
    </foreach>
</update>

注，如果运用了set语法，mysql的批量更新是要我们主动去配置数据库连接的，连接地址需加上&allowMultiQueries=true
如：

type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/dmp_experiment?characterEncoding=utf-8&allowMultiQueries=true
username: root
password: 123456

批量添加例子如下：

<insert id="insertParameter">
  INSERT INTO 
  dmp_parameter
  (tab, label, value_name, value_type, value_note, value_data) 
  VALUES
  <foreach collection="list" item="meta" separator=","> 
   (#{meta.tab},#{meta.label},#{meta.valueName},#{meta.valueType},#{meta.valueNote},#{meta.valueData})
  </foreach>
</insert>

若参数为数组， 则collection只能为“array” ；参数为List集合，则collection只能为 “list” ，item指代集合中的每一个对象，上述写作meta。separator=”,”的含义是每条数据以 ， 分割。 其他动态语句中有属性 open 和 close， 其含义是指在遍历开始和结束时分别添加其内容。