MyBatis实训案例2-学生信息管理系统

任务：

完成一个学生信息查询系统，该系统要求实现以下2个功能。

（1）多条件查询

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

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

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

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

实现代码：

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>jsj.hhtc</groupId>
    <artifactId>javaee1_01</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

    </dependencies>
    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>


</project>

db.properties

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

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC
        "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <properties resource="db.properties"/>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <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="jsj/hhtc/mapper/StudentMapper.xml"></mapper>
    </mappers>

</configuration>

实体类Student

package jsj.hhtc.pojo;

public class Student {
    private int id;
    private String xh;
    private String name;
    private String major;

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

    public int getId() {
        return id;
    }

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

    public String getXh() {
        return xh;
    }

    public void setXh(String xh) {
        this.xh = xh;
    }

    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;
    }
}

StudentMapper.xml

<?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="jsj.hhtc.mapper.StudentMapper">
    <select id="findStudentByNameOrMajor" parameterType="jsj.hhtc.pojo.Student" resultType="jsj.hhtc.pojo.Student" >
        select * from 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 like concat('%',#{major},'%')
        </when>
        <otherwise>
            and xh is not null
        </otherwise>
        </choose>
    </select>

    <select id="findStudentByIds" parameterType="java.util.Arrays" resultType="jsj.hhtc.pojo.Student">
        select * from student where id in
        <foreach item="id" index="index" collection="list"
        open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>
</mapper>

测试类

package Test;

import jsj.hhtc.pojo.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.Reader;
import java.util.*;

public class MybatisTest {
        private SqlSessionFactory sqlSessionFactory;
        private SqlSession sqlSession;

        @Before
        public void init() {
            //定义读取文件名
            String resources = "mybatis-config.xml";
            //创建流
            Reader reader = null;
            try {
                //读取mybatis-config.xml文件到reader对象中
                reader = Resources.getResourceAsReader(resources);
                //初始化mybatis,创建SqlSessionFactory类的对象
                SqlSessionFactory sqlMapper = new
                        SqlSessionFactoryBuilder().build(reader);
                //创建session对象
                sqlSession = sqlMapper.openSession();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

          @Test
        public void findStudentByNameOrMajorTest(){
            Student s = new Student();
            //s.setName("四");
            //s.setMajor("计算机");
            List<Student> list =
                    sqlSession.selectList("jsj.hhtc.mapper.StudentMapper." +
                            "findStudentByNameOrMajor",s);
            for (Student student : list) {
                System.out.println(student);
            }
        }

        @Test
        public void findStudentByIdsTest() {
            List<Integer> ids = new ArrayList<Integer>();
            for (int i = 1; i < 5; i++) {
                ids.add(i);
            }
            List<Student> list =
                    sqlSession.selectList("jsj.hhtc.mapper.StudentMapper." +
                            "findStudentByIds",ids);
            for (Student student : list) {
                System.out.println(student);
            }
        }



    @After
    public void destory() {
        //提交事务
        sqlSession.commit();
        //关闭事务
        sqlSession.close();
    }
}