【JavaEE案例】学生信息查询系统

梦里梦外; 2024-03-25 09:35 93阅读 0赞

**【案例介绍】**

利用动态SQL进行条件查询、更新和复杂查询操作等所学知识，完成一个学生信息查询系统，该系统要求实现以下2个功能。

（1）多条件查询。

当用户输入的学生姓名不为空时，则只根据学生姓名进行学生信息的查询。

当用户输入的学生姓名为空而学生专业不为空时，则只根据学生专业进行学生信息的查询。

当用户输入的学生姓名和专业都为空，则要求查询出所有学号不为空的学生信息。

（2）单条件查询出所有id值小于5的学生的信息。

**【案例实现】**

![0fd8d4485cd7447bb2e06407f7c2e422.png][]

**1.数据准备：**在MySQL中创建一个名称为mybatis的数据库。

CREATE DATABASE mybatis;

在数据库中创建dm\_student表

CREATE TABLE dm_student(
    	id int(32) PRIMARY KEY AUTO_INCREMENT,
    	name VARCHAR(50),
    	major VARCHAR(50),
    	sno VARCHAR(16)
    );

并插入几条测试数据

INSERT INTO dm_student VALUES('1','刘一','语文','10001');
    INSERT INTO dm_student VALUES('2','陈二','数学','10002');
    INSERT INTO dm_student VALUES('3','张三','英语','10003');
    INSERT INTO dm_student VALUES('4','李四','物理','10004');
    INSERT INTO dm_student VALUES('5','王五','生物','10005');
    INSERT INTO dm_student VALUES('6','赵六','化学','10006');
    INSERT INTO dm_student VALUES('7','孙七','体育','10007');

**2.引入相关依赖：**在项目的pom.xml文件中导入MySQL驱动包、JUnit测试包、MyBatis的核心包等相关依赖。

<?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.xinhua</groupId>
        <artifactId>MyBatisTest</artifactId>
        <version>1.0-SNAPSHOT</version>
     
        <properties>
            <maven.compiler.source>17</maven.compiler.source>
            <maven.compiler.target>17</maven.compiler.target>
            <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        </properties>
     
        <dependencies>
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis</artifactId>
                <version>3.5.11</version>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.28</version>
            </dependency>
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.12</version>
                <scope>compile</scope>
            </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>

**3.POJO类准备：**创建持久化类Student，在类中声明id、name、major和sno属性，以及属性对应的getter/setter方法。

package com.itheima.pojo;
    //Student类用于封装Student对象的属性
    
    public class Student {
        private Integer id;
        private String name;
        private String major;
        private String sno;
    
        public void setId(Integer id) {
            this.id = id;
        }
        public void setName(String name) {
            this.name = name;
        }
        public void setMajor(String major) {
            this.major = major;
        }
        public void setSno(String sno) {
            this.sno = sno;
        }
    
        public Integer getId() {
            return id;
        }
        public String getName() {
            return name;
        }
        public String getMajor() {
            return major;
        }
        public String getSno() {
            return sno;
        }
    
        @Override
        public String toString(){
            return "Student{" + "id=" + id + ",name=" + name +",major=" + major + ",sno=" + sno + "," ;
        }
    }

**4.创建映射文件StudentMapper.xml：**该文件主要用于配置SQL语句和Java对象之间的映射 。

<?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="com.itheima.mappers.StudentMapper">
        
        <select id="findStudentByNameAndMajor" parameterType="com.itheima.pojo.Student"
                resultType="com.itheima.pojo.Student">
            select * from dm_student where 1=1
            <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 sno is not null
                </otherwise>
            </choose>
        </select>
        
        <select id="findByList" parameterType="java.util.List" resultType="com.itheima.pojo.Student">
            select * from dm_student where id in
            <foreach item="id" index="index" collection="list"
                     open="(" separator="," close=")">
                #{id}
            </foreach>
        </select>
    </mapper>

**5.创建数据库连接信息配置文件db.properties：**在该文件中配置数据库连接的参数。

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

**6.创建MyBatis的核心配置文件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="db.properties"/>
        <environments default="development">
            <environment id="development">
                <transactionManager type="JDBC"/>
                
                <dataSource type="POOLED">
                    <property name="driver" value="${mysql.driver}"/>
                    <property name="url" value="${mysql.url}"/>
                    <property name="username" value="${mysql.username}"/>
                    <property name="password" value="${mysql.password}"/>
                </dataSource>
            </environment>
        </environments>
    
        
        <mappers>
            <mapper resource="mappers/StudentMapper.xml"/>
        </mappers>
    
    </configuration>

**7.编写mybatisUtils工具类：**该类用于封装读取配置文件信息的代码。

package com.itheima.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 = null;
        //初始化SQLSessionFactory类加载MyBatis的配置文件
        static {
            try {
                //使用MyBatis提供的Resources类加载MyBatis的配置文件
                Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
                //构建SqlSessionFactory工厂
                sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            }catch(Exception e){
                e.printStackTrace();
            }
        }
        public static SqlSession getSession(){
            //获取SqlSession对象的静态方法
            return sqlSessionFactory.openSession(true);
            //若传入true表示关闭事务控制，自动提交;false表示开启事务控制
        }
    }

**8.编写测试方法：**在测试类MybatisTest中，编写测试方法findStudentBynameOrMajorTest()和findByListTest()。

package com.itheima;
    import com.itheima.pojo.Student;
    import com.itheima.Utils.MyBatisUtils;
    import org.apache.ibatis.session.SqlSession;
    import org.junit.Test;
    import java.util.List;
    import java.util.ArrayList;
    
    public class MyBatisTest {
        @Test
        public void findStudentByNameOrMajorTest(){
            //获取SqlSession
            SqlSession sqlsession = MyBatisUtils.getSession();
            //创建Student对象，并向对象中添加数据
            Student student = new Student();
            student.setName("张三");
            student.setMajor("体育");
            //执行SqlSession的查询方法，返回结果集
            List<Student> students = sqlsession.selectList("com.itheima.mappers"
                    + ".StudentMapper.findStudentByNameAndMajor",student);
            //输出查询结果信息
            for(Student Student2: students){
                //输出结果信息
                System.out.println(Student2);
            }
            //关闭SqlSession
            sqlsession.close();
        }
    
        @Test
        public void findByListTest() {
            //获取SqlSession
            SqlSession sqlsession = MyBatisUtils.getSession();
            //创建List集合，封装查询id
            List<Integer> ids = new ArrayList<Integer>();
            //遍历id<5
            for (int i = 0; i < 5; i++) {
                ids.add(i);
            }
            //执行SqlSession的查询方法，返回结果集
            List<Student> students = sqlsession.selectList("com.itheima.mappers"
                    + ".StudentMapper.findByList", ids);
            //输出查询结果信息
            for (Student Student : students) {
                //输出结果信息
                System.out.println(Student);
            }
            //关闭SqlSession
            sqlsession.close();
        }
    }

**9.运行结果**

![c6f87e36337742c79a27567e4d8242a6.png][]

![fed0e8a3cb134585840bc188fb6478e8.png][]

[0fd8d4485cd7447bb2e06407f7c2e422.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/e9d324ec58244248a5cfa2a298091899.png
[c6f87e36337742c79a27567e4d8242a6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/4ff688ad1d484cb0b814c43b77be5b19.png
[fed0e8a3cb134585840bc188fb6478e8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/ae07a93da9f84df3a69e655345f0e757.png