Mybatis（第二篇：联表查询）
目录
一、前期 项目的搭建
1.数据库
user.sql
/*
Navicat MySQL Data Transfer
Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 50736
Source Host : localhost:3306
Source Schema : demo
Target Server Type : MySQL
Target Server Version : 50736
File Encoding : 65001
Date: 17/06/2022 09:52:53
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` int(11) NULL DEFAULT NULL,
`sex` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`birthday` date NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '小明', 20, '男', '武汉', '2022-06-10');
INSERT INTO `user` VALUES (2, '小红', 18, '女', '深圳', '2022-06-08');
INSERT INTO `user` VALUES (3, '小丽', 17, '女', '北京', '2019-12-02');
SET FOREIGN_KEY_CHECKS = 1;
department.sql
/*
Navicat MySQL Data Transfer
Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 50736
Source Host : localhost:3306
Source Schema : crm
Target Server Type : MySQL
Target Server Version : 50736
File Encoding : 65001
Date: 20/06/2022 17:33:24
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`sn` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of department
-- ----------------------------
INSERT INTO `department` VALUES (1, '总经办2', 'General Deparment2');
INSERT INTO `department` VALUES (2, '人力资源部', 'Human Resources Department');
INSERT INTO `department` VALUES (3, '采购部', 'Order Department');
INSERT INTO `department` VALUES (4, '仓储部', 'Warehousing Department');
INSERT INTO `department` VALUES (6, '技术部', 'Technolog Department ');
SET FOREIGN_KEY_CHECKS = 1;
employee.sql
/*
Navicat MySQL Data Transfer
Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 50736
Source Host : localhost:3306
Source Schema : crm
Target Server Type : MySQL
Target Server Version : 50736
File Encoding : 65001
Date: 20/06/2022 17:33:32
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` int(11) NULL DEFAULT NULL,
`admin` bit(1) NULL DEFAULT NULL,
`dept_id` bigint(20) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 20 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES (1, 'admin', '$2a$10$kYaoE4sx1uweWHoJt1CExO7ZpaM4Akt4rPnOcXK9/KRWxRuuEIfWC', 'admin@abc.com', 20, b'1', 2);
INSERT INTO `employee` VALUES (2, 'zhao', '$2a$10$kYaoE4sx1uweWHoJt1CExO7ZpaM4Akt4rPnOcXK9/KRWxRuuEIfWC', 'zhaoz@wolfcode.cn', 35, b'0', 1);
INSERT INTO `employee` VALUES (3, '赵一明', 'c4ca4238a0b923820dcc509a6f75849b', 'zhaoym@wolfcode.cn', 25, b'0', 1);
INSERT INTO `employee` VALUES (4, '钱总', 'c4ca4238a0b923820dcc509a6f75849b', 'qianz@wolfcode.cn', 35, b'0', 2);
INSERT INTO `employee` VALUES (5, '钱二明', 'c4ca4238a0b923820dcc509a6f75849b', 'qianem@wolfcode.cn', 25, b'0', 2);
INSERT INTO `employee` VALUES (6, '孙总', 'c4ca4238a0b923820dcc509a6f75849b', 'sunz@wolfcode.cn', 35, b'0', 3);
INSERT INTO `employee` VALUES (7, '孙三明', 'c4ca4238a0b923820dcc509a6f75849b', 'sunsm@wolfcode.cn', 25, b'0', 3);
INSERT INTO `employee` VALUES (9, '李四明', 'c4ca4238a0b923820dcc509a6f75849b', 'lism@wolfcode.cn', 25, b'0', 4);
INSERT INTO `employee` VALUES (10, '周总', 'c4ca4238a0b923820dcc509a6f75849b', 'zhouz@wolfcode.cn', 35, b'0', 5);
INSERT INTO `employee` VALUES (11, '周五明', 'c4ca4238a0b923820dcc509a6f75849b', 'zhouwm@wolfcode.cn', 25, b'0', 5);
INSERT INTO `employee` VALUES (12, '吴总', 'c4ca4238a0b923820dcc509a6f75849b', 'wuz@wolfcode.cn', 35, b'0', 6);
INSERT INTO `employee` VALUES (13, '吴六明', 'c4ca4238a0b923820dcc509a6f75849b', 'wulm@wolfcode.cn', 25, b'0', 6);
INSERT INTO `employee` VALUES (14, '郑总', 'c4ca4238a0b923820dcc509a6f75849b', 'zhengz@wolfcode.cn', 35, b'0', 7);
INSERT INTO `employee` VALUES (15, '郑七明', 'c4ca4238a0b923820dcc509a6f75849b', 'zhengqm@wolfcode.cn', 25, b'0', 7);
INSERT INTO `employee` VALUES (16, '孙四明', 'c4ca4238a0b923820dcc509a6f75849b', 'sunsim@wolfcode.cn', 25, b'0', 3);
INSERT INTO `employee` VALUES (17, '孙五明2', 'c4ca4238a0b923820dcc509a6f75849b', 'sunwm@wolfcode.cn', 25, b'0', 1);
INSERT INTO `employee` VALUES (18, '李五明', 'c4ca4238a0b923820dcc509a6f75849b', 'liwm@wolfcode.cn', 25, b'0', 4);
INSERT INTO `employee` VALUES (19, '李六明', 'c4ca4238a0b923820dcc509a6f75849b', 'lilm@wolfcode.cn', 25, b'0', 4);
SET FOREIGN_KEY_CHECKS = 1;
role.sql
/*
Navicat MySQL Data Transfer
Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 50736
Source Host : localhost:3306
Source Schema : crm
Target Server Type : MySQL
Target Server Version : 50736
File Encoding : 65001
Date: 20/06/2022 17:33:49
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for role
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`sn` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 15 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO `role` VALUES (1, '人事管理', 'HR_MGR');
INSERT INTO `role` VALUES (2, '采购管理', 'ORDER_MGR');
INSERT INTO `role` VALUES (3, '仓储管理', 'WAREHOUSING_MGR');
INSERT INTO `role` VALUES (4, '行政部管理', 'Admin_MGR');
INSERT INTO `role` VALUES (11, '市场经理', 'Market_Manager');
INSERT INTO `role` VALUES (12, '市场专员', 'Market');
INSERT INTO `role` VALUES (13, '2222222', '22222222');
INSERT INTO `role` VALUES (14, '1231', '3132');
SET FOREIGN_KEY_CHECKS = 1;
employee_role.sql
/*
Navicat MySQL Data Transfer
Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 50736
Source Host : localhost:3306
Source Schema : crm
Target Server Type : MySQL
Target Server Version : 50736
File Encoding : 65001
Date: 20/06/2022 17:33:41
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for employee_role
-- ----------------------------
DROP TABLE IF EXISTS `employee_role`;
CREATE TABLE `employee_role` (
`employee_id` bigint(20) NULL DEFAULT NULL,
`role_id` bigint(20) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of employee_role
-- ----------------------------
INSERT INTO `employee_role` VALUES (2, 1);
INSERT INTO `employee_role` VALUES (3, 12);
INSERT INTO `employee_role` VALUES (5, 11);
INSERT INTO `employee_role` VALUES (22, 11);
INSERT INTO `employee_role` VALUES (22, 12);
INSERT INTO `employee_role` VALUES (23, 3);
INSERT INTO `employee_role` VALUES (23, 4);
INSERT INTO `employee_role` VALUES (23, 11);
INSERT INTO `employee_role` VALUES (23, 12);
INSERT INTO `employee_role` VALUES (17, 1);
INSERT INTO `employee_role` VALUES (17, 2);
INSERT INTO `employee_role` VALUES (17, 3);
INSERT INTO `employee_role` VALUES (17, 4);
INSERT INTO `employee_role` VALUES (17, 11);
INSERT INTO `employee_role` VALUES (17, 12);
INSERT INTO `employee_role` VALUES (17, 13);
SET FOREIGN_KEY_CHECKS = 1;
2.IDEA项目架构搭建
2.1 pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.bjpowernode</groupId>
<artifactId>02_realtionproject</artifactId>
<version>1.0.0</version>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.28</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.9</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
</dependency>
<!--log-->
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.2</version>
</dependency>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-core</artifactId>
<version>2.13.3</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<!-- 分页插件jar -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.2.0</version>
</dependency>
</dependencies>
</project>
2.2 domain包
2.2.1 Department.java
package com.bjpowernode.domain;
import lombok.Data;
@Data
public class Department {
private Long id;
private String name;
private String sn;
}
2.2.2 Employee.java
package com.bjpowernode.domain;
import lombok.Data;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import java.util.ArrayList;
import java.util.List;
@Getter
@Setter
public class Employee {
private Long id;
private String username;
private String name;
private String password;
private String email;
private Integer age;
private boolean admin;
private Department dept; // 所属部门
private List<Role> roles =new ArrayList(); //拥有的角色
@Override
public String toString() {
return "Employee{" +
"id=" + id +
", username='" + username + '\'' +
", name='" + name + '\'' +
", password='" + password + '\'' +
", email='" + email + '\'' +
", age=" + age +
", admin=" + admin +
", dept=" + dept +
'}';
}
}
2.2.3 Role.java
package com.bjpowernode.domain;
import lombok.Data;
@Data
public class Role {
private Long id;
private String name;
private String sn;
}
2.3 util包
MybatisUtil.java
package com.bjpowernode.util;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
public class MyBatisUtil {
private MyBatisUtil(){}
private static SqlSessionFactory factory ;
static {
try {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
factory= new SqlSessionFactoryBuilder().build(resourceAsStream);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession openSession() {
return factory.openSession(true);
}
}
2.4 mybatis-config.xml
<?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>
<!--关联数据库的配置信息-->
<properties resource="db.properties"/>
<!--设置日志-->
<settings>
<setting name="logImpl" value="LOG4J"/>
<!-- 设置为 false 表示每个延迟加载属性会按需加载 -->
<setting name="aggressiveLazyLoading" value="false" />
<!--是否开启懒加载 总开关 延迟加载的全局开关-->
<setting name="lazyLoadingEnabled" value="true"/>
<!--指定对象的哪些方法触发一次延迟加载-->
<setting name="lazyLoadTriggerMethods" value="clone"/>
</settings>
<!--给所有的 javaBean 起别名-->
<typeAliases>
<package name="com.bjpowernode.domain"/>
</typeAliases>
<!--分页插件的 配置-->
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<!--通过这个配置，来动态添加分页的功能-->
<property name="helperDialect" value="mysql"/>
<!--通过pageSizeZero属性可以灵活处理分页 当pageSize 设置为0
就查询全部数据-->
<property name="pageSizeZero" value="true"/>
<!--通过reasonable 属性 可以合理化分页-->
<property name="reasonable" value="true"/>
</plugin>
</plugins>
<!--配置环境-->
<environments default="dev">
<environment id="dev">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driverClassName}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!--挂载sql 的映射文件-->
<mappers>
<mapper resource="com/bjpowernode/mapper/DepartmentMapper.xml"/>
<mapper resource="com/bjpowernode/mapper/EmployeeMapper.xml"/>
<mapper resource="com/bjpowernode/mapper/RoleMapper.xml"/>
</mappers>
</configuration>
2.5 db.properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///rbac?useSSL=false
username=root
password=kong1234
2.6 log4j.properties
log4j.rootLogger=ERROR, stdout
log4j.logger.com.bjpowernode.mapper=TRACE
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
2.7 mapper包
2.7.1 departmentMapper.java
package com.bjpowernode.mapper;
import com.bjpowernode.domain.Department;
import java.util.List;
public interface DepartmentMapper {
List<Department> selectAll();
// 根据id 查询部门
Department selectDeptById();
}
2.7.2 EmployeeMapper.java
package com.bjpowernode.mapper;
import com.bjpowernode.domain.Employee;
import java.util.List;
public interface EmployeeMapper {
List<Employee> selectAll();
// 关联查询
List<Employee> selectEmpWithDepart();
// 额外sql
List<Employee> selectEmpWithDepart2();
// 额外sql
List<Employee> selectEmpWithRoles();
}
2.7.3 RoleMapper.java
package com.bjpowernode.mapper;
import com.bjpowernode.domain.Department;
import com.bjpowernode.domain.Role;
import java.util.List;
public interface RoleMapper {
List<Role> selectAll();
List<Role> selectRolesByEmpId(Long empId);
}
2.8 xml
2.8.1 DepartmentMapper.xml
部分代码说明——resultMap的两种写法
写法一：
<resultMap id="baseMap" type="employee">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="password" property="password"/>
<result column="email" property="email"/>
<result column="age" property="age"/>
<result column="admin" property="admin"/>
<!--
association用来封装对象属性的
property 对象属性的名称（module层外键的那个属性 Employee的外键属性）
javaType 对象属性的全限定名 (外键属性的全限定名)
-->
<!-- <association property="department" javaType="com.bjpowernode.domain.Department">-->
<!-- <id column="d_id" property="id"/>-->
<!-- <result column="d_name" property="name"/>-->
<!-- </association>-->
<association property="department" javaType="com.bjpowernode.domain.Department" columnPrefix="d_">
<id column="id" property="id"/>
<result column="name" property="name"/>
</association>
</resultMap>
写法二：
<resultMap id="baseMap" type="employee">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="password" property="password"/>
<result column="email" property="email"/>
<result column="age" property="age"/>
<result column="admin" property="admin"/>
<result column="d_id" property="department.id"/>
<result column="d_name" property="department.name"/>
</resultMap>
全部代码
<?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="com.bjpowernode.mapper.DepartmentMapper">
<select id="selectAll" resultType="department">
select * from department
</select>
<select id="selectDeptById" resultType="department">
select * from department where id = #{id}
</select>
</mapper>
2.8.2 EmployeeMapper.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="com.bjpowernode.mapper.EmployeeMapper">
<select id="selectAll" resultType="employee">
select * from employee
</select>
<!-- <resultMap id="baseMap" type="employee">-->
<!-- <id column="id" property="id"/>-->
<!-- <result column="username" property="username"/>-->
<!-- <result column="password" property="password"/>-->
<!-- <result column="email" property="email"/>-->
<!-- <result column="age" property="age"/>-->
<!-- <result column="admin" property="admin"/>-->
<!-- <!–-->
<!-- association用来封装对象属性的-->
<!-- property 对象属性的名称（module层外键的那个属性 Employee的外键属性）-->
<!-- javaType 对象属性的全限定名 (外键属性的全限定名)-->
<!-- –>-->
<!-- <!– <association property="department" javaType="com.bjpowernode.domain.Department">–>-->
<!-- <!– <id column="d_id" property="id"/>–>-->
<!-- <!– <result column="d_name" property="name"/>–>-->
<!-- <!– </association>–>-->
<!-- <association property="department" javaType="com.bjpowernode.domain.Department" columnPrefix="d_">-->
<!-- <id column="id" property="id"/>-->
<!-- <result column="name" property="name"/>-->
<!-- </association>-->
<!-- </resultMap>-->
<resultMap id="baseMap" type="employee">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="password" property="password"/>
<result column="email" property="email"/>
<result column="age" property="age"/>
<result column="admin" property="admin"/>
<result column="d_id" property="department.id"/>
<result column="d_name" property="department.name"/>
</resultMap>
<select id="selectEmpWithDepart" resultMap="baseMap">
select emp.id,emp.name,emp.username,emp.age,emp.admin, emp.email,emp.`password`,
dept.id d_id, dept.name d_name
from employee emp left join department dept on emp.dept_id = dept.id
</select>
<resultMap id="baseMap2" type="employee">
<id column="id" property="id" />
<result column="username" property="username"/>
<result column="name" property="name"/>
<result column="password" property="password"/>
<result column="email" property="email"/>
<result column="age" property="age"/>
<result column="admin" property="admin"/>
<!--
column 属性 它是发送 额外sql的 条件
select 属性 它是指定发送额外sql的方法
-->
<association property="dept" column="dept_id"
select="com.bjpowernode.mapper.DepartmentMapper.selectDeptById"/>
</resultMap>
<select id="selectEmpWithDepart2" resultMap="baseMap2">
select * from employee
</select>
<resultMap id="baseMap3" type="employee">
<id column="id" property="id" />
<result column="username" property="username"/>
<result column="name" property="name"/>
<result column="password" property="password"/>
<result column="email" property="email"/>
<result column="age" property="age"/>
<result column="admin" property="admin"/>
<!--
collection 封装集合类型的属性
property 表示 集合类型的属性的名称
ofType 表示集合中存储的对象的类型
select 表示 指定发送的额外sql
column 表示 执行额外sql 需要的条件
-->
<collection property="roles" ofType="com.bjpowernode.domain.Role"
select="com.bjpowernode.mapper.RoleMapper.selectRolesByEmpId"
column="id"/>
</resultMap>
<select id="selectEmpWithRoles" resultMap="baseMap3">
select * from employee
</select>
</mapper>
2.8.3 RoleMapper.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="com.bjpowernode.mapper.RoleMapper">
<select id="selectAll" resultType="role">
select * from role
</select>
<select id="selectRolesByEmpId" resultType="role">
select r.* from role r join employee_role er on r.id = er.role_id
where er.employee_id = #{empId}
</select>
</mapper>
2.9 测试类
package com.bjpowernode.test;
import com.bjpowernode.domain.Department;
import com.bjpowernode.domain.Employee;
import com.bjpowernode.mapper.DepartmentMapper;
import com.bjpowernode.mapper.EmployeeMapper;
import com.bjpowernode.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class DemoTest {
// 需求： 查询所有的部门信息
@Test
public void testSelectAll(){
SqlSession sqlSession = MyBatisUtil.openSession();
DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);
List<Department> departments = mapper.selectAll();
departments.forEach(System.out::println);
sqlSession.close();
}
// 需求： 查询所有的员工信息
@Test
public void testSelectAll1(){
SqlSession sqlSession = MyBatisUtil.openSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> employees = mapper.selectAll();
employees.forEach(System.out::println);
sqlSession.close();
}
//需求: 查询员工信息以及对应的部门信息 （员工信息和对应的部门信心 都要展示）
@Test
public void testSelectEmpsWithDepart(){
SqlSession sqlSession = MyBatisUtil.openSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> employees = mapper.selectEmpWithDepart();
employees.forEach(System.out::println);
sqlSession.close();
}
@Test
public void testSelectEmpsWithDepart2(){
SqlSession sqlSession = MyBatisUtil.openSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> employees = mapper.selectEmpWithDepart2();
employees.forEach(System.out::println);
sqlSession.close();
}
// 需求：查询员工信息以及拥有的角色信息（员工信息 要展示）
@Test
public void testSelectEmpsWithRoles(){
SqlSession sqlSession = MyBatisUtil.openSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> employees = mapper.selectEmpWithRoles();
for (Employee employee : employees) {
System.out.println(employee.getRoles());
}
sqlSession.close();
}
/**
* 完成上述需求：有两种方案 ，一种是 关联查询，一种是 额外sql
* 特点： 关联查询 只发送一条sql ，关联了多张表
* 额外sql 发送 N + 1 条sql ， 没有关联表
* 优缺点：
*
* 如果需求中，查询员工以及部门信息，并要求展示在列表上，那么
* 关联查询 方案比较好，而额外sql 方案 不可取，因为发送sql 太多，
* 给 sql 服务器带来压力
*
* 如果需求中，查询员工信息以及部门信息，但是 要求展示员工信息，
* 不需要展示部门信息 ，那么这个时候 应当使用 额外sql ，而放弃关联查询
* 因为额外sql 可以
* 配置懒加载模式（需要的时候，发送额外sql，不需要的时候，不发送），
* 放弃关联查询 是由于关联表多 ，导致性能低
*
*
*
*/
}
3. 过程知识点细讲
3.1 一对多封装 （一个员工对应一个部门）
Employee.java
package com.bjpowernode.domain;
import lombok.Data;
@Data
public class Employee {
private Long id;
private String username;
private String password;
private String email;
private Integer age;
private boolean admin;
private Department department;// 所属部门
}
方案一:关联查询（association、property、javaType、[columnPrefix]）
EmployeeMapper.java
List<Employee> selectAllEmployeeWithDepartment();
EmployeeMapper.xml
association用来封装对象属性的
property 对象属性的名称（module层外键的那个属性 Employee的外键属性）
javaType 对象属性的全限定名 (外键属性的全限定名)
<resultMap id="baseMap" type="employee">
<id column="id" property="id"/>
<result column="username" property="username" />
<result column="password" property="password" />
<result column="email" property="email" />
<result column="age" property="age" />
<result column="admin" property="admin" />
<!--
association用来封装对象属性的
property 对象属性的名称（module层外键的那个属性 Employee的外键属性）
javaType 对象属性的全限定名 (外键属性的全限定名)
-->
<association property="department" javaType="com.bjpowernode.domain.Department">
<id column="d_id" property="id"/>
<result column="d_name" property="name"/>
</association>
</resultMap>
columnPrefix
用了columnPrefix属性
<resultMap id="baseMap" type="employee">
<id column="id" property="id"/>
<result column="username" property="username" />
<result column="password" property="password" />
<result column="email" property="email" />
<result column="age" property="age" />
<result column="admin" property="admin" />
<!--
association用来封装对象属性的
property 对象属性的名称（module层外键的那个属性 Employee的外键属性）
javaType 对象属性的全限定名 (外键属性的全限定名)
-->
<association property="department" javaType="com.bjpowernode.domain.Department" columnPrefix="d_">
<id column="id" property="id"/>
<result column="name" property="name"/>
</association>
</resultMap>
<select id="selectAllEmployeeWithDepartment" resultMap="baseMap">
SELECT
emp.id,
emp.username,
emp.age,
emp.admin,
emp.email,
emp.`password`,
dept.id d_id,
dept.NAME d_name
FROM
employee emp
LEFT JOIN department dept ON emp.dept_id = dept.id
</select>
方案二：发送额外sql（1）（推荐使用这种）
EmployeeMapper.java
// 发送额外SQL
List<Employee> selectAllEmployeeWithDepartmentBySendExtraSQL();
EmployeeMapper.xml
resultMap
column 属性 它是发送额外sql的条件
select 属性 它是指定发送额外sql的方法
<resultMap id="baseMapBySendExtraSQL" type="employee">
<id column="id" property="id"/>
<result column="username" property="username" />
<result column="password" property="password" />
<result column="email" property="email" />
<result column="age" property="age" />
<result column="admin" property="admin" />
<!--
column 属性 它是发送额外sql的条件
select 属性 它是指定发送额外sql的方法
-->
<association property="department" column="dept_id"
select="com.bjpowernode.mapper.DepartmentMapper.selectDepartmentById">
</association>
</resultMap>
select标签
<select id="selectAllEmployeeWithDepartmentBySendExtraSQL" resultMap="baseMapBySendExtraSQL">
select * from employee;
</select>
DepartmentMapper.java
// 根据id 查询部门
Department selectDepartmentById();
DepartmentMapper.xml
<select id="selectDepartmentById" resultType="department">
select * from department where id = #{id}
</select>
方案二：发送额外sql（2）
EmployeeMapper.java
// 发送额外SQL Tedu
List<Employee> selectAllEmployeeWithDepartmentBySendExtraSQLTedu();
EmployeeMapper.xml
<resultMap id="baseMapBySendExtraSQLTedu" type="employee">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="password" property="password"/>
<result column="email" property="email"/>
<result column="age" property="age"/>
<result column="admin" property="admin"/>
<!--
column 属性 它是发送额外sql的条件
select 属性 它是指定发送额外sql的方法
-->
<association property="department" column="dept_id"
select="selectDepartmentById">
</association>
</resultMap>
<select id="selectDepartmentById" resultType="department">
select * from department where id = #{id}
</select>
<select id="selectAllEmployeeWithDepartmentBySendExtraSQLTedu" resultMap="baseMapBySendExtraSQLTedu">
select * from employee;
</select>
测试类
@Test
public void selectAllEmployeeWithDepartmentBySendExtraSQLTedu(){
SqlSession sqlSession = MyBatisUtil.openSession();
EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> employeeList = employeeMapper.selectAllEmployeeWithDepartmentBySendExtraSQLTedu();
employeeList.forEach(System.out::println);
sqlSession.close();
}
两种方案的对比
/**
* 完成下述需求：有两种方案 ，一种是 关联查询，一种是 额外sql
* 特点： 关联查询 只发送一条sql ，关联了多张表
* 额外sql 发送 N + 1 条sql ， 没有关联表
* 优缺点：
*
* 如果需求中，查询员工以及部门信息，并要求展示在列表上，那么
* 关联查询 方案比较好，而额外sql 方案 不可取，因为发送sql 太多，
* 给 sql 服务器带来压力
*
* 如果需求中，查询员工信息以及部门信息，但是 要求展示员工信息，
* 不需要展示部门信息 ，那么这个时候 应当使用 额外sql ，而放弃关联查询
* 因为额外sql 可以
* 配置懒加载模式（需要的时候，发送额外sql，不需要的时候，不发送），
* 放弃关联查询 是由于关联表多 ，导致性能低
*
*/
3.2 多对多封装（员工和角色）
domain
Role.java
package com.bjpowernode.domain;
import lombok.Data;
@Data
public class Role {
private Long id;
private String name;
private String sn;
}
Employee.java表
```bash
package com.bjpowernode.domain;
import lombok.Data;
import java.util.*;
@Data
public class Employee {
private Long id;
private String username;
private String password;
private String email;
private Integer age;
private boolean admin;
private Department department;// 所属部门
private List<Role> roleList = new ArrayList<>();
}
RoleMapper.java
package com.bjpowernode.mapper;
import com.bjpowernode.domain.Department;
import com.bjpowernode.domain.Role;
import java.util.List;
public interface RoleMapper {
List<Role> selectAllRole();
}
RoleMapper.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="com.bjpowernode.mapper.RoleMapper">
<select id="selectAllRole" resultType="role">
select * from role;
</select>
</mapper>
mybatis-config.xml
<!-- 挂载sql的映射文件 -->
<mappers>
<mapper resource="com.bjpowernode.mapper\DepartmentMapper.xml"/>
<mapper resource="com.bjpowernode.mapper\EmployeeMapper.xml"/>
<mapper resource="com.bjpowernode.mapper\RoleMapper.xml"/>
</mappers>
测试类
@Test
public void selectAllRole(){
SqlSession sqlSession = MyBatisUtil.openSession();
RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);
List<Role> roleList = roleMapper.selectAllRole();
roleList.forEach(System.out::println);
sqlSession.close();
}
EmployeeMapper.java
// 发送额外SQL
List<Employee> selectAllEmployeeWithRole();
方案一：发送额外SQL （1）（推荐使用这种方式）
EmployeeMapper.xml
<resultMap id="EmpRoleMapBySendExtraSQL" type="employee">
<id column="id" property="id"/>
<result column="username" property="username" />
<result column="password" property="password" />
<result column="email" property="email" />
<result column="age" property="age" />
<result column="admin" property="admin" />
<!--
collection 封装集合类型的属性
property 表示 集合类型的属性的名称（module层外键集合属性的名称）
ofType 表示集合中存储的对象的类型
select 表示 指定发送的额外sql
column 表示 执行额外sql 需要的条件
-->
<collection property="roleList" ofType="com.bjpowernode.domain.Role"
select="com.bjpowernode.mapper.RoleMapper.selectRolesByEmpId" column="id">
</collection>
</resultMap>
<select id="selectAllEmployeeWithRole" resultMap="EmpRoleMapBySendExtraSQL">
select * from employee;
</select>
RoleMapper.java
List<Role> selectRolesByEmpId(Long empId);
RoleMapper.xml
<select id="selectRolesByEmpId" resultType="role">
select r.* from role r join employee_role er on r.id = er.role_id
where er.employee_id = #{empId}
</select>
测试类
// 需求：查询员工信息以及拥有的角色信息（员工信息要展示）
@Test
public void selectAllEmployeeWithRole(){
SqlSession sqlSession = MyBatisUtil.openSession();
EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> employeeList = employeeMapper.selectAllEmployeeWithRole();
employeeList.forEach(System.out::println);
sqlSession.close();
}
发送了额外sql
是否发送额外SQL 配置懒加载
如何让它不发额外SQL呢？配置一个懒加载
将以下代码圈出来的代码复制到mybatis-config.xml文件中
<!-- 设置为 false 表示每个延迟加载属性会按需加载 -->
<setting name="aggressiveLazyLoading" value="false" />
<!--是否开启懒加载 总开关 延迟加载的全局开关-->
<setting name="lazyLoadingEnabled" value="true"/>
<!--指定对象的哪些方法触发一次延迟加载-->
<setting name="lazyLoadTriggerMethods" value="clone"/>
修改Employee.java
@Data改为@Getter和@Setter
重写toString方法（不重写roleList）
package com.bjpowernode.domain;
import lombok.Data;
import lombok.Getter;
import lombok.Setter;
import java.util.*;
@Getter
@Setter
public class Employee {
private Long id;
private String username;
private String password;
private String email;
private Integer age;
private boolean admin;
private Department department;// 所属部门
private List<Role> roleList = new ArrayList<>();
@Override
public String toString() {
return "Employee{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", email='" + email + '\'' +
", age=" + age +
", admin=" + admin +
", department=" + department +
'}';
}
}
再次执行测试类
就没有发送额外SQL了
测试类
当测试类调用getRoleList的时候，就又会发送额外SQL了
// 需求：查询员工信息以及拥有的角色信息（员工信息要展示）
@Test
public void selectAllEmployeeWithRole(){
SqlSession sqlSession = MyBatisUtil.openSession();
EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> employeeList = employeeMapper.selectAllEmployeeWithRole();
for (Employee employee : employeeList) {
System.out.println(employee.getRoleList());
}
sqlSession.close();
}
又会发送额外SQL了
方案一：发送额外SQL（2）
EmployeeMapper.java
// 发送额外SQL Tedu
List<Employee> selectAllEmployeeWithRoleBySendExtraSQLTedu();
EmployeeMapper.xml
<resultMap id="EmpRoleMapBySendExtraSQLTedu" type="employee">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="password" property="password"/>
<result column="email" property="email"/>
<result column="age" property="age"/>
<result column="admin" property="admin"/>
<!--
collection 封装集合类型的属性
property 表示 集合类型的属性的名称（module层外键 集合属性的名称）
ofType 表示集合中存储的对象的类型
select 表示 指定发送的额外sql接口权限定名
column 表示 执行额外sql 需要的条件
-->
<collection property="roleList" ofType="com.bjpowernode.domain.Role"
select="selectRolesByEmpId" column="id">
</collection>
</resultMap>
<select id="selectRolesByEmpId" resultType="role">
select r.* from role r join employee_role er on r.id = er.role_id
where er.employee_id = #{empId}
</select>
<select id="selectAllEmployeeWithRoleBySendExtraSQLTedu" resultMap="EmpRoleMapBySendExtraSQLTedu">
select * from employee;
</select>
测试类
@Test
public void selectAllEmployeeWithRoleBySendExtraSQuLTedu(){
SqlSession sqlSession = MyBatisUtil.openSession();
EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> employeeList = employeeMapper.selectAllEmployeeWithRoleBySendExtraSQLTedu();
for (Employee employee : employeeList) {
System.out.println(employee.getRoleList());
}
sqlSession.close();
}
方案二：关联查询
EmployeeMapper.java
//关联查询
List<Employee> selectAllEmployeeWithRole();
EmployeeMapper.xml
<resultMap id="EmpRoleMap" type="employee">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="password" property="password"/>
<result column="email" property="email"/>
<result column="age" property="age"/>
<result column="admin" property="admin"/>
<!--
collection 封装集合类型的属性
property 表示 集合类型的属性的名称（module层外键 集合属性的名称）
ofType 表示集合中存储的对象的类型
select 表示 指定发送的额外sql接口权限定名
column 表示 执行额外sql 需要的条件
-->
<collection property="roleList" ofType="role" columnPrefix="r_">
<id column="id" property="id"/>
<result column="name" property="name"/>
</collection>
</resultMap>
<select id="selectAllEmployeeWithRole" resultMap="EmpRoleMap">
SELECT
emp.*,
r.id r_id,
r.NAME r_name
FROM
employee emp
JOIN employee_role er ON emp.id = er.employee_id
JOIN role r ON r.id = er.role_id
</select>
Emloyee.java
package com.bjpowernode.domain;
import lombok.Data;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import java.util.*;
@Getter
@Setter
@ToString
public class Employee {
private Long id;
private String username;
private String password;
private String email;
private Integer age;
private boolean admin;
private Department department;// 所属部门
private List<Role> roleList = new ArrayList<>();
// @Override
// public String toString() {
// return "Employee{" +
// "id=" + id +
// ", username='" + username + '\'' +
// ", password='" + password + '\'' +
// ", email='" + email + '\'' +
// ", age=" + age +
// ", admin=" + admin +
// ", department=" + department +
// '}';
// }
}
测试类
@Test
public void selectAllEmployeeWithRole(){
SqlSession sqlSession = MyBatisUtil.openSession();
EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> employeeList = employeeMapper.selectAllEmployeeWithRole();
for (Employee employee : employeeList) {
System.out.println(employee);
}
sqlSession.close();
}
总结
配置文件中 两个标签 ：association collection 的选择
首先 标签 和 使用的查询方案 没有任何关系
如果类中有对象属性，则使用association标签
如果类中有集合对象属性，则使用 collection
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/85570.html
