动态条件查询索引

转载

mob64ca14079fb3 2024-10-14 13:45:10

文章标签 动态条件查询索引 mybatis sql java xml 文章分类 数据仓库大数据

文章目录

一、动态SQL

1.创建student表
2.插入数据
3.创建工程
4.创建POJO类
5.创建mybatis-config.xml配置文件
6.创建StudentMapper.xml映射文件

（1）< if >元素
（2）< when >< choose >< otherwise >元素
（3）< where >元素
（4）< set >元素
（5）< trim >元素
（6）< foreach >元素
（7）< bind >元素

7.编写测试类

（1）TestIf类
（2）TestChoose类
（3）TestSet类
（4）TestForeach类
（5）TestBind类

一、动态SQL

1.创建student表

动态条件查询索引_sql

2.插入数据

动态条件查询索引_mybatis_02

3.创建工程

下面展示一些 pom.xml代码片。

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>chapter04</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <!-- Mybatis核心 -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.5</version>
        </dependency>

        <!-- junit测试 -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <!-- MySQL驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.27</version>
        </dependency>
    </dependencies>
</project>

动态条件查询索引_mybatis_03

4.创建POJO类

动态条件查询索引_sql_04

5.创建mybatis-config.xml配置文件

下面展示一些 代码片。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--    配置别名-->
    <typeAliases>
        <package name="com.qfedu.pojo"/>
    </typeAliases>
    <!--设置连接数据库的环境-->
    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url"
                          value="jdbc:mysql://localhost:3306/chapter04"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <!--引入映射文件-->
    <mappers>
        <mapper resource="com.qfedu.mapper/StudentMapper.xml"></mapper>
    </mappers>
</configuration>

动态条件查询索引_动态条件查询索引_05

6.创建StudentMapper.xml映射文件

（1）< if >元素

下面展示一些 代码片。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="student">
    <select id="findStudentBySnameAndCourse" parameterType="student" resultType="student">
            select * from student where sname = #{sname}
           <!--根据条件动态拼装SQL语句-->
           <if test="null!=course and ''!=course"> and course = #{course}</if>
    </select>
</mapper>

动态条件查询索引_sql_06

（2）< when >< choose >< otherwise >元素

下面展示一些 代码片。

代码中注释掉的内容记得恢复或者删除一下，比对着结果图看

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="student">
    <select id="findStudentBySnameAndCourse" parameterType="student" resultType="student">
            select * from student where sname = #{sname}
           <!--根据条件动态拼装SQL语句-->
           <if test="null!=course and ''!=course"> and course = #{course}</if>
    </select>
    <select id="findStudentByChoose" parameterType="student" resultType="student">
<!--        select * from student where 1 = 1-->
<!--        <choose>-->
<!--            <!–如果sid不为null或者空字符串–>-->
<!--            <when test="null != sid and '' != sid">-->
<!--                and sid = #{sid}-->
<!--            </when>-->
<!--             <!–如果sname不为null或者空字符串–>-->
<!--            <when test="null != sname and '' != sname">-->
<!--                and sname like '%${sname}%'-->
<!--            </when>-->
<!--            <!–如果上面不满足，则执行下面的–>-->
<!--            <otherwise>-->
<!--                and course = 'Java'-->
<!--            </otherwise>-->
<!--        </choose>-->
        select * from student
<!--        <where>-->
<!--            <if test="null != sid and '' != sid">-->
<!--                and sid  = #{sid}-->
<!--            </if>-->
<!--            <if test="null != sname and '' != sname">-->
<!--                and sanme like '%${sname}%'-->
<!--            </if>-->
<!--            <if test="null == course">-->
<!--                and course = 'Java'-->
<!--            </if>-->
<!--        </where>-->
        <trim prefix="where" prefixOverrides="and">
            <if test="null != sid and '' != sid">
                and sid  = #{sid}
            </if>
            <if test="null != sname and '' != sname">
                and sanme like '%${sname}%'
            </if>
            <if test="null == course">
                and course = 'Java'
            </if>
        </trim>
    </select>
    <update id="updateStudent" parameterType="student">
        update student
