【Mybatis】一对一，一对多，多对多映射

Mybatis和Hibernate的映射关系差不多，都有一对一，一对多，多对多，但其实现方式却不同,mybatis主要还是以sql语句为主，而hibernate以映射文件onetoone进行配置相应的属性。

一对一

我们以学生和身份证为例子

创建实体类Card

package cn.qblank.one2one;public class Card {private Integer id;private String num;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getNum() {return num;}public void setNum(String num) {this.num = num;}@Overridepublic String toString() {return "Card [id=" + id + ", num=" + num + "]";}}

Student类

package cn.qblank.one2one;public class Student {private Integer id;private String name;private Card card;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Card getCard() {return card;}public void setCard(Card card) {this.card = card;}@Overridepublic String toString() {return "Student [id=" + id + ", name=" + name + ", card=" + card + "]";}}

在数据库中创建对应的表

-- 创建学生表CREATE TABLE t_student(sid INT,sname VARCHAR(20),scid INT)-- 创建身份证表CREATE TABLE t_card(cid INT,cnum VARCHAR(20))

创建对应的映射文件

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.qblank.one2one.Student"><resultMap type="student" id="studentMap"><result property="id" column="sid"/><result property="name" column="sname"/><!-- 引入CardMapper.xml文件中的映射信息  property表示Student类的关联属性 resultMap表示引入CardMapper.xml文件的映射类型--><association property="card" resultMap="cn.qblank.one2one.Card.cardMap"></association></resultMap><!-- 通过id查询 --><select id="findById" parameterType="int" resultMap="studentMap">SELECT s.sid,s.sname,c.cnum FROM t_student s INNER JOIN t_card c ON s.scid = c.cidAND s.sid = #{id};</select><!-- 通过姓名查询 --><select id="findByName" parameterType="string" resultMap="studentMap">SELECT s.sid,s.sname,c.cnumFROM t_student s INNER JOIN t_card cON s.scid = c.cidAND s.sname = #{name};</select></mapper>

CardMapper.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.qblank.one2one.Card"><resultMap type="cn.qblank.one2one.Card" id="cardMap"><result property="id" column="cid"/><result property="num" column="cnum"/></resultMap></mapper>

将其配置到主配置文件mybatis.xml文件中

<mapper resource="cn/qblank/one2one/StudentMapper.xml"/><mapper resource="cn/qblank/one2one/CardMapper.xml"/>

然后在StudentCardDao类中写封装对应的方法

/** * 查询1号学生的信息 * @param id */public Student findById(int id) throws Exception {SqlSession sqlSession = null;try {sqlSession = MybatisUtil.getSqlSession();return sqlSession.selectOne(Student.class.getName() + ".findById",id);} catch (Exception e) {e.printStackTrace();throw e;} finally {MybatisUtil.closeSqlSession();}}/** * 通过姓名查学生信息 * @param name * @return * @throws Exception */public Student findByName(String name) throws Exception{SqlSession sqlSession = null;try {sqlSession = MybatisUtil.getSqlSession();return sqlSession.selectOne(Student.class.getName() + ".findByName",name);} catch (Exception e) {e.printStackTrace();throw e;} finally {MybatisUtil.closeSqlSession();}}

一对多

以班级和学生来做例子

创建实体类Student和Grade

Student类

package cn.qblank.one2many;/** * 学生 * @author Administrator */public class Student {private Integer id;private String name;private Grade grade;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Grade getGrade() {return grade;}public void setGrade(Grade grade) {this.grade = grade;}}

Grade类

package cn.qblank.one2many;import java.util.ArrayList;import java.util.List;/** * 班级 * @author Administrator */public class Grade {private Integer id;private String name;private List<Student> stuList = new ArrayList<>();public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public List<Student> getStuList() {return stuList;}public void setStuList(List<Student> stuList) {this.stuList = stuList;}}

创建对应表

-- 班级CREATE TABLE t_grades(gid INT(5) PRIMARY KEY,gname VARCHAR(10));-- 学生CREATE TABLE t_stu(sid INT(5) PRIMARY KEY,sname VARCHAR(10),sgid INT(5),CONSTRAINT sgid_fk FOREIGN KEY(sgid) REFERENCES t_grades(gid));

创建对应映射文件StudentMapper.xml和GrapeMapper.xml

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.qblank.one2many.Student"><resultMap type="cn.qblank.one2many.Student" id="studentMap"><result property="id" column="sid"/><result property="name" column="sname"/></resultMap><!-- 查询156班有哪些学生信息 --><select id="findAllByName" parameterType="string" resultMap="studentMap">SELECT s.sid,s.snameFROM t_stu s INNER JOIN t_grades gON s.sgid = g.gidAND g.gname = #{name};</select></mapper>

