<1>
<select id="selectUserByConditions" parameterType="user" resultType="user">
SELECT * FROM USER WHERE =
<if test="id != null and id!=''">
and id =#{id}
</if>
<if test="id != null and id!=''">
id =#{id}
</if>
<if test="email != null and email !=''">
and email =#{email}
</if>
<if test="lastName != null and lastName !=''">
and last_name =#{lastName}
</if>
<!-- ognl会进行数字和字符串之间的转换判断""== -->
<if test="gender == 1 or gender == 0">
and gender =#{gender}
</if>
</select>
<2>
<select id="selectUserByConditions" parameterType="user" resultType="user">
SELECT * FROM USER
<!-- where只会去掉第一个and ,如果and在后面的话就会出问题如下
<if test="id != null and id!=''">
id =#{id} and
</if>
<if test="email != null and email !=''">
email =#{email} and
</if>
<if test="lastName != null and lastName !=''">
last_name =#{lastName} and
</if>
<!-- ognl会进行数字和字符串之间的转换判断""== -->
<if test="gender == 1 or gender == 0">
gender =#{gender}
</if>
-->
<where>
<if test="id != null and id!=''">
id =#{id}
</if>
<if test="email != null and email !=''">
and email =#{email}
</if>
<if test="lastName != null and lastName !=''">
and last_name =#{lastName}
</if>
<!-- ognl会进行数字和字符串之间的转换判断""== -->
<if test="gender == 1 or gender == 0">
and gender =#{gender}
</if>
</where>
</select>
<3>前后缀解决
后缀解决
<select id="selectUserByConditions" parameterType="user"
resultType="user">
SELECT * FROM USER
<!-- 后面多出的and或者or where 不能解决
prefix 前缀
prefixOverrides：前缀覆盖，去掉字符串前面多余的字符
suffix：后缀 给拼装的字符串添加一个后缀
suffixOverrides：后缀覆盖
-->
<trim prefix="where" suffixOverrides="and">
<if test="id != null and id!=''">
id =#{id} and
</if>
<if test="email != null and email !=''">
email =#{email} and
</if>
<if test="lastName != null and lastName !=''">
last_name =#{lastName} and
</if>
<!-- ognl会进行数字和字符串之间的转换判断""== -->
<if test="gender == 1 or gender == 0">
gender =#{gender}
</if>
</trim>
</select>
前缀解决
<select id="selectUserByCons" parameterType="user"
resultType="user">
SELECT * FROM USER
<!-- where只会去掉第一个and -->
<trim prefix="where" prefixOverrides="and">
<if test="id != null and id!=''">
and id =#{id}
</if>
<if test="email != null and email !=''">
and email =#{email}
</if>
<if test="lastName != null and lastName !=''">
and last_name =#{lastName}
</if>
<!-- ognl会进行数字和字符串之间的转换判断""== -->
<if test="gender == 1 or gender == 0">
and gender =#{gender}
</if>
</trim>
</select>
<4>
<select id="selectUserByCons" parameterType="user"
resultType="user">
SELECT * FROM USER
<where>
<!-- 只选择其中的一个条件查 -->
<choose>
<when test="id != null and id!=''">
id =#{id}
</when>
<when test="email != null and email !=''">
and email =#{email}
</when>
<when test="lastName != null and lastName !=''">
and last_name =#{lastName}
</when>
<otherwise>
=
</otherwise>
</choose>
</where>
</select>
<5>更新
<update id="updateUserById" parameterType="model.User">
UPDATE user
<set>
<if test="email != null and email !=''">
email =#{email},
</if>
<if test="lastName != null and lastName !=''">
last_name =#{lastName},
</if>
<!-- ognl会进行数字和字符串之间的转换判断""== -->
<if test="gender == 1 or gender == 0">
gender =#{gender}
</if>
</set>
WHERE id=#{id}
</update>
<6>批量查询
<select id="selectUserByIds" resultType="user">
SELECT * FROM USER where id
in
<!-- foreach元素的属性主要有 item，index，collection，open，separator，close。
item表示集合中每一个元素进行迭代时的别名，
index指 定一个名字，用于表示在迭代过程中，每次迭代到的位置， 遍历list的时候index就是list的索引，item就是当前值
遍历map时，index就是key，item当前值
open表示该语句以什么开始， separator表示在每次进行迭代之间以什么符号作为分隔 符，
close表示以什么结束。
如果传入的是单参数且参数类型是一个List的时候，collection属性值为list .
如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array
如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map，实际上如果你在传入参数的时候，
在MyBatis里面也是会把它封装成一个Map的，map的key就是参数名，
所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key. -->
public List<User> selectUserByIds(@Param("ids")List ids);对应collection="ids"
<foreach collection="list" item="item_id" open="(" separator=","
close=")">
#{item_id}
</foreach>
</select>
<7>批量保存
mysql下
<!-- public void addDepts(@Param("emps") List<Dept> emps); -->
<!-- MySQL下批量保存，可以foreach遍历 mysql支持values(),(),()语法 --> //推荐使用
<insert id="addEmpsBatch">
INSERT INTO emp(ename,gender,email,did)
VALUES
<foreach collection="emps" item="emp" separator=",">
(#{emp.eName},#{emp.gender},#{emp.email},#{emp.dept.id})
</foreach>
</insert>
<!-- 这种方式需要数据库连接属性allowMutiQueries=true的支持 --> //在jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true
<!-- <insert id="addEmpsBatch"> 后加上allowMultiQueries=true
<foreach collection="emps" item="emp" separator=";"> 表示可以多次执行insert into语句，中间；不会错
INSERT INTO emp(ename,gender,email,did)
VALUES(#{emp.eName},#{emp.gender},#{emp.email},#{emp.dept.id})
</foreach>
</insert> -->
批量batch保存mybatis ExecutorType.BATCH
Mybatis内置的ExecutorType有3种，默认为simple,该模式下它为每个语句的执行创建一个新的预处理语句，单条提交sql；而batch模式重复使用已经预处理的语句，并且批量执行所有更新语句，
显然batch性能将更优； 但batch模式也有自己的问题，比如在Insert操作时，在事务没有提交之前，是没有办法获取到自增的id，这在某型情形下是不符合业务要求的
Oracle下批量保存
方法1，将多个insert放在begin和end之间执行
begin
insert into user(userno,username,email) values (seq_user.nextval,'','aaaaa');
insert into user(userno,username,email) values (seq_user.nextval,'','bbbbbb');
end;
方法2利用中间表
insert into user(userno,username,email)
select seq_user.nextval,username,email from(
select '' username ,'cccccc' email from dual
union
select '' username,'ddddddddddd' email from dual
union
select '' username,'eeeeeee' email from dual
)
--> <!--示例-->
<!-- public void addUsers(@Param("users") List<User> users);-->
<insert id = "addUsers"databaseId="oracle" parameterType="user">
<!--Oracle:批量保存方法1 -->
<foreach collection = "users" item="user" open="begin" close="end;">
insert into user(userno,username,email)
values (seq_user.nextval,#{user.username},#{user.email});
</foreach> <!--
<!-- Oracle:批量保存方法2 -->
insert into user(userno,username,email)
select seq_user.nextval,username,email from(
<foreach collection="users" item="user" separator="union" >
select #{user.username} username ,#{user.email} email from dual
</foreach>
)
-->
</insert>
<8>两个内置参数
<!-- mybatis的 两个内置参数,_parameter 代表整个参数 单个参数，_parameter 就是这个参数 多个参数 会被封装成一个map，_parameter就是代表这个map
_databaseId 如果配置了databaseIdProvider标签， _databaseId就是代表当前数据库的别名，mysql或者oracle -->
<select id="selectUserByDataBaseId" resultType="user">
<bind name="nameLike" value="'%' + lastName + '%'"/>
<if test="_databaseId=='mysql'">
select * from user
<if test="_parameter!=null">
where last_name=#{_parameter.lastName}
</if>
</if>
<if test="_databaseId=='oracle'">
select * from user where last_name = #{nameLike}
</if>
</select>
<9>sql片段
<sql id="userColumn">
id,email,last_name,gender
</sql>
<select id="selectUser" resultType="user">
select
<include refid="userColumn"/>
from user where id=#{id}
</select>
2.
<sql id="userColumn">
id,email,last_name,gender,${AA}
</sql>
<select id="selectUser" resultType="user">
select
<include refid="userColumn">
<property name="AA" value="depart_id"/>
</include>
from user where id=#{id}
</select>