MyBatis动态SQL语句

zhangyan_1010

已于 2024-04-17 16:55:57 修改

阅读量942

点赞数 9

文章标签： mybatis sql java

于 2024-04-17 11:05:23 首次发布

本文链接：https://blog.csdn.net/zhangyan_1010/article/details/137863756

版权

在实际的操作中，有时候一些查询条件是不确定是否传进来的，特别是在多条件查询的情况下，不一定要把多有的条件都传入。如果在执行一个操作的时候，一个参数没有传递进来，会直接影响查询的结果。MyBatis提供了完美解决这一问题的方案，就是使用动态SQL语句。

MyBatis的一个强大之处就是拥有动态SQL语句功能，相比较其他的持久化框架为了一个不确定的查询条件，需要编写大量的Java代码，而MyBatis只需要在配置文件中通过几个简单的标签就完美解决了。MyBatis的动态SQL语句提供了if、choose、when、otherwise、foreach等标签，有了这些标签，使得MyBatis在处理不确定条件的问题上变得得心应手。

if语句——选择语句

很明显，if语句是用来执行一些不确定的查询，用来编写一些不确定是否传递进来的查询条件。假如在查询t_person表的时候，传入两个条件一个是name，另一个是sex，其中sex是一个不确定的参数，要求是当传入sex参数时添加条件的查询，如果没有传入sex参数就不参加查询条件，具体写法如下所示。

<select id="dynamicLoad" parameterType="Person" resultMap="personMap">
	<bind name="parameter" value="'%' + _parameter.name + '%'"/>
	select * from t_person where person_name like #{parameter}
	<if test="sex != null">
		and person_sex=#{sex}
	</if>
</select>

注意此时的parameterType类型为Person，在执行查询的时候需要传递进来一个Person对象，具体的测试代码如下。

@Test
public void testDynamic() {
	SqlSession session = null;
	try {
		session = factory.openSession();
		Person person = new Person();
		person.setName("a");
		person.setSex("男");
		List<Person> persons = session.selectList("com.obj.model.Person.dynamicLoad", per);
		for(Person per:persons) {
			System.out.println(per);
		}
	} catch (Exception e) {
		// TODO: handle exception
		e.printStackTrace();
	}
}

此时传入了sex查询条件，具体的执行结果如下所示。

Preparing: select * from t_person where person_name like ? and person_sex=? 
Parameters: %a%(String), 男(String)
Total: 3
Person [id=1, name=aaa, age=20, sex=男, email=aaa@obj.com]
Person [id=5, name=abc, age=23, sex=男, email=abc@obj.com]
Person [id=6, name=abd, age=24, sex=男, email=abd@obj.com]

通过执行结果可以看到，sex条件参与的查询，而如果没有传入sex属性，执行结果如下所示。

Preparing: select * from t_person where person_name like ? 
Parameters: %a%(String)
Total: 3
Person [id=1, name=aaa, age=20, sex=男, email=aaa@obj.com]
Person [id=5, name=abc, age=23, sex=男, email=abc@obj.com]
Person [id=6, name=abd, age=24, sex=男, email=abd@obj.com]

很明显，这个时候sex就没有参与查询。

choose、when、otherwise语句——分支判断语句

遇到需要分支判断的时候，MyBatis提供了分支判断语句。当然了，多个分支判断语句都可以使用if语句来完成，不过有些时候使用多分支还是要更好一些。choose、when、otherwise等语句的使用方法，具体代码如下。

<select id="dynamicLoad" parameterType="Person" resultMap="personMap">
	<bind name="parameter" value="'%' + _parameter.name + '%'"/>
	select * from t_person where person_name like #{parameter}
	<choose>
		<when test="sex != null and age == 0">
			and person_sex=#{sex}
		</when>
		<when test="age != 0 and sex == null">
			and person_age>#{age}
		</when>
		<when test="sex != null and age != 0">
			and person_sex=#{sex} and person_age>#{age}
		</when>
		<otherwise>
			and person_sex in ('男','女') and person_age>18
		</otherwise>
	</choose>
</select>

以上的条件一共有四条，第一如果传入了性别而没有传入年龄，就执行第一条SQL语句；第二如果传入了年龄而没有传入性别，就执行第二条SQL语句；第三如果性别和年龄都传入了，就执行第三条SQL语句；第四如果性别和年龄都没有传入，就执行第四条SQL语句。很明显以上的四条SQL语句只会执行一条，从而更方便的控制了SQL语句的灵活度。在性别和年龄条件都传入的情况下，执行的结果如下所示。

