MyBatis研习录(10)——MyBatis多对多查询


C语言自学完备手册(33篇)

Android多分辨率适配框架

JavaWeb核心技术系列教程

HTML5前端开发实战系列教程

MySQL数据库实操教程(35篇图文版)

推翻自己和过往——自定义View系列教程(10篇)

走出思维困境,踏上精进之路——Android开发进阶精华录

讲给Android程序员看的前端系列教程(40集免费视频教程+源码)


版权声明

  • 本文原创作者:谷哥的小弟
  • 作者博客地址:http://blog.csdn.net/lfdfhl

概述

在本节教程中,我们来学习MyBatis的多对多查询。在此,我们以学生和老师为例介绍多对多:每个学生有多个老师,每个老师有多个学生。

数据准备

DROP DATABASE IF EXISTS mybatisDatabase;
CREATE DATABASE mybatisDatabase;
USE mybatisDatabase;

-- 创建学生表
CREATE TABLE student(
	studentID INT PRIMARY KEY,
	studentName VARCHAR(50) NOT NULL
);

-- 创建教师表
CREATE TABLE teacher(
	teacherID INT(4) PRIMARY KEY,
	teacherName VARCHAR(50) NOT NULL
);


-- 创建学生和老师的关系表
CREATE TABLE student_teacher_relation(
  stuID INT(4),
  teaID INT(4)
);

-- 为学生和老师的关系表添加外键
ALTER TABLE student_teacher_relation ADD CONSTRAINT fk_stuID FOREIGN KEY (stuID) REFERENCES student(studentID);
ALTER TABLE student_teacher_relation ADD CONSTRAINT fk_teaID FOREIGN KEY (teaID) REFERENCES teacher(teacherID);

-- 向学生表添加数据
INSERT INTO student(studentID,studentName) VALUES(1,'lucy');
INSERT INTO student(studentID,studentName) VALUES(2,'dila');
INSERT INTO student(studentID,studentName) VALUES(3,'yuki');
INSERT INTO student(studentID,studentName) VALUES(4,'kedo');

-- 向教师表添加数据
INSERT INTO teacher(teacherID,teacherName) VALUES(1,'lili');
INSERT INTO teacher(teacherID,teacherName) VALUES(2,'aiai');
INSERT INTO teacher(teacherID,teacherName) VALUES(3,'klkl');
INSERT INTO teacher(teacherID,teacherName) VALUES(4,'rqrq');

-- 向学生和老师的关系表添加数据
INSERT INTO student_teacher_relation(stuID,teaID) VALUES(1,1);
INSERT INTO student_teacher_relation(stuID,teaID) VALUES(1,3);
INSERT INTO student_teacher_relation(stuID,teaID) VALUES(2,1);
INSERT INTO student_teacher_relation(stuID,teaID) VALUES(2,2);
INSERT INTO student_teacher_relation(stuID,teaID) VALUES(2,3);
INSERT INTO student_teacher_relation(stuID,teaID) VALUES(3,4);
INSERT INTO student_teacher_relation(stuID,teaID) VALUES(4,1);

SELECT * FROM student;
SELECT * FROM teacher;
SELECT * FROM student_teacher_relation;

搭建开发环境

创建普通的Java工程,结构如下:
在这里插入图片描述

Student

package cn.com.pojo;

import java.util.List;
/**
 * 本文作者:谷哥的小弟 
 * 博客地址:http://blog.csdn.net/lfdfhl
 */
public class Student {
	private Integer studentID;
	private String studentName;
	private List<Teacher> teacherList;
	public Student() {
		super();
	}
	public Student(Integer studentID, String studentName, List<Teacher> teacherList) {
		super();
		this.studentID = studentID;
		this.studentName = studentName;
		this.teacherList = teacherList;
	}
	public Integer getStudentID() {
		return studentID;
	}
	public void setStudentID(Integer studentID) {
		this.studentID = studentID;
	}
	public String getStudentName() {
		return studentName;
	}
	public void setStudentName(String studentName) {
		this.studentName = studentName;
	}
	public List<Teacher> getTeacherList() {
		return teacherList;
	}
	public void setTeacherList(List<Teacher> teacherList) {
		this.teacherList = teacherList;
	}
	@Override
	public String toString() {
		return "Student [studentID=" + studentID + ", studentName=" + studentName + ", teacherList=" + teacherList
				+ "]";
	}
	
	
}

