MyBatis实现动态SQL

查询全部

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}";

/MyBatisProject3/src/org/myy/mapper/studentMapper.xml

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

<where>会自动处理第一个<if>标签中的and,但不会处理之后<if>中的and

/MyBatisProject3/src/org/myy/mapper/StudentMapper.java

List<Student> qureyStudentByNameOrAgeWithSQLTag(Student student);

/MyBatisProject3/src/org/myy/test/Test.java

        // Connection - SqlSession操作Mybatis
        // conf.xml->reader
        Reader reader = Resources.getResourceAsReader("conf.xml");
        // reader->sqlSession
        // 可以通过build的第二参数 指定数据库环境
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
        SqlSession session = sessionFactory.openSession();

        StudentMapper studentMapper = session.getMapper(StudentMapper.class);
        
        Student stu= new  Student();
        stu.setStuName("zs");
        stu.setStuAge(23);
        
        List<Student> students=studentMapper.qureyStudentByNameOrAgeWithSQLTag(stu);
        System.out.println(students);
        session.close();

<foreach>

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

ids={1,2,4}

select stuno,stuname from student1 where stuno in (1,2,4)

<foreach>迭代的类型:数组、对象数组、集合、属性（Grade类: List<Integer> ids）

属性（Grade类: List<Integer> ids）

/MyBatisProject3/src/org/myy/mapper/studentMapper.xml

<!--将多个元素值放入对象的属性中  -->
<select id="queryStudentWithNosInGrade" parameterType="grade" resultType="student">
        select * from student1
        <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>

/MyBatisProject3/src/org/myy/mapper/StudentMapper.java

List<Student> queryStudentWithNosInGrade(Grade grade);

/MyBatisProject3/src/org/myy/test/Test.java

　　　　　// Connection - SqlSession操作Mybatis
        // conf.xml->reader
        Reader reader = Resources.getResourceAsReader("conf.xml");
        // reader->sqlSession
        // 可以通过build的第二参数 指定数据库环境
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
        SqlSession session = sessionFactory.openSession();

        StudentMapper studentMapper = session.getMapper(StudentMapper.class);
        
        Grade grade=new Grade();
        List<Integer> stuNos=new ArrayList<>();
        stuNos.add(1);
        stuNos.add(2);
        stuNos.add(4);
        
        grade.setStuNos(stuNos);
        
        List<Student> students=studentMapper.queryStudentWithNosInGrade(grade);
        System.out.println(students);
        session.close();

数组:

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

/MyBatisProject3/src/org/myy/mapper/studentMapper.xml

<!--将多个元素值放入数组中 int[] stuNos={1,2,4}  -->
    <select id="queryStudentWithArray" parameterType="int[]" resultType="student">
        select * from student1
        <where>
            <if test="array!=null and array.length">
                <foreach collection="array" open=" and stuno in ("     close=")" item="stuNo" separator="," >
                    #{stuNo}
                </foreach>
                
            </if>
        </where>
    </select>

/MyBatisProject3/src/org/myy/mapper/StudentMapper.java

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

/MyBatisProject3/src/org/myy/test/Test.java

　　　　　// Connection - SqlSession操作Mybatis
        // conf.xml->reader
        Reader reader = Resources.getResourceAsReader("conf.xml");
        // reader->sqlSession
        // 可以通过build的第二参数 指定数据库环境
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
        SqlSession session = sessionFactory.openSession();

        StudentMapper studentMapper = session.getMapper(StudentMapper.class);
        
        int[] stuNos= {1,2,4};
        
        List<Student> students=studentMapper.queryStudentWithArray(stuNos);
        System.out.println(students);
        session.close();

集合:

无论编写代码时，传递的是什么参数，在mapper.xml中必须用list代替该集合

/MyBatisProject3/src/org/myy/mapper/studentMapper.xml

    <!--将多个元素值放入集合中List<Integer> stuNos值{1,2,4}  -->
    <select id="queryStudentWithList" parameterType="List" resultType="student">
        select * from student1
        <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>

/MyBatisProject3/src/org/myy/mapper/StudentMapper.java

List<Student> queryStudentWithList(List<Integer> stuNos);

/MyBatisProject3/src/org/myy/test/Test.java

        // Connection - SqlSession操作Mybatis
        // conf.xml->reader
        Reader reader = Resources.getResourceAsReader("conf.xml");
        // reader->sqlSession
        // 可以通过build的第二参数 指定数据库环境
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
        SqlSession session = sessionFactory.openSession();

        StudentMapper studentMapper = session.getMapper(StudentMapper.class);
        
        List<Integer> stuNos= new ArrayList<Integer>();
        stuNos.add(1);
        stuNos.add(2);
        stuNos.add(4);

        
        List<Student> students=studentMapper.queryStudentWithList(stuNos);
        System.out.println(students);
        session.close();

对象数组：

Student[ ] students={student0,student1,student2} 每个studentx包含一个学号属性

/MyBatisProject3/src/org/myy/mapper/studentMapper.xml

    <!--将多个元素值放入对象数组中Student[ ] students={student0,student1,student2} 每个studentx包含一个学号属性  -->
    <select id="queryStudentWithObjectArray" parameterType="Object[]" resultType="student">
        select * from student1
        <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>

/MyBatisProject3/src/org/myy/mapper/StudentMapper.java

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

/MyBatisProject3/src/org/myy/test/Test.java

        // Connection - SqlSession操作Mybatis
        // conf.xml->reader
        Reader reader = Resources.getResourceAsReader("conf.xml");
        // reader->sqlSession
        // 可以通过build的第二参数 指定数据库环境
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
        SqlSession session = sessionFactory.openSession();

        StudentMapper studentMapper = session.getMapper(StudentMapper.class);
        
        Student stu1=new Student();
        stu1.setStuNo(1);
        Student stu2=new Student();
        stu2.setStuNo(2);
        Student stu4=new Student();
        stu4.setStuNo(4);
        
        Student[] stus=new Student[] {stu1,stu2,stu4};

        
        List<Student> students=studentMapper.queryStudentWithObjectArray(stus);
        System.out.println(students);
        session.close();

SQL片段：

　　java：方法

　　数据库：存储过程、存储函数

　　MyBatis：SQL片段

a.提取相似代码

b.引用

<sql id="objectArrayStunos"> 
    <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>



<!--将多个元素值放入对象数组中Student[ ] students={student0,student1,student2} 每个studentx包含一个学号属性  -->
    <select id="queryStudentWithObjectArray" parameterType="Object[]" resultType="student">
        select * from student1
　　　　　<!--如果sql片段和引用处不在同一个文件中,则需要在refid引用时加上namespace，即namespace.id  -->
        <include refid="objectArrayStunos"></include>
    </select>