Preparing: select * from t_person where person_name like ? and person_sex=? and person_age>? 
Parameters: %a%(String), 男(String), 20(Integer)
Total: 2
Person [id=5, name=abc, age=23, sex=男, email=abc@obj.com]
Person [id=6, name=abd, age=24, sex=男, email=abd@obj.com]

在只传入性别，而没有传入年龄的情况下，执行结果如下所示。

Preparing: select * from t_person where person_name like ? and person_sex=? 
Parameters: %a%(String), 男(String)
Total: 3
Person [id=1, name=aaa, age=20, sex=男, email=aaa@obj.com]
Person [id=5, name=abc, age=23, sex=男, email=abc@obj.com]
Person [id=6, name=abd, age=24, sex=男, email=abd@obj.com]

在只传入年龄，而没有传入性别的情况下，执行结果如下所示。

Preparing: select * from t_person where person_name like ? and person_age>? 
Parameters: %a%(String), 20(Integer)
Total: 2
Person [id=5, name=abc, age=23, sex=男, email=abc@obj.com]
Person [id=6, name=abd, age=24, sex=男, email=abd@obj.com]

在性别和年龄都没有传入的情况下，执行结果如下所示。

Preparing: select * from t_person where person_name like ? and person_sex in ('男','女') and person_age>18 
Parameters: %a%(String)
Total: 3
Person [id=1, name=aaa, age=20, sex=男, email=aaa@obj.com]
Person [id=5, name=abc, age=23, sex=男, email=abc@obj.com]
Person [id=6, name=abd, age=24, sex=男, email=abd@obj.com]

通过以上的例子，很好的验证了MyBatis动态SQL功能的强大，使得编程更加灵活。

where、set、trim——逻辑语句

在讲述新的知识点之前，我们先来看一个问题，代码如下。

<select id="dynamicLoad" parameterType="Person" resultMap="personMap">
	select * from t_person where 
	<if test="name != null">
		person_name like "%"#{name}"%"
	</if>
	<if test="sex != null">
		and person_sex=#{sex}
	</if>
	<if test="age > 0">
		and person_age>#{age}
	</if>
</select>

上述的查询操作一共有三个条件判断，如果在执行的时候一个条件都没有传入，最后发出的SQL语句应该是“select * from t_person where”，很明显，这样的SQL语句执行下去肯定是错误的，会抛出“com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException”异常。如果在执行的时候只有第一个条件没有传入进来，其他的条件正常传入，最后的SQL语句应该是“select * from t_person where and person_sex=? and person_age>?”，在where语句之后多了一个and，这样也是错误的。

因此为了解决上述问题，MyBatis提供了where标签等逻辑语句，我们可以把语句写成这样。

<select id="dynamicLoad"  parameterType="Person" resultMap="personMap">
	select * from t_person 
	<where>
		<if test="name != null">
			person_name like "%"#{name}"%"
		</if>
		<if test="sex != null">
			and person_sex=#{sex}
		</if>
		<if test="age > 0">
			and person_age>#{age}
		</if>
	</where>
</select>

这个时候，如果一个条件都没传进来，就不会出现where语句，执行结果如下所示。

Preparing: select * from t_person 
Parameters: 
Total: 6
Person [id=1, name=aaa, age=20, sex=男, email=aaa@obj.com]
Person [id=2, name=bbb, age=20, sex=女, email=bbb@obj.com]
Person [id=3, name=ccc, age=21, sex=男, email=ccc@obj.com]
Person [id=4, name=ddd, age=22, sex=女, email=ddd@obj.com]
Person [id=5, name=abc, age=23, sex=男, email=abc@obj.com]
Person [id=6, name=abd, age=24, sex=男, email=abd@obj.com]

而如果只有第一个条件没有传递进来，会智能地去掉第一个无用的and，执行结果如下所示。

Preparing: select * from t_person WHERE person_sex=? and person_age>? 
Parameters: 男(String), 20(Integer)
Total: 3
Person [id=3, name=ccc, age=21, sex=男, email=ccc@obj.com]
Person [id=5, name=abc, age=23, sex=男, email=abc@obj.com]
Person [id=6, name=abd, age=24, sex=男, email=abd@obj.com]

