Java笔记之Mybatis(四):一对多、多对一

1.数据库新建t_department表

USE  keeper;
DROP TABLE IF EXISTS `t_department`;
CREATE TABLE `t_department`  (
  `dept_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '部门ID',
  `dept_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门名称',
  PRIMARY KEY (`dept_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

2.表t_department导入数据

USE keeper;
INSERT INTO `t_department` VALUES (null, '行政部');
INSERT INTO `t_department` VALUES (null, '财务部');
INSERT INTO `t_department` VALUES (null, '信息部');
INSERT INTO `t_department` VALUES (null, '设计部');

3.数据库中新建t_employee表

复制代码
USE keeper;
DROP TABLE IF EXISTS `t_employee`;
CREATE TABLE `t_employee`  (
  `emp_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '员工ID',
  `emp_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工名称',
  `dept_id` int(11) NULL DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`emp_id`) USING BTREE,
  INDEX `fk_deptId`(`dept_id`) USING BTREE,
  CONSTRAINT `fk_deptId` FOREIGN KEY (`dept_id`) REFERENCES `t_department` (`dept_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
复制代码

4.表t_employee导入数据

复制代码
USE keeper;
INSERT INTO `t_employee` VALUES (null, '张三', 1);
INSERT INTO `t_employee` VALUES (null, '李四', 1);
INSERT INTO `t_employee` VALUES (null, '王五', 1);
INSERT INTO `t_employee` VALUES (null, '赵六', 2);
INSERT INTO `t_employee` VALUES (null, '韩七', 2);
INSERT INTO `t_employee` VALUES (null, '小明', 2);
INSERT INTO `t_employee` VALUES (null, '小红', 3);
INSERT INTO `t_employee` VALUES (null, '小兰', 3);
INSERT INTO `t_employee` VALUES (null, '小绿', 3);
INSERT INTO `t_employee` VALUES (null, '李磊', 4);
INSERT INTO `t_employee` VALUES (null, '韩梅梅', 4);
INSERT INTO `t_employee` VALUES (null, '简', 4);
复制代码

5.在com.mybatis.demo.pojo包下新建Department类,这是一的一方

复制代码
package com.mybatis.demo.pojo;

import java.util.List;

public class Department {
    private int deptId;
    private String deptName;
    private List<Employee> employees;
    public int getDeptId() {
        return deptId;
    }
    public void setDeptId(int deptId) {
        this.deptId = deptId;
    }
    public String getDeptName() {
        return deptName;
    }
    public void setDeptName(String deptName) {
        this.deptName = deptName;
    }
    public List<Employee> getEmployees() {
        return employees;
    }
    public void setEmployees(List<Employee> employees) {
        this.employees = employees;
    } 
    
}
复制代码

6.在com.mybatis.demo.pojo包下新建Employee类,这是多的一方

复制代码
package com.mybatis.demo.pojo;

public class Employee {
    private int empId;
    private String empName;
    private Department department;
    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;
    }
    
}
复制代码

7.在com.mybatis.demo.pojo包下新建Department.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="getDepartmentById" parameterType="int" resultMap="departmentResultMap">
    select 
        dept.dept_id,
        dept.dept_name,
        emp.emp_id,
        emp.emp_name 
    from 
        t_department dept 
    left join 
        t_employee emp 
    on 
        dept.dept_id=emp.dept_id 
    where 
        dept.dept_id=#{value}
    </select>
    <resultMap type="Department" id="departmentResultMap">
        <id column="dept_id" property="deptId"/>
        <result column="dept_name" property="deptName"/>
        <collection property="employees" ofType="Employee" javaType="java.util.ArrayList">
            <id column="emp_id" property="empId"/>
            <result column="emp_name" property="empName"/>
        </collection>
    </resultMap>
</mapper>
复制代码

8.在com.mybatis.demo.pojo包下新建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>
</mapper>
复制代码

9.修改mybatisConfig.xml,添加Employee.xml和Department.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"/>
    </mappers>
</configuration>
复制代码

10.在com.mybatis.demo.test包下新建MybatisTest3类

复制代码
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;

public class MybatisTest3 {

    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();
        int empId=1;
        getEmployeeById(sqlSession,empId);
        int deptId=1;
        getDepartmentById(sqlSession, deptId);
    }
    /**
     * 根据部门ID查询部门信息和包含的员工信息
     * @param sqlSession
     * @param deptId
     */
    private static void getDepartmentById(SqlSession sqlSession, int deptId) {
        Department department=sqlSession.selectOne("getDepartmentById", deptId);
        if(department!=null){
            System.out.println("部门信息为:");
            System.out.println("\t"+department.getDeptId()+"\t"+department.getDeptName());
            System.out.println("部门员工信息为:");
            List<Employee> employees = department.getEmployees();
            for (Employee employee : employees) {
                System.out.println("\t"+employee.getEmpId()+"\t"+employee.getEmpName());    
            }
        }
    }
    
    /**
     * 根据员工ID获取员工信息及对应的部门信息
     * @param sqlSession
     * @param empId
     */
    private static void getEmployeeById(SqlSession sqlSession,int empId) {
        Employee employee = sqlSession.selectOne("getEmployeeById", empId);
        if(employee!=null){
            System.out.println("员工信息为:");
            System.out.println("\t"+employee.getEmpId()+"\t"+employee.getEmpName());
            Department department = employee.getDepartment();
            System.out.println("员工对应的部门信息为:");
            System.out.println("\t"+department.getDeptId()+"\t"+department.getDeptName());
        }
        
    }

}
复制代码