Teacher

package cn.com.pojo;

import java.util.List;
/**
 * 本文作者:谷哥的小弟 
 * 博客地址:http://blog.csdn.net/lfdfhl
 */
public class Teacher {
	private Integer teacherID;
	private String teacherName;
	private List<Student> StudentList;
	public Teacher() {
		super();
	}
	public Teacher(Integer teacherID, String teacherName, List<Student> studentList) {
		super();
		this.teacherID = teacherID;
		this.teacherName = teacherName;
		StudentList = studentList;
	}
	public Integer getTeacherID() {
		return teacherID;
	}
	public void setTeacherID(Integer teacherID) {
		this.teacherID = teacherID;
	}
	public String getTeacherName() {
		return teacherName;
	}
	public void setTeacherName(String teacherName) {
		this.teacherName = teacherName;
	}
	public List<Student> getStudentList() {
		return StudentList;
	}
	public void setStudentList(List<Student> studentList) {
		StudentList = studentList;
	}
	@Override
	public String toString() {
		return "Teacher [teacherID=" + teacherID + ", teacherName=" + teacherName + ", StudentList=" + StudentList
				+ "]";
	}
	
}

StudentTeacherRelation

package cn.com.pojo;
/**
 * 本文作者:谷哥的小弟 
 * 博客地址:http://blog.csdn.net/lfdfhl
 */
public class StudentTeacherRelation {
	private Integer stuID;
	private Integer teaID;
	public StudentTeacherRelation() {
		super();
		// TODO Auto-generated constructor stub
	}
	public StudentTeacherRelation(Integer stuID, Integer teaID) {
		super();
		this.stuID = stuID;
		this.teaID = teaID;
	}
	public Integer getStuID() {
		return stuID;
	}
	public void setStuID(Integer stuID) {
		this.stuID = stuID;
	}
	public Integer getTeaID() {
		return teaID;
	}
	public void setTeaID(Integer teaID) {
		this.teaID = teaID;
	}
	@Override
	public String toString() {
		return "StudentTeacherRelation [stuID=" + stuID + ", teaID=" + teaID + "]";
	}
	
}

StudentMapper.java

package cn.com.mapper;

import cn.com.pojo.Student;
/**
 * 本文作者:谷哥的小弟 
 * 博客地址:http://blog.csdn.net/lfdfhl
 */
public interface StudentMapper {
    //依据学生ID查询其所对应的老师
	public Student queryTeacherByStudentID(Integer id);
}

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.com.mapper.StudentMapper">
  
  <!-- 依据学生ID查询其所对应的老师 -->
  <select id="queryTeacherByStudentID" resultMap="queryTeacherByStudentIDResultMap">
		SELECT 
	       student.*,teacher.*
	    FROM 
	       student,teacher,student_teacher_relation
	    WHERE  
	       student.studentID=#{studentID}
	    AND
	       student.studentID=student_teacher_relation.stuID
	    AND  
	       student_teacher_relation.teaID=teacher.teacherID
  </select>
  
  <resultMap type="cn.com.pojo.Student" id="queryTeacherByStudentIDResultMap">
     <id column="studentID" property="studentID"/>
     <result column="studentName" property="studentName"/>
     <collection property="teacherList" ofType="cn.com.pojo.Teacher">
       <id column="teacherID" property="teacherID"/>
       <result column="teacherName" property="teacherName"/>
     </collection>
  </resultMap>
  
</mapper>

MybatisTest

package cn.com.test;

import java.io.InputStream;
import java.util.Iterator;
import java.util.List;
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 org.junit.Test;
import cn.com.mapper.StudentMapper;
import cn.com.pojo.Student;
import cn.com.pojo.Teacher;
/**
 * 本文作者:谷哥的小弟 
 * 博客地址:http://blog.csdn.net/lfdfhl
 */
public class MybatisTest {

	static SqlSessionFactory sqlSessionFactory = null;

	public static SqlSessionFactory getSqlSessionFactory() {
		try {
			if (sqlSessionFactory == null) {
				InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
				SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
				sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
			}
			return sqlSessionFactory;
		} catch (Exception e) {
			
		} finally {

		}
		return null;
	}
	
