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>