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;}@Overridepublic 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 configurationPUBLIC "-//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 mapperPUBLIC "-//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.Testpublic 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.Testpublic 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.Testpublic 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.Testpublic 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.测试结果