1.mapper.xml 配置
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.code.mapper.StudentMapper">
<!-- 开启二级缓存 -->
<cache size="1024" flushInterval="6000" readOnly="true" eviction="LRU"></cache>
<!-- 引用另一个映射文件中配置的二级缓存 -->
<!-- <cache-ref namespace="com.code.mapper.UserMapper"/> -->
<resultMap type="StudentBean" id="studentMap">
<id property="id" column="student_id" javaType="int"/>
<result property="name" column="f_name" javaType="java.lang.String"/>
<result property="age" column="f_age" javaType="int"/>
<result property="className" column="f_classes" javaType="java.lang.String"/>
<result property="java" column="f_java" javaType="int"/>
<result property="db" column="f_db" javaType="int"/>
<result property="web" column="f_web" javaType="int"/>
<result property="html" column="f_html" javaType="int"/>
</resultMap>
<!--
if条件:
在条件个数不确定的时候使用
比如根据各科分数进行查询(用户可选择查询哪几个以及分数线大于多少)
传递过来的参数是一个Map,在Map中元素是 <科目,分数>
-->
<select id="findStudentByCondition" parameterType="java.util.Map" resultMap="studentMap">
select * from t_student where 1=1
<if test="java != null">
and f_java >= #{java}
</if>
<if test="web != null">
and f_web >= #{web}
</if>
<if test="db != null">
and f_db >= #{db}
</if>
<if test="html != null">
and f_html >= #{html}
</if>
</select>
<!--
where条件:专门用于查询,可以嵌套if条件使用
1. 自动加上where语句以and/or开头,自动删除第一个and或or
如果
-->
<select id="findStudentByCondition2" parameterType="java.util.Map" resultMap="studentMap">
select * from t_student
<where>
<if test="java != null">
and f_java >= #{java}
</if>
<if test="web != null">
and f_web >= #{web}
</if>
<if test="db != null">
and f_db >= #{db}
</if>
<if test="html != null">
and f_html >= #{html}
</if>
</where>
</select>
<!--
与where功能类似,提供了前缀和后缀的功能
-->
<select id="findStudentByCondition3" parameterType="java.util.Map" resultMap="studentMap">
select * from t_student
<trim prefix="where" prefixOverrides="and">
<if test="java != null">
and f_java >= #{java}
</if>
<if test="web != null">
and f_web >= #{web}
</if>
<if test="db != null">
and f_db >= #{db}
</if>
<if test="html != null">
and f_html >= #{html}
</if>
</trim>
</select>
<!-- 根据Map集合中已有的key来更新数据表,不需要更新全部的数据表 -->
<update id="updateStudentByCondition" parameterType="java.util.Map">
update t_student
<trim prefix="set" suffixOverrides=",">
<if test="java != null">
f_java = #{java},
</if>
<if test="web != null">
f_web = #{web},
</if>
<if test="db != null">
f_db = #{db},
</if>
<if test="html != null">
f_html = #{html},
</if>
</trim>
<if test="id != null">
where student_id = #{id}
</if>
</update>
<!--
set条件:专门用于update语句
当为修改的字段数目不确定时使用
自动加上set,自动去掉最后一个逗号
-->
<update id="updateStudentByCondition2" parameterType="java.util.Map">
update t_student
<set>
<if test="java != null">
f_java = #{java},
</if>
<if test="web != null">
f_web = #{web},
</if>
<if test="db != null">
f_db = #{db},
</if>
<if test="html != null">
f_html = #{html},
</if>
</set>
<if test="id != null">
where student_id = #{id}
</if>
</update>
<!--
主要用于查询in的情况
-->
<select id="findStudentInClass" resultMap="studentMap">
select * from t_student
<if test="classes != null">
<where>
f_classes in
<foreach item="className" collection="classes"
open="(" separator="," close=")">
#{className}
</foreach>
</where>
</if>
</select>
<!--
choose:适用于条件个数不变,但是条件项改变的查询
-->
<select id="findStudentByOneCondition" resultMap="studentMap">
select * from t_student
<choose>
<when test="condition == 'name'">
where f_name like #{value}
</when>
<when test="condition == 'age'">
where f_age = #{value}
</when>
<when test="condition == 'className'">
where f_classes like '%${value}%'
</when>
<otherwise>
where student_id > 0
</otherwise>
</choose>
</select>
<select id="findStudentByOneCondition2" parameterType="java.util.Map" resultMap="studentMap">
select * from t_student
<choose>
<when test="condition == 'name'">
where f_name like #{value}
</when>
<when test="condition == 'age'">
where f_age = #{value}
</when>
<when test="condition == 'className'">
where f_classes like '%${value}%'
</when>
<otherwise>
where student_id > 0
</otherwise>
</choose>
</select>
</mapper>
2.编写 mapper.java
package com.code.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.code.bean.UserBean;
public interface UserMapper {
public int addUser(@Param("user")UserBean user);
public int deleteUserByID(@Param("userID")int userID);
public int updateUserNameByID(@Param("name")String name, @Param("id")int id);
public int updateUserByID(@Param("user")UserBean user,@Param("id")int id);
public UserBean getUserByID(@Param("id")int id);
public List<UserBean> getAllUsers(@Param("colName")String colName);
public List<UserBean> getAllUsersLikeName(@Param("name")String name);
}
当使用 Map 做为参数时，可以用 _parameter.containsKey(变量名)来判 断 map 中是否包含有些变量：
<select id="selectRule" parameterType="Map" resultType="com.ourangel.weixin.domain.Rule">
SELECT ruleId,msgType,event,respId,reqValue,firstRespId,createDate,yn
FROM oal_tb_rule
WHERE yn = 1
<if test="_parameter.containsKey('msgType')">
AND msgType = #{msgType,jdbcType=VARCHAR})
</if>
<if test="_parameter.containsKey('event')">
AND event = #{event,jdbcType=VARCHAR})
</if>
</select>