mybatis - 动态sql使用小结

什么是动态sql？

• 动态sql: 简单来说就是sql的内容是变化的，可以根据条件获取到不同的sql语句。
• 主要是sql语句中的where部分发生变化。
• 动态sql的实现，使用的是mybatis提供的标签<if> ,<where>,<foreach>...等

< if >

1. 简介

   < if >是用于条件判断的
   if：当条件为true，就会吧if之间的sql加入到主sql之后

2. 语法

   	 <if test="判断java对象的属性值(逻辑表达式)">
   			部分sql语句
   	 </if>
   

< choose >

1. 简介

   < choose >也是用于条件判断的

2. 语法

   <choose>
   	<when test="逻辑表达式">
   		sql语句
   	</when>
   	<when test="逻辑表达式">
   		sql语句
   	</when>
   	<otherwise>	<!--otherwise相当于if..else if.. else中的else-->
   		sql语句
   	</otherwise>
   </choose>
   

3. 举个栗子

   	<select id="findCustomerByNameOrJobsOfChoose" parameterType="com.zero.po.Customer" resultType="com.zero.po.Customer">
   			select * from t_customer
   			<where>
   				<choose>
   					<when test="username!=null and username!=''">
   						usernamelikeconcat('%',#{username},'%')
   					</when>
   					<when test="jobs!=null and jobs!=''">
   						and jobs=#{jobs}
   					</when>
   					<otherwise>
   						and 1=1
   					</otherwise>
   				</choose>
   			</where>
   	</select>
   

< where >

1. 简介

   < where >用来包含 多个< if >的， 当多个if有一个成立时， < where >会自动增加一个where关键字，并去掉 if中多余的 and ，or等。

2. 举个栗子

   	<select id="selectStudentsIfWhere" resultType="com.zero.domain.Student">
   		select * from student
   		<where>
   			<if test="name!=null and name!='' ">
   				name = #{name}
   			</if>
   			<if test="age>0">
   				or age > #{age}
   			</if>
   		</where>
   	</select>
   

< trim >

1. 简介

   < trim >标签一般用来取出sql语句中多余的and关键字，逗号。
   或者SQL语句前拼接“where”、“set”以及“values(”等前缀。
   或者添加“)”后缀，可以用于选择性插入、更新、删除或者条件查询等操作。

2. 属性

属性	描述
prefix	给sql语句拼接的前缀
suffix	给sql语句拼接的后缀
prefixOverrides	去除sql语句前面的关键字或者字符，该关键字或者字符由prefixOverrides属性指定，假设该属性指定为"AND"，当sql语句的开头为"AND"，trim标签将会去除该"AND"
suffixOverrides	去除sql语句后面的关键字或者字符，该关键字或者字符由suffixOverrides属性指定

3. 举个栗子 - 去除多余的and关键字

   	<select id="findActiveBlogLike" resultType="Blog">
   		SELECT * FROM BLOG
   		<trim prefix="WHERE" prefixOverrides="AND">
   			<if test="state != null">
   				state=#{state}
   			</if>
   			<if test="title != null">
   				ANDtitlelike#{title}
   			</if>
   			<if test="author !=null and author.name != null">
   				ANDauthor_namelike#{author.name}
   			</if>
   		</trim>
   	</select>
   

< set >

1. 简介

   Mybatis在生成updata语句时若使用if标签，如果前面的if没有执行，则可能有多余逗号的错误。
   使用set标签可以将动态的配置set关键字，和剔除追加到条件末尾的任何不相关的逗号。

2. 举个栗子

   	<update id="updateByPrimaryKeySelective" parameterType="RecruitmentConfBanner">
   		UPDATE conf_banner_t
   		<set> 
   			<if test="bannerName != null">
   				t.banner_name = #{bannerName},
   			</if>
   			<if test="bannerUrl != null">
   				t.banner_url = #{bannerUrl},
   			</if>
   			<if test="bannerLogo != null">
   				t.banner_logo = #{bannerLogo},
   			</if>
   			<if test="bannerDescription != null">
   				t.banner_description = #{bannerDescription},
   			</if>
   			<if test="sort != null">
   				t.sort = #{sort},
   			</if>
   			<if test="isEnabled != null">
   				t.is_enabled = #{isEnabled},
   			</if>
   		</set>
   		where t.banner_id = #{bannerId}
   	</update>
   

< foreach >

1. 简介

   < foreach > 循环java中的数组，list集合的。 主要用在sql的in语句中。

2. 语法说明

• mapper：

  	<foreach collection=" " item=" " open=" " close=" " separator=" ">
  		#{xxx}
  	</foreach>
  

属性	描述
collection	1. 表示接口中的方法参数的类型， 如果是数组使用array , 如果是list集合使用list，Map对象没有默认的键。 2. 当然在作为入参时可以使用@Param("keyName")来设置键，设置keyName后，list,array将会失效。 3. 除了入参这种情况外，还有一种作为参数对象的某个字段的时候。举个例子：如果User有属性List ids。入参是User对象，那么这个collection = "ids"；如果User有属性Ids ids;其中Ids是个对象，Ids有个属性List id;入参是User对象，那么collection = "ids.id"。
item	值是自定义的，表示数组和集合中的成员的变量
open	循环开始时的字符
close	循环结束时的字符
separator	集合成员之间的分隔符

3. 示例

• 要求：查询学生id是 1001,1002,1003的三个学生
• sql代码：

  select * from student where id in (1001,1002,1003)
  

• java接口代码：

  public List<Student> selectStudentsForList(List<Integer> ids);
  

• java测试代码

  		@Test
  		public void testSelectStudentsForList(){
  			SqlSession sqlSession = MybatisUtils.getSqlSession();
  			StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
  			List<Integer> list = new ArrayList<Integer>();
  			list.add(1001);
  			list.add(1002);
  			list.add(1004);
  			list.add(1009);
  			//list=null;
  			List<Student> students = studentDao.selectStudentsForList(list);
  			students.forEach(student->System.out.println(student));
  			sqlSession.close();
  		}
  
  

• mapper：

  	<select id="selectStudentsForList" resultType="com.zero.domain.Student">
  		select * from student
  		<where>
  			<if test="list==null">
  				id=-1
  			</if>
  			<if test="list!=null">
  				<foreach collection="list" open="idin(" close=")" item="stuId" separator=",">
  					#{stuId}
  				</foreach>
  			</if>
  		</where>
  	</select>
  

< bind >

1. 前景说明

   • 在进行模糊查询的时候，如果使用${}拼接字符串，则无法防止sql注入问题。
     例子：
     select * from user where username like '%${name}%'
   • 如果使用字符串拼接函数或者链接符号，可能会因为数据库的拼接函数或者连接符号不同，导致在更换数据库的时候带来不必要的麻烦。
     例子：
     使用concat函数拼接字符串，在MySQL中，这个函数支持多个参数，但在Oracle中支持两个参数。由于不同数据库之间的语法差异，如果更换数据库，有些SQL语句可能就需要重写。
     select * from user where username like concat ('1',#{userName},'2')

2. 简介

   bind标签可以使用OGNL表达式创建一个变量并将其绑定到上下文中。
   让我们完全不必要使用数据库语言，只需要使用即可与所需参数连接。

3. 优势

   解决SQL注入问题
   直接使用mybatis提供好的标签，解决因为更换数据库而不得不更新sql语句的烦恼

4. 举个栗子

   	<!-- 本来的模糊查询方式 -->
   	<!--         <select id="listProduct" resultType="Product"> -->
   	<!--             select * from   product_  where name like concat('%',#{0},'%') -->
   	<!--         </select> --> 
   	<select id="listProduct" resultType="Product">
   		<bind name="likename" value="'%' + name + '%'" />
   		select * from   product_  where name like #{likename}
   	</select>
   

sql代码片段

1. 简介

   简单来说，sql代码片段， 就是复用一些语法。

2. 步骤
   1. 先定义<sql id="自定义名称唯一"> sql语句, 表名, 字段等 </sql>
   2. 再使用， 复用sql代码