mybatis 单表最佳使用方式（Mybatis入门四------联表查询）
mybatis 单表最佳使用方式（Mybatis入门四------联表查询）<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<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper
Java语言凭借着其良好的特性与广泛的开源应用框架 以及众多的应用支持成为市场占有率最高的语言.SSM框架作为java开发目前应用最为广泛的框架组合 也是java学习必学的三大基础框架.今天小编将与大家一起进行Mybaits框架的学习 本期学习将分为四篇文章进行介绍 欢迎大家关注小编以便及时获得最新文章.
上一篇文章小编着重介绍了Mybaits框架的基础原理 以及相关的概念 本文小编将带着大家做一个简单的增删改查小案例 深入的了解下mybaits框架的实际应用.对于未观看上一篇文章 对Mybaits不是太了解的读者可以点击下面链接进行观看上一篇文章
上一篇: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.selectTeacher" 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(); } }
