JavaEE实验三：3.5学生信息查询系统（动态Sql）

使用动态SQL进行条件查询、更新以及复杂查询操作。本实验要求利用本章所学知识完成一个学生信息系统，该系统要求实现3个以下功能:
1、多条件查询： 当用户输入的学生姓名不为空，则根据学生姓名进行学生信息的查询； 当用户输入的学生姓名为空而学生专业不为空，则只根据学生专业进行学生的查询；当学生姓名和专业都为空，则查询所有学生信息
2、单条件查询：查询出所有id值小于5的学生的信息；

先创建一个数据库 user 表：

CREATE TABLE user(
    id int(32) PRIMARY KEY AUTO_INCREMENT,
    name varchar(50),
    major varchar(50),
    userId varchar(16)
);

再插入数据：

# 插入7条数据
INSERT INTO user VALUES ('1', '张三', 'spring', '202101');
INSERT INTO user VALUES ('2', '李四', 'mybatis', '202102');
INSERT INTO user VALUES ('3', '王二', 'reids', '202103');
INSERT INTO user VALUES ('4', '小张', 'springMVC', '202104');
INSERT INTO user VALUES ('5', '小红', 'springBoot', '202105');
INSERT INTO user VALUES ('6', '小王', 'springcloud', '202106');
INSERT INTO user VALUES ('7', '小芬', 'vue', '202107');

1.创建maven项目，在pom.xml文件中配置以依赖

2.创建实体类StudentEntity

3.创建jdbc.properties和mybatis-config.xml配置文件

4.创建StudentMapper接口

5.在mybatis-config.xml文件中注册StudentMapper.xml

6.创建测试类

7.工具类

8.测试结果

1.创建maven项目，在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>Example</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.2</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>
    </dependencies>
    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>
</project>

2.创建实体类StudentEntity

package com.gcy.entity;

public class StudentEntity {
    private Integer id;
    private String name;
    private String major;
    private String sno;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getMajor() {
        return major;
    }

    public void setMajor(String major) {
        this.major = major;
    }

    public String getSno() {
        return sno;
    }

    public void setSno(String sno) {
        this.sno = sno;
    }

    @Override
    public String toString() {
        return "StudentEntity{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", major='" + major + '\'' +
                ", sno='" + sno + '\'' +
                '}';
    }
}

3.创建jdbc.properties和mybatis-config.xml配置文件

jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/mybatis?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false
jdbc.username=root
jdbc.password=200381

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>
    <!-- 环境配置 -->
    <!-- 加载类路径下的属性文件 -->
    <properties resource="jdbc.properties"/>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <!-- 数据库连接相关配置 ,db.properties文件中的内容-->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="com/gcy/mapper/StudentMaper.xml"/>
    </mappers>
</configuration>

4.创建StudentMapper接口

package com.gcy.mapper;
import com.gcy.entity.StudentEntity;
import java.util.List;
public interface StudentMapper {
    List<StudentEntity> findStudentByName(StudentEntity  student);
    List<StudentEntity> findStudentById(Integer[] array);
    List<StudentEntity> findAllStudent(StudentEntity  student);
    List<StudentEntity> findStudentByNameOrMajor(StudentEntity  student);
}

5.在mybatis-config.xml文件中注册StudentMapper.xml

<?xml version="1.0" encoding="UTF8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.gcy.mapper.StudentMapper">
    <select id="findStudentByName" parameterType="com.gcy.entity.StudentEntity"
            resultType="com.gcy.entity.StudentEntity">
        select * from user where 1=1
        <if test="name!=null and name!=''">
            and name like concat('%',#{name},'%')
        </if>

    </select>
    <select id="findStudentByNameOrMajor" parameterType="com.gcy.entity.StudentEntity"
            resultType="com.gcy.entity.StudentEntity">
        select * from user
        <where>
            <choose>
                <when test="name !=null and name !=''">
                    and name like concat('%',#{name}, '%')
                </when>
                <when test="major !=null and major !=''">
                    and major= #{major}
                </when>
            </choose>
        </where>
    </select>
    <select id="findAllStudent" parameterType="com.gcy.entity.StudentEntity"
            resultType="com.gcy.entity.StudentEntity">
        select * from user
        <where>
            <choose>
                <when test="name !=null and name !=''">
                    and name like concat('%',#{name}, '%')
                </when>
                <when test="major !=null and major !=''">
                    and major= #{major}
                </when>
                <otherwise>and id is not null</otherwise>
            </choose>
        </where>
    </select>
    <select id="findStudentById" parameterType="java.util.Arrays"
            resultType="com.gcy.entity.StudentEntity">
        select * from user
        <where>
            <foreach item="id" index="index" collection="array"
                     open="id in(" separator="," close=")">#{id}
            </foreach>
        </where>
    </select>
</mapper>

6.工具类

package com.gcy.utils;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
/**
 * 工具类
 */
public class MyBatisUtils {
    private static SqlSessionFactory sqlSessionFactory;
    // 初始化SqlSessionFactory对象
    static {
        try {
            // 使用MyBatis提供的Resources类加载MyBatis的配置文件
            Reader reader =
                    Resources.getResourceAsReader("mybatis-config.xml");
            // 构建SqlSessionFactory工厂
            sqlSessionFactory =
                    new SqlSessionFactoryBuilder().build(reader);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    // 获取SqlSession对象的静态方法
    public static SqlSession getSession() {
        return sqlSessionFactory.openSession();
    }
}

7.创建测试类

import com.gcy.entity.StudentEntity;
import com.gcy.mapper.StudentMapper;
import com.gcy.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;

import java.util.List;

public class Test {

    @org.junit.Test
    public void  Test01(){
        SqlSession session = MyBatisUtils.getSession();
        StudentMapper mapper = session.getMapper(StudentMapper.class);
        StudentEntity student = new StudentEntity();
        student.setName("张三");
        List<StudentEntity> findStudentByName = mapper.findStudentByName(student);
        System.out.println("*************************  姓名不为空  *******************");
        for (StudentEntity s : findStudentByName) {
            System.out.println(s);
        }
        session.close();
    }

    @org.junit.Test
    public void Test02(){
        SqlSession session = MyBatisUtils.getSession();
        StudentMapper mapper = session.getMapper(StudentMapper.class);
        StudentEntity student = new StudentEntity();
        student.setMajor("spring");
        List<StudentEntity> studentByNameOrMajor = mapper.findStudentByNameOrMajor(student);
        System.out.println("*************************  专业不为空  *******************");
        for (StudentEntity s : studentByNameOrMajor) {
            System.out.println(s);
        }
        session.close();
    }

    @org.junit.Test
    public void Test03(){
        SqlSession session = MyBatisUtils.getSession();
        StudentMapper mapper = session.getMapper(StudentMapper.class);
        StudentEntity student = new StudentEntity();
        List<StudentEntity> allStudent = mapper.findAllStudent(student);
        System.out.println("*************************  学号不为空  *******************");
        for (StudentEntity s : allStudent) {
            System.out.println(s);
        }
        session.close();
    }

    @org.junit.Test
    public void Test04(){
        SqlSession session = MyBatisUtils.getSession();
        StudentMapper mapper = session.getMapper(StudentMapper.class);
        Integer[] strId = {1,2,3,4};
        List<StudentEntity> studentById = mapper.findStudentById(strId);
        System.out.println("*************************  前面4位  *******************");
        for (StudentEntity s : studentById) {
            System.out.println(s);
        }
    }
}

8.测试结果