mybatis_resultMap_级联查询_分布查询_懒加载配置

最新推荐文章于 2023-05-12 17:57:35 发布

拂晓神剑zzz

最新推荐文章于 2023-05-12 17:57:35 发布

阅读量835

点赞数

分类专栏：学习笔记文章标签： sql 数据库 database

本文链接：https://blog.csdn.net/weixin_44847885/article/details/122822234

版权

学习笔记专栏收录该内容

79 篇文章 0 订阅

订阅专栏

1.DAO层

Employee类

package mybatis.bean;

public class Employee {
    private Integer id;
    private String lastName;
    private String email;
    private String gender;
    private Department dept;

    public Department getDept() {
        return dept;
    }

    public Employee() {
    }

    public Employee(Integer id,String lastName, String email, String gender) {
        this.id = id;
        this.lastName = lastName;
        this.email = email;
        this.gender = gender;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", lastName='" + lastName + '\'' +
                ", email='" + email + '\'' +
                ", gender='" + gender + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }
}

Department类

package mybatis.bean;

public class Department {
    private Integer id;
    private String departmentName;

    @Override
    public String toString() {
        return "department{" +
                "id=" + id +
                ", departmentName='" + departmentName + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getDepartmentName() {
        return departmentName;
    }

    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }

    public Department(Integer id, String departmentName) {
        this.id = id;
        this.departmentName = departmentName;
    }

    public Department() {
    }


}

2.接口

EmployeeMapperPlus接口

public interface EmployeeMapperPlus {

    //测试 ResultMap
    Employee getEmployeeById(Integer id);
    
    //级联查询
    Employee getEmployeeByIdTest(Integer id);
    
    //分布查询
    Employee getEmployeeByIdStep(Integer id);
}

DepartmentMapper 接口

public interface DepartmentMapper {
    Department getDepartmentById(Integer id);
}

EmployeeMapper接口

package mybatis.mapper;

import mybatis.bean.Employee;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface EmployeeMapper {
    //查询
    Employee getEmployee(Integer id);
    //传多个值查询时,因为参数被封装到Map集合,无序无法确定是那个参数,单个传参不影响,通过设置注解获取
    Employee getEmployeeTest(@Param("id") Integer id, @Param("lastName") String lastName);
    //传入map集合
    Employee getEmpAfferentMap(Map<String,Object> map);
    //查询多条语句封装到List
    List<Employee> getEmpAfferentList(String lastName);

    //返回Map集合 key是字段名 value是参数
    Map<String,Object> getEmpAfferentReturnMap(Integer id);

    //返回 Map集合 key 为 主键 ，value 为 POJO对象
    @MapKey("lastName")
    Map<Integer,Employee> getEmpReturnPrimaryKeyAndEmp(String lastName);

    //添加
    void addEmployee(Employee employee);
    //修改
    void updateEmployee(Employee employee);
    //删除
    void deleteEmployee(Integer id);

}

3.全局配置 resources文件下

mybatis-config.xml

<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">


<configuration>

    <properties resource="dbconfig.properties"></properties>

    <!--开启驼峰映射 A_Column aColumn -->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING" />
<!--        <setting name="logImpl" value="LOG4J"/>-->

        <!--开启驼峰映射-->
<!--        <setting name="mapUnderscoreToCamelCase" value="true"/>-->

        <!--按需加载配置-->
        <!--懒加载（延时加载） 有需求的时加载需要的属性(已关联属性) 否则不加载属性-->
        <!--true:关联的属性在需要的时候被加载 默认为false-->
        <setting name="lazyLoadingEnabled" value="true"/>

        <!--侵入懒加载 需要加载属性时会加载全部属性 否则属性在需要时加载-->
        <!--true开启：加载所有属性   flase禁用：则按需加载-->
        <setting name="aggressiveLazyLoading" value="false"/>

    </settings>

    <!-- 环境配置 -->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <!-- 数据库连接相关配置 ,这里动态获取config.properties文件中的内容-->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}" />
                <property name="url" value="${jdbc.url}" />
                <property name="username" value="${jdbc.username}" />
                <property name="password" value="${jdbc.password}" />
            </dataSource>
        </environment>
    </environments>


    <!-- mapping文件路径配置 -->
    <mappers>
