Mybatis【关联映射】（二）-阿里云开发者社区

一对多

需求：

一个班级有多个学生,查询java学科有哪些学生信息

设计数据库表

create table grades(
  gid int(5) primary key,
  gname varchar(10)
);
create table students(
  sid int(5) primary key,
  sname varchar(10),
  sgid int(5),
  constraint sgid_fk foreign key(sgid) references grades(gid)
);
insert into grades(gid,gname) values(1,'java');
insert into students(sid,sname,sgid) values(1,'哈哈',1);
insert into students(sid,sname,sgid) values(2,'呵呵',1);
select * from grades;
select * from students;

实体

package zhongfucheng2;
import java.util.ArrayList;
import java.util.List;
/**
 * 学科(单方)
 * @author AdminTC
 */
public class Grade {
    private Integer id;
    private String name;
    private List<Student> studentList = new ArrayList<Student>();//关联属性
    public 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 List<Student> getStudentList() {
        return studentList;
    }
    public void setStudentList(List<Student> studentList) {
        this.studentList = studentList;
    }
}

package zhongfucheng2;
/**
 * 学生(多方)
 * @author AdminTC
 */
public class Student {
    private Integer id;
    private String name;
    private Grade grade;//关联属性
    public Student(){}
    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;
    }
}

映射文件SQL语句

<mapper namespace="studentNamespace">
    <resultMap type="zhongfucheng2.Student" id="studentMap">
        <id property="id" column="sid"/>
        <result property="name" column="sname"/>
    </resultMap>
    <!--查询选修的java学科有多少位学生-->
    <!--由于我们只要查询学生的名字，而我们的实体studentMap可以封装学生的名字，那么我们返回studentMap即可，并不需要再关联到学科表-->
    <select id="findByGrade" parameterType="string" resultMap="studentMap">
        select s.sname,s.sid from zhongfucheng.students s,zhongfucheng.grades g WHERE s.sgid=g.gid and g.gname=#{name};
    </select>
</mapper>
<?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="gradeNamespace">
    <resultMap type="zhongfucheng2.Grade" id="gradeMap">
        <id property="id" column="gid"/>
        <result property="name" column="gname"/>
    </resultMap>
</mapper>

DAO

public List<Student> findByGrade(String  grade) throws Exception {
        //得到连接对象
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        try{
            return sqlSession.selectList("studentNamespace.findByGrade", grade);
          /*  sqlSession.commit();*/
        }catch(Exception e){
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }finally{
            MybatisUtil.closeSqlSession();
        }
    }
    public static void main(String[] args) throws Exception {
        StudentDao studentDao = new StudentDao();
        List<Student> student = studentDao.findByGrade("java");
        for (Student student1 : student) {
            System.out.println(student1.getName());
        }
    }

多对多

需求：

学生和课程

数据库表

create table students(
    sid int(5) primary key,
    sname varchar(10)
);
create table courses(
    cid int(5) primary key,
    cname varchar(10)
);
create table middles(
    msid int(5),
    mcid int(5),
    primary key(msid,mcid)
);
insert into students(sid,sname) values(1,'哈哈');
insert into students(sid,sname) values(2,'呵呵');
insert into courses(cid,cname) values(1,'java');
insert into courses(cid,cname) values(2,'android');
insert into middles(msid,mcid) values(1,1);
insert into middles(msid,mcid) values(1,2);
insert into middles(msid,mcid) values(2,1);
insert into middles(msid,mcid) values(2,2);
select * from students;
select * from courses;
select * from middles;

实体

package cn.itcast.javaee.mybatis.many2many;
import java.util.ArrayList;
import java.util.List;
/**
 * 课程(多方)
 * @author AdminTC
 */
public class Course {
    private Integer id;
    private String name;
    private List<Student> studentList = new ArrayList<Student>();//关联属性
    public Course(){}
    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> getStudentList() {
        return studentList;
    }
    public void setStudentList(List<Student> studentList) {
        this.studentList = studentList;
    }
}

package cn.itcast.javaee.mybatis.many2many;
import java.util.ArrayList;
import java.util.List;
/**
 * 学生(多方)
 * @author AdminTC
 */
public class Student {
    private Integer id;
    private String name;
    private List<Course> courseList = new ArrayList<Course>();//关联属性
    public Student(){}
    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;
    }
}

映射文件

<?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="courseNamespace">
    <resultMap type="cn.itcast.javaee.mybatis.many2many.Course" id="courseMap">
        <id property="id" column="cid"/>
        <result property="name" column="cname"/>
    </resultMap>    
    <!-- 查询哈哈选学了哪些课程 -->
    <select id="findAllByName" parameterType="string" resultMap="courseMap">
        select c.cid,c.cname
        from students s inner join middles m
        on s.sid = m.msid
        inner join courses c
        on m.mcid = c.cid
        and s.sname = #{name}
    </select>
</mapper>

<?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="studentNamespace">
    <resultMap type="cn.itcast.javaee.mybatis.many2many.Student" id="studentMap">
        <id property="id" column="sid"/>
        <result property="name" column="sname"/>
    </resultMap>    
    <select id="findAllByCourseName" parameterType="string" resultMap="studentMap">
        select s.sname
        from students s inner join middles m
        on s.sid = m.msid 
        inner join courses c
        on m.mcid = c.cid
        and c.cname = #{name}
    </select>
</mapper>

DAO

package cn.itcast.javaee.mybatis.many2many;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import cn.itcast.javaee.mybatis.util.MybatisUtil;
/**
 * 持久层
 * @author AdminTC
 */
public class StudentCourseDao {
    /**
     * 查询哈哈选学了哪些课程
     * @param name 表示学生的姓名
     */
    public List<Course> findAllByName(String name) throws Exception{
        SqlSession sqlSession = null;
        try{
            sqlSession = MybatisUtil.getSqlSession();
            return sqlSession.selectList("courseNamespace.findAllByName",name);
        }catch(Exception e){
            e.printStackTrace();
            throw e;
        }finally{
            MybatisUtil.closeSqlSession();
        }
    }
    /**
     * 查询java课程有哪些学生选修
     * @param name 表示学生的课程
     */
    public List<Student> findAllByCourseName(String name) throws Exception{
        SqlSession sqlSession = null;
        try{
            sqlSession = MybatisUtil.getSqlSession();
            return sqlSession.selectList("studentNamespace.findAllByCourseName",name);
        }catch(Exception e){
            e.printStackTrace();
            throw e;
        }finally{
            MybatisUtil.closeSqlSession();
        }
    }
    public static void main(String[] args) throws Exception{
        StudentCourseDao dao = new StudentCourseDao();
        List<Course> courseList = dao.findAllByName("哈哈");
        System.out.print("哈哈选学了" + courseList.size()+"个课程,分别是：");
        for(Course c : courseList){
            System.out.print(c.getName()+" ");
        }
        System.out.println("\n-----------------------------------------------------");
        List<Student> studentList = dao.findAllByCourseName("android");
        System.out.println("选修了android课程的学生有"+studentList.size()+"个，分别是：");
        for(Student s : studentList){
            System.out.print(s.getName()+" ");
        }
    }
}

总结

对于Mybatis的多表连接就非常简单了，由于SQL语句全是由我们自己写，如果我们返回的数据类型在当前的实体中是不够封装的话，那么我们只要再关联对应的映射属性就行了！

Mybatis【关联映射】（二）

一对多

设计数据库表

实体

映射文件SQL语句

DAO

多对多

数据库表

实体

映射文件

DAO

总结

Java技术进阶

热门文章

最新文章

相关课程

相关电子书