使用Mybatis实现多表一对多查询实例之关联查询方式(含代码及解释)

一对多查询之关联查询实例

项目目录结构(maven):

一对多查询关联查询方式灵魂之处解析:

DepartmentMapper.xml

 

创建表t_dept:(一方)

创建表t_emp:(多方)

 

在Maven配置文件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.xiongluoluo</groupId>
    <artifactId>onetomany</artifactId>
    <version>1.0.0</version>

    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.8</version>
            <scope>provided</scope>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/junit/junit -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
        </dependency>

        <!--日志包-->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.25</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.7.25</version>
            <scope>test</scope>
        </dependency>

    </dependencies>

</project>

创建实体类Department:

package com.xiongluoluo.bean;

import lombok.*;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by IBM on 2019/12/24.
 */
@Setter
@Getter
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class Department {

    private Integer id ;
    private String name ;
    private List<Employee> employeeList = new ArrayList<Employee>();
    /*private List<Employee> employeeList;*/
    /*这个地方new不new都可以,没什么影响.因为employeeList是作为一个属性.具体情况具体分析*/

}

创建实体类Employee:

package com.xiongluoluo.bean;

import lombok.*;

/**
 * Created by IBM on 2019/12/24.
 */
@Setter
@Getter
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Employee {

    private Integer id ;
    private String name ;

}

 

创建mybatis配置文件Mybatis.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"/>
    <!--这里用的是resource属性,导入外部properties文件-->
    <typeAliases>
        <package name="com.xiongluoluo.bean"/>
    </typeAliases>
    
    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driverClassName}"/>
                <!--这里是driver-->
                <!--driverClassName是properties文件中的变量,通过${}取其对应的支持-->
                <!--下面同理-->
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="com/xiongluoluo/mapper/EmployeeMapper.xml"/>
        <mapper resource="com/xiongluoluo/mapper/DepartmentMapper.xml"/>
        <!--这里是斜线(因为是目录),不是点,不是点,不是点,你能不能不要在一个错误上躺平,每天忘提交事务,每天写...,唉-->
        <!--Mybatis的配置文件,也就是这个文件,是程序的入口.我们配置的mapper文件如果不在这里注册
        那么程序是读不到的.所以一定别忘记在这里添加.
        这里用的也是resource属性,引入外部的mapper.xml属性-->
    </mappers>
</configuration

db.properties文件:

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///mydb1228
username=root
password=1234

log4j.properties文件:

# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# MyBatis logging configuration...
log4j.logger.com.xiongluoluo.mapper=TRACE
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

 

创建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="com.xiongluoluo.mapper.DepartmentMapper">


    <!--一对多额外SQL查询
    <resultMap id="departmentResultMap" type="Department">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <!–collection标签:用户处理集合类型的关联对象
        property:把结果集封装成那个属性
        ofType:集合的泛型
        select:发送那一条Sql查询关联的对象
        column:查询部门的结果集中的那个列作为参数来查询关联信息–>
        <collection property="employeeList"
                    column="id" ofType="Employee"
                    select="com.xiongluoluo.mapper.EmployeeMapper.selectEmoloyeeByDeptId"/>
    </resultMap>

    <select id="selectDepartmentById" resultMap="departmentResultMap" parameterType="int">
        select id,name from t_dept where id=#{id}
    </select>-->

    <resultMap id="departmentResultMap" type="Department">
        <id column="did" property="id"/>
        <result column="name" property="name"/>
        <!--property:关联的属性的名字
        ofType:关联属性的泛型
        这里不用写column-->
        <collection property="employeeList" ofType="Employee">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
        </collection>
    </resultMap>
    <!--一对多关联查询-->
    <!--不管是额外SQL查询方式,还是关联查询方式,都必须写resultMap,不然关系无法体现-->
    <select id="selectDepartmentById" parameterType="int" resultMap="departmentResultMap">
        select e.id,e.name,d.id did,d.name from t_dept d left join t_emp e on d.id=e.dept_id where d.id=#{id}
    </select>
</mapper>

创建DepartmentMapper.java:

package com.xiongluoluo.mapper;

import com.xiongluoluo.bean.Department;

/**
 * Created by Administrator on 2019/12/28 0028.
 */
public interface DepartmentMapper {
    public Department selectDepartmentById(int id);
}

 

创建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.xiongluoluo.mapper.EmployeeMapper">
    <!--一对多额外SQL查询的额外SQL查询语句-->
    <select id="selectEmoloyeeByDeptId"  parameterType="int" resultType="Employee">
        select id,name,dept_id from t_emp where dept_id=#{id}
    </select>

</mapper>

创建DepartmentService.java:

package com.xiongluoluo.service;

import com.xiongluoluo.bean.Department;

/**
 * Created by Administrator on 2019/12/28 0028.
 */
public interface DepartmentService {
    public Department findDepartmentById(int id);
}

创建DepartmentServiceImpl.java:

package com.xiongluoluo.service.impl;

import com.xiongluoluo.bean.Department;
import com.xiongluoluo.mapper.DepartmentMapper;
import com.xiongluoluo.service.DepartmentService;
import com.xiongluoluo.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;

/**
 * Created by Administrator on 2019/12/28 0028.
 */
public class DepartmentServiceImpl implements DepartmentService {
    public Department findDepartmentById(int id) {
        SqlSession sqlSession = MybatisUtil.getSession();
        DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);
        Department department = mapper.selectDepartmentById(id);
        return department;
    }
}

 

创建测试类:

package com.xiongluoluo.test;

import com.xiongluoluo.bean.Department;
import com.xiongluoluo.service.DepartmentService;
import com.xiongluoluo.service.impl.DepartmentServiceImpl;
import org.junit.Test;

/**
 * Created by Administrator on 2019/12/28 0028.
 */
public class MybatisTest {
    /*一对多查询-----测试*/
    @Test
    public void test1(){
        DepartmentService departmentService = new DepartmentServiceImpl();
        Department department = departmentService.findDepartmentById(2);
        System.out.println(department);
    }
}