九、动态SQL

1.重复代码

// 查询全部

String statement = "select stuno,stuname from student";

// 根据年龄查询学生

String statement = "select stuno,stuname from student where stuage = #{stuage}"; 

// 根据姓名和年龄查询学生

String statement = "select stuno,stuname from student where stuname = #{stuName} and  stuage = #{stuAge} "; 

2.where、if解决

<!-- 
<select id="queryStuByNOrAWithSQLTag" parameterType="student" resultType="student" >
	select stuno,stuname,stuage from student where 1 = 1
	<if test="stuName != null and stuName!='' "> 
		and stuname = #{stuName}
	</if>
	<if test="stuAge !=null and stuAge!=0 "> 
			and  stuage = #{stuAge}
	</if>
</select>
-->
 	
<!--推荐用where  -->
<select id="queryStuByNOrAWithSQLTag" parameterType="student" resultType="student" >
	select stuno, stuname, stuage from student
	<where>
		<!-- <if test = "student有stuname属性且不为null"> -->
		<if test = "stuName != null and stuName != '' "> 
			and stuname = #{stuName}
		</if>
		<if test = "stuAge != null and stuAge != 0 "> 
			and stuage = #{stuAge}
		</if>
	</where>
</select>

会自动处理第一个标签中的and，但不会处理之后中的and

3.foreach

（1）将多个元素值放入对象的属性中

<select id="queryStuWithNosInGrade" parameterType="grade" resultType="student">
	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>

List<Student> queryStuWithNosInGrade(Grade grade);

public static void queryStuWithNosInGrade() throws IOException {
	Reader reader = Resources.getResourceAsReader("conf.xml");
	SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
	SqlSession session = sessionFactory.openSession();
	studentMapper studentmapper = session.getMapper(studentMapper.class);
	Grade grade = new Grade();
	List<Integer> stuNos = new ArrayList<Integer>();
	stuNos.add(2);
	stuNos.add(4);
	grade.setStuNos(stuNos);
	List<Student> students = studentmapper.queryStuWithNosInGrade(grade);
	System.out.println(students);
	session.close();
}

（2）将多个元素值放入数组中

<select id="queryStudentsWithArray" parameterType="int[]" resultType="student">
	select * from student
	<where>
		<if test="array!=null and array.length">
			<foreach collection="array" open="and stuno in (" close=")" item="stuNo" separator=",">
				#{stuNo}
			</foreach>
		</if>
	</where>
</select>

List<Student> queryStudentsWithArray(int[] stuNos);

public static void queryStudentsWithArray() throws IOException {
	Reader reader = Resources.getResourceAsReader("conf.xml");
	SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
	SqlSession session = sessionFactory.openSession();
	studentMapper studentmapper = session.getMapper(studentMapper.class);
	int[] stuNos = {2, 5};
	List<Student> students = studentmapper.queryStudentsWithArray(stuNos);
	System.out.println(students);
	session.close();
}

（3）将多个元素值放入对象数组中

<select id="queryStudentsWithObjectArray" parameterType="Object[]" resultType="student">
	select * from student
	<where>
		<if test="array!=null and array.length">
			<foreach collection="array" open="and stuno in (" close=")" item="student" separator=",">
					#{student.stuNo}
			</foreach>
		</if>
	</where>
</select>

List<Student> queryStudentsWithObjectArray(Student[] students);

public static void queryStudentsWithObjectArray() throws IOException {
	Reader reader = Resources.getResourceAsReader("conf.xml");
	SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
	SqlSession session = sessionFactory.openSession();
	studentMapper studentmapper = session.getMapper(studentMapper.class);
	Student student1 = new Student();
	Student student2 = new Student();
	student1.setStuNo(2);
	student2.setStuNo(5);
	Student[] students = new Student[] {student1, student2};
	List<Student> sList = studentmapper.queryStudentsWithObjectArray(students);
	System.out.println(sList);
	session.close();
}	

4.注意

（1）简单类型的数组

无论编写代码时，传递的是什么参数名(stuNos)，在mapper.xml中，必须用array代替该数组

（2）集合

无论编写代码时，传递的是什么参数名(stuNos)，在mapper.xml中，必须用list代替该数组

（3）对象数组

parameterType = "Object[]" 

<foreach collection="array" open=" and  stuno in (" close=")" item="student" separator=",">   
	#{student.stuNo}
</foreach>

5.SQL片段

提取+引用

java提取：通过方法提取提取相同代码

数据库提取：存储过程或存储函数提取相同代码

MaBatis：SQL片段

<sql id="ObjectArray">
 	<where>
		<if test="array!=null and array.length">
			<foreach collection="array" open="and stuno in (" close=")" item="student" separator=",">
				#{student.stuNo}
			</foreach>
		</if>
	</where>
</sql>
 			
<select id="queryStudentsWithObjectArray1" parameterType="Object[]" resultType="student">
	select * from student
	<include refid="ObjectArray"></include>
</select>