Mybatis入门（四）------联表查询
2024-09-07 05:46:34
Mybatis联表查询
一、1对1查询
1、数据库建表
假设一个老师带一个学生
CREATE TABLE teacher(
t_id INT PRIMARY KEY,
t_name VARCHAR(30)
);
CREATE TABLE student(
s_id INT PRIMARY KEY,
s_name VARCHAR(30),
t_id INT ,
FOREIGN KEY(t_id) REFERENCES teacher(t_id)
);
INSERT INTO teacher VALUES(1, "wanglaoshi");
INSERT INTO student VALUES(1, "zhangsan", 1);
2、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.mybatis.mapper.TeacherMapper">
<!-- 关联查询中你需要告诉 MyBatis 如何加载关联。MyBatis 在这方面会有两种不同的方式: -->
<!-- 1、嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集 -->
<resultMap type="com.mybatis.model.Student" id="Student1">
<id column="s_id" property="id"></id>
<result column="s_name" property="name"></result>
<association property="teacher" javaType="com.mybatis.model.Teacher">
<id column="t_id" property="id"></id>
<result column="t_name" property="name"></result>
</association>
</resultMap>
<select id="selectStudent1" resultMap="Student1">
select * from student s, teacher t where t.t_id = s.t_id and s.s_id = #{id}
</select>
<!-- 2、嵌套查询：通过执行另外一个 SQL 映射语句来返回预期的复杂类型 -->
<resultMap type="com.mybatis.model.Student" id="Student2">
<id column="s_id" property="id"></id>
<result column="s_name" property="name"></result>
<association column="t_id" property="teacher" select="selectTeacher"></association>
</resultMap>
<select id="selectStudent2" resultMap="Student2">
select * from student s, teacher t where t.t_id = s.t_id and s.s_id = #{id}
</select>
<select id="selectTeacher" resultType="com.mybatis.model.Teacher">
select t_id id, t_name name from teacher t where t.t_id = #{id}
</select>
</mapper>
3、在Configuration.xml中注册TeacherMapper.xml
<mappers>
<!-- 注册UserMapper.xml文件 -->
<mapper resource="com/mybatis/mapper/UserMapper.xml"/>
<!-- 注册TeacherMapper.xml文件 -->
<mapper resource="com/mybatis/mapper/TeacherMapper.xml"/>
<!-- 注册UserMapperI接口 -->
<mapper class="com.mybatis.mapper.UserMapperI"/>
</mappers>
4、编写JavaBean
public class Teacher {
//主键
private String id;
//姓名
private String name;
//学生
private Student student;
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 Student getStudent() {
return student;
}
public void setStudent(Student student) {
this.student = student;
}
}
public class Student {
//主键
private String id;
//姓名
private String name;
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;
}
}
5、单元测试代码
public class DbTest4 {
private SqlSessionFactory sqlSessionFactory;
@Before
public void init() throws IOException {
//通过mybatis提供的资源加载类加载配置文件
Reader reader = Resources.getResourceAsReader("Configuration.xml");
//获取SqlSessionFactoryBuilder
SqlSessionFactoryBuilder sqlSessionFactroyBuilder = new SqlSessionFactoryBuilder();
//根据XML配置文件构建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = sqlSessionFactroyBuilder.build(reader);
this.sqlSessionFactory = sqlSessionFactory;
}
@Test
public void queryStudent1() {
SqlSession session = sqlSessionFactory.openSession(true);
try {
Teacher teacher = session.selectOne("com.mybatis.mapper.TeacherMapper.selectTeacher1", 1);
System.out.println(JsonUtils.objectToJsonString(teacher));
} finally {
session.close();
}
}
@Test
public void queryStudent2() {
SqlSession session = sqlSessionFactory.openSession(true);
try {
Teacher teacher = session.selectOne("com.mybatis.mapper.TeacherMapper.selectTeacher2", 1);
System.out.println(JsonUtils.objectToJsonString(teacher));
} finally {
session.close();
}
}
}
6、输出结果
嵌套结果：
嵌套查询
二、1对多查询
1、数据库建表
CREATE TABLE task(
task_id INT PRIMARY KEY,
description VARCHAR(50),
score DOUBLE,
s_id INT,
FOREIGN KEY(s_id) REFERENCES student(s_id)
);
INSERT INTO task VALUES(1, "数学作业", "100", 1);
INSERT INTO task VALUES(2, "英语作业", "98", 1);
2、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.mybatis.mapper.TeacherMapper">
<resultMap type="com.mybatis.model.Teacher" id="Teacher">
<id column="t_id" property="id"></id>
<result column="t_name" property="name"></result>
<association property="student" javaType="com.mybatis.model.Student">
<id column="s_id" property="id"></id>
<result column="s_name" property="name"></result>
<collection property="tasks" ofType="com.mybatis.model.Task">
<id column="task_id" property="id"></id>
<result column="description" property="description"></result>
<result column="score" property="score"></result>
</collection>
</association>
</resultMap>
<select id="selectTeacher" resultMap="Teacher">
select * from student s, teacher t, task e where t.t_id = s.t_id and e.s_id = s.s_id and t.t_id = #{id}
</select>
</mapper>
3、编写JavaBean
public class Teacher {
//主键
private String id;
//姓名
private String name;
//学生
private Student student;
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 Student getStudent() {
return student;
}
public void setStudent(Student student) {
this.student = student;
}
}
public class Student {
//主键
private String id;
//姓名
private String name;
//作业集合
private List<Task> tasks;
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 List<Task> getTasks() {
return tasks;
}
public void setTasks(List<Task> tasks) {
this.tasks = tasks;
}
}
public class Task {
//主键
private String id;
//描述
private String description;
//分数
private double score;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public double getScore() {
return score;
}
public void setScore(double score) {
this.score = score;
}
}
4、单元测试代码
public class DbTest5 {
private SqlSessionFactory sqlSessionFactory;
@Before
public void init() throws IOException {
//通过mybatis提供的资源加载类加载配置文件
Reader reader = Resources.getResourceAsReader("Configuration.xml");
//获取SqlSessionFactoryBuilder
SqlSessionFactoryBuilder sqlSessionFactroyBuilder = new SqlSessionFactoryBuilder();
//根据XML配置文件构建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = sqlSessionFactroyBuilder.build(reader);
this.sqlSessionFactory = sqlSessionFactory;
}
@Test
public void queryStudent() {
SqlSession session = sqlSessionFactory.openSession(true);
try {
Teacher teacher = session.selectOne("com.mybatis.mapper.TeacherMapper.selectTeacher", 1);
System.out.println(JsonUtils.objectToJsonString(teacher));
} finally {
session.close();
}
}
}
5、输出结果
最新文章
- ajax基础学习
- how-to-redirect-cin-and-cout-to-files
- Entity Framework在Asp.net MVC中的实现One Context Per Request(附源码)
- ecshop后台,listtable.js使用
- Centos6.5 安装 RabbitMQ3.6.1
- linux_jvm_jhat_dump内存分析
- 一些css效果积累
- Codeforces Round #291 (Div. 2) C - Watto and Mechanism 字符串
- Route@简单应用
- hook
- 解决Safari页面缓存的问题
- Nginx 反向代理接收用户包体方式
- css背景色 透明字体不透明
- __ATTR引发的编译错误【原创】
- 洛谷P4069 [SDOI2016]游戏(李超线段树)
- bitnami下webmin安装
- 【Zookeeper】源码分析之持久化（三）之FileTxnSnapLog
- 操作系统-移动操作系统-百科： iOS（苹果公司的移动操作系统）
- 【转载】BP神经网络
- 怎么将Android的API demo导入到Eclipse工作区中