Mybatis的动态SQL的语句

本文主要是介绍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的语句的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！