未分类
2020-12-27 15:39:14
1822677238@qq.com
手机扫码查看
2020java框架教程之mybatis关联关系
1.一对一
数据库表：
create table member(#用户
uid int auto_increment primary key ,
username varchar(50) not null ,
sex varchar(2) not null
)charset=utf8;
create table resume(#简历
id int auto_increment primary key ,
resume_name varchar(50) not null ,
resume_uid int unique ,
foreign key (resume_uid) references member(uid)
)charset=utf8;
实体类：
public class Member {
private Integer uid;
private String username;
private String sex;
private Resume resume;
简历实体类：
public class Resume {
private Integer id;
private String resumeName;
private Integer resumeUid;
private Member member;
mapper
member：
public interface MemberMapper {
Member selectMemberById(Integer uid);
resume：
public interface ResumeMapper {
Resume selectResumeById(Integer id);
Membermapper.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="mapper.MemberMapper" >
<resultMap id="BaseResultMap" type="entity.Member" >
<id column="uid" property="uid" jdbcType="INTEGER" />
<result column="uname" property="username" jdbcType="VARCHAR" />
<result column="sex" property="sex" jdbcType="VARCHAR" />
<association property="resume" column="resume" javaType="Resume">
<id column="resume_id" property="id" jdbcType="INTEGER" />
<result column="resume_name" property="resumeName" jdbcType="VARCHAR" />
<result column="resume_uid" property="resumeUid" jdbcType="INTEGER" />
</association>
</resultMap>
<select id="selectMemberById" parameterType="int" resultMap="BaseResultMap">
select
member.uid uid,member.username uname,member.sex sex,resume.id resume_id,resume.resume_name resume_name,resume.resume_uid resume_uid
from member join resume on uid=resume_uid where uid=#{id}
</select>
</mapper>
在mybatis-config.xml文件添加注册
<mapper resource="MemberMapper.xml"/>
测试：
@Test
public void TestSelectMember(){
SqlSession ss = MybatisUtils.getSqlSession();
MemberMapper mapper = ss.getMapper(MemberMapper.class);
Member member = mapper.selectMemberById(1);
System.out.println(member);
System.out.println(member.getResume());
}
2.一对多
数据库：
create table dept(#部门
id int primary key auto_increment,
name varchar(50) ,
loc varchar(100)
)charset=utf8;
create table emp(#员工
id int auto_increment primary key ,
name varchar(50),
salary double,
dep_id int ,
foreign key (dep_id) references dept(id)
)charset=utf8;
实体类：
Emp:
public class Emp {
private Integer id;
private String name;
private Double salary;
private Integer depId;
private Dept dept;
Dept:
public class Dept {
private Integer id;
private String name;
private String loc;
private List<Emp> empList;
mapper：
DeptMapper：
public interface DeptMapper {
Dept selectDeptById(Integer id);
DeptMapper.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="mapper.DeptMapper" >
<resultMap id="BaseResultMap" type="entity.Dept" >
<id column="dep_id" property="id" jdbcType="INTEGER" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="loc" property="loc" jdbcType="VARCHAR" />
<collection property="empList" ofType="Emp">
<id column="emp_id" property="id" jdbcType="INTEGER" />
<result column="emp_name" property="name" jdbcType="VARCHAR" />
<result column="salary" property="salary" jdbcType="DOUBLE" />
<result column="dep_id" property="depId" jdbcType="INTEGER" />
</collection>
</resultMap>
<select id="selectDeptById" parameterType="int" resultMap="BaseResultMap">
select
dept.id dep_id,dept.name,dept.loc,emp.id emp_id,emp.name emp_name,emp.salary
from dept join emp on dept.id=emp.dep_id where dept.id=#{id}
</select>
</mapper>
在mybatis-config.xml文件添加注册
<mapper resource="DeptMapper.xml"/>
测试：
@Test
public void TestSelectDept(){
SqlSession ss = MybatisUtils.getSqlSession();
DeptMapper mapper = ss.getMapper(DeptMapper.class);
Dept dept = mapper.selectDeptById(2);
System.out.println(dept);
List<Emp> empList = dept.getEmpList();
for (Emp emp : empList) {
System.out.println(emp);
}
}
3.多对一
利用员工编号查询部门
EmpMapper.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="mapper.EmpMapper" >
<resultMap id="BaseResultMap" type="entity.Emp" >
<id column="emp_id" property="id" jdbcType="INTEGER" />
<result column="ename" property="name" jdbcType="VARCHAR" />
<result column="salary" property="salary" jdbcType="DOUBLE" />
<result column="dep_id" property="depId" jdbcType="INTEGER" />
<association property="dept" column="dept" javaType="Dept">
<id column="dept_id" property="id" jdbcType="INTEGER" />
<result column="dept_name" property="name" jdbcType="VARCHAR" />
<result column="loc" property="loc" jdbcType="VARCHAR" />
</association>
</resultMap>
<select id="selectEmpById" resultMap="BaseResultMap" parameterType="int">
select
emp.id emp_id,emp.name ename,emp.salary,emp.dep_id dep_id,dept.id dept_id,dept.name dept_name,dept.loc
from emp join dept on emp.dep_id=dept.id where emp.id=#{id}
</select>
</mapper>
测试
4.多对多
数据库表：
create table stu(#学生表
sid int auto_increment primary key ,
sname varchar(20) not null ,
sex varchar(2) not null
)charset=utf8;
create table subject(#科目表
id int auto_increment primary key ,
subname varchar(20) not null
)charset=utf8;
create table stu_sub(#学生和科目关联表
stu_id int ,
sub_id int ,
foreign key (stu_id) references stu(sid),
foreign key (sub_id) references subject(id),
primary key(stu_id,sub_id)
)charset=utf8;
实体类：
stu:
public class Stu {
private Integer sid;
private String sname;
private String sex;
private List<Subject> subjects;
subject:
public class Subject {
private Integer id;
private String subname;
private List<Stu> stus;
Mapper：
subjectMapper:
public interface SubjectMapper {
Subject selectSubjectById(Integer id);
Mapper.xml：
subjectMapper.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="mapper.SubjectMapper" >
<resultMap id="BaseResultMap" type="entity.Subject" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="subname" property="subname" jdbcType="VARCHAR" />
<collection property="stus" ofType="Stu">
<id column="sid" property="sid" jdbcType="INTEGER" />
<result column="sname" property="sname" jdbcType="VARCHAR" />
<result column="sex" property="sex" jdbcType="VARCHAR" />
</collection>
</resultMap>
<select id="selectSubjectById" resultMap="BaseResultMap" parameterType="int">
select
subject.id,subject.subname,stu.sid,stu.sname,stu.sex
from subject join stu_sub on subject.id=stu_sub.sub_id
join mybatis.stu on stu_sub.stu_id = stu.sid
where subject.id=#{id}
</select>
</mapper>
测试：
@Test
public void TestSelectSubject(){
SqlSession ss = MybatisUtils.getSqlSession();
SubjectMapper mapper = ss.getMapper(SubjectMapper.class);
Subject subject = mapper.selectSubjectById(1001);
System.out.println(subject);
List<Stu> stus = subject.getStus();
for (Stu stu : stus) {
System.out.println(stu);
}
}
发表回复