很常用却又容易忘的一些sql知识

几种数据库的模糊查询语法

<if test="name != null and name != ''">  
AND a.name LIKE   
<if test="dbName == 'oracle'">'%'||#{name}||'%'</if>  
<if test="dbName == 'mssql'">'%'+#{name}+'%'</if>  
<if test="dbName == 'mysql'">concat('%',#{name},'%')</if>  
</if>  

mybatis批量插入：

  dao层：Integer insertBatch(List <Entity> list);

xml:

 <insert id ="insertBatch" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
    insert into dbName
      (数据库表的字段1，字段2..字段n)
    values  
 <foreach collection="list" item="item" index="index" separator=",">
    (
     #{item.Entity(实体里面定义的字段1)},
     #{item.Entity(字段2)},
     #{item.Entity(字段n)}
    )
  </foreach>
 </insert>

mybatis批量删除

dao层 int deleteBatch(@Param("ids") List<BigInteger> ids);

xml：

    <delete id="deleteBatch">
	DELETE FROM DBname(数据库表名字) WHERE id IN
	<foreach item="item" collection="ids" open="(" separator="," close=")">
	#{item}
	</foreach>
    </delete>