<!--        <mapper resource="mapper/EmployeeMapper.xml"/>-->
        <mapper resource="mapper/EmployeeMapperPlus.xml"/>
        <mapper resource="mapper/DepartmentMapper.xml"/>
        <!--class 注册接口-->
        <!--1.没有sql映射文件 注解实现-->
<!--        <mapper class="mybatis.mapperannotation.EmployeeMapperAnnotation"></mapper>-->

        <!--2.有sql映射文件 idea 报错-->
<!--        <mapper class="mybatis.mapper.EmployeeMapper"></mapper>-->

        <!--批量注册-->
<!--        <package name="mybatis.mapper"/>-->

    </mappers>

</configuration>

dbconfig.properties

jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/mybatis
jdbc.username = root
jdbc.password = root

log4j.properties

log4j.rootLogger=DEBUG,A1

log4j.appender.A1=org.apache.log4j.ConsoleAppender
log4j.appender.A1.layout=org.apache.log4j.PatternLayout
log4j.appender.A1.layout.ConversionPattern=[%t] [%c]-[%p] %m%n

4.resources.mapper文件下 mapper映射文件配置

EmployeeMapperPlus.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指定Dao接口的完整类名
mybatis会依据这个接口动态创建一个实现类去实现这个接口，
而这个实现类是一个Mapper对象-->

<mapper namespace="mybatis.mapper.EmployeeMapperPlus">

    <!--映射规则-->
    <resultMap id="durunwu" type="mybatis.bean.Employee">
        <!--主键 primaryKey 的映射-->
        <id column="id" property="id"/>

        <result column="last_name" property="lastName"/>
        <result column="email" property="email"/>
        <result column="gender" property="gender"/>
    </resultMap>

    <select id="getEmployeeById" resultMap="durunwu">
        select * from tbl_employee where id = #{id}
    </select>

    <!--级联查询-->
    <!--注意：使用别名进行配置-->
    <resultMap id="MyDifMap" type="mybatis.bean.Employee">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>
        <result column="email" property="email"/>

        <!--指定关联的javaBean-->
        <association property="dept" javaType="mybatis.bean.Department">
            <result column="did" property="id"/>
            <result column="dept_name" property="departmentName"/>
        </association>

    </resultMap>

    <select id="getEmployeeByIdTest" resultMap="MyDifMap">
        SELECT emp.id id,
               emp.last_name last_name,
               emp.gender gender,
               emp.email email,
               emp.dept_id eid,
               dept.id did,
               dept.dept_name dept_name
        FROM tbl_employee emp,tbl_dept dept
        WHERE emp.dept_id = dept.id AND emp.dept_id = #{id}
    </select>

    <!--分布查询-->
    <!--    id  last_name  gender  email  dept_id  -->
    <resultMap id="step" type="mybatis.bean.Employee">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>
        <result column="email" property="email"/>
        <association property="dept"
                     select="mybatis.mapper.DepartmentMapper.getDepartmentById"
                     column="dept_id">
        </association>

    </resultMap>
    <select id="getEmployeeByIdStep" resultMap="step">
        select * from tbl_employee where id = #{id}
    </select>
    
</mapper>

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指定Dao接口的完整类名
mybatis会依据这个接口动态创建一个实现类去实现这个接口，
而这个实现类是一个Mapper对象-->

<mapper namespace="mybatis.mapper.DepartmentMapper">

    <select id="getDepartmentById" resultType="mybatis.bean.Department">
        select id,dept_name departmentName from tbl_dept where id = #{id}
    </select>

</mapper>

5.数据库

tbl_employee表