GradeMapper.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.qblank.one2many.Grade"><resultMap type="cn.qblank.one2many.Grade" id="gradeMap"><result property="id" column="gid"/><result property="name" column="gname"/></resultMap><!-- 查询evan_qb是哪个班的 --><select id="findByName" parameterType="string" resultMap="gradeMap">SELECT g.gnameFROM t_stu s INNER JOIN t_grades g ON s.sgid = g.gidAND s.sname = #{name};</select></mapper>

在主文件mybatis.xml中加载两文件

<mapper resource="cn/qblank/one2many/GradeMapper.xml"/><mapper resource="cn/qblank/one2many/StudentMapper.xml"/>

然后封装对应的方法到StuGraDao类中

/** * 查询156班有哪些人 * @param name 班级名 * @return * @throws Exception */public List<Student> findAllByName(String name) throws Exception{SqlSession sqlSession = null;try {sqlSession = MybatisUtil.getSqlSession();return sqlSession.selectList(Student.class.getName() + ".findAllByName",name);} catch (Exception e) {e.printStackTrace();throw e;} finally {MybatisUtil.closeSqlSession();}}/** * 查询evan_qb是哪个班的 * @param name 学生姓名 * @return * @throws Exception */public Grade findByName(String name) throws Exception{SqlSession sqlSession = null;try {sqlSession = MybatisUtil.getSqlSession();return sqlSession.selectOne(Grade.class.getName() + ".findByName",name);} catch (Exception e) {e.printStackTrace();throw e;} finally {MybatisUtil.closeSqlSession();}}

多对多

以学生和课程为例

创建实体类Student和Course

Student类

package cn.qblank.many2many;import java.util.ArrayList;import java.util.List;public class Student {private Integer id;private String name;private List<Course> courseList = new ArrayList<>();public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public List<Course> getCourseList() {return courseList;}public void setCourseList(List<Course> courseList) {this.courseList = courseList;}}

Course类

package cn.qblank.many2many;import java.util.ArrayList;import java.util.List;public class Course {private Integer id;private String name;private List<Student> stuList = new ArrayList<>();public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public List<Student> getStuList() {return stuList;}public void setStuList(List<Student> stuList) {this.stuList = stuList;}}

创建对应的数据库表

-- 学生表CREATE TABLE t_stus(sid INT PRIMARY KEY,sname VARCHAR(20));-- 课程表CREATE TABLE t_courses(cid INT PRIMARY KEY,cname VARCHAR(20));-- 中间表CREATE TABLE middle(msid INT,mcid INT);

然后创建对应的映射文件StudentMapper.xml和CourseMapper.xml

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.qblank.many2many.Student"><resultMap type="cn.qblank.many2many.Student" id="studentMap"><result property="id" column="sid"/><result property="name" column="sname"/></resultMap><!-- 查询某课程有哪些学生信息 --><select id="findAllByCourseName" parameterType="string" resultMap="studentMap">SELECT s.snameFROM t_stus s INNER JOIN middle m ONs.sid = m.msid INNER JOIN t_courses cON m.mcid = c.cidAND c.cname= #{name};</select></mapper>

CourseMapper.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.qblank.many2many.Course"><resultMap type="cn.qblank.many2many.Course" id="courseMap"><result property="id" column="cid"/><result property="name" column="cname"/></resultMap><!-- 查询某学生有选了哪些课 --><select id="findAllByName" parameterType="string" resultMap="courseMap">SELECT c.cnameFROM t_stus s INNER JOIN middle m ONs.sid = m.msid INNER JOIN t_courses cON m.mcid = c.cidAND s.sname = #{name};</select></mapper>

在主文件mybatis.xml中加载文件

<mapper resource="cn/qblank/many2many/StudentMapper.xml"/><mapper resource="cn/qblank/many2many/CourseMapper.xml"/>

在CourseStuDao类中封装相应的方法

/** * 查询某人选学了哪些课程 * @param name 表示学生的姓名 */public List<Course> findAllByName(String name) throws Exception{SqlSession sqlSession = null;try{sqlSession = MybatisUtil.getSqlSession();return sqlSession.selectList(Course.class.getName() + ".findAllByName",name);}catch(Exception e){e.printStackTrace();throw e;}finally{MybatisUtil.closeSqlSession();}}/** * 查询某课程有哪些学生选修 * @param name 表示学生的课程 */public List<Student> findAllByCourseName(String name) throws Exception{SqlSession sqlSession = null;try{sqlSession = MybatisUtil.getSqlSession();return sqlSession.selectList(Student.class.getName() + ".findAllByCourseName",name);}catch(Exception e){e.printStackTrace();throw e;}finally{MybatisUtil.closeSqlSession();}}

就完成了mybatis的一对一，一对多，多对多的配置，通过配置我们可以发现，主要还是对sql语句的应用，所以学习mybatis还需对数据库的语句有一定的熟悉