准备
项目结构
以下包含了一对多和多对一的公共部分
数据库,数据表
CREATE DATABASE mybatis_study;
USE mybatis_study;
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(10) NOT NULL,
`name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`tid` int(10) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `fktid`(`tid`) USING BTREE,
CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '小明', 1);
INSERT INTO `student` VALUES (2, '小红', 1);
INSERT INTO `student` VALUES (3, '小张', 1);
INSERT INTO `student` VALUES (4, '小李', 1);
INSERT INTO `student` VALUES (5, '小王', 1);
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`id` int(10) NOT NULL,
`name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '秦老师');
依赖
<?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.twgfs</groupId>
<artifactId>mybatis-study</artifactId>
<version>1.0.0-SNAPSHOT</version>
<properties>
<mybatis-version>3.5.2</mybatis-version>
<junit-version>4.12</junit-version>
<connect-version>8.0.12</connect-version>
<lombok-version>1.18.12</lombok-version>
</properties>
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>${mybatis-version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${connect-version}</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>${junit-version}</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>${lombok-version}</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.16</version>
</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>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>8</source>
<target>8</target>
</configuration>
</plugin>
</plugins>
</build>
</project>
工具类
SqlSessionUtils
package com.twgfs.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;
/**
* @Author:twg
* @Date: 2021/1/12 10:18
* @Description: TODO
* @Version: 1.0.0
*/
public class SqlSessionUtils {
static SqlSessionFactory sqlSessionFactory = null;
static {
String resource = "mybatis-config.xml";
InputStream inputStream = null;
try {
inputStream = Resources.getResourceAsStream(resource);
} catch (IOException e) {
e.printStackTrace();
System.out.println("没有找到配置文件");
}
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
public static SqlSession returnSqlSession() {
// 将下面的参数设置为true,mybatis会自动提交事务
return sqlSessionFactory.openSession(true);
}
}
日志文件
log4j.properties
# 全局日志配置
log4j.rootLogger=DEBUG, stdout
# MyBatis 日志配置
log4j.logger.org.mybatis.example.BlogMapper=TRACE
# 控制台输出
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%-5p %c %x - %m%n
#日志输出级别
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PrepareStatement=DEBUG
配置文件
位置在resource目录下
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>
<!--开启日志打印功能-->
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>
<!--开启实体类的包扫描,让类名作为返回类型也可,不用在写全限定名-->
<typeAliases>
<package name="com.twgfs.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url"
value="jdbc:mysql://localhost:3306/mybatis_study?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Hongkong"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/twgfs/dao/TeacherMapper.xml"/>
<mapper resource="com/twgfs/dao/StudentMapper.xml"/>
</mappers>
</configuration>
多对一
获取所有学生及对应老师的信息
实体类
- 从以下的实体类中可以看出,老师这个对象是放在了学生类当中的,那么学生和老师之间就是多对一的关系,而且从上面的sql可以看出老师的id和学生的tid是关联的。
- 如果是在数据库中查询可以直接根据两表关联的tid=id即可查询。但是放在mybatis中就只能通过多对一关系实现
Teacher
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Teacher {
private int id;
private String name;
}
Student
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
private int id;
private String name;
private Teacher teacher;
}
数据接口dao层
StudentDao
public interface StudentDao {
/*一堆多方式1*/
List<Student> getStudent2();
/*一对多方式2*/
List<Student> getStudent1();
Teacher getTeacher(int id);
}
数据接口mapper文件
以下有两种多对一实现关系mybatis实现方式
- 方式一:(常用)
- 如下可以通过结果查询的方式查询,先按照常规的查询数据的方式写好SQL语句
- 定义返回的类型为resultMap并且为resultMap起了一个关联别名st2(这个名字任意,目的就是用来关联resultMap定义的)
- 例如当前查询的是学生信息,包含了学生的老师信息,所以在实体类中把老师作为属性放到了学生的属性中,所以 中的type是Student
- 为固定方式 property表示实体类中的字段,column是数据库中的字段,如果查询sql中取别名了,这里就要写成别名。例如这里实体类的是id 数据库虽然也是id但是别名中取名为sid所以这里就是写的sid
- 因为teacher是作为属性放到student中作为属性的,所以下面就用标签标识 property="teacher"表示在student中的属性名,javaType="Teacher"表示类型。而association下面的result就是Teacher中的属性和数据库中Teacher的字段
- 方式二:
- 这种方式是在Student的查询嵌套了老师的查询
- 如下先是进行了student的查询,返回类型还是用resultMap表示,st用来关联resultMap的别名
- 中property="teacher"表示当前的类型column="tid"表示关联的列javaType="Teacher"表示当前的类型select="getTeacher"表示关联的子查询
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="com.twgfs.dao.StudentDao"> <!--方式一 按照查询结果的方式--> <select id="getStudent1" resultMap="st2"> select s.name sname ,s.id sid,t.name tname,t.id tid from student s, teacher t where s.tid=t.id; </select> <resultMap id="st2" type="Student"> <result property="id" column="sid"/> <result property="name" column="sname"/> <association property="teacher" javaType="Teacher"> <result property="name" column="tname"></result> <result property="id" column="tid"></result> </association> </resultMap> <!--方式二 按照子查询方式--> <select id="getStudent2" resultMap="st"> select * from student </select> <resultMap id="st" type="Student"> <result property="id" column="id"/> <result property="name" column="name"/> <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/> </resultMap> <select id="getTeacher" resultType="Teacher"> select * from teacher where id=#{id} </select> </mapper>
测试
在test/java测试包下面新建一个包和测试类
TestThree
public class TestThree {
/*一对多方式一查询*/
@Test
public void Test1() {
SqlSession sqlSession = SqlSessionUtils.returnSqlSession();
TeacherDao mapper = sqlSession.getMapper(TeacherDao.class);
Teacher teacher = mapper.getTeacher(1);
System.out.println(teacher);
}
/*一对多方式 -1:*/
@Test
public void oneDMore2() {
SqlSession sqlSession = SqlSessionUtils.returnSqlSession();
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
List<Student> student = mapper.getStudent1();
student.forEach(System.out::println);
}
/*一对多方式 -2*/
@Test
public void oneDMore1() {
SqlSession sqlSession = SqlSessionUtils.returnSqlSession();
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
List<Student> student = mapper.getStudent2();
student.forEach(System.out::println);
}
}
一对多
获取指定老师下的所有学生信息
实体类
- 从以下的实体类中可以看出,学生这个对象集合是放在了老师类当中的,那么老师和学生之间就是一对多的关系,而且从下面mapper中的sql可以看出老师的id和学生的tid是关联的。
- 如果是在数据库中查询可以直接根据两表关联的tid=id即可查询。但是放在mybatis中就只能通过一对多关系实现
Teacher
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Teacher {
private int id;
private String name;
//一个老师对应多个学生
private List<Student> student;
}
Student
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
private int id;
private String name;
private int tid;
}
数据接口dao层
TeacherDao
public interface TeacherDao {
//多对一,一个老师对应一个集合的学生
List<Teacher> getListTeacher(@Param("teacherid") int id);
}
数据接口mapper文件
以下有两种一对多实现关系。但是下面用的是最容易理解的一个mybatis实现方式
- 方式一:(常用)
- 如下可以通过结果查询的方式查询,先按照常规的查询数据的方式写好SQL语句
- 定义返回的类型为resultMap并且为resultMap起了一个关联别名t(这个名字任意,目的就是用来关联resultMap定义的)
- 例如当前查询的是老师的信息,包含了所教学生的信息,所以在实体类中把学生作为集合属性放到了老师的属性中,所以 中的type是Teacher
- 因为这里是用集合的方式,所以是使用的而不是association,而且集合应该使用ofType而不是javaType
- 因为老师的属性中放的是student所以应该使用property=“student”,而Teacher中的类型是集合,所以这里应该使用ofType=“Student”
- 如果需要两个查询之间传值可以在collection中加入column=“传递的数据库字段名或列名”
TeacherMapper.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.twgfs.dao.TeacherDao">
<select id="getListTeacher" resultMap="t">
select s.id sid,s.name sname,t.name tname,t.id tid
from student s,teacher t
where s.tid = t.id and t.id=#{teacherid}
</select>
<resultMap id="t" type="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<!--集合需要是用collection而不是association,而且集合应该使用ofType而不是javaType-->
<collection property="student" ofType="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
</mapper>
测试
在test/java测试包下面新建一个包和测试类
TestFour
public class TestFour {
@Test
public void testTeacher(){
SqlSession sqlSession = SqlSessionUtils.returnSqlSession();
TeacherDao mapper = sqlSession.getMapper(TeacherDao.class);
List<Teacher> listTeacher = mapper.getListTeacher(1);
listTeacher.forEach((a)->System.out.println(a));
}
}