Mybatis入门-04-多对一
一、 前言
一切当以官方文档为基准。
参考视频:
在我学习MySQL的时候,老师告诉我尽量不要使用外键,而是在JDBC代码中用逻辑代码去替代他。
至于原话,似乎是下面这句:
不得使用外键与级联,一切外键概念必须在应用层解决。
秉承这样的概念,在下面的演示中数据库只有一个“概念上”的外键,实质上是多表查询。
二、准备工作
2.0 依赖、路径
为了方便,这里使用前面留下来的配置:
本文之前的操作步骤:
上面是我喜欢的一些配置,一般来说按自己配置来并不会太影响。
2.1 创建数据库
create database if not exists `mybatis`;
use `mybatis`;
create table if not exists `User`(
`id` INT(20) not null primary key,
`name` VARCHAR(20) default null,
`pwd` varchar(20) default null
)ENGINE=INNODB default CHARSET = UTF8;
insert into `User`(`id`,`name`,`pwd`)
values (1,'admin','123456'),
(2,'Jax','123456'),
(3,'Jinx','123455'),
(4,'Query','123456'),
(5,'biubiu','123456');
select `id`,`name` from mybatis.user;
CREATE TABLE `teacher` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO teacher(`id`, `name`) VALUES (1, '秦老师');
CREATE TABLE `student` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
`tid` INT(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小张', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');
可以看到,没有定义外键。
大致效果:
2.2 创建实体类
Student.java
package com.duzhuan.pojo;
/**
* @Autord: HuangDekai
* @Date: 2020/9/16 14:45
* @Version: 1.0
* @since: jdk11
*/
public class Student {
private int id;
private String name;
private Teacher teacher;
public Student() {
}
public Student(int id, String name, Teacher teacher) {
this.id = id;
this.name = name;
this.teacher = teacher;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", teacher=" + teacher +
'}';
}
}
//省略空间,省略Getter和Setter方法
Teacher.java
package com.duzhuan.pojo;
/**
* @Autord: HuangDekai
* @Date: 2020/9/16 14:42
* @Version: 1.0
* @since: jdk11
*/
public class Teacher {
private int id;
private String name;
public Teacher() {
}
public Teacher(int id, String name) {
this.id = id;
this.name = name;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Teacher{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
2.3 MyBatis配置文件
db.properties:
driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=UTF8&serverTimezone=UTC
username = root
password = qq123456
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"></properties>
<settings>
<setting name="logImpl" value="SLF4J"/>
</settings>
<typeAliases>
<package name="com.duzhuan.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<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>
<mappers>
<mapper class="com.duzhuan.dao.TeacherMapper"></mapper>
<mapper class="com.duzhuan.dao.StudentMapper"></mapper>
</mappers>
</configuration>
注意:
-
此处使用了日志,不使用日志
删除
,或修改为相应日志,我这里的配置具体可看Mybatis入门-03-日志工厂:<settings> <setting name="logImpl" value="SLF4J"/> </settings>
-
此处命名了别名,可以看出是使用了pojo包里的所有实体类的名称作为别名。按照规定别名应为全小写,但是由于mybatis不区分大小写,之后再Mapper.xml中一般写时候都是完全使用实体类的类名,如
Student
:<typeAliases> <package name="com.duzhuan.pojo"/> </typeAliases>
-
此处为写Mapper之后添加:
<mappers> <mapper class="com.duzhuan.dao.TeacherMapper"></mapper> <mapper class="com.duzhuan.dao.StudentMapper"></mapper> </mappers>
2.4 工具类
在一整个程序里,只需要有一个sqlSessionFactory。
MybatisUtils.java:
package com.duzhuan.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;
/**
* @Autord: HuangDekai
* @Date: 2020/9/16 12:32
* @Version: 1.0
* @since: jdk11
*/
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
try {
String configuration = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(configuration);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSqlSession(){
return sqlSessionFactory.openSession();
}
}
三、Mapper
其中TeacherMapper和TeacherMapper.xml并未用到,只是出于惯例随便写的。
TeacherMapper:
package com.duzhuan.dao;
import com.duzhuan.pojo.Teacher;
/**
* @Autord: HuangDekai
* @Date: 2020/9/16 14:56
* @Version: 1.0
* @since: jdk11
*/
public interface TeacherMapper {
Teacher getTeacherById(int id);
}
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.duzhuan.dao.TeacherMapper">
<resultMap id="StudentMap" type="Student">
</resultMap>
<select id="getTeacherById" resultType="Teacher">
select * from mybatis.teacher where id = #{id}
</select>
</mapper>
StudentMapper:
package com.duzhuan.dao;
import com.duzhuan.pojo.Student;
import java.util.List;
/**
* @Autord: HuangDekai
* @Date: 2020/9/16 14:55
* @Version: 1.0
* @since: jdk11
*/
public interface StudentMapper {
List<Student> getStudentList();
}
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.duzhuan.dao.StudentMapper">
<select id="getStudentList" resultMap="StudentTeacher">
select s.id id,s.name sname,t.id tid,t.name tname
from mybatis.student s, mybatis.teacher t
where s.tid = t.id
</select>
<resultMap id="StudentTeacher" type="Student">
<result property="id" column="id"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
</association>
</resultMap>
</mapper>
-
由于Student与Teacher都有id,且字段名都为
id
,因此不得不使用别名。select s.id id,s.name sname,t.id tid,t.name tname from mybatis.student s, mybatis.teacher t where s.tid = t.id
这也方便MyBatis映射
-
<resultMap id="StudentTeacher" type="Student">
显然,Student
就是使用了之前在mybatis-config.xml
中设定好的别名,而不是使用全限定名 -
在这里,
property
的属性是实体类中对应的属性名,column
对应的是SQL查询语句查询的字段名(应该是数据库中的字段名,由于有重名,因此使用了查询语句中设好的别名,Mybatis会自动映射)由于type
的值是Student
,即可以视为返回一个(多个)Student
对象。<result property="id" column="id"/> <result property="name" column="sname"/>
-
这里由于返回的不是一个属性,而是一个对象,因此使用
association
,依旧得,在<association>
中property
的值依旧是对应的类的名称,javaType
表示返回一个Teacher
的实例(对象),里面的小标签是Teacher
这个实体类里面的属性(显然,如果Teacher
里面聚合有对象的话,需要再在<association>
里面嵌套一个<association>
):<association property="teacher" javaType="Teacher"> <result property="id" column="tid"/> <result property="name" column="tname"/> </association>
四、测试类
其中TeacherMapperTest为空。
StudentMapperTest:
package com.duzhuan.dao;
import com.duzhuan.pojo.Student;
import com.duzhuan.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
/**
* @Autord: HuangDekai
* @Date: 2020/9/16 23:14
* @Version: 1.0
* @since: jdk11
*/
public class StudentMapperTest {
@Test
public void getStudentListTest(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = mapper.getStudentList();
for (Student student : studentList) {
System.out.println(student);
}
sqlSession.close();
}
}