题目要求:

使用动态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.Driverjdbc.url=jdbc:mysql://127.0.0.1:3306/mybatis?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=falsejdbc.username=rootjdbc.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.测试结果