11.运行MybatisTest3类,结果如下:

复制代码
员工信息为:
    1    张三
员工对应的部门信息为:
    1    行政部
部门信息为:
    1    行政部
部门员工信息为:
    1    张三
    2    李四
    3    王五
复制代码

12.配置说明

　　(1)Department.xml的一些说明:

复制代码
<!-- resultMap属性指定使用哪个resultMap进行关系映射 -->
<select id="getDepartmentById" parameterType="int" resultMap="departmentResultMap">
    select 
        dept.dept_id,
        dept.dept_name,
        emp.emp_id,
        emp.emp_name 
    from 
        t_department dept 
    left join 
        t_employee emp 
    on 
        dept.dept_id=emp.dept_id 
    where 
        dept.dept_id=#{value}
    </select>
    <!--  声明resultMap,type属性指定结果类型,id属性指定resultMap的唯一标识 -->
    <resultMap type="Department" id="departmentResultMap">
        <!--  id标签指定主键,column属性指定查询语句的字段名,property属性指定结果类型与之对应的属性名 -->
        <id column="dept_id" property="deptId"/>
        <!--  result表指定非主键字段和属性的对应 -->
        <result column="dept_name" property="deptName"/>
        <!--  collection标签指定一对多关系中多的一方,property属性指定一的一方中定义的多的一方的属性名称,ofType属性指定多的一方的类型,javaType属性指定多的一方的属性类型 -->
        <collection property="employees" ofType="Employee" javaType="java.util.ArrayList">
            <id column="emp_id" property="empId"/>
            <result column="emp_name" property="empName"/>
        </collection>
    </resultMap>
复制代码

　　(2)Employee.xml的一些说明

复制代码
<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属性指定多的一方中定义的一的一方的属性名称,javaType属性指定一的一方的类型 -->
        <association property="department" javaType="Department">
            <id column="dept_id" property="deptId"/>
            <result column="dept_name" property="deptName"/>
        </association>
    </resultMap>
复制代码

 

posted @   安徒生敲代码  阅读(228)  评论(0)  编辑  收藏  举报
编辑推荐：
· AI与.NET技术实操系列：基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
阅读排行：
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 零经验选手，Compose 一天开发一款小游戏！
· 一起来玩mcp_server_sqlite，让AI帮你做增删改查！！
历史上的今天：
2019-03-26 Scanner
点击右上角即可分享
微信分享提示