1、一对多关系(OneToMany)
在实际项目中,一对多是非常常见的关系。比如:一个班级可以有多个学生,一个学生只能属于一个班级,班级和学生是一对多关系,而学生和班级是多对一关系。
数据库中一对多关系通常使用主外键关联,外键列应该在多方,即由多方维护关系(一对多中多的一方维护)。
(1)创建数据库
班级表t_clazz:
CREATE TABLE t_clazz(
cid INT PRIMARY KEY AUTO_INCREMENT COMMENT ‘ID’,
code VARCHAR(18) COMMENT ‘班级号’,
name VARCHAR(18) COMMENT ‘班级名’
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
说明:
ENGINE=InnoDB:MySQL 的数据库引擎的设置
AUTO_INCREMENT=1:自动递增列的 初始数值 = 1
DEFAULT CHARSET=utf8:默认字符集 = utf8
学生表t_student:
CREATE TABLE t_student(
sid INT PRIMARY KEY AUTO_INCREMENT COMMENT ‘学生ID’,
name VARCHAR(18) COMMENT ‘姓名’,
sex VARCHAR(18) COMMENT ‘性别’,
age INT COMMENT ‘年龄’,
clazz_cid INT, #外键:班级ID
CONSTRAINT fk_stu FOREIGN KEY(clazz_cid) REFERENCES t_clazz(cid)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
(2)持久化类
创建一个Clazz类和一个Student类,分别映射t_clazz表和t_student表。
// 班级类
public class Clazz implements Serializable {
private Integer cid;
private String code;
private String name;
//班级和学生是一对多的关系,即一个班级可以有多个学生
private List studentList;
//省略构造方法和set/get方法……
}
// 学生类
public class Student implements Serializable {
private Integer sid;
private String name;
private String sex;
private Integer age;
//学生和班级是多对一的关系,即一个学生只属于一个班级
private Clazz clazz;
//省略构造方法和set/get方法……
}
(3)接口
在com.zking.dao中创建实体类的接口
// 班级类接口
public interface ClazzService{
// SQL1:添加Clazz对象
public int addClazz(Clazz clazz);
// SQL2:删除Clazz对象
public int deleteClazzById(Clazz clazz);
// SQL3:修改Clazz对象
public int updateClazz(Clazz clazz);
// SQL4:根据ID查询Clazz对象
public Clazz selectClazzById(Integer cid);
// SQL5:查询所有Clazz对象
public List<Clazz> selectAllClazz();
// SQL6:模糊查询Clazz对象
public List<Clazz> selectClazzWhere(Clazz clazz);
// SQL7:多表联查:查询班级信息及学员信息
public Clazz selectClazzAndStudent(Integer cid);
// SQL8:包含查询
public List<Clazz> selectClazzIn(Clazz clazz);
}
// 学生类接口
public interface StudentService{
// SQL1:添加Student对象
public int addStudent(Student student);
// SQL2:根据SID删除Student对象
public int deleteStudentById(Integer sid);
// SQL3:修改Student对象
public int updateStudent(Clazz clazz);
// SQL4:根据SID查询Student对象
public Student selectStudentById(Integer cid);
// SQL5:查询所有Student对象
public List<Student> selectAllStudent();
// SQL6:模糊查询Student对象
public List<Student> selectStudentWhere(Student student);
// SQL7:多表联查:查询班级信息及学员信息
public Student selectClazzAndStudent(Integer sid);
// SQL8:包含查询
public List<Student> selectStudentIn(Student student);
}
(4)对象/关系映射文件
ClazzMapper.xml代码:
<!--SQL1 :根据cid查询Clazz,返回Clazz对象-->
<select id=”selectClazzById” parameterType=”Integer” resultMap=”clazzMapper”>
SELECT * FROM t_clazz WHERE cid=#{cid}
</select>
<!--映射Clazz对象的resultMap(结果映射集)-->
<resultMap id=”clazzMapper” type=”com.zking.pojo.Clazz”>
<id property=”cid” column=”CID” />
<result property=”code” column=”CODE” />
<result property=”name” column=”NAME” />
<!--一对多关联映射:
Collection表示关联的是集合
Property=”studentList”对应的是Clazz类中List<Student> studentList属性名
ofType="com.zking.pojo.Student"表示映射的类为List<Student>中的Student
fetchType=”lazy”表示懒加载,即使用时才实例化
-->
<collection property="studentList" ofType="com.zking.pojo.Student" fetchType=”lazy”>
<id property=”sid” column=”SID” />
<!--<result>中出现的属性、列名不一定全是类中的属性,如果只查出要sid和name,那么后面的sex,age都可以不用写这里的name,sex,age最好不要和上面<resultMap>中的name,sex,age名字重复property对应com.zking.pojo.Student实体类中的属性,column对应表中的字段名-->
<result property="name" column="name" />
<result property="sex" column="sex" />
<result property="age" column="age" />
</collection>
<!--写法二:
将cid做为参数传递给com.zking.dao.StudentService.selectClazzStudent
-->
<collection property=”studentList” ofType=”com.zking.pojo.Student”
column=”cid” Select=”com.zking.dao.StudentService.selectClazzStudent”>
</collection>
</resultMap>
StudentMapper.xml代码:
<!--SQL1:根据id查询Student,返回Student对象-->
<select id=”selectStudentById” parameterType=”Integer” resultMap=”studentMapper”>
SELECT * FROM t_student WHERE sid=#{sid}
</select>
<!--映射Student对象的resultMap(结果映射集)-->
<resultMap id=”studentMapper” type=”com.zking.pojo.Student”>
<id property=”sid” column=”sid” />
<result property=”name” column=”name” />
<result property=”sex” column=”sex” />
<result property=”age” column=”age” />
<!--多对一关联映射:association
assoclation表示映射一个对象
-->
<association property="clazz" javaType="com.zking.pojo.Clazz">
<id property=”id” column=”id” />
<result property="code" column="code" />
<result property="name" column="name" />
<!--方式二:
将CLAZZ_CID做为参数传递给com.zking.dao.ClassService.selectClazzById
-->
<association property="clazz" javaType="com.zking.pojo.Clazz"
column=”CLAZZ_CID” select=”com.zking.dao.ClazzService.selectClazzById”>
</association>
<!--SQL2:查询所有Student对象-->
<select id=”selectAllStudent” resultType=”com.zking.pojo.Student”>
SELECT * FROM t_student
</select>
<!--SQL3:根据班级号查询所有Student对象-->
<select id=”selectClazzStudent” paramerType=”Integer” resultType=”com.zking.pojo.Student”>
SELECT * FROM t_student WHERE Clazz_cid = #{Clazz.cid}
</select>
(5)测试
public class TestDemo{
private SqlSession sqlSession;
@Before
public void before(){
try {
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
sqlSession = sqlSessionFactory.openSession();
}catch(Exception e){
e.printStackTrace();
}
}
@After
public void after(){
sqlSession.commit();
sqlSession.close();
}
@Test
public void testClazz(){
ClazzDao clazzDao = sqlSession.getMapper(ClazzDao.class);
Clazz clazz = clazzDao.getClazzById(2);
System.out.println(clazz);
System.out.println(clazz.getId());
System.out.println(clazz.getCode());
System.out.println(clazz.getStudentList());
}
@Test
public void testStudent(){
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
Student student = studentDao.getStudentById(2);
System.out.println(student);
System.out.println(student.getName());
System.out.println(student.getSex());
System.out.println(student.getClazz().getCode());
}
}
2、多对多(ManyToMany)
在实际项目中,多对多是非常常见的关系。比如:课程管理中,一个学生对应多个课程,一个课程对应多个学生,他们之间多个一对多组成,就形成了多对多。
对于数据库中多对多关系建议使用一个中间表来维护关系。
(1)创建数据库
学生表 t_student:
create table t_student(
student_id INT PRIMARY KEY AUTO_INCREMENT,
student_name VARCHAR(10),
student_sex VARCHAR(10), #性别
student_major VARCHAR(10), #专业
student_grade VARCHAR(10) #年级
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
课程表 t_course:
create table t_course(
course_id INT PRIMARY KEY AUTO_INCREMENT, #课程ID
course_code VARCHAR(30), #课程编号
course_name VARCHAR(30) #课程名
)ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
学生vs课程关系表 t_student_course:
create table t_student_course (
student_id INT, #学生ID,FK
course_id INT, #课程ID,FK
CONSTRAINT fk_student_id FOREIGN KEY(student_id) REFERENCES t_student(student_id),
CONSTRAINT fk_course_id FOREIGN KEY(course_id) REFERENCES t_course(course_id)
)ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
(2)持久化类
学生实体Student:
public class Student {
private Integer studentID;
private String studentName; // 姓名
private String studentSex; // 性别
private String studentMajor; // 专业
private String studentGrade; // 年级
//一对多:即一个学生对象对应多个课程对象
private List courses; // 所选的课程
//省略getter和setter方法
//构造方法及toString()
}
课程实体Course:
public class Course {
private Integer courseID;
private String courseCode; //课程编号
private String courseName; //课程名称
//一对多:即一个课程对象对应多个学生对象
private List students; //选课学生
//省略getter和setter方法
//构造方法及toString()
}
注意:关系表不需要创建实体类。
(3)接口
注意:在学习过程中,我们会为Student类建立StudentService接口,在该接口中指定Student类的CURD操作,会为Course类建立CourseService接口,在该接口中指定Course类的CURD操作。
为操作便捷,这里我统一写个接口:Many2ManyService,该接口负责Student类和Course类的CURD操作。
在com.zking.dao中创建实体类的接口:
public interface Many2ManyService {
//SQL1:插入student对象
public int addStudent(Student student);
//SQL2:插入course对象
public int addCourse(Course course);
//SQL3:根据student_id查询学生
public Student selectStudentById(Integer student_id);
//SQL4:通过course_id查询课程
public Course selectCourseById(Integer course_id);
//SQL5:学生x选课y
public int studentSelectCourse(Student student, Course course);
//SQL6:查询比指定student_id值小的学生信息
public List<Student> getStudentByIdOnCondition(Integer student_id);
//SQL7:查询student级联查询出所选的course并且组装成完整的对象
public Student getStudentByIdWithCourses(Integer course_id);
}
(4)对象/关系映射文件
为Many2ManyService接口创建Mapper映射文件,实现接口中的方法:
<!-- SQL1:插入student对象 -->
<insert id="addStudent" parameterType="Student">
INSERT INTO
t_student(student_name, student_sex, student_major, student_grade)
VALUES(
#{studentName},#{studentSex},#{studentMajor},#{studentGrade}
)
</insert>
<!-- SQL2:插入course对象 -->
<insert id="addCourse" parameterType="Course">
INSERT INTO
t_course(course_code, course_name)
VALUES(
#{courseCode}, #{courseName}
)
</insert>
<!--SQL: 添加关系表-->
<insert id="studentSelectCourse">
<!-- param1代表方法中第一个参数 以此类推 -->
INSERT INTO
t_student_course(student_id, course_id)
VALUES(
//第一个参数的student_id,第二个参数的course_id
#{param1.studentID}, #{param2.courseID}
)
</insert>
<!--SQL3:根据student_id查询学生-->
<select id="selectStudentById" parameterType="Integer" resultType="Student">
SELECT
student_id, student_name, student_sex, student_major, student_grade
from
t_student
Where
student_id = #{studentID}
</select>
<!--SQL4:通过course_id查询课程-->
<select id="selectCourseById" parameterType="Integer" resultType="Course">
SELECT
Course_id AS courseID, course_code AS courseCode, course_name AS courseName
FROM
t_course
Where
Course_Id = #{courseID}
</select>
<!--SQL5:学生x选课y-->
<insert id="studentSelectCourse">
<!-- param1代表方法中第一个参数 以此类推 -->
INSERT INTO
t_student_course(student_id, course_id)
VALUES(
//第一个参数的student_id,第二个参数的course_id
#{param1.studentID}, #{param2.courseID}
)
</insert>
<!-- 如果有特殊符号的话 需要用 <![CDATA[ 特殊符号 ]]> 例如 < & 等等 -->
<select id="getStudentByIdOnCondition" parameterType="Integer" resultType="Student">
<![CDATA[
SELECT
student_id, student_name, student_sex, student_major, student_grade
FROM
t_student
WHERE
student_id < #{studentID}
]]>
</select>
<!-- 这里使用了嵌套结果ResultMap的方式进行级联查询,当然也可以使用嵌套查询select -->
<!-- 映射一个基本的Student查询结果 -->
<resultMap id="StudentResult" type="Student">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="gender" column="gender"/>
<result property="major" column="major"/>
<result property="grade" column="grade"/>
</resultMap>
<!-- 继承上面那个基本的映射,再扩展出级联查询 -->
<resultMap id="StudentResultWithCourses" type="Student" extends="StudentResult">
<collection property="courses" resultMap="CourseResult"></collection>
</resultMap>
<!-- 这里特别是column="cid"是和select语句中的 c.id as cid对应的 一定一定一定要对应起来 -->
<resultMap id="CourseResult" type="Course">
<id property="id" column="cid"/>
<result property="courseCode" column="course_code"/>
<result property="courseName" column="course_name"/>
</resultMap>
<!--
注意:查询语句的中的c.id as cid这个地方,避免名字相同出现查询结果不正确的情况
同时在id="CourseResult"的resultMap中也有与这里对应的设置要特别特别注意
-->
<select id="getStudentByIdWithCourses" parameterType="Integer" resultMap="StudentResultWithCourses">
SELECT
s.id, s.name, s.gender, s.major, s.grade, c.id AS cid, c.course_code, c.course_name, sc.id, sc.student_id, sc.course_id
FROM
student s, course c, student_course sc
WHERE
s.id=#{id} AND s.id=sc.student_id AND sc.course_id=c.id
</select>
(5)测试
public class Many2ManyServiceTest {
private SqlSession sqlSession;
Private Many2ManyService mapper;
@Before
public void before(){
try {
InputStream inputStream = Resources.getResourceAsStream(“mybatis-config.xml”);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
sqlSession = sqlSessionFactory.openSession();
// 使用sqlsession获得映射接口的实现类对象,接口的引用指向实现类的对象
mapper = sqlSession.getMapper(Many2ManyService.class);
}catch(Exception e){
e.printStackTrace();
}
}
@After
public void after(){
sqlSession.commit();
sqlSession.close();
}
@Test
public void test_insertStudent() {
Student student = new Student(1,"Suwu150","男","计算机","21");
mapper.addStudent(student);
session.commit();
System.out.println("执行完毕");
}
@Test
public void test_insertCourse(){
SqlSession session=null;
session = MyBatisSqlSessionFactory.openSession();//使用封装之后的类
// 使用sqlsession获得映射接口的实现类对象,接口的引用指向实现类的对象
Many2ManyMapper mapper = session.getMapper(Many2ManyMapper.class);
mapper.insertCourse(new Course(1,"001","corejava"));
mapper.insertCourse(new Course(2,"002","oracle"));
session.commit();
System.out.println("执行完毕");
}
@Test
public void test_selectStudentById(){
SqlSession session=null;
session = MyBatisSqlSessionFactory.openSession();//使用封装之后的类
// 使用sqlsession获得映射接口的实现类对象,接口的引用指向实现类的对象
Many2ManyMapper mapper = session.getMapper(Many2ManyMapper.class);
Student student = mapper.selectStudentById(2);
System.out.println(student);
System.out.println("执行完毕");
}
@Test
public void test_selectCourseById(){
SqlSession session=null;
session = MyBatisSqlSessionFactory.openSession();//使用封装之后的类
// 使用sqlsession获得映射接口的实现类对象,接口的引用指向实现类的对象
Many2ManyMapper mapper = session.getMapper(Many2ManyMapper.class);
Course course = mapper.selectCourseById(2);
System.out.println(course);
System.out.println("执行完毕");
}
@Test
public void test_studentSelectCourse(){
SqlSession session=null;
session = MyBatisSqlSessionFactory.openSession();//使用封装之后的类
// 使用sqlsession获得映射接口的实现类对象,接口的引用指向实现类的对象
Many2ManyMapper mapper = session.getMapper(Many2ManyMapper.class);
Student student=new Student();
Course course=new Course();
student.setId(2);
course.setId(1);
mapper.studentSelectCourse(5, student, course);
session.commit();
}
@Test
public void test_getStudentByIdOnCondition(){
SqlSession session=null;
session = MyBatisSqlSessionFactory.openSession();//使用封装之后的类
// 使用sqlsession获得映射接口的实现类对象,接口的引用指向实现类的对象
Many2ManyMapper mapper = session.getMapper(Many2ManyMapper.class);
List<Student> list = mapper.getStudentByIdOnCondition(3);
System.out.println(list);
}
@Test
public void test_getStudentByIdWithCourses(){
SqlSession session=null;
session = MyBatisSqlSessionFactory.openSession();//使用封装之后的类
// 使用sqlsession获得映射接口的实现类对象,接口的引用指向实现类的对象
Many2ManyMapper mapper = session.getMapper(Many2ManyMapper.class);
Student student = mapper.getStudentByIdWithCourses(2);
System.out.println(student);
}
}