一对多
需求：
- 一个班级有多个学生,查询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语句全是由我们自己写，如果我们返回的数据类型在当前的实体中是不够封装的话，那么我们只要再关联对应的映射属性就行了！