这样看来where标签功能确实强大，其适用的地方就是不确定的条件语句，适时地插入where语句，并且可以智能地去掉多余的and或or语句。另外，where标签不仅仅适用于查询操作，其他的操作同样适用。

不过MyBatis为了防止where语句不能正常执行，提供了trim标签。从字面意思上看trim意为修剪，在实际的应用中也是这个意思，上面的代码可以修改为。

<select id="dynamicLoad"  parameterType="Person" resultMap="personMap">
	select * from t_person 
	<trim prefix="where" prefixOverrides="and |or">
		<if test="name != null">
			person_name like "%"#{name}"%"
		</if>
		<if test="sex != null">
			and person_sex=#{sex}
		</if>
		<if test="age > 0">
			and person_age>#{age}
		</if>
	</trim>
</select>

其中属性prefix为前缀，执行的过程中会在条件之前加上where语句，prefixOverrides可以去掉指定的语句，其中的and和or就是指定的语句，不要忘了中间有一个空格。除此之外trim标签中还有另外两个属性，分别是suffix（后缀）和suffixOverrides（指定去掉的语句），使用方法是相同的。

在MyBatis动态SQL语句中，还有一个set标签用来执行修改操作的动态语句。我们都知道在执行修改操作的时候有一个“set”语句，并且在所有的修改字段语句之间都有一个逗号，这样一来如果使用普通的动态修改语句，会导致在语句中多一些逗号，造成语句执行错误。具体的语句如下。

<update id="dynamicUpdate" parameterType="Person">
	update t_person 
	<set>
		<if test="name != null">
			person_name=#{name},
		</if>
		<if test="sex != null">
			person_sex=#{sex},
		</if>
		<if test="age > 0">
			person_age=#{age},
		</if>
		<if test="email != null">
			person_email=#{email}
		</if>
	</set>
	where person_id=#{id}
</update>

在执行的过程中，会加上set语句，并且如果其中的第一个条件没有传入数据，或者最后一个条件没有传入数据，都会多一个逗号，在执行的过程中，会自动去掉多余的逗号。

同样，也可以使用trim语句来替换上述代码，具体的操作代码如下。

<update id="dynamicUpdate" parameterType="Person">
	update t_person 
	<trim prefix="set" prefixOverrides=",">
		<if test="name != null">
			person_name=#{name},
		</if>
		<if test="sex != null">
			person_sex=#{sex},
		</if>
		<if test="age > 0">
			person_age=#{age},
		</if>
		<if test="email != null">
			person_email=#{email}
		</if>
	</trim>
	where person_id=#{id}
</update>

foreach——循环遍历语句

在动态SQL语句中还有一个常用的foreach语句，用来执行循环遍历操作，特别是在in语句中经常用到，具体的代码如下。

<select id="dynamicList" resultMap="personMap">
	select * from t_person where person_id in
	<foreach collection="list" item="id" index="i" open="(" close=")" separator=",">
		#{id}
	</foreach>
</select>

其中collection属性用来指定具体遍历的类型，其中的属性值有三个，分别是list、map和array，分别对应List集合、Map集合和数组。item属性是用来指定具体遍历的对象，要和下面的参数#{id}对应。index属性是用来指定每次遍历元素的下标，open和close分别表示前缀和后缀，separator表示每个条件之间的分隔符。

具体的测试代码如下所示。

@Test
public void testDynamicList() {
	SqlSession session = null;
	try {
		session = factory.openSession();
		List<Integer> ids = new ArrayList<Integer>();
		ids.add(1);
		ids.add(2);
		ids.add(3);
		List<Person> persons = session.selectList("com.obj.model.Person.dynamicList", ids);
		for(Person person:persons) {
			System.out.println(person);
		}
	} catch (Exception e) {
		// TODO: handle exception
		e.printStackTrace();
	}
}

具体的执行结果如下所示。

Preparing: select * from t_person where person_id in ( ? , ? , ? ) 
Parameters: 1(Integer), 2(Integer), 3(Integer)
Total: 3
Person [id=1, name=aaa, age=20, sex=男, email=aaa@obj.com]
Person [id=2, name=bbb, age=20, sex=女, email=bbb@obj.com]
Person [id=3, name=ccc, age=21, sex=男, email=ccc@obj.com]

foreach通常用于执行批量删除和批量添加等操作。