<!--        <set>-->
<!--            <if test="null != sname and '' != sname">-->
<!--                 sname = #{sname},-->
<!--            </if>-->
<!--            <if test="null != age and '' != age">-->
<!--                 age = #{age},-->
<!--            </if>-->
<!--        </set>-->
<!--        where sid = #{sid}-->
        <trim prefix="set" suffixOverrides=",">
            <if test="null != sname and '' != sname">
                sname = #{sname},
            </if>
            <if test="null != age and '' != age">
                age = #{age},
            </if>
        </trim>
        where sid = #{sid}
    </update>

    <select id="findStudentByForeach" resultType="student">
        select * from student where sid in
        <foreach collection="list" item="sid" index="index" open="(" separator="," close=")">
            #{sid}
        </foreach>
    </select>
    <select id="findStudentByBind" resultType="student" parameterType="student">
        select * from student where
        <bind name="sname_pattern" value="'%'+sname+'%'"></bind>
        <if test="null != sname_pattern and '' != sname_pattern ">
            sname like #{sname_pattern}
        </if>
    </select>
</mapper>

动态条件查询索引_动态条件查询索引_07

（3）< where >元素

下面展示一些 代码片。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="student">
    <select id="findStudentBySnameAndCourse" parameterType="student" resultType="student">
            select * from student where sname = #{sname}
           <!--根据条件动态拼装SQL语句-->
           <if test="null!=course and ''!=course"> and course = #{course}</if>
    </select>
    <select id="findStudentByChoose" parameterType="student" resultType="student">
<!--        select * from student where 1 = 1-->
<!--        <choose>-->
<!--            <!–如果sid不为null或者空字符串–>-->
<!--            <when test="null != sid and '' != sid">-->
<!--                and sid = #{sid}-->
<!--            </when>-->
<!--             <!–如果sname不为null或者空字符串–>-->
<!--            <when test="null != sname and '' != sname">-->
<!--                and sname like '%${sname}%'-->
<!--            </when>-->
<!--            <!–如果上面不满足，则执行下面的–>-->
<!--            <otherwise>-->
<!--                and course = 'Java'-->
<!--            </otherwise>-->
<!--        </choose>-->
        select * from student
<!--        <where>-->
<!--            <if test="null != sid and '' != sid">-->
<!--                and sid  = #{sid}-->
<!--            </if>-->
<!--            <if test="null != sname and '' != sname">-->
<!--                and sanme like '%${sname}%'-->
<!--            </if>-->
<!--            <if test="null == course">-->
<!--                and course = 'Java'-->
<!--            </if>-->
<!--        </where>-->
        <trim prefix="where" prefixOverrides="and">
            <if test="null != sid and '' != sid">
                and sid  = #{sid}
            </if>
            <if test="null != sname and '' != sname">
                and sanme like '%${sname}%'
            </if>
            <if test="null == course">
                and course = 'Java'
            </if>
        </trim>
    </select>
    <update id="updateStudent" parameterType="student">
        update student
<!--        <set>-->
<!--            <if test="null != sname and '' != sname">-->
<!--                 sname = #{sname},-->
<!--            </if>-->
<!--            <if test="null != age and '' != age">-->
<!--                 age = #{age},-->
<!--            </if>-->
<!--        </set>-->
<!--        where sid = #{sid}-->
        <trim prefix="set" suffixOverrides=",">
            <if test="null != sname and '' != sname">
                sname = #{sname},
            </if>
            <if test="null != age and '' != age">
                age = #{age},
            </if>
        </trim>
        where sid = #{sid}
    </update>

    <select id="findStudentByForeach" resultType="student">
        select * from student where sid in
        <foreach collection="list" item="sid" index="index" open="(" separator="," close=")">
            #{sid}
        </foreach>
    </select>
    <select id="findStudentByBind" resultType="student" parameterType="student">
        select * from student where
        <bind name="sname_pattern" value="'%'+sname+'%'"></bind>
        <if test="null != sname_pattern and '' != sname_pattern ">
            sname like #{sname_pattern}
        </if>
    </select>
