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>