	@Test
	public void testQueryTeacherByStudentID() {
		//获取SqlSession
		SqlSession sqlSession=getSqlSessionFactory().openSession();
		//利用SqlSession得到Mapper接口
		StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
		//利用SqlSession执行数据操作
		Student student = studentMapper.queryTeacherByStudentID(1);
		List<Teacher> teacherList = student.getTeacherList();
		Iterator<Teacher> iterator = teacherList.iterator();
		while(iterator.hasNext()) {
			Teacher teacher = iterator.next();
			System.out.println(teacher.getTeacherName());
		}
		//关闭SqlSession
		sqlSession.close();
	}
	
	
}

log4j.properties

# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

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>
  
  <!-- 配置数据源 -->
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
        <property name="driver" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/mybatisDatabase"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
      </dataSource>
    </environment>
  </environments>
  
  <!-- 配置mapper -->
  <mappers>
    <mapper resource="cn/com/pojo/StudentMapper.xml"/>
  </mappers>
  
</configuration>

实验过程及其分析

在此,我们以示例结合试验的方式学习MyBatis多对多查询。

StudentMapper.java

//依据学生ID查询其所对应的老师
public Student queryTeacherByStudentID(Integer id);

StudentMapper.xml

  <!-- 依据学生ID查询其所对应的老师 -->
  <select id="queryTeacherByStudentID" resultMap="queryTeacherByStudentIDResultMap">
		SELECT 
	       student.*,teacher.*
	    FROM 
	       student,teacher,student_teacher_relation
	    WHERE  
	       student.studentID=#{studentID}
	    AND
	       student.studentID=student_teacher_relation.stuID
	    AND  
	       student_teacher_relation.teaID=teacher.teacherID
  </select>
  
  <resultMap type="cn.com.pojo.Student" id="queryTeacherByStudentIDResultMap">
     <id column="studentID" property="studentID"/>
     <result column="studentName" property="studentName"/>
     <collection property="teacherList" ofType="cn.com.pojo.Teacher">
       <id column="teacherID" property="teacherID"/>
       <result column="teacherName" property="teacherName"/>
     </collection>
  </resultMap>
  • 1、在select标签中将查询结果交给resultMap标签处理。所以,select标签中resultMap属性的值与resultMap标签中id属性的值相同。
  • 2、resultMap标签中type属性的值表示将查询结果转换JavaBean的类型。
  • 3、resultMap标签中的id子标签用于将查询结果的主键列转换到JavaBean对象对应的属性。其中,column属性表示查询结果的列名,property属性表示JavaBean对象对应的属性
  • 4、resultMap标签中的result 子标签用于将查询结果的非主键列转换到JavaBean对象对应的属性。其中,column属性表示查询结果的列名,property属性表示JavaBean对象对应的属性
  • 5、在处理完Student的简单属性studentID和studentName之后再使用collection标签处理Student中List<Teacher>类型的teacherList,它也常被称为Student的子集合对象。collection标签的property属性表示子对象的名称,collection标签的ofType属性表示子集合对象中各元素的全路径(类型)。collection标签中的id子标签用于将查询结果的主键列转换到元素对应的属性;其中,column属性表示查询结果的列名,property属性表示元素对应的属性。类似地,collection标签中的result子标签用于将查询结果的非主键列转换到元素对应的属性;其中,column属性表示查询结果的列名,property属性表示元素对应的属性。

MybatisTest.java

@Test
public void testQueryTeacherByStudentID() {
	//获取SqlSession
	SqlSession sqlSession=getSqlSessionFactory().openSession();
	//利用SqlSession得到Mapper接口
	StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
	//利用SqlSession执行数据操作
	Student student = studentMapper.queryTeacherByStudentID(1);
	List<Teacher> teacherList = student.getTeacherList();
	Iterator<Teacher> iterator = teacherList.iterator();
	while(iterator.hasNext()) {
		Teacher teacher = iterator.next();
		System.out.println(teacher.getTeacherName());
	}
	//关闭SqlSession
	sqlSession.close();
}

测试结果
在这里插入图片描述

发布了1019 篇原创文章 · 获赞 1913 · 访问量 233万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 精致技术 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览