</mapper>

动态条件查询索引_mybatis_08

（4）< set >元素

下面展示一些 代码片。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="student">
    <select id="findStudentBySnameAndCourse" parameterType="student" resultType="student">
            select * from student where sname = #{sname}
           <!--根据条件动态拼装SQL语句-->
           <if test="null!=course and ''!=course"> and course = #{course}</if>
    </select>
    <select id="findStudentByChoose" parameterType="student" resultType="student">
<!--        select * from student where 1 = 1-->
<!--        <choose>-->
<!--            <!–如果sid不为null或者空字符串–>-->
<!--            <when test="null != sid and '' != sid">-->
<!--                and sid = #{sid}-->
<!--            </when>-->
<!--             <!–如果sname不为null或者空字符串–>-->
<!--            <when test="null != sname and '' != sname">-->
<!--                and sname like '%${sname}%'-->
<!--            </when>-->
<!--            <!–如果上面不满足，则执行下面的–>-->
<!--            <otherwise>-->
<!--                and course = 'Java'-->
<!--            </otherwise>-->
<!--        </choose>-->
        select * from student
<!--        <where>-->
<!--            <if test="null != sid and '' != sid">-->
<!--                and sid  = #{sid}-->
<!--            </if>-->
<!--            <if test="null != sname and '' != sname">-->
<!--                and sanme like '%${sname}%'-->
<!--            </if>-->
<!--            <if test="null == course">-->
<!--                and course = 'Java'-->
<!--            </if>-->
<!--        </where>-->
        <trim prefix="where" prefixOverrides="and">
            <if test="null != sid and '' != sid">
                and sid  = #{sid}
            </if>
            <if test="null != sname and '' != sname">
                and sanme like '%${sname}%'
            </if>
            <if test="null == course">
                and course = 'Java'
            </if>
        </trim>
    </select>
    <update id="updateStudent" parameterType="student">
        update student
<!--        <set>-->
<!--            <if test="null != sname and '' != sname">-->
<!--                 sname = #{sname},-->
<!--            </if>-->
<!--            <if test="null != age and '' != age">-->
<!--                 age = #{age},-->
<!--            </if>-->
<!--        </set>-->
<!--        where sid = #{sid}-->
        <trim prefix="set" suffixOverrides=",">
            <if test="null != sname and '' != sname">
                sname = #{sname},
            </if>
            <if test="null != age and '' != age">
                age = #{age},
            </if>
        </trim>
        where sid = #{sid}
    </update>

    <select id="findStudentByForeach" resultType="student">
        select * from student where sid in
        <foreach collection="list" item="sid" index="index" open="(" separator="," close=")">
            #{sid}
        </foreach>
    </select>
    <select id="findStudentByBind" resultType="student" parameterType="student">
        select * from student where
        <bind name="sname_pattern" value="'%'+sname+'%'"></bind>
        <if test="null != sname_pattern and '' != sname_pattern ">
            sname like #{sname_pattern}
        </if>
    </select>
</mapper>

动态条件查询索引_xml_09

（5）< trim >元素

下面展示一些 代码片。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="student">
    <select id="findStudentBySnameAndCourse" parameterType="student" resultType="student">
            select * from student where sname = #{sname}
           <!--根据条件动态拼装SQL语句-->
           <if test="null!=course and ''!=course"> and course = #{course}</if>
    </select>
    <select id="findStudentByChoose" parameterType="student" resultType="student">
<!--        select * from student where 1 = 1-->
<!--        <choose>-->
<!--            <!–如果sid不为null或者空字符串–>-->
<!--            <when test="null != sid and '' != sid">-->
<!--                and sid = #{sid}-->
<!--            </when>-->
<!--             <!–如果sname不为null或者空字符串–>-->
<!--            <when test="null != sname and '' != sname">-->
<!--                and sname like '%${sname}%'-->
<!--            </when>-->
<!--            <!–如果上面不满足，则执行下面的–>-->
<!--            <otherwise>-->
<!--                and course = 'Java'-->
<!--            </otherwise>-->
<!--        </choose>-->
        select * from student
