1、使用动态SQL语句查询
例如想要根据学生的姓名或年龄查询：
1、在mapper文件中写入：
<select id="queryStudentByNOrAWithSQLTag" 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>
2、在mapper接口声明方法
3、测试类中写入：
//查询单个学生,使用了SQL标签 public static void queryStudentByNOrAWithSQLTag() throws IOException{ //Connection - SqlSession //conf.xml -> reader Reader reader = Resources.getResourceAsReader("conf.xml"); //reader -> SqlSession //可以通过build的第二个参数指定数据库环境 SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader); SqlSession session = sessionFactory.openSession(); StudentMapper studentMapper = session.getMapper(StudentMapper.class); Student stu = new Student(); stu.setStuAge(21); stu.setStuName("ls"); Student student = studentMapper.queryStudentByNOrAWithSQLTag(stu);//接口中的方法->SQL语句 System.out.println(student); session.close(); }
2、使用foreach迭代查询
1>迭代属性(Grade类： List<Integer> ids)
1、在mapper文件中写入：
<!--将多个元素放入List集合中-->
<select id="queryStudentWithNosInGrade" 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>
2、在mapper接口声明方法
3、测试类中写入：
//查询全部学生,使用grade public static void queryStudentWithNosInGrade() throws IOException{ //Connection - SqlSession //conf.xml -> reader Reader reader = Resources.getResourceAsReader("conf.xml"); //reader -> SqlSession //可以通过build的第二个参数指定数据库环境 SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader); SqlSession session = sessionFactory.openSession(); // List<Student> students = session.selectList(statement); StudentMapper studentMapper = session.getMapper(StudentMapper.class); Grade grade = new Grade(); List<Integer> stuNos = new ArrayList<>(); stuNos.add(1); stuNos.add(2); stuNos.add(13); grade.setStuNos(stuNos); List<Student> students = studentMapper.queryStudentWithNosInGrade(grade); System.out.println(students); session.close(); }
2>使用简单类型的数组
注意：无论编写代码时，传递的是什么参数名(stuNos)，在mapper.xml中 必须用array代替该数组
<!-- 将多个元素放入数组中 --> <select id="queryStudentsWithArray" parameterType="int[]" resultType="student"> select * from student <where> <if test="array!=null and array.length"><!-- 必须用array代替数组 --> <foreach collection="array" open=" and stuno in (" close=")" item="stuNo" separator=","> #{stuNo} </foreach> </if> </where> </select>
3>使用集合
注意：无论编写代码时，传递的是什么参数名(stuNos)，在mapper.xml中 必须用list代替该数组
4>使用对象数组
Student[] students = {student0,student1,student2} 每个studentx包含一个学号属性
注意：
parameterType="Object[]" <foreach collection="array" open=" and stuno in (" close=")" item="student" separator=","> #{student.stuNo} </foreach>
3、SQL片段
<sql id="自己取的名字"> 想要提取的片段 </sql>
在想要使用的地方加入
<include refid="SQL名字"></include>
原文：https://www.cnblogs.com/hsy-go/p/12630401.html