JavaEE实验三：动态SQL

实验三：动态SQL

使用动态SQL进行条件查询、更新以及复杂查询操作。本实验要求利用本章所学知识完成一个学生信息系统，该系统要求实现3个以下功能:
1、多条件查询： 当用户输入的学生姓名不为空，则根据学生姓名进行学生信息的查询； 当用户输入的学生姓名为空，而学生专业不为空，则只根据学生专业进行学生的查询； 当用户输入的学生姓名不为空，且专业不为空，则根据姓名和专业查询学生信息。
当学生姓名和专业都为空，则查询所有学生信息
2、单条件查询：查询出所有id值小于5的学生的信息；
3、学生数据插入：插入三条学生的信息；
创建学生表(tb_student)并输入3条记录，表结构如下:

 CREATE TABLE tb_student(
    id int(32) PRIMARY KEY AUTO_INCREMENT,
    name varchar(50),  
    major varchar(50),    
    varchar(16) );

1、创建maven项目，在pom.xml文件中配置依赖：MyBatis、mysql、Junit依赖

<?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>sy2</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>
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                    <include>**/*.properties</include>
                </includes>
                <filtering>true</filtering>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.xml</include>
                    <include>**/*.properties</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>
    </project>

2、新建实体类Student

package com.pojo;
public class Student {
    private  Integer id;
    private  String name;
    private  String major;
    private  String sno;
    public Student() {
    }

    public Student(String name, String major, String sno) {
        this.name = name;
        this.major = major;
        this.sno = 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 "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", major='" + major + '\'' +
                ", sno='" + sno + '\'' +
                '}';
    }
}

3、在resources下建db.properties、mybatis-config.xml文件。注意要在mybatis-config.xml文件中引入db.properties文件

//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=123456
//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="mapper/StudentMapper.xml"/>
    </mappers>
</configuration>

4、在src.main.resources中建包com.mapper,包中建，StudentMapper接口

package com.mapper;
import com.pojo.Student;
import java.util.List;
import java.util.Map;
public interface StudentMapper {
    List<Student> findStudentByName(Student  student);
    List<Student> findStudentById(Integer[] array);
    List<Student> findAllStudent(Student  student);
    int saveStudent(Map map);
    List<Student> findStudentByNameOrMajor(Student  student);
    List<Student> findStudentByNameAndMajor(Student  student);
}

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

<?xml version="1.0" encoding="UTF8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- namespace指定Dao接口的完整类名,mybatis会依据这个接口动态创建一个实现类去实现这个接口，
     而这个实现类是一个Mapper对象-->
<mapper namespace="com.mapper.StudentMapper">
    <select id="findStudentByName" parameterType="student"
            resultType="student">
        select * from tb_student where 1=1
        <if test="name!=null and name!=''">
            and name like concat('%',#{name},'%')
        </if>

    </select>
    <select id="findStudentByNameOrMajor" parameterType="student"
            resultType="student">
        select * from tb_student
        <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="findStudentByNameAndMajor" parameterType="student"
            resultType="student">
        select * from tb_student
        <where>
            <if test="name!=null and name!=''">
                and name like concat('%',#{name},'%')
            </if>
            <if test="major!=null and major!=''">
                and major=#{major}
            </if>
        </where>
    </select>
    <select id="findAllStudent" parameterType="student"
            resultType="student">
        select * from tb_student
        <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="student">
        select * from tb_student
        <where>
            <foreach item="id" index="index" collection="array"
                     open="id in(" separator="," close=")">#{id}
            </foreach>
        </where>
    </select>
    <insert id="saveStudent" parameterType="map" >
        insert into tb_student(name,major,sno) values
        <foreach collection="students" item="student" index="i" separator=",">
            (#{student.name},#{student.major},#{student.sno})
        </foreach>
    </insert>
</mapper>

6、在Test.java目录中创建测试类。

import com.mapper.StudentMapper;
import com.pojo.Student;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class IfTest {
    @Test
    public void findStudentTest1() {
        SqlSession session = MybatisUtils.getSqlsession();
        StudentMapper mapper = session.getMapper(StudentMapper.class);
        Student s1 = new Student();
        s1.setName("朱其林");

        List<Student> students = mapper.findStudentByName(s1);
        for (Student s : students) {
            System.out.println(s);
        }
        session.close();
    }

    @Test
    public void findStudentTest2() {
        SqlSession session = MybatisUtils.getSqlsession();
        StudentMapper mapper = session.getMapper(StudentMapper.class);
        Student s2 = new Student();
        s2.setMajor("软件工程");
        List<Student> students = mapper.findStudentByNameOrMajor(s2);
        for (Student s : students) {
            System.out.println(s);
        }
        session.close();
    }

    @Test
    public void findStudentsTest3() {
        SqlSession session = MybatisUtils.getSqlsession();
        StudentMapper mapper = session.getMapper(StudentMapper.class);
        Student s3 = new Student();
        s3.setName("朱其林");
        s3.setMajor("计算机科学");
        List<Student> students = mapper.findStudentByNameAndMajor(s3);
        for (Student s : students) {
            System.out.println(s);
        }
        session.close();
    }
    @Test
    public void findStudentsTest4() {
        SqlSession session = MybatisUtils.getSqlsession();
        StudentMapper mapper = session.getMapper(StudentMapper.class);
        Student s4 = new Student();
        List<Student> students = mapper.findAllStudent(s4);
        for (Student s : students) {
            System.out.println(s);
        }
        session.close();
    }

    @Test
    public void findStudentsTest5() {
        SqlSession session = MybatisUtils.getSqlsession();
        StudentMapper mapper = session.getMapper(StudentMapper.class);
        Integer[] Ids = {1,2,3,4};
        List<Student> students = mapper.findStudentById(Ids);
        for (Student s : students) {
            System.out.println(s);
        }
        session.close();
    }
    @Test
    public void saveStudentTest() {
        SqlSession session = MybatisUtils.getSqlsession();
        StudentMapper mapper = session.getMapper(StudentMapper.class);
        Map<String,Object> map=new HashMap<>();
        Student s1=new Student("张三","体育","031");
        Student s2=new Student("李四","机电","041");
        Student s3=new Student("朱六","多媒体","051");
        List<Student> students= new ArrayList<Student>();
        students.add(s1);
        students.add(s2);
        students.add(s3);
        map.put("students",students);
        int n = mapper.saveStudent(map);
        if (n > 0)
            session.commit();
        List<Student> cs = mapper.findStudentByNameAndMajor(new Student());
        for (Student s : cs) {
            System.out.println(s);
        }
        session.close();

    }
}

7、MybatisUtils

package com.utils;

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 java.io.IOException;
import java.io.InputStream;

public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;
    static {
    try
    {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }
    catch(IOException e) {
        e.printStackTrace();
    }
}
    public  static SqlSession getSqlsession(){return  sqlSessionFactory.openSession();}
}

8、运行测试方法。

1、多条件查询： 当用户输入的学生姓名不为空，则根据学生姓名进行学生信息的查询；

2、多条件查询： 当用户输入的学生姓名为空，而学生专业不为空，则只根据学生专业进行学生的查询；

3、多条件查询：当用户输入的学生姓名不为空，且专业不为空，则根据姓名和专业查询学生信息。

4、多条件查询：当学生姓名和专业都为空，则查询所有学生信息

5.单条件查询：查询出所有id值小于5的学生的信息；
6.学生数据插入：插入三条学生的信息；