<!--        <where>-->
<!--            <if test="null != sid and '' != sid">-->
<!--                and sid  = #{sid}-->
<!--            </if>-->
<!--            <if test="null != sname and '' != sname">-->
<!--                and sanme like '%${sname}%'-->
<!--            </if>-->
<!--            <if test="null == course">-->
<!--                and course = 'Java'-->
<!--            </if>-->
<!--        </where>-->
        <trim prefix="where" prefixOverrides="and">
            <if test="null != sid and '' != sid">
                and sid  = #{sid}
            </if>
            <if test="null != sname and '' != sname">
                and sanme like '%${sname}%'
            </if>
            <if test="null == course">
                and course = 'Java'
            </if>
        </trim>
    </select>
    <update id="updateStudent" parameterType="student">
        update student
<!--        <set>-->
<!--            <if test="null != sname and '' != sname">-->
<!--                 sname = #{sname},-->
<!--            </if>-->
<!--            <if test="null != age and '' != age">-->
<!--                 age = #{age},-->
<!--            </if>-->
<!--        </set>-->
<!--        where sid = #{sid}-->
        <trim prefix="set" suffixOverrides=",">
            <if test="null != sname and '' != sname">
                sname = #{sname},
            </if>
            <if test="null != age and '' != age">
                age = #{age},
            </if>
        </trim>
        where sid = #{sid}
    </update>

    <select id="findStudentByForeach" resultType="student">
        select * from student where sid in
        <foreach collection="list" item="sid" index="index" open="(" separator="," close=")">
            #{sid}
        </foreach>
    </select>
    <select id="findStudentByBind" resultType="student" parameterType="student">
        select * from student where
        <bind name="sname_pattern" value="'%'+sname+'%'"></bind>
        <if test="null != sname_pattern and '' != sname_pattern ">
            sname like #{sname_pattern}
        </if>
    </select>
</mapper>

动态条件查询索引_sql_10

动态条件查询索引_sql_11

（6）< foreach >元素

下面展示一些 代码片。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="student">
    <select id="findStudentBySnameAndCourse" parameterType="student" resultType="student">
            select * from student where sname = #{sname}
           <!--根据条件动态拼装SQL语句-->
           <if test="null!=course and ''!=course"> and course = #{course}</if>
    </select>
    <select id="findStudentByChoose" parameterType="student" resultType="student">
<!--        select * from student where 1 = 1-->
<!--        <choose>-->
<!--            <!–如果sid不为null或者空字符串–>-->
<!--            <when test="null != sid and '' != sid">-->
<!--                and sid = #{sid}-->
<!--            </when>-->
<!--             <!–如果sname不为null或者空字符串–>-->
<!--            <when test="null != sname and '' != sname">-->
<!--                and sname like '%${sname}%'-->
<!--            </when>-->
<!--            <!–如果上面不满足，则执行下面的–>-->
<!--            <otherwise>-->
<!--                and course = 'Java'-->
<!--            </otherwise>-->
<!--        </choose>-->
        select * from student
<!--        <where>-->
<!--            <if test="null != sid and '' != sid">-->
<!--                and sid  = #{sid}-->
<!--            </if>-->
<!--            <if test="null != sname and '' != sname">-->
<!--                and sanme like '%${sname}%'-->
<!--            </if>-->
<!--            <if test="null == course">-->
<!--                and course = 'Java'-->
<!--            </if>-->
<!--        </where>-->
        <trim prefix="where" prefixOverrides="and">
            <if test="null != sid and '' != sid">
                and sid  = #{sid}
            </if>
            <if test="null != sname and '' != sname">
                and sanme like '%${sname}%'
            </if>
            <if test="null == course">
                and course = 'Java'
            </if>
        </trim>
    </select>
    <update id="updateStudent" parameterType="student">
        update student