CREATE TABLE `tbl_employee` (
  `id` int NOT NULL AUTO_INCREMENT,
  `last_name` varchar(255) DEFAULT NULL,
  `gender` char(1) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `dept_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tbl_emp_dept` (`dept_id`),
  CONSTRAINT `tbl_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `tbl_dept` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb3

tbl_employee表数据

    id  last_name  gender  email       dept_id  
------  ---------  ------  ----------  ---------
     1  李白         男       libai@haha          1
     7  runwu      男       runwu@123           3
     8  effect1    女       effect@1            1
     9  effect2    女       effect@2            2
    10  effect3    女       effect@3            1

tbl_dept表

CREATE TABLE `tbl_dept` (
  `id` int NOT NULL AUTO_INCREMENT,
  `dept_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3

tbl_dept表数据

    id  dept_name  
------  -----------
     1  研发部  
     2  测试部  
     3  运维部

6.测试类

package mybatis.test;

import mybatis.bean.Employee;
import mybatis.mapper.EmpTest;
import mybatis.mapper.EmployeeMapper;
import mybatis.mapper.EmployeeMapperPlus;
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 org.junit.jupiter.api.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MyBatisTest {

    //获取 SqlSessionFactory
    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        return sqlSessionFactory;
    }

    @Test
    public void test01() throws IOException {
        SqlSession sqlSession = getSqlSessionFactory().openSession(true);
        try{
            EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
            //查询
//            System.out.println(mapper.getEmployee(1));
//            System.out.println(mapper.getEmployeeTest(1,"李白"));

            //传入两个查询参数 id 和 lastName ，把这两个参数封装到 Map ，在把 Map传入 mybtis会自动从Map解析出参数
//            Map<String, Object> map = new HashMap<>();
//            map.put("id",1);
//            map.put("lastName","李白");
//            Employee empAfferentMap = mapper.getEmpAfferentMap(map);
//            System.out.println(empAfferentMap.toString());

//            List<Employee> empAfferentList = mapper.getEmpAfferentList("%e%");
//            for(Employee employee : empAfferentList) System.out.println(employee);
//            System.out.println("查询成功！");

            //查询结果封装到Map返回
//            Map<String, Object> empAfferentReturnMap = mapper.getEmpAfferentReturnMap(1);
//            System.out.println(empAfferentReturnMap);


            //Map<Primarykey,Employee>
//            Map<Integer, Employee> empReturnPrimaryKeyAndEmp = mapper.getEmpReturnPrimaryKeyAndEmp("%e%");
//            System.out.println(empReturnPrimaryKeyAndEmp);

            //添加
//            Employee employee = new Employee(null,"runwu", "runwu@123", "男");
//            mapper.addEmployee(employee);
//            System.out.println(employee.getId());
//            System.out.println("添加成功!");

            //修改
//            Employee employee = new Employee(1,"李白", "libai@haha", "男");
//            mapper.updateEmployee(employee);
//            System.out.println("修改完成!");

            //删除
//            mapper.deleteEmployee(6);
//            System.out.println("删除成功!");

//            sqlSession.commit();//手动提交
        }finally {
            sqlSession.close();
        }
    }


    //自定义结果映射集 resultMap
    @Test
    public void testResultMap() throws IOException{
        SqlSession sqlSession = getSqlSessionFactory().openSession(true);
        try {
//            EmployeeMapperPlus sqlSessionMapper = sqlSession.getMapper(EmployeeMapperPlus.class);
//            Employee employeeById = sqlSessionMapper.getEmployeeById(1);
//            System.out.println(employeeById);

//            EmployeeMapperPlus mapper = sqlSession.getMapper(EmployeeMapperPlus.class);
//            Employee employee = mapper.getEmployeeByIdTest(2);
//            System.out.println(employee);
//            System.out.println(employee.getDept() );

            //分布查询
            EmployeeMapperPlus mapper = sqlSession.getMapper(EmployeeMapperPlus.class);
            Employee employeeByIdStep = mapper.getEmployeeByIdStep(9);
            System.out.println(employeeByIdStep);
            System.out.println(employeeByIdStep.getDept());
        }finally {
            sqlSession.close();
        }
    }
}

7.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.atguigu</groupId>
    <artifactId>MyBatis_demo3</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

    <dependencies>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.49</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.0</version>
        </dependency>

        <!-- junit测试包 -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter</artifactId>
            <version>RELEASE</version>
            <scope>compile</scope>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.18</version>
        </dependency>

        <!-- 日志 -->
<!--        <dependency>-->
<!--            <groupId>log4j</groupId>-->
<!--            <artifactId>log4j</artifactId>-->
<!--            <version>1.2.17</version>-->
<!--        </dependency>-->

    </dependencies>

</project>