题目要求:

使用动态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文件中配置以依赖

    4.0.0
    org.example
    Example
    1.0-SNAPSHOT
    
        17
        17
    
    
        
            org.mybatis
            mybatis
            3.5.2
        
        
            junit
            junit
            4.12
            test
        
        
            mysql
            mysql-connector-java
            8.0.11
        
    
    
        
            
                src/main/java
                
                    **/*.properties
                    **/*.xml
                
                true
            
        
    

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

4.创建StudentMapper接口

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

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

    
        select * from user where 1=1
        
            and name like concat('%',#{name},'%')
        
    
    
        select * from user
        
            
                
                    and name like concat('%',#{name}, '%')
                
                
                    and major= #{major}
                
            
        
    
    
        select * from user
        
            
                
                    and name like concat('%',#{name}, '%')
                
                
                    and major= #{major}
                
                and id is not null
            
        
    
    
        select * from user
        
            #{id}
            
        
    

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 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 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 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 studentById = mapper.findStudentById(strId);
        System.out.println("*************************  前面4位  *******************");
        for (StudentEntity s : studentById) {
            System.out.println(s);
        }
    }
}

8.测试结果