Mybatis动态SQL，Mybatis解决JDBC问题之一：SQL语句参数固定

Mybatis动态SQL

• 定义：根据不同条件拼接 SQL 语句，实现对数据库更准确的操作

• 实现：映射器配置文件或者注解

• 常用的动态SQL元素

if元素

判断语句，单条件分支

• 语法

  < if test =”条件”> 满足条件的语句 </ if>

• 注意

  拼接 SQL 语句的时候注意 AND 和逗号

需求：通过班级或性别查询

分析：通过不同的属性查找sql语句的条件也不同，此时就可以用动态sql来解决这个问题

Mapper.java

public List<Student> selectStu(Student s);

Mapper.xml

 <select id="selectStu" parameterType="Student" resultType="Student">
 	select * from student where 1=1
	<if test="ssex != null">
	 	and ssex = #{ssex}
	</if>
	<if test="classid != 0">
	 	and classid = #{classid}
	</if>
</select>

如果if中的条件(ssex !=null)成立，就会把and ssex = #{ssex}拼接到sql语句中，

同理如果if中的条件(classid !=0)成立，就会把and classid = #{classid}拼接到sql语句中

存在问题：where的作用是筛选每一条数据，如果ssex和classid都没传值，sql语句就做一全查，后面的1=1每次都要执行一次，造成效率降低，解决–> where元素

where元素

where 元素只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入

“WHERE”子句。而且，若语句的开头为“AND”或“OR”，where 元素也会将它们去除

• 语法

<where>
	<if test =”条件”> 满足条件的语句 </if>
</where>

Mapper.java

public List<Student> selectStuwhere(Student s);

Mapper.xml

<select id="selectStuwhere" parameterType="Student" resultType="Student">
		<!-- where作用
				1.如果有条件，自动加where，没有则不加where
				2.自动去掉第一个条件的and
		-->
		select * from student
		<where>
			<if test="ssex !=null"> 
				and ssex=#{ssex}
			</if>
			<if test="classid != 0"> 
				and classid = #{classid}
			</if>
		</where>
</select>

choose、when、othewise元素

• 语法：

  <choose> 
  	<when test=“条件”>满足条件的语句</ when>
  	<otherwise> 满足其他条件的语句 <otherwise>
  </choose>
  

Mapper.java

public List<Student> selectStuChoose(Student s);

Mapper.xml

<select id="selectStuChoose" parameterType="Student" resultType="Student">
	select * from student 
	<where>
		<choose>
			<when test="sid != 0"> and sid =#{sid}</when>
			<when test="sname != null"> and sname =#{sname}</when>
			<when test="birthday != null"> and birthday =#{birthday}</when>
			<when test="ssex != null"> and ssex =#{ssex}</when>
			<when test="classid != 0"> and classid =#{classid}</when>
			<otherwise>sid = (select max(sid) from student)</otherwise>
		</choose>
	</where>
</select>

trim元素

万能标签

• 语法：
  1. prefix：
  2. prefixOverrides：
  3. suffix：
  4. suffixOverrides：

<trim prefix ="" prefixOverrides=""suffix="" suffixOverrides ="">
    
</trim>

• 使用trim实现where标签的效果

Mapper.java

public List<Student> findStuTrim(Student s);

Mapper.xml

<select id="findStuTrim" parameterType="Student" resultType="Student">
	 	select * from student 
	 	<trim prefix="where" prefixOverrides="and" >
	 		<if test="ssex != null"> and ssex = #{ssex}</if>
	 		<if test="classid != 0"> and classid = #{classid}</if>
	 	</trim>
</select>

• 使用trim实现添加

Mapper.java

public int insertStuTrim(Student s);

Mapper.xml

<insert id="insertStuTrim" parameterType="Student">
		insert into student
		<trim prefix="(" suffix=")" suffixOverrides=",">
			<if test="sname != null"> sname, </if>
			<if test="birthday != null"> birthday,</if>
			<if test="ssex != null"> ssex,</if>
			<if test="classid != 0"> classid,</if>
		</trim>
		values
		<trim prefix="(" suffix=")" suffixOverrides=",">
			<if test="sname != null">#{sname},</if>
			<if test="birthday != null">#{birthday},</if>
			<if test="ssex != null">#{ssex},</if>
			<if test="classid != 0">#{classid},</if>
		</trim>
</insert> 

set元素

set 标签元素主要是用在更新操作的时候，它的主要功能和 where 标签元素其实是差不多的，主要是在包含的语句前输出一个 set，然后如果包含的语句是以逗号结束的话将会把该逗号忽略，如果 set 包含的内容为空的话则会出错。有了 set 元素就可以动态的更新那些修改了的字段

