文章目录
一、要求
MyBatis框架的优化练习要求
要求:使用 mybatis 框架对数据表进行 CRUD 操作。
步骤:
(1) 数据库准备: 在 MySchool 数据库中,建立一个student 表,表结构如下:
student(
id bigint(20) 自增,
sname varchar(40),
dept varchar(40),
age int)
并在表中插入几条数据,其中一条为自己的信息 (2) 创建 Maven项目,项目名为” ” week8_ 学号_1 ” ;
项目的包名及类名要符合规范;
每个类前要加上文档注释模板;
完成对 student 表的 CRUD 操作;
封装 Mybatis架 框架 session 对象的工具类; 只使用 mapper层接口和 SQL 配置文件,不用 mapper 层接口的实现类;
对测试类使用 @Before 和 @After 注解,优化代码;
在 Mybatis 配置文件中,配置实体类包的别名,简化代码;
使用单元测试进行代码测试;
将程序的运行结果截屏,复制到一个 word 文件中 , 和项目一起提交。
MyBatis框架的多表查询练习要求
作业要求:
利用instructor表和department表,完成以下查询:
一对多:根据系的名字,查询该系及其包含的老师信息;
多对一:根据老师的ID,查询每个老师及其所属系的信息;
利用instructor表、course表和teaches表,完成以下查询:
多对多:根据课程的ID,查询该课程及其授课老师的信息;
多对多:根据老师的ID,查询每个老师及其所授课程的信息;
创建普通项目,项目名为” week8 __ 学号_ _2 2”;
使用Idea自动生成相关表的实体类;
项目的包名及类名要符合规范;
使用实体类别名,简化代码;
使用包名完成局部SQL配置文件和主配置文件的关联;
将程序的运行结果截屏,复制到一个word文件中,和项目一 起提交。
二、知识总结
1、MyBatis 框架的优化练习知识总结
编程规范
- 项目名:英文名
- 包名:公司域名倒写,如 cn.edu.gdufs
- 类名:大驼峰,如 UserMapper
- 方法或属性名:小驼峰,如 getUser
- 实体层:entity,model,bean,pojo(Spring 框架用, 如 cn.edu.gdufs.pojo)
其中的类:关系表名- 数据访问层(持久层):dao, mapper (MyBatis 建议用,如 cn.edu.gdufs.mapper)
其中的接口:实体类名+Mapper
接口的实现类:实体类名+Mapper+Impl- 业务逻辑层:service, 如 cn.edu.gdufs.service
其中的接口:实体类名+Service
接口的实现类:实体类名+ Service +Impl- 控制器层:controller,如 cn.edu.gdufs.controller
接口的类:实体类名+ Controller- 注释:
类上和方法上使用文档注释/** /
方法里面使用多行/ */或单行//
封装一个用来获取MyBatis中 Session的工具类
MybatisUtil.java
package cn.java.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 MybatisUtil {
// 获取SqlSession
public static SqlSession getSession(){
SqlSession session=null;
InputStream inputStream=null;
try {
//配置文件的路径
String resource = "mybatis.xml";
//加载配置文件,得到一个输入流
inputStream = Resources.getResourceAsStream(resource);
//获取MyBatis的Session工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//通过session工厂获取到一个session
session = sqlSessionFactory.openSession(true); //true表示自动提交事务
//调用session的查询集合方法
return session;
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
// 关闭SqlSession
public static void closeSession(SqlSession session){
if(session!=null){
session.close();
}
}
}
使用database.properties 文件进行数据库连接相关配置
database.properties
#mysql8
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/myschool?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
username=root
password=密码
#mysql 5
#driver=com.mysql.jdbc.Driver
#url=jdbc:mysql://localhost:3306/myschool?useUnicode=true&characterEncoding=utf8
#username=root
#password=
需要在主配置文件mybatis.xml中引入配置文件!
<!-- 引入数据库配置文件 -->
<properties resource="database.properties"/>
在mybatis.xml中使用"${…}"即可取到database.properties中对应的值
使用@Before 和@After 注解,简化代码
在测试类中的某个方法前加 @Before 注解 , 可以使得在执行其他方法前 , 先执行该方法,如以下代码:
@Before
public void init(){
session= MybatisUtil.getSession ();
//um 就是 Mapper 的实现类
um= session.getMapper(UserMapper.class);
}
在测试类中的某个方法前加 @After 注解,可以使得在执行其他方法后,再执行该方法,如以下代码:
@After
public void destory(){
//关闭 session 会话
MybatisUtil. closeSession (session);
}
使用别名
指定包名,直接给包下所有的实体类取别名,默认的实体类的别名就是类名(不区分小大写),如 User 实体类的别名可以是 User、user、USER。
<!-- 配置别名 -->
<typeAliases>
<typeAlias type="cn.java.pojo.Student" alias="student"/>
<typeAlias type="cn.java.pojo.User" alias="user"/>
</typeAliases>
使用包名完成局部SQL配置文件和主配置文件的关联
<!-- 关联局部SQL映射配置文件 ,在每一个mapper里,指定SQL映射文件名及全路径,可使用“copy qualified name””-->
<mappers>
<!-- <mapper resource="cn/java/mapper/UserMapper.xml"/>-->
<!-- <mapper resource="cn/java/mapper/StudentMapper.xml"/>-->
<!-- 使用包名完成局部SQL配置文件和主配置文件的关联-->
<!-- 直接映射包的名字,那么这个包下面所有的 Mapper 配置文件全部关联!
<package name="cn.java.mapper"/>
</mappers>
2、MyBatis框架的多表查询练习知识总结
关系型数据库中,表与表之间的联系可以分为:
- 一对一 (1:1)
- 一对多(1:n )
- 多对一(n:1)
- 多对多m (n:m )
resultMap
resultMap 标签可以表示两个表之间的关系
一对多(使用collection)
例:一个系可以有多个学生,则 系 和 学生 是一对多的关系
如果想查询每个系的学生信息时,可以在DepartmentMapper.xml 局部配置文件中,使用 resultMap 标签进行如下配置:
<resultMap id="departmentMap" type="cn.java.pojo.Department">
<!—定义主键-->
<id property="deptName" column="dept_name"/>
<result property="building" column="building"/> <!—定义普通属性-->
<result property="budget" column="budget"/>
<!--配置一个包含关系 “有很多”关系,表达一对多联系 -->
<collection property="students" ofType="cn.java.pojo.Student">
<id property="ID" column="ID"/>
<result property="sname" column="name"/>
<result property="sdept" column="dept_name"/>
<result property="tot_cred" column="tot_cred"/>
</collection>
</resultMap>
多对一(一对一或多对一)(使用association)
一对一
例:一个系有一个系主任,则 系 和 系主任 是一对一的关系,如果想查询每个系的系主任信息时,可以在DepartmentMapper.xml 局部配置文件中,使用 resultMap 标签进行如下配置:
<resultMap id="departmentMap" type="cn.java.pojo.Department">
<!—定义主键-->
<id property="deptName" column="dept_name"/>
<result property="building" column="building"/> <!—定义普通属性-->
<result property="budget" column="budget"/>
<!—每个系有一个系主任,表达一对一联系-->
<association property="Dean" javaType="cn.java.pojo.Dean">
<id property="id" column="did"/>
<result property="name" column="name"/>
</association>
</resultMap>
多对一
例:多个老师属于同一个系,则 老师 和 系 是多对一的关系,如果想查询每个系的系主任信息时,可以在InstructorMapper.xml 局部配置文件中,使用 resultMap 标签进行如下配置(实体类使用别名简化了代码):
<!-- 多对一:根据老师的ID,查询每个老师及其所属系的信息 结果集-->
<resultMap id="TeacherMap" type="Instructor">
<id property="id" column="ID"/>
<result property="name" column="name" />
<result property="deptName" column="dept_name" />
<result property="salary" column="salary" />
<!--对应一个系,用association-->
<!-- 注意要配置对应的pojo文件-->
<association property="department" javaType="Department">
<id property="deptName" column="dept_name"/>
<result property="building" column="building"/>
<result property="budget" column="budget"/>
</association>
</resultMap>
重要标签或属性说明
<resultMap id="departmentMap" type="cn.java.pojo.Department">
功能: resultMap: 进行多表查询的查询结果集说明;
id: 给结果集命名,要唯一
type: 对应相应的实体类
<id property="deptName" column="dept_name"/>
功能:定义该实体类的主键 property=“deptName” 定义在实体类中的主键的属性名
column="dept_name"定义在关系表中的主键的字段名
属性名指的是在项目中实体类(pojo包)中对应写的属性名,而字段名指的是在数据表中对应的列名!一般数据表中使用下划线“_”来连接两个词(如dept_name),而在实体类中使用驼峰法命名(如deptName)
<result property="building" column="building"/>
功能: 定义实体类的普通属性
property=" building " 定义在实体类中的普通属性名
column=" building "定义在关系表中的普通字段名
<collection property="students" ofType="cn.java.bean.Student">
功能:定义一对多关系,如一个系里有多个学生,定义多方的数据集合
property=“students” 定义多方学生集合
ofType=“cn.java.bean.Student” 定义对应的实体类
<association property="Dean" javaType="cn.java.bean.Dean">
功能:定义一对一或多对一关系,如一个系对应一个系主任,多个老师对应一个系
多对多
注意多对多配置完resultMap后要记得配置对应的实体类!!
例:
观察 student 表和 course 表,两个表之间的关系是多对多联系,即每个学生可以选修多门课,每门课程可以由多位学生学习
第三方表: takes 表,该表存储学生选修某门课程的成绩数据;
注意:多对多联系一定涉及到 3 个表;
查询需求:查询每个学生选修的课程信息,及课程的分数。
分析:每个学生(对应Student实体类)可以选修多门课程(对应Course实体类),因此后面会在Student实体类中添加一个List<Course>
courseList属性及对应的get/set方法,同理一门课程(对应Course实体类)可能对应多个成绩(对应takes实体类),因此后面会在Course实体类中添加一个 List<Takes> takeList的属性及其对应的get/set方法
修改 Student 实体类
- 在== Student 实体类中==,添加一个 List<Course>
courseList 属性:
private List<Course> courseList ; //表示某个学生的选的课程(可能不止一节课)
- 为该属性生成相应的 get/set 方法;
修改 Course 实体类
- 在 Course 实体类中,添加一个 ==List<Takes> takeList ==属性:
private List<Takes> takesList; //表示某门课程的分数情况
- 为该属性生成相应的 get/set 方法;
编写 sql 映射文件 StudentMapper.xml
<!--定义结果集:学生的多个选课及每门课程的分数-->
<resultMap id" ="StudentCourseTakesMap" type ="cn.java.pojo.Student">
<id property" ="id" column ="ID"/>
<result property" ="name" column" ="name" />
<result property" ="deptName" column" ="dept_name" />
<result property" ="totCred" column" ="tot_cred" />
<!--选修多门课程-->
<collection property" ="courseList" ofType ="cn.java.bean.Course">
<id property" ="courseId" column ="course_id"/>
<result property" ="title" column ="title"/>
<result property" ="deptName" column ="dept_name"/>
<result property" ="credits" column ="credits"/>
<!--每门课程有多个成绩-->
<collection property" ="takesList" ofType ="cn.java.bean.Takes">
<id property" ="id" column ="ID"/>
<id property" ="courseId" column ="course_id"/>
<id property" ="secId" column ="sec_id"/>
<id property" ="semester" column ="semester"/>
<id property" ="year" column ="year"/>
<result property" ="grade" column ="grade"/>
</collection>
</collection>
</resultMap>
<select id" ="getStudGrade" resultMap ="StudentCourseTakesMap">
SELECT *
FROM student,takes,course
WHERE student. `ID`=takes. `ID`
AND course. `course_id`=takes. `course_id`
</select>
三、项目结构
week8_学号_1
week8_学号_2
四、完整参考代码
week8_学号_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>org.example</groupId>
<artifactId>week8_学号_1</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<dependencies>
<!-- mysql驱动包 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.13</version>
</dependency>
<!-- myBatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.5</version>
</dependency>
<!-- 单元测试 -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<!--使用apache的log4j日志-->
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.2</version>
</dependency>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-api</artifactId>
<version>2.11.0</version>
</dependency>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-core</artifactId>
<version>2.11.0</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.25</version>
</dependency>
</dependencies>
<!--如果是WEB项目,那么不用创建bulid标签-->
<build>
<!--编译的时候同时也把包下面的xml同时编译进去-->
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
</resources>
<plugins>
<!-- 指定jdk版本 -->
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>utf-8</encoding>
</configuration>
</plugin>
</plugins>
</build>
</project>
cn/java/utils包
cn/java/utils/MybatisUtil.java
package cn.java.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 MybatisUtil {
// 获取SqlSession
public static SqlSession getSession(){
SqlSession session=null;
InputStream inputStream=null;
try {
//配置文件的路径
String resource = "mybatis.xml";
//加载配置文件,得到一个输入流
inputStream = Resources.getResourceAsStream(resource);
//获取MyBatis的Session工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//通过session工厂获取到一个session
session = sqlSessionFactory.openSession(true); //true表示自动提交事务
//调用session的查询集合方法
return session;
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
// 关闭SqlSession
public static void closeSession(SqlSession session){
if(session!=null){
session.close();
}
}
}
cn/java/pojo包
cn/java/pojo/Student.java
package cn.java.pojo;
/**
* @projectName: week8_ _1
* @package: cn.java.pojo
* @className: Student
* @author: GCT
* @description: TODO
* @date: 2022/10/18 21:49
* @version: 1.0
*/
public class Student {
private Long id;
private String sname;
private String dept;
private int age;
public Student() {
}
public Student(Long id, String sname, String dept, int age) {
this.id = id;
this.sname = sname;
this.dept = dept;
this.age = age;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public String getDept() {
return dept;
}
public void setDept(String dept) {
this.dept = dept;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", sname='" + sname + '\'' +
", dept='" + dept + '\'' +
", age=" + age +
'}';
}
}
cn/java/pojo/User.java
package cn.java.pojo;
public class User {
private Long id;
private String username;
private String password;
public User(Long id, String username, String password) {
super();
this.id = id;
this.username = username;
this.password = password;
}
public User() {
super();
// TODO Auto-generated constructor stub
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", password=" + password + "]";
}
}
cn/java/mapper包
cn/java/mapper/StudentMapper.java
package cn.java.mapper;
import cn.java.pojo.Student;
import cn.java.pojo.User;
import org.apache.ibatis.annotations.Select;
import java.util.List;
import java.util.Map;
public interface StudentMapper {
// 该方法使用Student实体类
public List<Student> getAllStudent();
//直接在以下方法上加@Select注解,简化代码
@Select("select * from student")
public List<Student> findAllStudent();
// 该方法返回多条记录,不使用实体类,用Map数据类型去接受
public List<Map<String, Object>> getAllStudentMap();
// 该方法使用了带一个参数的查询语句,返回一条记录
public Map<String, Object> getStudentById(long id);
// 该方法使用了有多个参数的 select语句
public Map<String, Object> getStudentByMulCondition(Map<String, Object> map);
// 该方法插入一条记录,带参数,更新操作一定要提交事务
public int addStudent(Map<String, Object> map);
// 该方法插入多条记录,带参数,更新操作一定要提交事务
public int addStudentBatch(List<Student> list) ;
// 该方法使用了动态查询,查询条件不确定
public List<Map<String, Object>> getStudentByDynam(Map<String, Object> map) ;
// 该方法使用了动态修改,查询条件不确定
public int updateStudentByDynam(Map<String, Object> map) ;
// 根据id删除记录
public int deleteStudentById(int id);
// 根据多个id删除多条记录
public int deleteStudentByIds(int[] ids);
}
cn/java/mapper/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="cn.java.mapper.StudentMapper">
<!--
1.select语句返回多条Student实体对象
resultType="cn.java.entity.Student"表示返回User实体类
-->
<!-- <select id="getAllStudent" resultType="cn.java.pojo.Student">-->
<!-- 使用别名-->
<select id="getAllStudent" resultType="student">
SELECT * FROM student
</select>
<!--
2.select语句返回List<Map<String,Object>,可以不使用实体类,直接用Map数据类型,更加简单,简化程序
-->
<select id="getAllStudentMap" resultType="Map">
SELECT * FROM student
</select>
<!--
3.SQL语句带一个参数
parameterType:指定SQL语句接收的参数类型
-->
<select id="getStudentById" resultType="map" parameterType="Long">
SELECT * FROM student WHERE id=#{0}
</select>
<!--
4.SQL语句带多个参数,用Map封装,parameterType=Map,返回一条记录,按key取参数值
#在获取参数时可防止SQL注入攻击,应尽量使用#;模糊查询时,使用$
-->
<select id="getStudentByMulCondition" resultType="map" parameterType="Map">
SELECT * FROM student WHERE sname='${sname}' AND dept='${dept}' AND age='${age}'
</select>
<!-- 添加数据
5. delete、insert、update操作没有resultType属性,默认返回int型
parameterType=Map,表示参数类型为Map,用Map封装参数
#表示在获取参数时可防止SQL注入攻击,应尽量使用#;模糊查询时,使用$
INSERT INTO users SET username=,这种SQL语句是MmySql特有的扩展功能
-->
<insert id="addStudent" parameterType="Map">
INSERT INTO student SET sname=#{sname},dept=#{dept},age=#{age}
</insert>
<!-- <insert id="addStudentBatch" parameterType="cn.java.pojo.Student">-->
<!-- 使用别名-->
<insert id="addStudentBatch" parameterType="student">
insert into
student(sname,dept,age)
values
<foreach collection="list" item="student" separator=",">
(#{student.sname},#{student.dept},#{student.age})
</foreach>
</insert>
<!--
6.动态SQL语句,实现动态查询
<:小于
>:大于
where标签:当有查询条件时,就使用where命令,当没有查询条件时,就不用where命令
if test:判断是否对某个字段进行查询
-->
<select id="getStudentByDynam" resultType="Map" parameterType="Map">
SELECT * FROM student
<where>
<if test="sname!=null">
sname=#{sname}
</if>
<if test="dept!=null">
and dept=#{dept}
</if>
<if test="age!=null">
and age=#{age}
</if>
</where>
</select>
<!--
7.动态修改update语句
set标签:当有更新字段时,就使用set命令,当没有更新字段时,
语句为UPDATE course SET id=NULL WHERE id=NULL
-->
<update id="updateStudentByDynam" parameterType="Map">
update student
<set>
<if test="sname!=null">
sname=#{sname},
</if>
<if test="dept!=null">
dept=#{dept},
</if>
<if test="age!=null">
age=#{age},
</if>
id=#{id}
</set>
where id=#{id}
</update>
<!-- 根据id删除记录-->
<delete id="deleteStudentById" parameterType="int">
DELETE FROM student WHERE id=#{id}
</delete>
<!-- 批量删除 -->
<delete id="deleteStudentByIds" parameterType="int[]" >
DELETE FROM student WHERE id IN
<foreach collection="array" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</delete>
</mapper>
cn/java/mapper/UserMapper.java
package cn.java.mapper;
import cn.java.pojo.User;
import org.apache.ibatis.annotations.Select;
import java.util.List;
import java.util.Map;
/**
* description:
* author :
* date: 2020/10/3
*/
public interface UserMapper {
// 该方法使用User实体类
public List<User> getAllUser();
//直接在以下方法上加@Select注解,简化代码
@Select("select * from users")
public List<User> findAllUser();
// 该方法返回多条记录,不使用实体类,用Map数据类型去接受
public List<Map<String, Object>> getAllUserMap();
// 该方法使用了带一个参数的查询语句,返回一条记录
public Map<String, Object> getUserById(long id);
// 该方法使用了有多个参数的 select语句
public Map<String, Object> getUserByMulCondition(Map<String, Object> map);
// 该方法插入一条记录,带参数,更新操作一定要提交事务
public int addUser(Map<String, Object> map);
// 该方法插入多条记录,带参数,更新操作一定要提交事务
public int addUserBatch(List<User> list) ;
// 该方法使用了动态查询,查询条件不确定
public List<Map<String, Object>> getCourseByDynam(Map<String, Object> map) ;
// 该方法使用了动态修改,查询条件不确定
public int updateCourseByDynam(Map<String, Object> map) ;
}
cn/java/mapper/UserMapper.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">
<!-- sql语句保存在Mybatis的局部配置文件中
解释:
(1)namespace:命名空间,其值为某一个dao层接口的具体路径,
表示这个类要使用相应的SQL语句,这个具体路径不要自己写,可以选中该类,右键,选择“copy reference”,然后粘贴即可
(2)select标签存放查询语句;
(3)id:在整个配置文件中id值必须唯一,一般情况下,其值与dao层类中的使用该SQL语句的方法名保持一致;
(4)resultType:指定当前sql查询语句返回的数据类型。类型不是sql语句的最终类型,
而是某一条数据的类型,一般用实体类表示,也要用该实体类的“全路径”来表示,运行时系统会自动将实体类的对象创建出来
(5)可以编写多条sql语句
-->
<mapper namespace="cn.java.mapper.UserMapper">
<!--
1.select语句返回多条User实体对象
resultType="cn.java.entity.User"表示返回User实体类
-->
<!-- <select id="getAllUser" resultType="cn.java.pojo.User">-->
<!-- 使用别名-->
<select id="getAllUser" resultType="user">
SELECT * FROM users
</select>
<!--
2.select语句返回List<Map<String,Object>,可以不使用实体类,直接用Map数据类型,更加简单,简化程序
-->
<select id="getAllUserMap" resultType="Map">
SELECT * FROM users
</select>
<!--
3.SQL语句带一个参数
parameterType:指定SQL语句接收的参数类型
-->
<select id="getUserById" resultType="map" parameterType="Long">
SELECT * FROM users WHERE id=#{0}
</select>
<!--
4.SQL语句带多个参数,用Map封装,parameterType=Map,返回一条记录,按key取参数值
#在获取参数时可防止SQL注入攻击,应尽量使用#;模糊查询时,使用$
-->
<select id="getUserByMulCondition" resultType="map" parameterType="Map">
SELECT * FROM users WHERE username='${username}' AND PASSWORD='${pwd}'
</select>
<!-- 添加数据
5. delete、insert、update操作没有resultType属性,默认返回int型
parameterType=Map,表示参数类型为Map,用Map封装参数
#表示在获取参数时可防止SQL注入攻击,应尽量使用#;模糊查询时,使用$
INSERT INTO users SET username=,这种SQL语句是MmySql特有的扩展功能
-->
<insert id="addUser" parameterType="Map">
INSERT INTO users SET username=#{username},PASSWORD=#{password}
</insert>
<!-- <insert id="addUserBatch" parameterType="cn.java.pojo.User">-->
<!-- 使用别名-->
<insert id="addUserBatch" parameterType="user">
insert into
users(username,PASSWORD)
values
<foreach collection="list" item="user" separator=",">
(#{user.username},#{user.password})
</foreach>
</insert>
<!--
6.动态SQL语句,实现动态查询
<:小于
>:大于
where标签:当有查询条件时,就使用where命令,当没有查询条件时,就不用where命令
if test:判断是否对某个字段进行查询
-->
<select id="getCourseByDynam" resultType="Map" parameterType="Map">
SELECT * FROM course
<where>
<if test="cname!=null">
cname=#{cname}
</if>
<if test="credit!=null">
and credit=#{credit}
</if>
<if test="department!=null">
and department=#{department}
</if>
</where>
</select>
<!--
7.动态修改update语句
set标签:当有更新字段时,就使用set命令,当没有更新字段时,
语句为UPDATE course SET id=NULL WHERE id=NULL
-->
<update id="updateCourseByDynam" parameterType="Map">
update course
<set>
<if test="cname!=null">
cname=#{cname},
</if>
<if test="credit!=null">
credit=#{credit},
</if>
<if test="department!=null">
department=#{department},
</if>
id=#{id}
</set>
where id=#{id}
</update>
</mapper>
src/main/resources包
database.properties
#mysql8
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/myschool?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
username=root
password=密码
#mysql 5
#driver=com.mysql.jdbc.Driver
#url=jdbc:mysql://localhost:3306/myschool?useUnicode=true&characterEncoding=utf8
#username=root
#password=
log4j.properties
# log4J日志框架的配置文件 文件名字不能改
# 第一句:控制日志的输出级别及往哪里输出信息
# 日志的输出级别:fatal>error>warn>info>debug
# 日志输出在控制台:Console
log4j.rootLogger=DEBUG, CONSOLE
#也可以如下设置,提高总的输出级别,但是降低某一个包或类或方法的级别,这样可以减少输出日志信息
#log4j.rootLogger=ERROR,CONSOLE
#log4j.logger.cn.java.dao.impl=DEBUG
#负责输出日志的类,格式
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
mybatis.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?,settings?,typeAliases?,typeHandlers?,objectFactory?,objectWrapperFactory?,reflectorFactory?,plugins?,environments?,databaseIdProvider?,mappers?-->
<!-- 引入数据库配置文件 -->
<properties resource="database.properties"/>
<!-- 开启log4j,输出SQL语句 -->
<settings>
<setting name="logImpl" value="LOG4j"/>
</settings>
<!-- 配置别名 -->
<typeAliases>
<typeAlias type="cn.java.pojo.Student" alias="student"/>
<typeAlias type="cn.java.pojo.User" alias="user"/>
</typeAliases>
<!-- 配置数据库连接环境:driver、url、username、password -->
<environments default="mysql">
<!-- 开始配置mysql -->
<environment id="mysql">
<!--配置事务 -->
<transactionManager type="JDBC"></transactionManager>
<!-- 配置数据源 -->
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!-- 关联局部SQL映射配置文件 ,在每一个mapper里,指定SQL映射文件名及全路径,可使用“copy qualified name””-->
<mappers>
<!-- <mapper resource="cn/java/mapper/UserMapper.xml"/>-->
<!-- <mapper resource="cn/java/mapper/StudentMapper.xml"/>-->
<!-- 使用包名完成局部SQL配置文件和主配置文件的关联-->
<package name="cn.java.mapper"/>
</mappers>
</configuration>
src/test/java包
src/test/java/Test_Student.java
import cn.java.mapper.StudentMapper;
import cn.java.mapper.UserMapper;
import cn.java.pojo.Student;
import cn.java.pojo.User;
import cn.java.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @projectName: week8_ _1
* @package: PACKAGE_NAME
* @className: Test_Student
* @author: GCT
* @description: TODO
* @date: 2022/10/18 21:59
* @version: 1.0
*/
public class Test_Student {
// SqlSession session=null;
SqlSession session=null;
StudentMapper um=null;
//Before注解:在每次调用测试方法之前,自动调用init()方法
@Before
public void init(){
session= MybatisUtil.getSession();
//um就是Mapper的实现类
um=session.getMapper(StudentMapper.class);
}
//每次调用测试方法之后,自动调用一下destory()
@After
public void destory(){
MybatisUtil.closeSession(session);
}
@Test
public void testGetAllStudent(){
um= session.getMapper(StudentMapper.class);
List<Student> studentList = um.getAllStudent();
for (Student student : studentList) {
System.out.println(student);
}
}
@Test
public void testFindAllStudent(){
List<Student> studentList = um.findAllStudent();
for (Student student : studentList) {
System.out.println(student);
}
}
@Test
// 2.该方法返回多条记录,不使用实体类,用Map数据类型去接受
public void testGetAllStudentMap() {
List<Map<String, Object>> studentList =um.getAllStudentMap();
for (Map<String, Object> map : studentList) {
System.out.println(map);
}
}
@Test
// 3.该方法使用了带一个参数的查询语句,返回一条记录
public void testGetStudentById() {
// 传递参数,直接传
Long id =2L;
Map<String, Object> studentMap = um.getStudentById(id);
System.out.println(studentMap);
}
@Test
// 4.该方法使用了有多个参数的 select语句
public void testGetStudentByMulCondition() {
// 声明一个Map对象,可以使用Map或实体类同时传递多个参数,用map更简单
Map<String, Object> paramMap = new HashMap<String, Object>();
// 封装参数
paramMap.put("sname", "ss");
paramMap.put("dept", "ss");
paramMap.put("age", "1");
// 传递参数
Map<String, Object> studentMap = um.getStudentByMulCondition(paramMap);
System.out.println(studentMap);
}
// 5.该方法插入一条记录,带参数,更新操作一定要提交事务
@Test
public void testAddStudent() {
Map<String, Object> paramMap = new HashMap<String, Object>();
paramMap.put("sname", "test");
paramMap.put("dept", "test");
paramMap.put("age", "2");
int resultInt = um.addStudent(paramMap);
System.out.println(resultInt);
}
// 6.该方法插入多条记录,带参数,更新操作一定要提交事务
@Test
public void testAddStudentBatch() {
List<Student> list = new ArrayList<>();
Student student;
for (int i = 0; i < 10; i++) {
student = new Student();
student.setSname("test" + i);
student.setDept("test666");
student.setAge(i);
list.add(student);
}
int resultInt = um.addStudentBatch(list);
System.out.println(resultInt);
};
// 7.该方法使用了动态查询,查询条件不确定
@Test
public void getStudentByDynam() {
// 可以使用Map或实体类同时传递多个参数
Map<String, Object> paramMap = new HashMap<String, Object>();
paramMap.put("sname", "test");
paramMap.put("dept", "test");
paramMap.put("age", "2");
List<Map<String, Object>> studentList =um.getStudentByDynam(paramMap);
for (Map<String, Object> map : studentList) {
System.out.println(map);
}
}
// 8.该方法使用了动态修改,查询条件不确定
@Test
public void updateStudentByDynam() {
// 可以使用Map或实体类同时传递多个参数
Map<String, Object> paramMap = new HashMap<String, Object>();
paramMap.put("id",1);
paramMap.put("sname", "test");
paramMap.put("dept", "test");
paramMap.put("age", "2");
int resultInt = um.updateStudentByDynam(paramMap);
System.out.println(resultInt);
}
@Test
public void deleteStudentById(){
int id = 80;
int resInt = um.deleteStudentById(id);
System.out.println(resInt);
}
@Test
public void deleteStudentByIds(){
int[] ids = new int[]{77,78,79};
int resInt = um.deleteStudentByIds(ids);
System.out.println(resInt);
}
}
src/test/java/Test_User.java
import cn.java.mapper.UserMapper;
import cn.java.pojo.User;
import cn.java.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* description:
* author :hj
* date: 2020/10/12
*/
public class Test_User {
// SqlSession session=null;
SqlSession session=null;
UserMapper um=null;
//Before注解:在每次调用测试方法之前,自动调用init()方法
@Before
public void init(){
session= MybatisUtil.getSession();
//um就是Mapper的实现类
um=session.getMapper(UserMapper.class);
}
//每次调用测试方法之后,自动调用一下destory()
@After
public void destory(){
MybatisUtil.closeSession(session);
}
@Test
public void testGetAllUser(){
um= session.getMapper(UserMapper.class);
List<User> userList = um.getAllUser();
for (User user : userList) {
System.out.println(user);
}
}
@Test
public void testFindAllUser(){
SqlSession session = MybatisUtil.getSession();
//MyBatis在底层,使用反射技术自动生成Mapper实现类,不需要实现类了
//um就是Mapper的实现类
UserMapper um= session.getMapper(UserMapper.class);
List<User> userList = um.findAllUser();
for (User user : userList) {
System.out.println(user);
}
}
@Test
// 2.该方法返回多条记录,不使用实体类,用Map数据类型去接受
public void testGetAllUserMap() {
List<Map<String, Object>> userList =um.getAllUserMap();
for (Map<String, Object> map : userList) {
System.out.println(map);
}
}
@Test
// 3.该方法使用了带一个参数的查询语句,返回一条记录
public void testGetUserById() {
// 传递参数,直接传
Long id =2L;
Map<String, Object> userMap = um.getUserById(id);
System.out.println(userMap);
}
@Test
// 4.该方法使用了有多个参数的 select语句
public void testGetUserByMulCondition() {
// 声明一个Map对象,可以使用Map或实体类同时传递多个参数,用map更简单
Map<String, Object> paramMap = new HashMap<String, Object>();
// 封装参数
paramMap.put("username", "admin");
paramMap.put("pwd", "123");
// 传递参数
Map<String, Object> userMap = um.getUserByMulCondition(paramMap);
System.out.println(userMap);
}
// 5.该方法插入一条记录,带参数,更新操作一定要提交事务
@Test
public void testAddUser() {
Map<String, Object> paramMap = new HashMap<String, Object>();
paramMap.put("username", "Rose");
paramMap.put("password", "1234");
int resultInt = um.addUser(paramMap);
System.out.println(resultInt);
}
// 6.该方法插入多条记录,带参数,更新操作一定要提交事务
@Test
public void testAddUserBatch( ) {
List<User> list = new ArrayList<>();
User user;
for (int i = 0; i < 10; i++) {
user = new User();
user.setUsername("test" + i);
user.setPassword("666");
list.add(user);
}
int resultInt = um.addUserBatch(list);
System.out.println(resultInt);
};
// 7.该方法使用了动态查询,查询条件不确定
@Test
public void getCourseByDynam() {
// 可以使用Map或实体类同时传递多个参数
Map<String, Object> paramMap = new HashMap<String, Object>();
paramMap.put("cname", "数据结构");
paramMap.put("credit", 4);
paramMap.put("department", "软件工程");
List<Map<String, Object>> courseList =um.getCourseByDynam(paramMap);
for (Map<String, Object> map : courseList) {
System.out.println(map);
}
}
// 8.该方法使用了动态修改,查询条件不确定
@Test
public void updateCourseByDynam() {
// 可以使用Map或实体类同时传递多个参数
Map<String, Object> paramMap = new HashMap<String, Object>();
paramMap.put("cname", "数据库原理");
paramMap.put("credit", 4);
// paramMap.put("department", "软工1");
paramMap.put("id", 1);
int resultInt = um.updateCourseByDynam(paramMap);
System.out.println(resultInt);
}
}
week8_学号_2项目参考代码
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>org.example</groupId>
<artifactId>week8_20201003016_2</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<dependencies>
<!-- mysql驱动包 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.13</version>
</dependency>
<!-- myBatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.5</version>
</dependency>
<!-- 单元测试 -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
<!--如果是WEB项目,那么不用创建bulid标签-->
<build>
<!--编译的时候同时也把包下面的xml同时编译进去-->
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
</resources>
<plugins>
<!-- 指定jdk版本 -->
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>utf-8</encoding>
</configuration>
</plugin>
</plugins>
</build>
</project>
cn/java/utils包
cn/java/utils/MybatisUtil.java
package cn.java.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;
/**
* description:
* author :hj
* date: 2020/10/11
*/
public class MybatisUtil {
// 获取SqlSession
public static SqlSession getSession(){
SqlSession session=null;
InputStream inputStream=null;
try {
//配置文件的路径
String resource = "mybatis.xml";
//加载配置文件,得到一个输入流
inputStream = Resources.getResourceAsStream(resource);
//获取MyBatis的Session工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//通过session工厂获取到一个session
session = sqlSessionFactory.openSession(true); //true表示自动提交事务
//调用session的查询集合方法
return session;
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
// 关闭SqlSession
public static void closeSession(SqlSession session){
if(session!=null){
session.close();
}
}
}
cn/java/pojo包
cn/java/pojo/Course.java
package cn.java.pojo;
import java.util.List;
public class Course {
private String courseId;
private String title;
private String deptName;
private double credits;
//表示含义 每个课程由多个老师授课
private List<Instructor> instructorList; //老师集合
public List<Instructor> getInstructorList() {
return instructorList;
}
public void setInstructorList(List<Instructor> instructorList) {
this.instructorList = instructorList;
}
public String getCourseId() {
return courseId;
}
public void setCourseId(String courseId) {
this.courseId = courseId;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getDeptName() {
return deptName;
}
public void setDeptName(String deptName) {
this.deptName = deptName;
}
public double getCredits() {
return credits;
}
public void setCredits(double credits) {
this.credits = credits;
}
@Override
public String toString() {
return "Course{" +
"courseId='" + courseId + '\'' +
", title='" + title + '\'' +
", deptName='" + deptName + '\'' +
", credits=" + credits +
'}';
}
}
cn/java/pojo/Department.java
package cn.java.pojo;
import java.util.List;
public class Department {
private String deptName;
private String building;
private double budget;
//表示含义 1个系里有多个老师
private List<Instructor> instructors; //老师集合 多的一方
public List<Instructor> getInstructors() {
return instructors;
}
public void setInstructors(List<Instructor> instructors) {
this.instructors = instructors;
}
public String getDeptName() {
return deptName;
}
public void setDeptName(String deptName) {
this.deptName = deptName;
}
public String getBuilding() {
return building;
}
public void setBuilding(String building) {
this.building = building;
}
public double getBudget() {
return budget;
}
public void setBudget(double budget) {
this.budget = budget;
}
// 测试类中System.out.println("系的信息是 " + dept);会用到toString
@Override
public String toString() {
return "Department{" +
"deptName='" + deptName + '\'' +
", building='" + building + '\'' +
", budget=" + budget +
// ", instructors=" + instructors +
'}';
}
}
cn/java/pojo/Instructor.java
package cn.java.pojo;
import java.util.List;
public class Instructor {
private String id;
private String name;
private String deptName;
private double salary;
//表示含义 老师属于哪个系
private Department department; //系 多对一中“1”的一方
//表示含义 每个老师授多门课程
private List<Teaches> teachesList; //老师教授的课程集合
public List<Teaches> getTeachesList() {
return teachesList;
}
public void setTeachesList(List<Teaches> teachesList) {
this.teachesList = teachesList;
}
public Department getDepartment() {
return department;
}
public void setDepartment(Department department) {
this.department = department;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getDeptName() {
return deptName;
}
public void setDeptName(String deptName) {
this.deptName = deptName;
}
public double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
@Override
public String toString() {
return "Instructor{" +
"id='" + id + '\'' +
", name='" + name + '\'' +
", deptName='" + deptName + '\'' +
", salary=" + salary +
// ", department=" + department +
'}';
}
}
cn/java/pojo/Teaches.java
package cn.java.pojo;
import java.util.List;
public class Teaches {
private String id;
private String courseId;
private String secId;
private String semester;
private double year;
//表示含义 该老师的信息
private Instructor instructor; //该老师的信息
//表示含义 该老师授课课程(可能不止一个)及其信息
private List<Course> courseList; //老师教授的课程集合
public Instructor getInstructor() {
return instructor;
}
public void setInstructor(Instructor instructor) {
this.instructor = instructor;
}
public List<Course> getCourseList() {
return courseList;
}
public void setCourseList(List<Course> courseList) {
this.courseList = courseList;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getCourseId() {
return courseId;
}
public void setCourseId(String courseId) {
this.courseId = courseId;
}
public String getSecId() {
return secId;
}
public void setSecId(String secId) {
this.secId = secId;
}
public String getSemester() {
return semester;
}
public void setSemester(String semester) {
this.semester = semester;
}
public double getYear() {
return year;
}
public void setYear(double year) {
this.year = year;
}
@Override
public String toString() {
return "Teaches{" +
"id='" + id + '\'' +
", courseId='" + courseId + '\'' +
", secId='" + secId + '\'' +
", semester='" + semester + '\'' +
", year=" + year +
'}';
}
}
cn/java/mapper包
cn/java/mapper/CourseMapper.java
package cn.java.mapper;
import cn.java.pojo.Course;
import java.util.List;
public interface CourseMapper {
/**
* @param :
* @return List<Course>
* @author GCT
* @description 多对多:查询全部课程及其授课老师的信息;
* @date 2022/10/19 20:48
*/
public List<Course> getAllCourseAndTeacher();
/**
* @param Cid:
* @return Course
* @author GCT
* @description 多对多:根据课程的ID,查询该课程及其授课老师的信息;
* @date 2022/10/19 20:46
*/
public Course getAllCourseAndTeacherByCid(String Cid);
}
cn/java/mapper/CourseMapper.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="cn.java.mapper.CourseMapper">
<!-- 多对多:根据课程的ID,查询该课程及其授课老师的信息;-->
<!--定义结果集:课程的信息及对应老师的信息-->
<resultMap id="TeachesInstructorCourseMap" type="Course">
<id property="courseId" column="course_id"/>
<result property="title" column="title"/>
<result property="deptName" column="dept_name"/>
<result property="credits" column="credits"/>
<!--每门课程可能由一个或多个老师授课-->
<!-- 注意要配置对应的pojo文件-->
<collection property="instructorList" ofType="Instructor">
<id property="id" column="ID"/>
<result property="name" column="name"/>
<result property="deptName" column="dept_name"/>
<result property="salary" column="salary"/>
<!--每个老师可能授一门或多门课-->
<!-- 注意要配置对应的pojo文件-->
<collection property="teachesList" ofType="Teaches">
<id property="id" column="ID"/>
<id property="courseId" column="course_id"/>
<id property="secId" column="sec_id"/>
<id property="semester" column="semester"/>
<id property="year" column="year"/>
</collection>
</collection>
</resultMap>
<!-- 查询所有课程的信息及其授课老师的信息(老师个人信息+老师授课信息)-->
<select id="getAllCourseAndTeacher" resultMap="TeachesInstructorCourseMap">
SELECT *
FROM course C, teaches T, instructor I
WHERE C.course_id = T.course_id
AND T.id = I.id
</select>
<!-- 多对多:根据课程的ID,查询该课程的信息及其授课老师的信息(老师个人信息+老师授课信息);-->
<!-- select * from -->
<!-- course t1, teaches t2, instructor t3 -->
<!-- where t1.course_id = t2.course_id -->
<!-- and t2.id = t3.id -->
<!-- and t1.course_id = #{id};-->
<select id="getAllCourseAndTeacherByCid" resultMap="TeachesInstructorCourseMap">
SELECT *
FROM course C, teaches T, instructor I
WHERE C.course_id = T.course_id
AND T.id = I.id
AND C.course_id = #{Cid}
</select>
</mapper>
cn/java/mapper/DepartmentMapper.java
package cn.java.mapper;
import cn.java.pojo.Department;
import java.util.List;
/**
* description:
* author :hj
* date: 2020/10/17
*/
public interface DepartmentMapper {
/**
* @param :
* @return List<Department>
* @author GCT
* @description 一对多:查询所有系及其包含的老师信息
* @date 2022/10/20 15:33
*/
public List<Department> getAllDeptAndTeacher();
/**
* @param dept:
* @return Department
* @author GCT
* @description 一对多:根据系的名字,查询该系及其包含的老师信息
* @date 2022/10/19 20:41
*/
public Department getDeptAndTeacherByDName(String DName);
}
cn/java/mapper/DepartmentMapper.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="cn.java.mapper.DepartmentMapper">
<!-- 查询一个系里的老师信息-->
<!--配置1对多 结果集映射-->
<resultMap id="departmentMap" type="Department">
<!--主键-->
<id property="deptName" column="dept_name"/>
<result property="building" column="building"/>
<result property="budget" column="budget"/>
<!--配置一对多关系 “一个系包含多个老师 -->
<!-- 注意要配置对应的pojo文件-->
<collection property="instructors" ofType="Instructor">
<id property="id" column="ID"/>
<result property="name" column="name"/>
<result property="deptName" column="dept_name"/>
<result property="salary" column="salary"/>
</collection>
</resultMap>
<!--查询1:查询一个系和该系的老师信息,结果集用resultMap="departmentMap"来封装-->
<select id="getAllDeptAndTeacher" resultMap="departmentMap">
select D.*,I.*
from department D , instructor I
where D.dept_name = I.dept_name
</select>
<!--一对多:根据系的名字,查询该系及其包含的老师信息;-->
<!-- select t1.*,t2.* -->
<!-- from department t1 , instructor t2 -->
<!-- where t1.dept_name = t2.dept_name-->
<!-- and D.dept_name=#{dept_name}-->
<!--查询2:根据系的名字,查询该系及其包含的老师信息-->
<select id="getDeptAndTeacherByDName" resultMap="departmentMap">
select D.*,I.*
from department D , instructor I
where D.dept_name = I.dept_name
and D.dept_name=#{dept_name}
</select>
</mapper>
cn/java/mapper/InstructorMapper.java
package cn.java.mapper;
import cn.java.pojo.Instructor;
import cn.java.pojo.Teaches;
import java.util.List;
public interface InstructorMapper {
/**
* @param :
* @return List<Instructor>
* @author GCT
* @description 多对一:查询每个老师及其所属系的信息
* @date 2022/10/20 15:34
*/
public List<Instructor> getAllTeacherAndDept();
/**
* 多对一:根据老师的ID,查询每个老师及其所属系的信息
* @param Iid
* @return
*/
public Instructor getTeacherAndDeptByIid(String Iid);
/**
* @param :
* @return List<Instructor>
* @author GCT
* @description 多对多:查询每个老师及其所授课程的信息
* @date 2022/10/19 20:48
*/
public List<Instructor> getAllTeacherAndCourse();
/**
* @param Tid:
* @return Instructor
* @author GCT
* @description 多对多:根据老师的ID,查询每个老师及其所授课程的信息
* @date 2022/10/19 20:47
*/
public Instructor getTeacherAndCourseByTid(String Tid);
}
cn/java/mapper/InstructorMapper.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="cn.java.mapper.InstructorMapper">
<!-- 多对一:根据老师的ID,查询每个老师及其所属系的信息 结果集-->
<resultMap id="TeacherMap" type="Instructor">
<id property="id" column="ID"/>
<result property="name" column="name" />
<result property="deptName" column="dept_name" />
<result property="salary" column="salary" />
<!--对应一个系,用association-->
<!-- 注意要配置对应的pojo文件-->
<association property="department" javaType="Department">
<id property="deptName" column="dept_name"/>
<result property="building" column="building"/>
<result property="budget" column="budget"/>
</association>
</resultMap>
<!-- 多对多:根据老师的ID,查询每个老师及其所授课程的信息 结果集-->
<resultMap id="TeachesInstructorCourseMap" type="Instructor">
<id property="id" column="ID"/>
<result property="name" column="name" />
<result property="deptName" column="dept_name" />
<result property="salary" column="salary" />
<!--该老师授课课程(可能不止一个)-->
<!-- 注意要配置对应的pojo文件-->
<collection property="teachesList" ofType="Teaches">
<id property="id" column="ID"/>
<id property="courseId" column="course_id"/>
<id property="secId" column="sec_id"/>
<id property="semester" column="semester"/>
<id property="year" column="year"/>
<!--每个课程的信息(在course表)-->
<!-- 注意要配置对应的pojo文件-->
<collection property="courseList" ofType="Course">
<id property="courseId" column="course_id"/>
<result property="title" column="title"/>
<result property="deptName" column="dept_name"/>
<result property="credits" column="credits"/>
</collection>
</collection>
</resultMap>
<!-- 查询每个老师及其所属系的信息-->
<select id="getAllTeacherAndDept" resultMap="TeacherMap">
select * from
instructor I left join department D
on I.dept_name=D.dept_name
</select>
<!-- 多对一:根据老师的ID,查询每个老师及其所属系的信息-->
<!-- select * from -->
<!-- instructor t1 left join department t2 -->
<!-- on t1.dept_name=t2.dept_name
where id = #{id};-->
<select id="getTeacherAndDeptByIid" resultMap="TeacherMap">
select * from
instructor I left join department D
on I.dept_name=D.dept_name
where I.id = #{Iid}
</select>
<!-- 查询每个老师及其所授课程的信息-->
<select id="getAllTeacherAndCourse" resultMap="TeachesInstructorCourseMap">
SELECT *
FROM teaches T, instructor I,course C
WHERE T.id = I.id
AND T.course_id = C.course_id
</select>
<!-- 多对多:根据老师的ID,查询每个老师及其所授课程的信息;-->
<!-- select * from -->
<!-- teaches T, instructor I,course C -->
<!-- where T.id = I.id -->
<!-- and T.course_id = C.course_id -->
<!-- and T.id = #{id}-->
<select id="getTeacherAndCourseByTid" resultMap="TeachesInstructorCourseMap">
SELECT *
FROM teaches T, instructor I,course C
WHERE T.id = I.id
AND T.course_id = C.course_id
AND T.id = #{Tid}
</select>
</mapper>
src/main/resources文件夹
src/main/resources/database.properties
#mysql8
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/university?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
username=root
password=你的密码
#mysql 5
#driver=com.mysql.jdbc.Driver
#url=jdbc:mysql://localhost:3306/myschool?useUnicode=true&characterEncoding=utf8
#username=root
#password=
src/main/resources/mybatis.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="database.properties"/>
<!--优化1:给实体类取别名-->
<typeAliases>
<!-- 指定包名,直接给包下所有的实体类取别名。默认的实体类的别名就是类名(不区分小大写)-->
<!-- User实体类:User、user、USER-->
<package name="cn.java.pojo"/>
</typeAliases>
<!-- 配置数据库连接环境:driver、url、username、password -->
<environments default="mysql">
<!-- 开始配置mysql -->
<environment id="mysql">
<!--配置事务 -->
<transactionManager type="JDBC"></transactionManager>
<!-- 配置数据源 -->
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!-- 关联局部SQL映射配置文件 ,在每一个mapper里,指定SQL映射文件名及全路径-->
<mappers>
<!-- <mapper resource="cn/java/mapper/DepartmentMapper.xml"/>-->
<!-- <mapper resource="cn/java/mapper/StudentMapper.xml"/>-->
<!-- <mapper resource="cn/java/mapper/CourseMapper.xml"/>-->
<!-- <mapper resource="cn/java/mapper/TakesMapper.xml"/>-->
<!--也可以使用如下命令,把该包下全部.xml文件映射过来-->
<!-- 使用包名完成局部SQL配置文件和主配置文件的关联-->
<package name="cn.java.mapper"/>
</mappers>
</configuration>
src/test/java包
src/test/java/TestCourseMapper.java
import cn.java.mapper.CourseMapper;
import cn.java.pojo.Course;
import cn.java.pojo.Instructor;
import cn.java.pojo.Teaches;
import cn.java.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.util.List;
/**
* @projectName: week8_ _2
* @package: PACKAGE_NAME
* @className: TestCourseMapper
* @author: GCT
* @description: TODO
* @date: 2022/10/19 21:52
* @version: 1.0
*/
public class TestCourseMapper {
SqlSession session = null;
CourseMapper courseMapper = null;
@Before
public void init() {
session = MybatisUtil.getSession();
//dm就是Mapper的实现类
courseMapper = session.getMapper(CourseMapper.class);
// studentMapper = session.getMapper(StudentMapper.class);
// takesMapper = session.getMapper(TakesMapper.class);
}
// 查询全部课程及其授课老师的信息(老师的学习及其教授的课程);
@Test
public void getAllCourseAndTeacher() {
List<Course> courses = courseMapper.getAllCourseAndTeacher();
for (Course course : courses) {
System.out.println("课程的信息是 " + course);
System.out.println("================================================");
System.out.println(course.getCourseId()+" 课程的授课教师信息是:");
List<Instructor> instructorList = course.getInstructorList();
for (Instructor instructor : instructorList) {
System.out.println(instructor);
System.out.println("该教师 "+instructor.getName()+" 教授的课程有:");
List<Teaches> teaches = instructor.getTeachesList();
for (Teaches teach : teaches) {
System.out.println(teach);
}
System.out.println("===========================================");
}
System.out.println("===========================================");
}
}
@Test
public void getAllCourseAndTeacherByCid() {
Course course = courseMapper.getAllCourseAndTeacherByCid("CS-101");
// for (Course course : courses) {
System.out.println("课程的信息是 " + course);
System.out.println("================================================");
System.out.println(course.getCourseId()+" 课程的授课教师信息是:");
List<Instructor> instructorList = course.getInstructorList();
for (Instructor instructor : instructorList) {
System.out.println(instructor);
System.out.println("该教师 "+instructor.getName()+" 教授的课程有:");
List<Teaches> teaches = instructor.getTeachesList();
for (Teaches teach : teaches) {
System.out.println(teach);
}
System.out.println("===========================================");
}
System.out.println("===========================================");
// }
}
@After
public void destory () {
MybatisUtil.closeSession(session);
}
}
src/test/java/TestDepartmentMapper.java
import cn.java.mapper.DepartmentMapper;
import cn.java.pojo.Department;
import cn.java.pojo.Instructor;
import cn.java.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.util.List;
/**
* @projectName: week8_ _2
* @package: PACKAGE_NAME
* @className: TestDepartmentMapper
* @author: GCT
* @description: TODO
* @date: 2022/10/19 21:53
* @version: 1.0
*/
public class TestDepartmentMapper {
SqlSession session=null;
DepartmentMapper departmentMapper=null;
// StudentMapper studentMapper =null;
@Before
public void init(){
session= MybatisUtil.getSession();
departmentMapper=session.getMapper(DepartmentMapper.class);
// studentMapper = session.getMapper(StudentMapper.class);
}
//1:n 查询每个系里的老师
@Test
public void testGetAllDeptAndTeacher() {
List<Department> depts = departmentMapper.getAllDeptAndTeacher();
for (Department dept : depts) {
System.out.println("系的信息是 " + dept);
// System.out.println("-----------------");
System.out.println("该系包含的老师信息是:");
List<Instructor> instructors = dept.getInstructors();
for (Instructor instructor : instructors) {
System.out.println(instructor);
}
System.out.println("-----------------");
}
}
// 一对多:根据系的名字,查询该系及其包含的老师信息
@Test
public void testGetDeptAndTeacherByDName() {
Department dept = departmentMapper.getDeptAndTeacherByDName("Comp. Sci.");
System.out.println("该系信息是:" + dept);
System.out.println("该系包含的老师信息是:");
List<Instructor> instructors = dept.getInstructors();
for (Instructor instructor : instructors) {
System.out.println(instructor);
}
}
@After
public void destory(){
MybatisUtil.closeSession(session);
}
}
src/test/java/TestInstructorMapper.java
import cn.java.mapper.DepartmentMapper;
import cn.java.mapper.InstructorMapper;
import cn.java.pojo.Course;
import cn.java.pojo.Department;
import cn.java.pojo.Instructor;
import cn.java.pojo.Teaches;
import cn.java.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.util.List;
/**
* @projectName: week8_ _2
* @package: PACKAGE_NAME
* @className: TestInstructorMapper
* @author: GCT
* @description: TODO
* @date: 2022/10/19 21:53
* @version: 1.0
*/
public class TestInstructorMapper {
SqlSession session=null;
InstructorMapper instructorMapper=null;
// StudentMapper studentMapper =null;
@Before
public void init(){
session= MybatisUtil.getSession();
instructorMapper=session.getMapper(InstructorMapper.class);
}
//n:1 多对一,查询所有老师及其所属的系的信息
@Test
public void testGetAllTeacherAndDept() {
List<Instructor> instructors = instructorMapper.getAllTeacherAndDept();
for (Instructor instructor : instructors) {
System.out.println("老师的信息是 " + instructor);
// System.out.println("-----------------");
System.out.println("老师对应的系信息是:");
Department dept = instructor.getDepartment();
// for (Instructor instructor : instructors) {
System.out.println(dept);
// }
System.out.println("-----------------");
}
}
//n:1 多对一,查询老师所属的系的信息
@Test
public void getTeacherAndDeptByIid(){
Instructor instructor = instructorMapper.getTeacherAndDeptByIid("15151");
System.out.println("老师的信息是:"+instructor);
Department dept = instructor.getDepartment();
System.out.println("老师所属系的信息是:"+dept);
}
//多对多
// 查询全部老师(的信息)及其所授课程的信息(教授的课程(教哪些课)及对应课程的信息);
@Test
public void getAllTeacherAndCourse() {
List<Instructor> instructors = instructorMapper.getAllTeacherAndCourse();
for (Instructor instructor : instructors) {
System.out.println("该老师的信息是 " + instructor);
System.out.println("================================================");
System.out.println("该教师 "+instructor.getName()+" 教授的课程及其对应信息如下:");
List<Teaches> teachesList = instructor.getTeachesList();
for (Teaches teach : teachesList) {
System.out.println(teach);
List<Course> courseList = teach.getCourseList();
for (Course course : courseList) {
System.out.println("课程"+course.getCourseId()+"的信息为:");
System.out.println(course);
System.out.println("---------------------------------");
}
}
System.out.println("===========================================");
System.out.println("===========================================");
}
}
//多对多
// 根据老师的ID,查询每个老师(的信息)及其所授课程的信息(教授的课程(教哪些课)及对应课程的信息)
@Test
public void getTeacherAndCourseByTid() {
Instructor instructor = instructorMapper.getTeacherAndCourseByTid("10101");
System.out.println("该老师的信息是 " + instructor);
System.out.println("================================================");
System.out.println("该教师 "+instructor.getName()+" 教授的课程及其对应信息如下:");
List<Teaches> teachesList = instructor.getTeachesList();
for (Teaches teach : teachesList) {
System.out.println(teach);
List<Course> courseList = teach.getCourseList();
for (Course course : courseList) {
System.out.println("课程"+course.getCourseId()+"的信息为:");
System.out.println(course);
System.out.println("---------------------------------");
}
}
System.out.println("===========================================");
System.out.println("===========================================");
}
@After
public void destory(){
MybatisUtil.closeSession(session);
}
}
五、项目中用到的数据库表结构
university数据库
course
department
instructor
teaches