web实验：MyBatis框架应用
前言：
记录自己的web作业，第一个web项目，从忘记数据库密码等等折腾了两天，还遇到超级超级多的bug怪！ε=(´ο｀*)))害
下面虽然没有划出每个函数，不过代码里注释都比较清楚。这里指条明路，看懂一个测试函数的步骤：类——接口——映射（xml）——测试类
目录
- 一、实验环境
- 二、实验内容
- 三、实验过程
- (1）搭建数据库、建表、外键主键
- (2）新建一个IDEA maven项目
- (3）按顺序打包、连接数据库，创建类、接口、配置文件、测试文件
1 开始打包
2 编写连接数据库配置文件
3 配置日志文件
4 创建五个类和三个接口
5 创建三个配置文件
6 编写测试类 - 四、程序运行
1.实验环境
Tomcat9.0+IDEA+Mybatis+mysql+Navicat for Mysql+JDK1.8
注：
(1)此实验程序的包名：com.dgut.huangxiaoqing
(2)数据库：localhost/web
2.实验内容
掌握mybatis持久层框架，对数据库进行增删查改的操作，包括以下操作：
（1）设计数据库
（1）添加学生
（2）修改学生信息
（3）根据SID查询学生信息
（4）根据姓名、学号、性别组合查询，其中姓名支持模糊查询
（1）添加课程
（2）修改课程信息
（3）根据课程名、课程编号组合查询，其中课程名支持模糊查询，课程编号支持列表查询
（1）添加选课记录
（2）根据id删除选课记录
（3）根据选课id查询选课记录对应的课程信息及学生信息
（4）根据课程ID（即CID）统计选课的学生人数
三、实验过程
（1）搭建数据库、建表、外键主键
当然啦比较懒就手动创建了数据库，贴一位网友的Navicat for Mysql手动设置外键：点我
（2）新建一个IDEA maven项目
应该会吧？不会点我
（3）按顺序打包、连接数据库，创建：类、接口、配置文件、测试文件
<1>开始打包
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>com.dgut</groupId>
<artifactId>Mybatis01</artifactId>
<!-- 注意这里容易打包错误，不是pom，应该是jar -->
<packaging>jar</packaging>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.3</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.17</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
</dependencies>
</project>`
<2>连接数据库SqlMap-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>
<!-- 配置环境 -->
<environments default="development">
<!-- 配置mysql环境 -->
<environment id="development">
<!-- 配置事务 -->
<transactionManager type="JDBC"/>
<!-- 配置数据源连接池 -->
<dataSource type="POOLED">
<!-- 这⾥⽤得是mysql8的驱动包 -->
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url"
value="jdbc:mysql://localhost:3306/web?serverTimezone=UTC"/>
<property name="username" value="root"/>
<property name="password" value="填数据库密码"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="istudentdao.xml"/>
<mapper resource="icoursedao.xml"/>
<mapper resource="ichoosedao.xml"/>
</mappers>
</configuration>
<3>配置日志文件log4j.properties
为了看bug、查看系统反馈，网上很多的，可再找别人写好的，谁用谁知道。
# Set root category priority to INFO and its only appender to CONSOLE.
#log4j.rootCategory=INFO, CONSOLE debug info warn error fatal
log4j.rootCategory=debug, CONSOLE, LOGFILE
# Set the enterprise logger category to FATAL and its only appender to
CONSOLE.
log4j.logger.org.apache.axis.enterprise=FATAL, CONSOLE
# CONSOLE is set to be a ConsoleAppender using a PatternLayout.
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t]
# LOGFILE is set to be a File appender using a PatternLayout.
log4j.appender.LOGFILE=org.apache.log4j.FileAppender
log4j.appender.LOGFILE.File=d:axis.log
log4j.appender.LOGFILE.Append=true
log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout
log4j.appender.LOGFILE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t]
%-5p %30.30c %x - %m\n
<4>开始创建五个类和三个接口
因为类名一般都是大写字幕开头，接口名，一般是“I+类名+Dao“”命名，这里随意了。（写的时候内心：管他呢）
学生类Student & 接口istudentdao
package com.dgut.huangxiaoqing;
import java.util.List;
public class Student {
private Integer SID;
private String num;
private String sname;
private Integer age;
private String sex;
private List<Choose> choose;
public List<Choose> getChoose(){
return choose;
}
public void setChoose(List<Choose> choose) {
this.choose = choose;
}
//以下省略一万字get、set方法（留下了一对作记忆力不好时参考），下面四个类也省略略略略略略略略略略略略略略略略略略略略略略略略略略略略略略略略
@Override
public String toString() {
return "Student{" +
"SID=" + SID +
", num='" + num + '\'' +
", sname='" + sname + '\'' +
", age=" + age +
", sex='" + sex + '\'' +
'}';
}
}
package com.dgut.dao;
import com.dgut.huangxiaoqing.QureyStudentVO;
import com.dgut.huangxiaoqing.Student;
import java.util.List;
public interface istudentdao {
// 1.实现学生信息的添加、修改、查询；
// 1.1保存学生信息
public int save(Student s);
// 1.2修改、更新学生信息
public int update(Student s);
// 1.3查询所有学生
public List<Student> findAll();
// 1.4根据学生SID查询学生信息
public Student findStuByID(Integer SID);
// 1.5根据姓名、学号、性别组合查询，其中姓名支持模糊查询
public List<Student> findByCondition(QureyStudentVO q);
//1.6根据学号查找该学生的选课信息
public List<Student> findChooseBySID(Integer SID);
}
选课类Choose & 接口ichoosedao
package com.dgut.huangxiaoqing;
import java.util.List;
public class Course {
private Integer CID;
private String cname;
private String materials;
private Integer hour;
private List<Student> student;
//省略get、set方法
@Override
public String toString() {
return "Course{" +
"CID=" + CID +
", cname='" + cname + '\'' +
", materials=" + materials + '\'' +
",hour=" + hour +
'}';
}
}
package com.dgut.dao;
import com.dgut.huangxiaoqing.Choose;
import java.util.List;
public interface ichoosedao {
// 3.实现选课信息的添加、删除、查询
//3.1添加选课记录
public int save(Choose r);
//3.2根据id删除选课记录
public int delete(Integer r);
//3.3根据选课记录id查询对应的课程信息及学生信息
public List<Choose > findById(Integer id);
//3.4根据课程ID（即CID）统计选课的学生人数
public Integer studentCounts(Integer ID);
}
课程类Course & 接口icoursedao
package com.dgut.huangxiaoqing;
import java.sql.Date;
import java.util.List;
public class Choose {
private Integer id;
private Integer SID;
private Integer CID;
private Date time;
private List<Student> student;
private List<Course> course;
//省略get、set方法
@Override
public String toString() {
return "Choose{" +
"id=" + id +
",CID=" + CID +
",SID=" + SID +
", date='" + time + '\'' +
'}';
}
}
package com.dgut.dao;
import com.dgut.huangxiaoqing.Course;
import com.dgut.huangxiaoqing.QureyCourseVO;
import java.util.List;
public interface icoursedao {
// 2.实现课程信息的添加、修改、查询
//2.1添加课程
public int save(Course c);
//2.2修改课程信息
public int update(Course s);
//2.3 根据课程名、课程编号组合查询，其中课程名支持模糊查询，课程编号支持列表查询
public List<Course> findByCondition(QureyCourseVO q);
}
QureyStudentVO类 & QureyCourseVO类
package com.dgut.huangxiaoqing;
import java.util.List;
public class QureyStudentVO {
private String sname;
private String sex;
List<Integer> nums;
//省略get、set方法
}
}
package com.dgut.huangxiaoqing;
import java.util.List;
public class QureyCourseVO {
private String cname;
List<Integer> CIDs;
//省略get、set方法
}
}
<5>创建下面三个配置文件
（这一步是sql语句所在地）
istudentdao.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="com.dgut.dao.istudentdao">
<resultMap id="studentChooseMap" type="com.dgut.huangxiaoqing.Student">
<id column="SID" property="SID"/>
<result property="num" column="num"/>
<result property="sname" column="sname"/>
<result property="sex" column="sex"/>
<result property="age" column="age"/>
</resultMap>
<!-- 1.1查询整个学生表-->
<select id="findAll" resultMap="studentChooseMap">
select student.*,choose.id,choose.CID,choose.time
from Student
LEFT JOIN choose ON student.SID=choose.SID
</select>
<!-- 1.2新增学生-->
<insert id="save">
insert into student(SID,num) values (#{SID},#{num})
</insert>
<!-- 1.3更新学生信息-->
<update id="update">
update student set sname=#{sname},num=#{num}
where SID=#{SID}
</update>
<!-- 1.4根据学生SID查询学生，注意不是根据学号num-->
<select id="findStuByID" resultType="com.dgut.huangxiaoqing.Student">
select * from student where SID=#{SID}
</select>
<!-- 1.5根据姓名、学号、性别组合查询，其中姓名支持模糊查询-->
<select id="findByCondition" resultType="com.dgut.huangxiaoqing.Student"
parameterType= "com.dgut.huangxiaoqing.QureyStudentVO">
select * from student
<where>
<if test="sname != null">
and sname like #{sname}
</if>
<if test="sex != null">
and sex = #{sex}
</if>
<if test="nums != null and nums.size() > 0">
<foreach collection="nums" open="and num in (" close=")"
item="abc" separator=",">
#{abc}
</foreach>
</if>
</where>
</select>
<!-- 根据SID查询该学生的选课信息-->
<select id="findChooseBySID" resultMap="studentChooseMap">
SELECT student.*,choose.id,choose.CID,choose.time
From student
LEFT JOIN choose on choose.SID = student.SID
WHERE student.SID = #{SID}
</select>
</mapper>
icoursedao.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="com.dgut.dao.icoursedao">
<resultMap id="courseMap" type="com.dgut.huangxiaoqing.Course">
<id property="CID" column="CID"/>
<result property="cname" column="cname"/>
<result property="materials" column="materials"/>
<result property="hour" column="hour"/>
</resultMap>
<!-- 2.1添加课程-->
<insert id="save">
insert into course(CID,cname,materials) values (#{CID},#{cname},#{materials})
</insert>
<!-- 2.2修改课程信息-->
<update id="update">
update course set cname=#{cname},materials=#{materials},hour=#{hour}
where CID=#{CID}
</update>
<!-- 2.3根据课程名、课程编号组合查询，其中课程名支持模糊查，课程编号支持列表查询-->
<select id="findByCondition" resultType="com.dgut.huangxiaoqing.Course"
parameterType="com.dgut.huangxiaoqing.QureyCourseVO">
select * from course
<where>
<if test="cname != null">
and cname like #{cname}
</if>
<if test="CIDs != null and CIDs.size()>0">
<foreach collection="CIDs" open="and CID in (" close=")" item="abc" separator=",">
#{abc}
</foreach>
</if>
</where>
</select>
<!-- 通过课程ID查找课程-->
<select id="findCtuByID" resultType="com.dgut.huangxiaoqing.Course">
select * from course where CID=#{CID}
</select>
</mapper>
ichoosedao.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="com.dgut.dao.ichoosedao">
<resultMap id="chooseMap" type="com.dgut.huangxiaoqing.Choose">
<id property="id" column="id"/>
<result property="SID" column="SID"/>
<result property="CID" column="CID"/>
<result property="time" column="time"/>
<collection property="student" ofType="com.dgut.huangxiaoqing.Student">
<id property="SID" column="SID"/>
<result property="num" column="num"/>
<result property="sname" column="sname"/>
<result property="sex" column="sex"/>
<result property="age" column="age"/>
</collection>
<collection property="course" ofType="com.dgut.huangxiaoqing.Course">
<id property="CID" column="CID" jdbcType="INTEGER"/>
<result property="cname" column="cname" jdbcType="VARCHAR"/>
<result property="materials" column="materials" jdbcType="VARCHAR"/>
<result property="hour" column="hour" jdbcType="INTEGER"/>
</collection>
</resultMap>
<!-- 3.1添加选课记录-->
<insert id="save">
insert into choose(id,SID,CID,time) values (#{id},#{SID},#{CID},#{PRIMARY})
</insert>
<!-- 3.2根据id删除选课记录-->
<delete id="delete" >
delete from choose where id = #{uid}
</delete>
<!-- 3.3根据选课id查询选课记录对应的课程信息及学生信息-->
<select id="findById" resultMap="chooseMap">
select *
from choose a
LEFT JOIN student st ON a.SID=st.SID
RIGHT JOIN course ch ON a.CID=ch.CID
WHERE a.id=#{id}
</select>
<!-- 3.4根据课程ID（即CID）统计选课的学生人数-->
<select id="studentCounts" resultType="int">
select count(*)
from choose
WHERE CID= #{CID}
</select>
</mapper>
<6>开始写测试类
好激动！！！！终于要开始run了！！！！！！！！
好激动！！！！终于要开始run了！！！！！！！！
好激动！！！！终于要开始run了！！！！！！！
有请下面三位哥登场❀❀❀❀❀❀❀❀❀❀
StudentTest
package com.dgut;
import com.dgut.dao.istudentdao;
import com.dgut.huangxiaoqing.QureyStudentVO;
import com.dgut.huangxiaoqing.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.InputStream;
import java.util.ArrayList;
import java.util.List;
public class StudentTest {
InputStream inputStream;
SqlSession session;
istudentdao studentDao;
@Before
public void before() throws IOException {
//1.读取配置⽂件
inputStream =
Resources.getResourceAsStream("SqlMap-Config.xml");
//2.创建SqlSessionFactory的构建者对象,使⽤构建者创建⼯⼚对象
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder().build(inputStream);
//3.使⽤SqlSessionFactory⽣产SqlSession对象
session = sqlSessionFactory.openSession();
//4.使⽤SqlSession创建dao接⼝的代理对象
studentDao = session.getMapper(com.dgut.dao.istudentdao.class);
}
@After
public void after() throws IOException {
//6.释放资源
session.close();
inputStream.close();
}
@Test
public void testFindAll() throws IOException {
//1.1查询所有学生
List<Student> studnets = studentDao.findAll();
for(Student student : studnets){
System.out.println(student);
System.out.println(student.getChoose()+"\n");
}
// List<Student> students = studentDao.findAll();
// for (Student student : students){
// System.out.println(students);
// }
}
@Test
public void testsave() throws IOException {
//1.2新增学生
Student student = new Student();
student.setId(4);
student.setNum("202056");
studentDao.save(student);
session.commit();
}
@Test
public void testupdate() throws IOException {
//1.3更新学生
Student student =studentDao.findStuByID(19);
student.setSname("小抗体");
student.setNum("202020");
student.setAge(1);
student.setSex("未知");
int res = studentDao.update(student);
session.commit();
System.out.println(res);
}
@Test
public void testfindStuByID() throws IOException {
//1.4通过SID查询学生
Student student= studentDao.findStuByID(2);
System.out.println(student);
}
@Test
//1.5根据姓名、学号、性别组合查询，其中姓名支持模糊查询
public void testFindBySQV() throws IOException{
QureyStudentVO u = new QureyStudentVO();
u.setSname("%抗%");
List list = new ArrayList();
list.add(202020);
list.add(202056);
u.setIds(list);
List<Student> students = studentDao.findByCondition(u);
for(Student student : students){
System.out.println(student);
}
}
@Test
//1.6根据学号查找该学生的选课信息
public void testfindChooseBySID(){
List<Student> students = studentDao.findChooseBySID(1);
for(Student student : students){
System.out.println(student);
System.out.println(student.getChoose());
}
}
}
CourseTest
package com.dgut;
import com.dgut.dao.icoursedao;
import com.dgut.huangxiaoqing.Course;
import com.dgut.huangxiaoqing.QureyCourseVO;
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.InputStream;
import java.util.ArrayList;
import java.util.List;
public class CourseTest {
InputStream inputStream;
SqlSession session;
icoursedao courseDao;
@Before
public void before() throws IOException {
//1.读取配置⽂件
inputStream =
Resources.getResourceAsStream("SqlMap-Config.xml");
//2.创建SqlSessionFactory的构建者对象,使⽤构建者创建⼯⼚对象
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder().build(inputStream);
//3.使⽤SqlSessionFactory⽣产SqlSession对象
session = sqlSessionFactory.openSession();
//4.使⽤SqlSession创建dao接⼝的代理对象
courseDao = session.getMapper(com.dgut.dao.icoursedao.class);
}
@After
public void after() throws IOException {
//6.释放资源
session.close();
inputStream.close();
}
@Test
public void testsave() throws IOException {
//2.1新增课程
Course course = new Course();
course.setCID(202003);
course.setCname("组网技术");
course.setMaterials("华为内部教材");
courseDao.save(course);
session.commit();
}
@Test
public void testupdate(){
//2.2修改课程信息，先通过课程ID查找课程
Course course = courseDao.findCtuByID(202003);
course.setHour(42);
int res = courseDao.update(course);
session.commit();
System.out.println(res);
}
@Test
public void testfindByCondition() {
// 2.3根据课程名、课程编号组合查询，其中课程名支持模糊查，课程编号支持列表查询
QureyCourseVO u = new QureyCourseVO();
u.setCname("组%");
List list = new ArrayList();
list.add(202001);
list.add(202002);
list.add(202003);
u.setCID(list);
List<Course> coursess = courseDao.findByCondition(u);
for (Course course : coursess){
System.out.println(course);
}
}
}
ChooseTest
package com.dgut;
import com.dgut.dao.ichoosedao;
import com.dgut.huangxiaoqing.Choose;
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.InputStream;
import java.util.List;
public class ChooseTest {
InputStream inputStream;
SqlSession session;
ichoosedao chooseDao;
@Before
public void before() throws IOException {
//1.读取配置⽂件
inputStream =
Resources.getResourceAsStream("SqlMap-Config.xml");
//2.创建SqlSessionFactory的构建者对象,使⽤构建者创建⼯⼚对象
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder().build(inputStream);
//3.使⽤SqlSessionFactory⽣产SqlSession对象
session = sqlSessionFactory.openSession();
//4.使⽤SqlSession创建dao接⼝的代理对象
chooseDao = session.getMapper(com.dgut.dao.ichoosedao.class);
}
@After
public void after() throws IOException {
//6.释放资源
session.close();
inputStream.close();
}
@Test
public void testsave(){
// 3.1增加选课记录
Choose choose = new Choose();
choose.setID(2);
choose.setSID(1);
choose.setCID(202001);
chooseDao.save(choose);
session.commit();;
}
@Test
public void testdelete() throws IOException{
// 3.2根据id删除选课记录
int res = chooseDao.delete(2);
System.out.println(res);
session.commit();
}
@Test
public void testfindById(){
// 3.3根据选课id查询选课记录对应的课程信息及学生信息
List<Choose> list = chooseDao.findById(1);
for(Choose choose : list) {
System.out.println(list);
System.out.println(choose.getStudent()+"\n");
System.out.println(choose.getCourse()+"\n");
}
}
@Test
// 3.4根据课程ID（即CID）统计选课的学生人数
public void teststudentCounts(){
Integer count=
chooseDao.studentCounts(202001);
System.out.println("\n"+"选该课的人数："+count+"\n");
}
}
(4)程序运行
按下左边绿绿的小按钮，策马奔腾！！
120
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