• 语法

<set>
	<if test =”条件”> 满足条件的语句 </if>
</set>

Mapper.java

public int updateStuSet(Student s);

Mapper.xml

<update id="updateStuSet" parameterType="Student">
		update student
		<set>
			<if test="sname != null"> sname = #{sname},</if>
			<if test="birthday != null"> birthday = #{birthday},</if>
			<if test="ssex != null"> ssex = #{ssex},</if>
			<if test="classid != 0"> classid =#{classid},</if>
		</set>
		<where> sid=#{sid} </where>
</update>

foreach元素

适用于参数为数组或集合

• 语法
  1. item:循环中的当前元素
  2. index:当前循环元素的位置下标
  3. collection:方法传递的参数，一个数组或者集合
  4. open:以什么符号开始将这些集合元素包装起来
  5. close:以什么符号结束将这些集合元素包装起来；
  6. separator:各个元素的间隔符号

<foreach item = “”index=“” collection=“” open=“” separator=“” close=“”>
    
</foreach>

• 批量删除：参数传入一个数组

Mapper.java

public int deleteStuForeach(int[] arr);

Mapper.xml

<delete id="deleteStuForeach" >
	delete from student
	<where>
		<foreach collection="array" item="x" open="sid in (" close=")" separator="," >
			#{x}
		</foreach>
	</where>
</delete>

参数是一个数组，collection中填写array

• 批量插入：参数传入一个集合

Mapper.java

public int insertStuList(List<Student> slist);

Mapper.xml

<insert id="insertStuList">
	insert into student(sname,birthday,ssex,classid) 
	<foreach collection="list" item="x" open="values" separator="," >
			(#{x.sname},#{x.birthday},#{x.ssex},#{x.classid})
	</foreach>
</insert>

参数是一个集合，collection中填写list，传入是一个对象，属性用名字.属性的方式传

模糊查询

需求：通过姓名进行模糊查询

Mapper.java

public List<Student> selectStuLikeSname(String s);

Mapper.xml

<select id="selectStuLikeSname" parameterType="String" resultType="Student">
	 	<!-- 方式一 -->
	 	<!-- select * from student where sname like #{v} -->
	 	<!-- 方式二 -->
	 	<!-- select * from student where sname like concat("%",#{v},"%") -->
	 	
	 	<!-- 方式三 
	 		#{} 和 ${} 区别:	
	 	-->
	 	<!-- select * from student where sname like "%${v}%" -->
	 	
	 	<!-- 方式四 -->
	 	<!-- select * from student where sname like "%"#{v}"%" -->
	 	
	 	<!-- 方式五  推荐 -->
	 	<bind name="x" value="'%'+_parameter+'%'"/>
	 	select <include refid="stusql"></include> from student where sname like #{x}
</select>

#{}和${}的区别

• #{} 防止 SQL 注入的占位符，防止sql注入的
• ${} 字符串的替换，不能防止sql注入

bind元素

• 作用

定义变量，然后将输入参数中的值拼接一些其他字符串后组成的字符串赋值给该变量

• 语法
  1. name：自定义变量的变量名
  2. value：自定义变量的变量值
  3. _parameter：传递进来的参数

<bind   name="" value="_parameter"> </bind>

需求：通过名字进行模糊查询

Mapper.java

public List<Student> selectStuLikeSname(String s);

Mapper.xml

<select id="selectStuLikeSname" parameterType="String" resultType="Student">
    <bind name="x" value="'%'+_parameter+'%'"/>
    select * from student where sname like #{x}
</select>

推荐使用bind定义变量的方式进行模糊查询

sql、include元素

sql元素用来写sql片段，include元素调用sql片段，两个搭配使用

• 语法
  1. sql中的id：唯一标识
  2. include中的refid：sql中的sid

<!-- sql片段-->
<sql id=""> </sql>
<!-- 调用sql片段 -->
<include refid=""></include>

用sql片段进行查询代码就可以这样写

Mapper.java

public List<Student> selectStuwhere(Student s);

Mapper.xml

<sql id="stusql">select sname,birthday,ssex,classid from student</sql>
<select id="selectStuwhere" parameterType="Student" resultType="Student">
		<include refid="stusql"></include>
		<where>
			<if test="ssex !=null"> and ssex=#{ssex}</if>
			<if test="classid != 0"> and classid = #{classid}</if>
		</where>
</select>