题目要求:
使用动态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); } }}