<!--        <set>-->
<!--            <if test="null != sname and '' != sname">-->
<!--                 sname = #{sname},-->
<!--            </if>-->
<!--            <if test="null != age and '' != age">-->
<!--                 age = #{age},-->
<!--            </if>-->
<!--        </set>-->
<!--        where sid = #{sid}-->
        <trim prefix="set" suffixOverrides=",">
            <if test="null != sname and '' != sname">
                sname = #{sname},
            </if>
            <if test="null != age and '' != age">
                age = #{age},
            </if>
        </trim>
        where sid = #{sid}
    </update>

    <select id="findStudentByForeach" resultType="student">
        select * from student where sid in
        <foreach collection="list" item="sid" index="index" open="(" separator="," close=")">
            #{sid}
        </foreach>
    </select>
    <select id="findStudentByBind" resultType="student" parameterType="student">
        select * from student where
        <bind name="sname_pattern" value="'%'+sname+'%'"></bind>
        <if test="null != sname_pattern and '' != sname_pattern ">
            sname like #{sname_pattern}
        </if>
    </select>
</mapper>

动态条件查询索引_java_12

（7）< bind >元素

下面展示一些 代码片。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="student">
    <select id="findStudentBySnameAndCourse" parameterType="student" resultType="student">
            select * from student where sname = #{sname}
           <!--根据条件动态拼装SQL语句-->
           <if test="null!=course and ''!=course"> and course = #{course}</if>
    </select>
    <select id="findStudentByChoose" parameterType="student" resultType="student">
<!--        select * from student where 1 = 1-->
<!--        <choose>-->
<!--            <!–如果sid不为null或者空字符串–>-->
<!--            <when test="null != sid and '' != sid">-->
<!--                and sid = #{sid}-->
<!--            </when>-->
<!--             <!–如果sname不为null或者空字符串–>-->
<!--            <when test="null != sname and '' != sname">-->
<!--                and sname like '%${sname}%'-->
<!--            </when>-->
<!--            <!–如果上面不满足，则执行下面的–>-->
<!--            <otherwise>-->
<!--                and course = 'Java'-->
<!--            </otherwise>-->
<!--        </choose>-->
        select * from student
<!--        <where>-->
<!--            <if test="null != sid and '' != sid">-->
<!--                and sid  = #{sid}-->
<!--            </if>-->
<!--            <if test="null != sname and '' != sname">-->
<!--                and sanme like '%${sname}%'-->
<!--            </if>-->
<!--            <if test="null == course">-->
<!--                and course = 'Java'-->
<!--            </if>-->
<!--        </where>-->
        <trim prefix="where" prefixOverrides="and">
            <if test="null != sid and '' != sid">
                and sid  = #{sid}
            </if>
            <if test="null != sname and '' != sname">
                and sanme like '%${sname}%'
            </if>
            <if test="null == course">
                and course = 'Java'
            </if>
        </trim>
    </select>
    <update id="updateStudent" parameterType="student">
        update student
<!--        <set>-->
<!--            <if test="null != sname and '' != sname">-->
<!--                 sname = #{sname},-->
<!--            </if>-->
<!--            <if test="null != age and '' != age">-->
<!--                 age = #{age},-->
<!--            </if>-->
<!--        </set>-->
<!--        where sid = #{sid}-->
        <trim prefix="set" suffixOverrides=",">
            <if test="null != sname and '' != sname">
                sname = #{sname},
            </if>
            <if test="null != age and '' != age">
                age = #{age},
            </if>
        </trim>
        where sid = #{sid}
    </update>

    <select id="findStudentByForeach" resultType="student">
        select * from student where sid in
        <foreach collection="list" item="sid" index="index" open="(" separator="," close=")">
            #{sid}
        </foreach>
    </select>
    <select id="findStudentByBind" resultType="student" parameterType="student">
        select * from student where
        <bind name="sname_pattern" value="'%'+sname+'%'"></bind>
        <if test="null != sname_pattern and '' != sname_pattern ">
            sname like #{sname_pattern}
        </if>
    </select>
</mapper>

动态条件查询索引_mybatis_13

7.编写测试类

