Mybatis【多表连接】
我们在学习Hibernate的时候,如果表涉及到两张的话,那么我们是在映射文件中使用<set>
..<many-to-one>
等标签将其的映射属性关联起来的…那么在我们Mybatis中又怎么做呢???
先来回顾一下我们SQL99的语法:
一)内连接(等值连接):查询客户姓名,订单编号,订单价格 --------------------------------------------------- select c.name,o.isbn,o.price from customers c inner join orders o where c.id = o.customers_id; --------------------------------------------------- select c.name,o.isbn,o.price from customers c join orders o where c.id = o.customers_id; --------------------------------------------------- select c.name,o.isbn,o.price from customers c,orders o where c.id = o.customers_id; --------------------------------------------------- select c.name,o.isbn,o.price from customers c join orders o on c.id = o.customers_id; --------------------------------------------------- 注意:内连接(等值连接)只能查询出多张表中,连接字段相同的记录二)外连接:按客户分组,查询每个客户的姓名和订单数 --------------------------------------------------- 左外连接: select c.name,count(o.isbn) from customers c left outer join orders o on c.id = o.customers_id group by c.name; --------------------------------------------------- 右外连接: select c.name,count(o.isbn) from orders o right outer join customers c on c.id = o.customers_id group by c.name; --------------------------------------------------- 注意:外连接既能查询出多张表中,连接字段相同的记录;又能根据一方,将另一方不符合相同记录强行查询出来三)自连接:求出AA的老板是EE --------------------------------------------------- 内自连接: select users.ename,boss.ename from emps users inner join emps boss on users.mgr = boss.empno; --------------------------------------------------- 外自连接: select users.ename,boss.ename from emps users left outer join emps boss on users.mgr = boss.empno; --------------------------------------------------- 注意:自连接是将一张表,通过别名的方式,看作多张表后,再进行连接。 这时的连接即可以采用内连接,又可以采用外连接---------------------------------------------------
select c.name,o.isbn,o.price
from customers c inner join orders o
where c.id = o.customers_id;
---------------------------------------------------
select c.name,o.isbn,o.price
from customers c join orders o
where c.id = o.customers_id;
---------------------------------------------------
select c.name,o.isbn,o.price
from customers c,orders o
where c.id = o.customers_id;
---------------------------------------------------
select c.name,o.isbn,o.price
from customers c join orders o
on c.id = o.customers_id;
---------------------------------------------------
注意:内连接(等值连接)只能查询出多张表中,连接字段相同的记录
二)外连接:按客户分组,查询每个客户的姓名和订单数
---------------------------------------------------
左外连接:
select c.name,count(o.isbn)
from customers c left outer join orders o
on c.id = o.customers_id
group by c.name;
---------------------------------------------------
右外连接:
select c.name,count(o.isbn)
from orders o right outer join customers c
on c.id = o.customers_id
group by c.name;
---------------------------------------------------
注意:外连接既能查询出多张表中,连接字段相同的记录;又能根据一方,将另一方不符合相同记录强行查询出来
三)自连接:求出AA的老板是EE
---------------------------------------------------
内自连接:
select users.ename,boss.ename
from emps users inner join emps boss
on users.mgr = boss.empno;
---------------------------------------------------
外自连接:
select users.ename,boss.ename
from emps users left outer join emps boss
on users.mgr = boss.empno;
---------------------------------------------------
注意:自连接是将一张表,通过别名的方式,看作多张表后,再进行连接。
这时的连接即可以采用内连接,又可以采用外连接
由于我们Mybatis中并没有像Hibernate这样全自动化的,因此我们是没有<set>
..<many-to-one>
等标签的,我们还是使用手写SQL语句来使我们的关联属性连接起来…
一对一
需求:
![这里写图片描述 640?wx_fmt=jpeg](https://i-blog.csdnimg.cn/blog_migrate/33d5ef86f25fffeaa94d4692af6052b3.jpeg)
设计表:
--mysqlcreate table cards( cid int(5) primary key, cnum varchar(10));create table students( sid int(5) primary key, sname varchar(10), scid int(5), constraint scid_fk foreign key(scid) references cards(cid));insert into cards(cid,cnum) values(1,'111');insert into students(sid,sname,scid) values(1,'哈哈',1);select * from cards;select * from students;
create table cards(
cid int(5) primary key,
cnum varchar(10)
);
create table students(
sid int(5) primary key,
sname varchar(10),
scid int(5),
constraint scid_fk foreign key(scid) references cards(cid)
);
insert into cards(cid,cnum) values(1,'111');
insert into students(sid,sname,scid) values(1,'哈哈',1);
select * from cards;
select * from students;
实体
/** * 身份证(单方) * @author AdminTC */public class Card { private Integer id; private String num; public Card(){} 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; }}
public class Card {
private Integer id;
private String num;
public Card(){}
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;
}
}
/** * 学生(单方) * @author AdminTC */public class Student { private Integer id; private String name; private Card card;//关联属性 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 Card getCard() { return card; } public void setCard(Card card) { this.card = card; }}
public class Student {
private Integer id;
private String name;
private Card card;//关联属性
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 Card getCard() {
return card;
}
public void setCard(Card card) {
this.card = card;
}
}
映射文件
由于我们有两个实体,因此我们会有两个映射文件
Student映射文件
<?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="zhongfucheng2.Student" id="studentMap"> <id property="id" column="sid"/> <result property="name" column="sname"/> </resultMap></mapper>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="studentNamespace">
<resultMap type="zhongfucheng2.Student" id="studentMap">
<id property="id" column="sid"/>
<result property="name" column="sname"/>
</resultMap>
</mapper>
Card映射文件
<?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="cardNamespace"> <resultMap type="zhongfucheng2.Card" id="cardMap"> <id property="id" column="cid"/> <result property="num" column="cnum"/> </resultMap> </mapper>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cardNamespace">
<resultMap type="zhongfucheng2.Card" id="cardMap">
<id property="id" column="cid"/>
<result property="num" column="cnum"/>
</resultMap>
</mapper>
DAO层
现在我想根据学生的编号查询学生的信息和身份证信息!
由于该查询着重是查询学生的信息,于是我们在学生的映射文件中写SQL语句
按照需求,我们写出来的SQL语句是这样子的。
select * from zhongfucheng.students s,zhongfucheng.cards c where c.cid = s.scid and sid=1;from zhongfucheng.students s,zhongfucheng.cards c where c.cid = s.scid and sid=1;
我来看一下查询结果:
![这里写图片描述 640?wx_fmt=png](https://i-blog.csdnimg.cn/blog_migrate/b0e1749182561c2e6d966e6f5fe58683.png)
我们的实体与映射表中,Student实体是没有关联其他的字段的,仅仅是写出了该实体的自带的属性。
<resultMap type="zhongfucheng2.Student" id="studentMap"> <id property="id" column="sid"/> <result property="name" column="sname"/> </resultMap>
<id property="id" column="sid"/>
<result property="name" column="sname"/>
</resultMap>
明显地,我们Student是不能封装返回的结果,因此我们需要将关联属性进行关联起来!
<resultMap type="zhongfucheng2.Student" id="studentMap"> <id property="id" column="sid"/> <result property="name" column="sname"/> <!-- property写的是在Student实体中写关联字段的属性变量名称 resultMap写的是映射文件中的命名空间.id --> <association property="card" resultMap="cardNamespace.cardMap"/> </resultMap>
<id property="id" column="sid"/>
<result property="name" column="sname"/>
<!--
property写的是在Student实体中写关联字段的属性变量名称
resultMap写的是映射文件中的命名空间.id
-->
<association property="card" resultMap="cardNamespace.cardMap"/>
</resultMap>
我们关联了以后,Student实体就能够封装返回的结果了
<resultMap type="zhongfucheng2.Student" id="studentMap"> <id property="id" column="sid"/> <result property="name" column="sname"/> <!-- property写的是在Student实体中写关联字段的属性变量名称 resultMap写的是映射文件中的命名空间.id --> <association property="card" resultMap="cardNamespace.cardMap"/> </resultMap> <select id="findById" parameterType="int" resultMap="studentMap"> select * from zhongfucheng.students s,zhongfucheng.cards c where c.cid = s.scid and sid=#{id}; </select>
<id property="id" column="sid"/>
<result property="name" column="sname"/>
<!--
property写的是在Student实体中写关联字段的属性变量名称
resultMap写的是映射文件中的命名空间.id
-->
<association property="card" resultMap="cardNamespace.cardMap"/>
</resultMap>
<select id="findById" parameterType="int" resultMap="studentMap">
select * from zhongfucheng.students s,zhongfucheng.cards c where c.cid = s.scid and sid=#{id};
</select>
查询编号为1的学生信息【包括身份证编号】
public Student findById(int id) throws Exception { //得到连接对象 SqlSession sqlSession = MybatisUtil.getSqlSession(); try{ return sqlSession.selectOne("studentNamespace.findById", id); /* 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(); Student student = studentDao.findById(1); System.out.println(student.getId() + "----" + student.getName() + "----" + student.getCard().getNum()); }
//得到连接对象
SqlSession sqlSession = MybatisUtil.getSqlSession();
try{
return sqlSession.selectOne("studentNamespace.findById", id);
/* 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();
Student student = studentDao.findById(1);
System.out.println(student.getId() + "----" + student.getName() + "----" + student.getCard().getNum());
}
![这里写图片描述 640?wx_fmt=png](https://i-blog.csdnimg.cn/blog_migrate/508ac4bf6889eac0658a26d45aeb5d53.jpeg)
一对多
需求:
![这里写图片描述 640?wx_fmt=jpeg](https://i-blog.csdnimg.cn/blog_migrate/fc57b9a6f32d5c28c6a75a5f6ea3d1c1.jpeg)
设计数据库表
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;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; }}
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; }}
/**
* 学生(多方)
* @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>
<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()); } }
//得到连接对象
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());
}
}
![这里写图片描述 640?wx_fmt=png](https://i-blog.csdnimg.cn/blog_migrate/0636c80ffe41db05d1ffac2b2d8f6b35.jpeg)
多对多
需求:
![这里写图片描述 640?wx_fmt=jpeg](https://i-blog.csdnimg.cn/blog_migrate/de09f873c6e2d8b00dda906f3dc7b5df.jpeg)
数据库表
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;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; }}
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; }}
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>
<!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>
<!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()+" "); } }}
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语句全是由我们自己写,如果我们返回的数据类型在当前的实体中是不够封装的话,那么我们只要再关联对应的映射属性就行了!
如果文章有错的地方欢迎指正,大家互相交流。习惯在微信看技术文章,想要获取更多的Java资源的同学,可以关注微信公众号:Java3y