一、什么是动态SQL呢?
就是where后面的条件不是确定的 根据用户的选择动态产生的sql就是动态sql。
1.1 动态sql之查询 select
<select id="selectlike" parameterType="map" resultType="app_dynamicSql.User">
select * from t_user
<where>
<if test="pid!=null">
and id = #{pid}
</if>
<if test="pname!=null">
userName like #{pname}
</if>
</where>
</select>
1.2动态sql之更新 update
update t_user set name = ? password = ?;
<update id="" parameterType="Map">
update t_user
<set>
<if test="pid!=null">
id = #{pid},
</test>
<if test="pname!=null">
name = #{name},
</test>
</set>
where id = #{pid}
</update>
1.3 动态sql之删除 delete 批量删除
foreach 属性详解
collection 表示参数类型 比如是数组或者集合
open 表示以神符号开始
close 表示以什么符号结束
separator 表示分隔符
item 表示要遍历的名称
<!-- 动态删除之集合版本 -->
<delete id="dynaDeleteList">
delete from t_user where id in
<foreach collection="list" open="(" close=")" separator="," item="ids" >
#{ids}
</foreach>
</delete>
<!--动态删除之数组版本-->
<delete id="dynaDeleteArray">
delete from t_user where id in
<foreach collection="参数类型" open="(" close=")" separator="," item="最好是形参名字">
#{最好是形参的名字}
</foreach>
</delete>
1.4 动态sql之插入 inert
<sql id="key">
<trim suffixOverrides=",">
<if test="id!=null">
id,
</if>
...
</trim>
</sql>
<sql id="value">
<trim suffixOverrides=",">
<if test="id!=null">
#{id},
</if>
...
</trim>
</sql>
<insert id="dynaInsert" parameterType="参数类型">
insert into t_user(<include refid="key"></include>) values(<include refid="value"></include>)
</insert>
XML配置文件:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mappe