目录
ResultMap实现复杂映射
多对一
1. E-R图
2. 创建表
CREATE TABLE department(
d_id INT NOT NULL AUTO_INCREMENT,
d_name VARCHAR(100),
PRIMARY KEY(d_id)
);
CREATE TABLE employee(
e_id INT NOT NULL AUTO_INCREMENT,
e_name VARCHAR(30),
e_gender VARCHAR(6),
e_depart_id INT,
PRIMARY KEY(e_id),
FOREIGN KEY(e_depart_id) REFERENCES department(d_id)
);
INSERT INTO department(d_name) VALUES('技术部'),('财务器');
INSERT INTO employee (e_name,e_gender,e_depart_id) VALUES('叶先龙','男',1);
INSERT INTO employee (e_name,e_gender,e_depart_id) VALUES('刘林','男',1);
INSERT INTO employee (e_name,e_gender,e_depart_id) VALUES('曲河','男',1);
INSERT INTO employee (e_name,e_gender,e_depart_id) VALUES('李晓红','女',2);
INSERT INTO employee (e_name,e_gender,e_depart_id) VALUES('张梅','女',2);
3. 创建实体类
4. 接口
public interface EmployeeMapper {
/**
* 根据id查询员工信息
* @param id
* @return
*/
public Employee getEmployeeById(Integer id);
}
联合查询
5. 联合查询 映射文件
<mapper namespace="cn.offcn.mapper.EmployeeMapper">
<select id="getEmployeeById" resultMap="EmployeeResultMap">
SELECT e.*,d.* FROM employee e,department d WHERE e.e_depart_id=d.d_id AND e.e_id=#{id}
</select>
<resultMap id="EmployeeResultMap" type="Employee">
<id column="e_id" property="id"></id>
<result column="e_name" property="name"></result>
<result column="e_gender" property="gender"></result>
<association property="depart" javaType="Department">
<id column="d_id" property="id"></id>
<result column="d_name" property="name"></result>
</association>
</resultMap>
</mapper>
association用来描述自定义的复杂字段,property表示复杂属性名称。JavaType表示复杂属性的类型
如果接口和接口的映射文件名相同,可以在mybatis核心配置文件中使用Mapper中的calss属性来制定
分表查询
EmployeeMapper接口
DepartmentMapper接口
public interface DepartmentMapper {
public Department getDepartmentById(Integer id);
}
EmployeeMapper.xml
<resultMap id="BaseResultMap" type="Employee">
<id column="e_id" property="id"></id>
<result column="e_name" property="name"></result>
<result column="e_gender" property="gender"></result>
</resultMap>
<select id="getEmployeeById" resultMap="EmployeeResultMap">
SELECT * FROM employee WHERE e_id=#{id}
</select>
<resultMap id="EmployeeResultMap" type="Employee" extends="BaseResultMap">
<association property="depart" javaType="Department" column="e_depart_id"
select="cn.offcn.mapper.DepartmentMapper.getDepartmentById">
</association>
</resultMap>
分表查询:association column属性表示:取当前结果表中指定列的值,作为select查询输入参数。select指定另外一个查询namespace+id
departmentMapper.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="cn.offcn.mapper.DepartmentMapper">
<select id="getDepartmentById" resultMap="DepartmentResultMap">
SELECT * FROM department WHERE d_id=#{id}
</select>
<resultMap id="DepartmentResultMap" type="Department">
<id column="d_id" property="id"></id>
<result column="d_name" property="name"></result>
</resultMap>
</mapper>
测试:
public class Many2One {
@Test
public void testGetEmployeeById(){
SqlSession session= MyBatisUtils.getSession();
EmployeeMapper employeeMapper=session.getMapper(EmployeeMapper.class);
Employee employee= employeeMapper.getEmployeeById(2);
System.out.println(employee.getName()+"\t"+employee.getDepart().getName());
MyBatisUtils.close(session);
}
}
一对多
1. E-R图
2. 实体类
3. 接口
public interface DepartmentMapper {
public Department getDepartmentById(Integer id);
public Department getDepartmentById2(Integer id);
}
联合查询
4. 映射文件 联合查询方式
<resultMap id="DepartmentResultMap" type="Department">
<id column="d_id" property="id"></id>
<result column="d_name" property="name"></result>
</resultMap>
<select id="getDepartmentById2" resultMap="DepartmentResultMap2">
SELECT e.*,d.* FROM employee e,department d
WHERE e.e_depart_id=d.d_id AND d.d_id=#{id}
</select>
<resultMap id="DepartmentResultMap2" type="Department" extends="DepartmentResultMap">
<collection property="emps" ofType="Employee">
<id column="e_id" property="id"></id>
<result column="e_name" property="name"></result>
<result column="e_gender" property="gender"></result>
</collection>
</resultMap>
Collection标签用来映射集合,List、 Set都可以。
Property: 集合属性名称
ofType: 指定集合每个元素的类型
分表查询
在EmployeeMapper接口中定义方法
public interface EmployeeMapper {
/**
* 根据id查询员工信息
* @param id
* @return
*/
public Employee getEmployeeById(Integer id);
public List<Employee> getEmployeeByDepartId(Integer departId);
}
DepartmentMapper.xml
<select id="getDepartmentById2" resultMap="DepartmentResultMap2">
select * from department where d_id=#{id}
</select>
<resultMap id="DepartmentResultMap2" type="Department" extends="DepartmentResultMap">
<collection property="emps" ofType="Employee" column="d_id"
select="cn.offcn.mapper.EmployeeMapper.getEmployeeByDepartId"></collection>
</resultMap>
employeeMapper.xml
<select id="getEmployeeByDepartId" resultType="Employee">
select e_id id,e_name name,e_gender gender from employee where e_depart_id=#{id}
</select>
测试:
public class One2Many {
@Test
public void testGetDepartmentById(){
SqlSession session= MyBatisUtils.getSession();
DepartmentMapper departmentMapper= session.getMapper(DepartmentMapper.class);
Department department=departmentMapper.getDepartmentById2(1);
System.out.println(department.getName());
List<Employee> employeeList=department.getEmps();
for (Employee employee : employeeList) {
System.out.println(employee.getName()+"\t"+employee.getGender());
}
}
}
一对一
1. E-R图
2. 创建表
CREATE TABLE person(
p_id INT NOT NULL AUTO_INCREMENT,
p_name VARCHAR(30),
PRIMARY KEY(p_id)
);
CREATE TABLE idcard(
c_id INT NOT NULL AUTO_INCREMENT,
c_cardno VARCHAR(18),
c_uselife DATE,
c_person_id INT NOT NULL,
PRIMARY KEY(c_id),
FOREIGN KEY(c_person_id) REFERENCES person(p_id),
UNIQUE KEY(c_cardno),
UNIQUE KEY(c_person_id)
);
INSERT INTO person(p_name) VALUES('赵正日'),('金正恩');
INSERT INTO idcard (c_cardno,c_uselife,c_person_id) VALUES('110291199210192178','2023-10-10',1);
INSERT INTO idcard (c_cardno,c_uselife,c_person_id) VALUES('222877198110128814','2030-10-12',2);
3. 实体类
4. 接口
public interface PersonMapper {
public Person getPersonById(Integer id);
}
联合查询
5. 映射文件 联合查询:
<mapper namespace="cn.offcn.mapper.PersonMapper">
<select id="getPersonById" resultMap="PersonResultMap">
SELECT p.*,c.* FROM person p,idcard c WHERE p.p_id=c.c_person_id AND p_id=#{id}
</select>
<resultMap id="BaseResultMap" type="Person">
<id column="p_id" property="id"></id>
<result column="p_name" property="name"></result>
</resultMap>
<resultMap id="PersonResultMap" type="Person" extends="BaseResultMap">
<association property="idCard" javaType="IdCard">
<id column="c_id" property="id"></id>
<result column="c_cardno" property="cardno"></result>
<result column="c_uselife" property="useLife"></result>
</association>
</resultMap>
</mapper>
分表查询
IDCardMapper接口
public interface IdCardMapper {
public IdCard getIdCardByPersonId(Integer personId);
}
PersonMapper.xml
<select id="getPersonById" resultMap="PersonResultMap">
select * from person where p_id=#{id}
</select>
<resultMap id="PersonResultMap" type="Person" extends="BaseResultMap">
<association property="idCard" javaType="IdCard" column="p_id"
select="cn.offcn.mapper.IdCardMapper.getIdCardByPersonId"></association>
</resultMap>
IdCardMapper.xml
<select id="getIdCardByPersonId" resultMap="IdCardResultMap">
select * from idcard where c_person_id=#{id}
</select>
<resultMap id="IdCardResultMap" type="IdCard">
<id column="c_id" property="id"></id>
<result column="c_cardno" property="cardno"></result>
<result column="c_uselife" property="useLife"></result>
</resultMap>
测试:
public class One2One {
@Test
public void testGetPersonById(){
SqlSession session= MyBatisUtils.getSession();
PersonMapper personMapper=session.getMapper(PersonMapper.class);
Person person= personMapper.getPersonById(2);
System.out.println(person.getName());
IdCard card=person.getIdCard();
System.out.println(card.getCardno()+"\t"+card.getUseLife());
MyBatisUtils.close(session);
}
}
多对多
1. E-R图
2. 创建表
CREATE TABLE student(
s_id INT NOT NULL AUTO_INCREMENT,
s_name VARCHAR(30),
PRIMARY KEY(s_id)
);
CREATE TABLE teacher(
t_id INT NOT NULL AUTO_INCREMENT,
t_name VARCHAR(30),
PRIMARY KEY(t_id)
);
CREATE TABLE student_teacher(
st_sid INT NOT NULL,
st_tid INT NOT NULL,
PRIMARY KEY(st_sid,st_tid),
FOREIGN KEY(st_sid) REFERENCES student(s_id),
FOREIGN KEY(st_tid) REFERENCES teacher(t_id)
);
INSERT INTO student(s_name) VALUES('李晓刚'),('张文龙');
INSERT INTO teacher(t_name) VALUES('王超群'),('代彪');
INSERT INTO student_teacher(st_sid,st_tid) VALUES(1,1),(1,2),(2,1),(2,2);
3. 实体类
4. 接口
public interface StudentMapper {
public Student getStudentById(Integer id);
}
联合查询
5. 映射文件联合查询
StudentMapper.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="cn.offcn.mapper.StudentMapper">
<select id="getStudentById" resultMap="StudentResultMap">
SELECT s.*,st.*,t.* FROM student s,student_teacher st,teacher t
WHERE s.s_id=st.st_sid AND t.t_id=st.st_tid AND s.s_id=#{id}
</select>
<resultMap id="StudentResultMap" type="Student">
<id column="s_id" property="id"></id>
<result column="s_name" property="name"></result>
<collection property="studentTeacherList" ofType="StudentTeacher">
<result column="st_sid" property="stSid"></result>
<result column="st_tid" property="stTid"></result>
<association property="teacher" javaType="Teacher">
<id column="t_id" property="id"></id>
<result column="t_name" property="name"></result>
</association>
</collection>
</resultMap>
</mapper>
测试:
public class Many2Many {
@Test
public void testGetStudentById(){
SqlSession session= MyBatisUtils.getSession();
StudentMapper studentMapper=session.getMapper(StudentMapper.class);
Student student= studentMapper.getStudentById(1);
System.out.println("学生姓名:"+student.getName());
List<StudentTeacher> studentTeacherList=student.getStudentTeacherList();
for (StudentTeacher studentTeacher : studentTeacherList) {
System.out.println(studentTeacher.getTeacher().getName());
}
MyBatisUtils.close(session);
}
}
分表查询
接口
studentMapper.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="cn.offcn.mapper.StudentMapper">
<select id="getStudentById" resultMap="StudentResultMap">
select * from student where s_id=#{id}
</select>
<resultMap id="StudentResultMap" type="Student">
<id column="s_id" property="id"></id>
<result column="s_name" property="name"></result>
<collection property="studentTeacherList" ofType="StudentTeacher" column="s_id"
select="cn.offcn.mapper.StudentTeacherMapper.getStudentTeacherBySid">
</collection>
</resultMap>
</mapper>
StudentTeacherMapper.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="cn.offcn.mapper.StudentTeacherMapper">
<select id="getStudentTeacherBySid" resultMap="StudentTeacherResultMap">
SELECT * FROM student_teacher WHERE st_sid=#{id}
</select>
<resultMap id="StudentTeacherResultMap" type="StudentTeacher">
<result column="st_sid" property="stSid"></result>
<result column="st_tid" property="stTid"></result>
<association property="teacher" javaType="Teacher" column="st_tid"
select="cn.offcn.mapper.TeacherMapper.getTeacherById">
</association>
</resultMap>
</mapper>
TeacherMapper.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="cn.offcn.mapper.TeacherMapper">
<select id="getTeacherById" resultType="Teacher">
SELECT t_id id,t_name name FROM teacher WHERE t_id=#{id}
</select>
</mapper>
测试:
public class Many2Many {
@Test
public void testGetStudentById(){
SqlSession session= MyBatisUtils.getSession();
StudentMapper studentMapper=session.getMapper(StudentMapper.class);
Student student= studentMapper.getStudentById(1);
System.out.println("学生姓名:"+student.getName());
List<StudentTeacher> studentTeacherList=student.getStudentTeacherList();
for (StudentTeacher studentTeacher : studentTeacherList) {
System.out.println(studentTeacher.getTeacher().getName());
}
MyBatisUtils.close(session);
}
}