一、关系映射:一对一(学生-学生卡)
1、数据准备(MySQL)
(1)建库、建表
drop database if exists chapter03;
create database chapter03;
use chapter03;
create table stu_card(
cid int primary key auto_increment,
balance double # 余额
);
create table stu(
sid int primary key auto_increment,
sname varchar(20),
age varchar(20),
course varchar(20),
cardid int unique,
foreign key(cardid) references stu_card(cid)
);
(2)插入数据
insert into stu_card(balance) values(1000.5);
insert into stu_card(balance) values(5000.5);
insert into stu(sname,age,course,cardid) values('ZhangSan','20','Java',1);
insert into stu(sname,age,course,cardid) values('Lisi','21','Java',2);
2、创建POJO类
(1)StuCard
package com.qf.chapter03.pojo;
public class StuCard {
private int cid;
private double balance;
public StuCard() {
super();
}
public StuCard(int cid, double balance) {
super();
this.cid = cid;
this.balance = balance;
}
public int getCid() {
return cid;
}
public void setCid(int cid) {
this.cid = cid;
}
public double getBalance() {
return balance;
}
public void setBalance(double balance) {
this.balance = balance;
}
@Override
public String toString() {
return "StuCard [cid=" + cid + ", balance=" + balance + "]";
}
}
(2)Stu
package com.qf.chapter03.pojo;
public class Stu {
private int sid;
private String sname;
private String age;
private String course;
private StuCard sc;
public int getSid() {
return sid;
}
public void setSid(int sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
public String getCourse() {
return course;
}
public void setCourse(String course) {
this.course = course;
}
public StuCard getSc() {
return sc;
}
public void setSc(StuCard sc) {
this.sc = sc;
}
@Override
public String toString() {
return "Stu [sid=" + sid + ", sname=" + sname + ", age=" + age + ", course=" + course + ", sc=" + sc + "]";
}
}
3、映射文件
resources目录下新建chapter03目录
(1)StuCardMapper.xml(在chapter03目录中新建映射文件)
<?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="stucard">
<select id="findStuCardBycid" parameterType="Integer"
resultType="com.qf.chapter03.pojo.StuCard">
select * from stu_card where cid=#{cid}
</select>
</mapper>
(2)StuMapper.xml(在chapter03目录中新建映射文件)
<?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="stu">
<select id="findStudentBySid" parameterType="Integer"
resultMap="stuResultsMap">
select s.*, c.* from stu s, stu_card c
where s.cardid=c.cid and s.sid=#{sid}
</select>
<resultMap type="com.qf.chapter03.pojo.Stu" id="stuResultsMap">
<id column="sid" property="sid" />
<result column="sname" property="sname" />
<result column="age" property="age" />
<result column="course" property="course" />
<association property="sc" javaType="com.qf.chapter03.pojo.StuCard">
<id column="cid" property="cid" />
<result column="balance" property="balance" />
</association>
</resultMap>
</mapper>
(3)mybatis-config.xml(在resources目录中修改映射文件)
注:修改数据库名称、2个映射文件路径
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--配置环境 -->
<environments default="mysql">
<environment id="mysql">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url"
value="jdbc:mysql://localhost:3306/chapter03" />
<property name="username" value="root" />
<property name="password" value="root" />
</dataSource>
</environment>
</environments>
<!-- 配置映射文件的位置 -->
<mappers>
<mapper resource="chapter03/StuCardMapper.xml"/>
<mapper resource="chapter03/StuMapper.xml"/>
</mappers>
</configuration>
4、测试类:TestOneToOne
package com.qf.chapter03.test;
import java.io.InputStream;
import com.qf.chapter03.pojo.Stu;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.*;
public class TestOneToOne {
public static void main(String[] args) throws Exception {
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(in);
SqlSession session = sessionFactory.openSession();
// 使用MyBatis查询结果sid为1的学生信息(包括学生的校园卡信息)
Stu stu = session.selectOne("stu.findStudentBySid", 1);
System.out.println(stu);
session.close();
}
}
二、关系映射:一对多(班级-学生)
1、数据准备(MySQL)
(1)建表
use chapter03;
create table stu_class(
cid int primary key auto_increment,
cname varchar(20),
sum int
);
create table stu_info(
sid int primary key auto_increment,
sname varchar(20),
age varchar(20),
course varchar(20),
classid int,
foreign key(classid) references stu_class(cid)
);
(2)插入数据
insert into stu_class(cname,sum) values('BigData2201',47);
insert into stu_class(cname,sum) values('BigData2202',47);
insert into stu_class(cname,sum) values('BigData2203',47);
insert into stu_class(cname,sum) values('BigData2204',46);
insert into stu_info(sname,age,course,classid) values('ZhangSan','20','Java',1);
insert into stu_info(sname,age,course,classid) values('LiSi','21','Java',2);
insert into stu_info(sname,age,course,classid) values('WangWu','20','Java',3);
insert into stu_info(sname,age,course,classid) values('ZhaoLiu','19','Java',4);
insert into stu_info(sname,age,course,classid) values('Sunqi','22','Java',1);
2、创建POJO类
(1)StuInfo
package com.qf.chapter03.pojo;
public class StuInfo {
private int sid;
private String sname;
private String age;
private String course;
public int getSid() {
return sid;
}
public void setSid(int sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
public String getCourse() {
return course;
}
public void setCourse(String course) {
this.course = course;
}
@Override
public String toString() {
return "Stu [sid=" + sid + ", sname=" + sname + ", age=" + age + ", " + " course=" + course + "]";
}
}
(2)StuClass
package com.qf.chapter03.pojo;
import java.util.List;
public class StuClass {
private Integer cid;
private String cname;
private Integer sum;
private List<StuInfo> stuInfoList; // 存储班级所以的学生信息对象的列表
@Override
public String toString() {
return "StuClass{" +
"cid=" + cid +
", cname='" + cname + '\'' +
", sum=" + sum +
", stuInfoList=" + stuInfoList +
'}';
}
public Integer getCid() {
return cid;
}
public void setCid(Integer cid) {
this.cid = cid;
}
public String getCname() {
return cname;
}
public void setCname(String cname) {
this.cname = cname;
}
public Integer getSum() {
return sum;
}
public void setSum(Integer sum) {
this.sum = sum;
}
public List<StuInfo> getStuInfoList() {
return stuInfoList;
}
public void setStuInfoList(List<StuInfo> stuInfoList) {
this.stuInfoList = stuInfoList;
}
}
3、映射文件
(1)StuClassMapper.xml(在chapter03目录中新建映射文件)
<?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="stuClass">
<select id="findStuClassByCid" parameterType="Integer"
resultMap="stuClassResultsMap">
select c.*,s.sid,s.sname,s.age,s.course from stu_class c,stu_info s
where s.classid=c.cid and c.cid=#{cid}
</select>
<resultMap type="com.qf.chapter03.pojo.StuClass" id="stuClassResultsMap">
<id column="cid" property="cid" />
<result column="cname" property="cname" />
<result column="sum" property="sum" />
<collection property="stuInfoList" ofType="com.qf.chapter03.pojo.StuInfo">
<id column="sid" property="sid" />
<result column="sname" property="sname" />
<result column="age" property="age" />
<result column="course" property="course" />
</collection>
</resultMap>
</mapper>
(3)mybatis-config.xml(在resources目录中修改映射文件)
注:增加1个映射文件路径
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--配置环境 -->
<environments default="mysql">
<environment id="mysql">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url"
value="jdbc:mysql://localhost:3306/chapter03" />
<property name="username" value="root" />
<property name="password" value="root" />
</dataSource>
</environment>
</environments>
<!-- 配置映射文件的位置 -->
<mappers>
<mapper resource="chapter03/StuCardMapper.xml"/>
<mapper resource="chapter03/StuMapper.xml"/>
<mapper resource="chapter03/StuClassMapper.xml"/>
</mappers>
</configuration>
4、测试类:TestOneToMany
package com.qf.chapter03.test;
import java.io.InputStream;
import java.util.List;
import com.qf.chapter03.pojo.StuClass;
import com.qf.chapter03.pojo.StuInfo;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.*;
public class TestOneToMany {
public static void main(String[] args) throws Exception {
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(in);
SqlSession session = sessionFactory.openSession();
// 使用MyBatis查询cid为1的班级信息(包括该班级的学生信息)
StuClass stuClass = session.selectOne("stuClass.findStuClassByCid", 1);
System.out.println("班级ID:" + stuClass.getCid() + "\n班级名称:" + stuClass.getCname() + "\n班级人数:" + stuClass.getSum()
+ "\n学生信息:");
List<StuInfo> stuInfoList = stuClass.getStuInfoList();
for (StuInfo stuInfo : stuInfoList) {
System.out.println(stuInfo);
}
session.close();
}
}
三、关系映射:多对多(班级-教师)
1、数据准备(MySQL)
(1)建表
use chapter03;
create table class_info(
cid int primary key auto_increment,
cname varchar(20),
sum int
);
create table teach_info(
tid int primary key auto_increment,
tname varchar(20),
age varchar(20),
course varchar(20)
);
create table class_teach(
id int primary key auto_increment,
class_id int,
teach_id int,
course varchar(20),
foreign key(class_id) references class_info(cid),
foreign key(teach_id) references teach_info(tid)
);
(2)插入数据
insert into class_info(cname,sum) values('BigData2201',47);
insert into class_info(cname,sum) values('BigData2202',47);
insert into class_info(cname,sum) values('BigData2203',47);
insert into class_info(cname,sum) values('BigData2204',46);
insert into teach_info(tname,age,course) values('CFL','18','JavaEE');
insert into teach_info(tname,age,course) values('Other','81','Other');
insert into class_teach(class_id,teach_id) values(1,1);
insert into class_teach(class_id,teach_id) values(1,2);
insert into class_teach(class_id,teach_id) values(2,1);
insert into class_teach(class_id,teach_id) values(3,1);
insert into class_teach(class_id,teach_id) values(4,1);
2、创建POJO类
(1)TeachInfo
package com.qf.chapter03.pojo;
import java.util.List;
public class TeachInfo {
private Integer tid;
private String tname;
private String age;
private String course;
private List<ClassInfo> classInfoList;
public Integer getTid() {
return tid;
}
public void setTid(Integer tid) {
this.tid = tid;
}
public String getTname() {
return tname;
}
public void setTname(String tname) {
this.tname = tname;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
public String getCourse() {
return course;
}
public void setCourse(String course) {
this.course = course;
}
public List<ClassInfo> getClassInfoList() {
return classInfoList;
}
public void setClassInfoList(List<ClassInfo> classInfoList) {
this.classInfoList = classInfoList;
}
@Override
public String toString() {
return "TeachInfo [tid=" + tid + ", tname=" + tname + ", age=" + age + ", course=" + course + ", classInfoList="
+ classInfoList + "]";
}
}
(2)ClassInfo
package com.qf.chapter03.pojo;
import java.util.List;
public class ClassInfo {
private Integer cid;
private String cname;
private Integer sum;
private List<TeachInfo> teachInfoList;
public Integer getCid() {
return cid;
}
public void setCid(Integer cid) {
this.cid = cid;
}
public String getCname() {
return cname;
}
public void setCname(String cname) {
this.cname = cname;
}
public Integer getSum() {
return sum;
}
public void setSum(Integer sum) {
this.sum = sum;
}
public List<TeachInfo> getTeachInfoList() {
return teachInfoList;
}
public void setTeachInfoList(List<TeachInfo> teachInfoList) {
this.teachInfoList = teachInfoList;
}
@Override
public String toString() {
return "ClassInfo [cid=" + cid + ", cname=" + cname + ", sum=" + sum + ", teachInfoList=" + teachInfoList + "]";
}
}
3、映射文件
(1)ClassInfoMapper.xml(在chapter03目录中新建映射文件)
<?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="classInfo">
<select id="findClassInfoByCid" parameterType="Integer"
resultMap="classInfoResultsMap">
select c.*,t.* from class_info c, teach_info t, class_teach ct
where c.cid=ct.class_id and ct.teach_id=t.tid and c.cid=#{cid}
</select>
<resultMap type="com.qf.chapter03.pojo.ClassInfo" id="classInfoResultsMap">
<id column="cid" property="cid" />
<result column="cname" property="cname" />
<result column="sum" property="sum" />
<collection property="teachInfoList" ofType="com.qf.chapter03.pojo.TeachInfo">
<id column="tid" property="tid" />
<result column="tname" property="tname" />
<result column="age" property="age" />
<result column="course" property="course" />
</collection>
</resultMap>
</mapper>
(3)mybatis-config.xml(在resources目录中修改映射文件)
注:增加1个映射文件路径
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--配置环境 -->
<environments default="mysql">
<environment id="mysql">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url"
value="jdbc:mysql://localhost:3306/chapter03" />
<property name="username" value="root" />
<property name="password" value="root" />
</dataSource>
</environment>
</environments>
<!-- 配置映射文件的位置 -->
<mappers>
<mapper resource="chapter03/StuCardMapper.xml"/>
<mapper resource="chapter03/StuMapper.xml"/>
<mapper resource="chapter03/StuClassMapper.xml"/>
<mapper resource="chapter03/ClassInfoMapper.xml"/>
</mappers>
</configuration>
4、测试类:TestOneToMany
package com.qf.chapter03.test;
import java.io.InputStream;
import java.util.List;
import com.qf.chapter03.pojo.ClassInfo;
import com.qf.chapter03.pojo.TeachInfo;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.*;
public class TestManyToMany {
public static void main(String[] args) throws Exception {
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(in);
SqlSession session = sessionFactory.openSession();
// 使用MyBatis查询cid为1的班级信息(包括该班级的教师信息)
ClassInfo classInfo = session.selectOne("classInfo.findClassInfoByCid", 1);
System.out.println("班级ID:" + classInfo.getCid() + "\n班级名称:" + classInfo.getCname() + "\n班级人数:"
+ classInfo.getSum() + "\n教师信息:");
List<TeachInfo> teachInfoList = classInfo.getTeachInfoList();
for (TeachInfo teachInfo : teachInfoList) {
System.out.println(teachInfo.toString());
}
session.close();
}
}