Java笔记之Mybatis(五):多对多
0.说在前面
(1).员工Employee与项目Project之间存在多对多的关系
一个员工参与多个项目;
一个项目要有多个员工共同参与;
(2)员工Employee的定义和数据同Java笔记之Mybatis(四):一对多、多对一
1.数据库新建表t_project
USE keeper; DROP TABLE IF EXISTS `t_project`; CREATE TABLE `t_project` ( `pro_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '项目ID', `pro_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '项目名称', PRIMARY KEY (`pro_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
2.表t_project中导入数据
INSERT INTO `t_project` VALUES (null, '项目一'); INSERT INTO `t_project` VALUES (null, '项目二'); INSERT INTO `t_project` VALUES (null, '项目三'); INSERT INTO `t_project` VALUES (null, '项目四');
3.数据库新建表t_peitem,作为表t_project和t_employee的中间表
USE keeper; DROP TABLE IF EXISTS `t_peitem`; CREATE TABLE `t_peitem` ( `pro_id` int(11) NOT NULL COMMENT '项目ID', `emp_id` int(11) NOT NULL COMMENT '员工ID', PRIMARY KEY (`pro_id`, `emp_id`) USING BTREE, INDEX `fk_empId`(`emp_id`) USING BTREE, CONSTRAINT `fk_empId` FOREIGN KEY (`emp_id`) REFERENCES `t_employee` (`emp_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_proId` FOREIGN KEY (`pro_id`) REFERENCES `t_project` (`pro_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
4.表t_peitem中导入数据
INSERT INTO `t_peitem` VALUES (1, 1); INSERT INTO `t_peitem` VALUES (1, 2); INSERT INTO `t_peitem` VALUES (1, 3); INSERT INTO `t_peitem` VALUES (4, 3); INSERT INTO `t_peitem` VALUES (1, 4); INSERT INTO `t_peitem` VALUES (4, 4); INSERT INTO `t_peitem` VALUES (2, 5); INSERT INTO `t_peitem` VALUES (2, 6); INSERT INTO `t_peitem` VALUES (4, 6); INSERT INTO `t_peitem` VALUES (2, 7); INSERT INTO `t_peitem` VALUES (2, 8); INSERT INTO `t_peitem` VALUES (3, 9); INSERT INTO `t_peitem` VALUES (4, 9); INSERT INTO `t_peitem` VALUES (3, 10); INSERT INTO `t_peitem` VALUES (3, 11); INSERT INTO `t_peitem` VALUES (3, 12);
4.在com.mybatis.demo.pojo包下新建Project类
package com.mybatis.demo.pojo; import java.util.List; public class Project { private int proId; private String proName; private List<PeItem> peItems; public int getProId() { return proId; } public void setProId(int proId) { this.proId = proId; } public String getProName() { return proName; } public void setProName(String proName) { this.proName = proName; } public List<PeItem> getPeItems() { return peItems; } public void setPeItems(List<PeItem> peItems) { this.peItems = peItems; } }
5.在com.mybatis.demo.pojo包下新建PeItem类
package com.mybatis.demo.pojo; public class PeItem { private Project project; private Employee employee; public Project getProject() { return project; } public void setProject(Project project) { this.project = project; } public Employee getEmployee() { return employee; } public void setEmployee(Employee employee) { this.employee = employee; } }
6.在com.mybatis.demo.pojo包下新建Project.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.mybatis.demo.pojo"> <select id="getAllProjectDetail" resultMap="projectResultMap"> select pro.pro_id, pro.pro_name, emp.emp_id, emp.emp_name from t_project pro left join t_peitem item on pro.pro_id=item.pro_id left join t_employee emp on item.emp_id=emp.emp_id </select> <resultMap type="Project" id="projectResultMap"> <id column="pro_id" property="proId"/> <result column="pro_name" property="proName"/> <collection property="peItems" ofType="PeItem"> <association property="employee" javaType="Employee"> <id column="emp_id" property="empId"/> <result column="emp_name" property="empName"/> </association> </collection> </resultMap> </mapper>
7.mybatisConfig.xml中添加Project.xml的sql映射配置文件信息
<?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> <settings> <!-- 开启驼峰命名规则映射 --> <setting name="mapUnderscoreToCamelCase" value="true"/> </settings> <!-- 对类起别名 --> <typeAliases> <!-- 类的别名默认为类名 --> <package name="com.mybatis.demo.pojo"/> </typeAliases> <environments default="development"> <environment id="development"> <!-- 使用JDBC事务管理 --> <transactionManager type="JDBC"></transactionManager> <!-- 定义数据库连接池 --> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/keeper?characterEncoding=utf-8"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> </environments> <!-- 配置sql映射配置文件 --> <mappers> <mapper resource="com/mybatis/demo/pojo/Student.xml"/> <mapper resource="com/mybatis/demo/pojo/Employee.xml"/> <mapper resource="com/mybatis/demo/pojo/Department.xml"/> <mapper resource="com/mybatis/demo/pojo/Project.xml"/> </mappers> </configuration>
8.在com.mybatis.demo.test包下新建MybatisTest4类
package com.mybatis.demo.test; import java.io.IOException; import java.io.InputStream; import java.util.List; 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 com.mybatis.demo.pojo.Employee; import com.mybatis.demo.pojo.PeItem; import com.mybatis.demo.pojo.Project; public class MybatisTest4 { public static void main(String[] args) throws IOException { //加载mybatis的配置文件 InputStream inputStream = Resources.getResourceAsStream("mybatisConfig.xml"); //获取SqlSessionFactory对象 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); //使用SqlSessionFactory对象创建SqlSession对象 SqlSession sqlSession = sqlSessionFactory.openSession(); List<Project> projects = sqlSession.selectList("getAllProjectDetail"); for (Project project : projects) { System.out.println(project.getProId()+"\t"+project.getProName()); List<PeItem> peItems = project.getPeItems(); for (PeItem peItem : peItems) { Employee employee = peItem.getEmployee(); System.out.println("\t"+employee.getEmpId()+"\t"+employee.getEmpName()); } } } }
10.运行MybatisTest4类,结果如下:
1 项目一 1 张三 2 李四 3 王五 4 赵六 2 项目二 5 韩七 6 小明 7 小红 8 小兰 3 项目三 9 小绿 10 李磊 11 韩梅梅 12 简 4 项目四 3 王五 4 赵六 6 小明 9 小绿
11.修改Employee类,添加关于PeItem类的部分
package com.mybatis.demo.pojo; import java.util.List; public class Employee { private int empId; private String empName; private Department department; private List<PeItem> peItems; public int getEmpId() { return empId; } public void setEmpId(int empId) { this.empId = empId; } public String getEmpName() { return empName; } public void setEmpName(String empName) { this.empName = empName; } public Department getDepartment() { return department; } public void setDepartment(Department department) { this.department = department; } public List<PeItem> getPeItems() { return peItems; } public void setPeItems(List<PeItem> peItems) { this.peItems = peItems; } }
12.修改Employee.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.mybatis.demo.pojo"> <select id="getEmployeeById" parameterType="int" resultMap="employeeResultMap"> select emp.emp_id, emp.emp_name, dept.dept_id, dept.dept_name from t_employee emp left join t_department dept on emp.dept_id=dept.dept_id where emp.emp_id=#{value} </select> <resultMap type="Employee" id="employeeResultMap"> <id column="emp_id" property="empId"/> <result column="emp_name" property="empName"/> <association property="department" javaType="Department"> <id column="dept_id" property="deptId"/> <result column="dept_name" property="deptName"/> </association> </resultMap> <select id="getAllEmployeeDetail" resultMap="employeeResultMap2"> select emp.emp_id, emp.emp_name, dept.dept_id, dept.dept_name, pro.pro_id, pro.pro_name from t_employee emp left join t_department dept on emp.dept_id=dept.dept_id left join t_peitem item on emp.emp_id=item.emp_id left join t_project pro on item.pro_id=pro.pro_id </select> <resultMap type="Employee" id="employeeResultMap2"> <id column="emp_id" property="empId"/> <result column="emp_name" property="empName"/> <association property="department" javaType="Department"> <id column="dept_id" property="deptId"/> <result column="dept_name" property="deptName"/> </association> <collection property="peItems" ofType="PeItem"> <association property="project" javaType="Project"> <id column="pro_id" property="proId"/> <result column="pro_name" property="proName"/> </association> </collection> </resultMap> </mapper>
13,修改MybatisTest4类,封装查询所有项目详情信息的方法,添加查询所有员工的所在部门和参与项目的详细信息的方法
package com.mybatis.demo.test; import java.io.IOException; import java.io.InputStream; import java.util.List; 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 com.mybatis.demo.pojo.Department; import com.mybatis.demo.pojo.Employee; import com.mybatis.demo.pojo.PeItem; import com.mybatis.demo.pojo.Project; public class MybatisTest4 { public static void main(String[] args) throws IOException { //加载mybatis的配置文件 InputStream inputStream = Resources.getResourceAsStream("mybatisConfig.xml"); //获取SqlSessionFactory对象 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); //使用SqlSessionFactory对象创建SqlSession对象 SqlSession sqlSession = sqlSessionFactory.openSession(); //getAllProjectDetail(sqlSession); getAllEmployeeDetail(sqlSession); } /** * 获取所有员工的部门和参与项目的详情信息 * @param sqlSession */ private static void getAllEmployeeDetail(SqlSession sqlSession) { List<Employee> employees = sqlSession.selectList("getAllEmployeeDetail"); for (Employee employee : employees) { System.out.println(employee.getEmpId()+"\t"+employee.getEmpName()); Department department = employee.getDepartment(); if(department!=null){ System.out.println("\t员工的部门信息为:"+department.getDeptId()+"\t"+department.getDeptName()); } List<PeItem> peItems = employee.getPeItems(); System.out.println("\t员工参与的项目信息为:"); for (PeItem peItem : peItems) { Project project = peItem.getProject(); System.out.println("\t\t"+project.getProId()+"\t"+project.getProName()); } } } /** * 查询所有项目的详情信息 * @param sqlSession */ private static void getAllProjectDetail(SqlSession sqlSession) { List<Project> projects = sqlSession.selectList("getAllProjectDetail"); for (Project project : projects) { System.out.println(project.getProId()+"\t"+project.getProName()); List<PeItem> peItems = project.getPeItems(); for (PeItem peItem : peItems) { Employee employee = peItem.getEmployee(); System.out.println("\t"+employee.getEmpId()+"\t"+employee.getEmpName()); } } } }
14.运行MybatisTest4类,结果如下:
1 张三 员工的部门信息为:1 行政部 员工参与的项目信息为: 1 项目一 2 李四 员工的部门信息为:1 行政部 员工参与的项目信息为: 1 项目一 3 王五 员工的部门信息为:1 行政部 员工参与的项目信息为: 1 项目一 4 项目四 4 赵六 员工的部门信息为:2 财务部 员工参与的项目信息为: 1 项目一 4 项目四 5 韩七 员工的部门信息为:2 财务部 员工参与的项目信息为: 2 项目二 6 小明 员工的部门信息为:2 财务部 员工参与的项目信息为: 2 项目二 4 项目四 7 小红 员工的部门信息为:3 信息部 员工参与的项目信息为: 2 项目二 8 小兰 员工的部门信息为:3 信息部 员工参与的项目信息为: 2 项目二 9 小绿 员工的部门信息为:3 信息部 员工参与的项目信息为: 3 项目三 4 项目四 10 李磊 员工的部门信息为:4 设计部 员工参与的项目信息为: 3 项目三 11 韩梅梅 员工的部门信息为:4 设计部 员工参与的项目信息为: 3 项目三 12 简 员工的部门信息为:4 设计部 员工参与的项目信息为: 3 项目三
