关于动态sql

if

按姓名和年龄查询

1
2
3
4
5
6
7
8
9
10
11
<select id="qStuByNOrAWithSQLTag" parameterType="top.eshyee.entity.Student" resultType="top.eshyee.entity.Student">
		select stuno ,stuname  from student where 1=1
		<if test="stuName!=null and stuName!= ''">
		student有stuname属性且不为null
		and stuname=#{stuName}
		</if>
		<if test="stuAge!=null and stuAge!=0">
		student有stunage属性且不为null
		and stuage=#{stuAge}
		</if>
</select>

这里and会出问题

where

where:处理第一个and

1
2
3
4
5
6
7
8
9
10
11
<select id="qStuByNOrAWithSQLTag" parameterType="top.eshyee.entity.Student" resultType="top.eshyee.entity.Student">
		select stuno ,stuname  from student
		<where>
		<if test="stuName!=null and stuName!= ''">
		and stuname=#{stuName}
		</if>
		<if test="stuAge!=null and stuAge!=0">
		and stuage=#{stuAge}
		</if>
		</where>
</select>

foreach

查询学号为1 2 4的学生学号信息

迭代的类型：数组、对象数组、集合、属性

数组

数组固定写法：array 这是约定

1
2
3
4
5
6
7
8
9
10
<select id="queryStuwithNoWitharray" resultType="top.eshyee.entity.Student" parameterType="int[]">
		select * from student 
		<where>
	<if test="array!=null and array.length>0">
		<foreach collection="array" open="and stuno in(" close=")" item="stuNo" separator=",">
		#{stuNo}
		</foreach>
		</if>
		</where>
</select>

放入对象的属性中

1
2
3
4
5
6
7
8
9
10
<select id="queryStuwithNoInGra" resultType="top.eshyee.entity.Student" parameterType="top.eshyee.entity.Grade">
		select * from student 
		<where>
		<if test="stuNos!=null and stuNos.size>0">
		<foreach collection="stuNos" open="and stuno in(" close=")" item="stuNo" separator=",">
		#{stuNo}
		</foreach>
		</if>
		</where>
</select>

集合

1
2
3
4
5
6
7
8
9
10
<select id="queryStuwithNowithlist" resultType="top.eshyee.entity.Student" parameterType="top.eshyee.entity.Grade">
		select * from student 
		<where>
		<if test="list!=null and list.size>0">
		<foreach collection="list" open="and stuno in(" close=")" item="stuNo" separator=",">
		#{stuNo}
		</foreach>
		</if>
		</where>
</select>

对象数组

必须使用Object[]

1
2
3
4
5
6
7
8
<select id="queryStuwithNowithObjArr" 
	resultType="top.eshyee.entity.Student" parameterType="Object[]">
		select * from student 
		<include refid="objectArraStuno"></include>
		<!-- 如果sql片段不在一个文件 
		<include refid="top.eshyee.mapper.StudentMapper.objectArraStuno"></include>
		-->
</select>

sql片段

重复使用的提取出来

1
2
3
4
5
6
7
8
9
<sql id="objectArraStuno">
	<where>
	<if test="array!=null and array.length>0">
	<foreach collection="array" open="and stuno in(" close=")" item="student" separator=",">
		#{student.stuNo}
	</foreach>
	</if>
	</where>
</sql>