（1）TestIf类

下面展示一些 代码片。

package com.qfedu.test;

import com.qfedu.pojo.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.*;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class TestIf {
    public static void main(String[] args) {
        String resource = "mybatis-config.xml";
        try {
            InputStream in = Resources.getResourceAsStream(resource);
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
            SqlSession sqlSession = factory.openSession();
            Student student = new Student();
            student.setSname("LiSi");
            student.setCourse("Java");
            List<Student> selectList = sqlSession.selectList("student.findStudentBySnameAndCourse", student);
            for (Student stu : selectList){
                System.out.println(stu.toString());
            }
            sqlSession.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}

动态条件查询索引_mybatis_14

动态条件查询索引_java_15

动态条件查询索引_xml_16

（2）TestChoose类

下面展示一些 代码片。

package com.qfedu.test;

import com.qfedu.pojo.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class TestChoose {
    public static void main(String[] args) {
        String resource = "mybatis-config.xml";
        try {
            InputStream in = Resources.getResourceAsStream(resource);
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
            SqlSession sqlSession = factory.openSession();
            Student student = new Student();
//            student.setSid(2);
//            student.setSname("LiSi");
            List<Student> selectList = sqlSession.selectList("student.findStudentByChoose", student);
            for (Student stu : selectList){
                System.out.println(stu.toString());
            }
            sqlSession.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}

动态条件查询索引_mybatis_17

动态条件查询索引_sql_18

动态条件查询索引_动态条件查询索引_19

动态条件查询索引_动态条件查询索引_20

动态条件查询索引_xml_21

（3）TestSet类

下面展示一些 代码片。

package com.qfedu.test;

import com.qfedu.pojo.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

public class TestSet {
    public static void main(String[] args) {
        String resource = "mybatis-config.xml";
        try {
            InputStream in = Resources.getResourceAsStream(resource);
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
            SqlSession sqlSession = factory.openSession();
            Student student = new Student();
//            为student成员属性赋值
            student.setSid(4);
//            student.setSname("ZhaoLiu");
//            student.setAge("20");
            student.setSname("SunQi");
            student.setAge("21");
//            调用sqlSession的update()方法
            int result = sqlSession.update("student.updateStudent",student);
            if(result>0){
                System.out.println("成功更新"+result+"条数据");
            }else{
                System.out.println("更新操作失败");
            }
            sqlSession.commit();
            sqlSession.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}

动态条件查询索引_sql_22

动态条件查询索引_sql_23

（4）TestForeach类

下面展示一些 代码片。

package com.qfedu.test;

import com.qfedu.pojo.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class TestForeach {
    public static void main(String[] args) {
//        读取配置文件
        String resource = "mybatis-config.xml";
//        输入流
        try {
            InputStream in = Resources.getResourceAsStream(resource);
//            创建SQLSessionFactory对象
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
//            创建sqlSession对象
            SqlSession sqlSession = factory.openSession();
            ArrayList<Integer> list = new ArrayList<Integer>();
            list.add(1);
            list.add(2);
            list.add(3);
//            调用SqlSession的selectList()方法查询
            List<Student> stuList = sqlSession.selectList("student.findStudentByForeach", list);
            for (Student stu:stuList){
                System.out.println(stu.toString());
            }
//            关闭SqlSession
            sqlSession.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

动态条件查询索引_java_24

（5）TestBind类

下面展示一些 代码片。

package com.qfedu.test;

import com.qfedu.pojo.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class TestBind {
    public static void main(String[] args) {
//        加载配置文件
        String resource = "mybatis-config.xml";
        try {
            InputStream in = Resources.getResourceAsStream(resource);
//            创建SQLSessionFactory对象
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
//            创建SqlSession对象
            SqlSession sqlSession = factory.openSession();
            Student student = new Student();
            student.setSname("LiSi");
            List<Student> selectList = sqlSession.selectList("student.findStudentByBind",student);
            for (Student stu:selectList){
                System.out.println(stu.toString());
            }
            sqlSession.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

动态条件查询索引_java_25