Mybatis的动态SQL的语句

例子、

    <?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.atguigu.dao.TeacherDao">
<resultMap type="com.atguigu.bean.Teacher" id="teacherMap">
	<id property="id" column="id" />
	<result property="address" column="address" />
	<result property="birth" column="birth_date" />
	<result property="course" column="class_name" />
	<result property="name" column="teacherName" />
</resultMap>

<!--public Teacher getTeacherById(Integer id); -->
<select id="getTeacherById" resultMap="teacherMap">
	select * from t_teacher
	where id=#{id}
</select>

<!-- if:判断 -->
<!--public List<Teacher> getTeacherByCondition(Teacher teacher); -->
<select id="getTeacherByCondition" resultMap="teacherMap">
	select * from t_teacher
	<!-- test="":编写判断条件 id!=null:取出传入的javaBean属性中的id的值,判断其是否为空 -->
	<!-- where可以帮我们去除掉前面的and; -->

	<!-- trim:截取字符串 
		prefix="":前缀;为我们下面的sql整体添加一个前缀 
		prefixOverrides="": 取出整体字符串前面多余的字符 
		suffix="":为整体添加一个后缀 
		suffixOverrides="":后面哪个多了可以去掉; -->
	<!-- 我们的查询条件就放在where标签中;每个and写在前面,
		where帮我们自动取出前面多余的and -->
	<trim prefix="where" prefixOverrides="and" suffixOverrides="and">
		<if test="id!=null">
			id > #{id} and
		</if>
		<!-- 空串 "" and; && or: ||; if():传入非常强大的判断条件;
		OGNL表达式;对象导航图
			Person
				===lastName
				===email
				===Address
					===city
					===province
					===Street
						===adminName
						===info
						===perCount
		方法、静态方法、构造器。xxx
		在mybatis中,传入的参数可以用来做判断;
		额外还有两个东西;
		_parameter:代表传入来的参数;
			1)、传入了单个参数:_parameter就代表这个参数
			2)、传入了多个参数:_parameter就代表多个参数集合起来的map
		_databaseId:代表当前环境
			如果配置了databaseIdProvider:_databaseId就有值
			
		 -->
		<if test="name!=null &amp;&amp; !name.equals(&quot;&quot;)">
			teacherName like #{name} and
		</if>
		<if test="birth!=null">
			birth_date &lt; #{birth} and
		</if>
	</trim>
</select>

<!-- public List<Teacher> getTeacherByIdIn(List<Integer> ids); -->
<select id="getTeacherByIdIn" resultMap="teacherMap">
	
	SELECT * FROM t_teacher WHERE id IN
	<!-- 帮我们遍历集合的; collection="":指定要遍历的集合的key 
	close="":以什么结束 
	index="i":索引; 
		如果遍历的是一个list; 
			index:指定的变量保存了当前索引 
			item:保存当前遍历的元素的值 
		如果遍历的是一个map: 
			index:指定的变量就是保存了当前遍历的元素的key 
			item:就是保存当前遍历的元素的值
	item="变量名":每次遍历出的元素起一个变量名方便引用 
	open="":以什么开始 
	separator="":每次遍历的元素的分隔符 
		(#{id_item},#{id_item},#{id_item} -->
	<if test="ids.size >0">
		<foreach collection="ids" item="id_item" separator="," open="("
			close=")">
			#{id_item}
		</foreach>
	</if>
</select>

<!--public List<Teacher> getTeacherByConditionChoose(Teacher teacher); -->
<select id="getTeacherByConditionChoose" resultMap="teacherMap">
	select * from t_teacher
	<where>
		<choose>
			<when test="id!=null">
				id=#{id}
			</when>
			<when test="name!=null and !name.equals(&quot;&quot;)">
				teacherName=#{name}
			</when>
			<when test="birth!=null">
				birth_date = #{birth}
			</when>
			<otherwise>
				1=1
			</otherwise>
		</choose>


     实例、

    <mapper namespace="com.apcstudy.mybatis.dao.TeacherDao">

<resultMap id="teacherMap" type="com.apcstudy.mybatis.bean.Teacher">
	<id property="id" column="id" />
	<result property="name" column="name"/>
	<result property="course" column="course" />
	<result property="address" column="address" />
	<result property="birth" column="birth" />
</resultMap>

<select id="getTeacherById" parameterType="integer" resultMap="teacherMap">
	select * from t_teacher
	where id=#{id}
</select>



<select id="getTeacherByCondition01" resultMap="teacherMap">
	  select * from t_teacher
	  where 1=1
	  <if test="name!=null and name!=‘‘ ">
		and name=#{name}
	  </if>
	  <if test="course!=null and course!=‘‘ ">
		and course=#{course}
	  </if>
</select>


<select id="getTeacherByCondition02" resultMap="teacherMap">
	select * from t_teacher
	<where>
		<if test="name!=null and name!=‘‘ ">
			name=#{name}
		</if>
		<if test="course!=null and course!=‘‘ ">
			and course=#{course}
		</if>
	</where>
</select>

<select id="getTeacherByCondition03" resultMap="teacherMap">
	select * from t_teacher
	<trim prefix="where" prefixOverrides="and" suffixOverrides="and">
		<if test="name!=null and name!=‘‘ ">
			name=#{name} and
		</if>
		<if test="course!=null and course!=‘‘ ">
			course=#{course} and
		</if>
	</trim>
</select>

<update id="updateTeacher">
	update t_teacher
	<set>
		<if test="name!=null and name!=‘‘ ">
			name=#{name},
		</if>
		<if test="course!=null and course!=‘‘ ">
		   course=#{course},
		</if>
	    <if test="address!=null and address!=‘‘ ">
			address=#{address},
		</if>
	    <if test="birth!=null and birth!=‘‘ ">
			birth=#{birth}
		</if>
	</set>
	<where>
		id=#{id}
	</where>
</update>

<select id="getTeacherByIdIn" resultMap="teacherMap">
	select * from t_teacher where id in
	<if test="ids!=null">
		<foreach collection="ids" item="id" separator="," open="(" close=")">
			#{id}
		</foreach>
	</if>
</select>

<select id="getTeacherByConditionChoose" resultMap="teacherMap">
	select * from t_teacher
	<where>
		<choose>
			<when test="name!=null and name!=‘‘ ">
				name=#{name}
			</when>
			<when test="course!=null and course!=‘‘">
				course=#{course}
			</when>
			<when test="address!=null and address!=‘‘">
				address=#{address}
			</when>
			<otherwise>
				1=1
			</otherwise>
		</choose>
	</where>
</select>

Mybatis的动态SQL的语句

上一篇:Windows 远程时提示CredSSP 加密数据库修正 问题的简单处理.


下一篇:MySQL性能压力基准测试工具sysbench