动态SQL

目录

 

概述

一.if元素

二.choose,when,otherwise

三.trim，where，set

四.foreach

补充 bind元素

---

概述

 

动态SQL的元素

元素	作用	备注
if	判断语句	单条件分支判断
choose（when，otherwise）	相当于Java中的switch和case语句	多条件分支判断
trim（where，set）	辅助元素，用于处理特定的SQL拼装问题，比如去掉多余的and，or等	用于处理SQL拼装的问题
foreach	循环语句	在（select）in语句等列举条件常用

数据库

一.if元素

!!（要在接口方法的参数里@param（"name"））

mapper.java

List<Dy> selectScoredayu60(@Param("name")String name);

<?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="dynamicSQL.DynamicSQLMapper">
	<select id="selectScoredayu60" parameterType="string" resultType="dynamicSQL.Dy">
		select * from dy where 1=1 
		<if test="name != null">
			and name like concat('%',#{name},'%')
		</if>
	</select>
</mapper>

 

二.choose,when,otherwise

大于 &gt  

小于&lt

 Mapper.java

List<Dy> selectScores(@Param("score")Float score);

	<select id="selectScores" parameterType="float" resultType="dynamicSQL.Dy">
		select * from dy where 1=1
		<choose>
			<when test="score &gt;60">and score&gt;60</when>
			<when test="score &lt;60">and score&lt;60</when>
			<otherwise>and score=60</otherwise>
		</choose>
	</select>

 

三.trim，where，set

where省掉了where  1 = 1 

	<select id="selectScores" parameterType="float" resultType="dynamicSQL.Dy">
		select * from dy 
		<where>
		<choose>
			<when test="score &gt;60">and score&gt;60</when>
			<when test="score &lt;60">and score&lt;60</when>
			<otherwise>and score=60</otherwise>
		</choose>
		</where>
	</select>

trim标签省掉了where 和要忽略的关键字

	<select id="selectScores" parameterType="float" resultType="dynamicSQL.Dy">
		select * from dy 
		<trim prefix="where" prefixOverrides="and">
		<choose>
			<when test="score &gt;60">and score&gt;60</when>
			<when test="score &lt;60">and score&lt;60</when>
			<otherwise>and score=60</otherwise>
		</choose>
		</trim>
	</select>

set用在update中

mapper.java

int updateScore(@Param("name")String name,@Param("score")Float score);

	<update id="updateScore">
		update dy
		<set>
			<if test="score&gt;60">score=#{score},</if>
			<if test="score&lt;60">score=101</if>
		</set>
		where name=#{name}
	</update>

main()  注意要提交session.commit();

public class TestDemo {
	public static void main(String[] args) {
		SqlSession session = DBhelper.getFactory().openSession(false);
		DynamicSQLMapper dynamicSQLMapper = session.getMapper(DynamicSQLMapper.class);
		int bool = dynamicSQLMapper.updateScore("强月城",(float) 50);
		System.out.println(bool);
		session.commit();
		session.close();
	}
}

 

四.foreach

mapper.java

List<Dy> select1819Stu(List<String> list);

	<select id="select1819Stu" resultType="dynamicSQL.Dy">
		select * from dy where SUBSTRING(id,1,4) in 
		<foreach collection="list" item="listid" index="index" open="(" separator="," close=")">
			#{listid}
		</foreach>
	</select>

main

public class TestDemo {
	public static void main(String[] args) {
		SqlSession session = DBhelper.getFactory().openSession(false);
		DynamicSQLMapper dynamicSQLMapper = session.getMapper(DynamicSQLMapper.class);
		List<String> listj = new ArrayList<String>();
		listj.add("2018");
		listj.add("2019");
		List<Dy> list = dynamicSQLMapper.select1819Stu(listj);

		session.commit();
		session.close();
		for(Dy dy:list) {
			System.out.println(dy);
		}

	}
}

 

 

补充 bind元素

bind元素用于通过OGNL表达式去自定义一个上下文变量，这样更方便使用。

• 在进行模糊查询时，MySQL数据库有concat，name like concat('%',#{name},'%') 
• Oracle数据库则没有，所以使用bind元素即可实现

mapper.java

List<Dy> selectScoredayu60(@Param("name")String name);

	<select id="selectScoredayu60" parameterType="string" resultType="dynamicSQL.Dy">
	<bind name="pattern" value="'%' + name + '%'"/>
		select * from dy where 1=1 
		<if test="name != null">
			and name like #{pattern}
		</if>
	</select>