Mybatis手动配置ORM，不用自动ORM等操作

男娘i 2024-03-23 13:36 48阅读 0赞

**目录**

一、解决mapper.xml存放在resources以外路径中的读取问题

二、模糊查询

三、主键回填

插入之后：（主键是自增的）

插入之前：

四、MyBatis自动ORM失效

结果映射（ResultMap - 查询结果的封装规则）：

或者起别名：在SQL中使用 as 为查询字段添加列别名，以匹配属性名。

五、MyBatis处理关联关系-多表连接【重点】

1.一对一

2.一对多（以部门员工为例，一部门多个员工）

3、多对多（学生、科目、中间表）

## 一、解决mapper.xml存放在resources以外路径中的读取问题 ##

在pom.xml文件最后**追加< build >**标签，以便可以将xml文件复制到classes中，并在程序运行时正确读取。

<build>
            
            <resources>
                <resource>
                    <directory>src/main/java</directory>
                    <includes>
                        <include>**/*.xml</include>
                    </includes>
                </resource>
                <resource>
                    <directory>src/main/resources</directory>
                    <includes>
                        <include>**/*.xml</include>
                        <include>**/*.properties</include>
                        <include>**/*.ini</include>
                    </includes>
                </resource>
            </resources>
        </build>

## 二、模糊查询 ##

用 **concat('%',\#\{keyword\},'%')**

<mapper namespace="com.zhp.mapper.UserMapper">
        <select id="selectUsersByKeyword" resultType="user">
            SELECT * FROM t_users 
      		WHERE name LIKE concat('%',#{keyword},'%') 
        </select>
    </mapper>

## 三、主键回填 ##

标签：< selectKey id="" parameterType="" order="AFTER|BEFORE">

#### 插入之后：（主键是自增的） ####

<mapper namespace="com.zhp.mapper.ProductMapper">
        <insert id="insertProduct">
    
            /*主键回填    在插入语句执行之后  查询刚刚插入的 记录 的 id  赋值给 productId */
            <selectKey keyProperty="productId" resultType="int" order="AFTER" >
                select last_insert_id()
            </selectKey>
    
            insert into t_product (productId,productName,brand) values (null,#{productName},#{brand})
        </insert>
    </mapper>

#### 插入之前： ####

<mapper namespace="com.glls.mybatis.mapper.OrderMapper">
    
        <insert id="insertOrder">
            
            <selectKey keyProperty="id" resultType="string" order="BEFORE">
                select REPLACE(UUID(),'-','') 
            </selectKey>
    
            insert into t_order2 (id,name) values (#{id},#{name});
        </insert>
    </mapper>

## 四、MyBatis自动ORM失效 ##

当**表的字段和实体类的属性名不同时**，自动ORM就会失效，那么这时候我们就可以通过手动配置ORM来解决了

![9621c7b52e384fcbb85c32e506f6b15e.png][]

#### 结果映射（ResultMap - 查询结果的封装规则）： ####

通过 **< resultMap id="" type="" >** 映射，匹配列名与属性名。

#### 或者起别名：在SQL中使用 as 为查询字段添加列别名，以匹配属性名。 ####

<select id="selectManagerByIdAndPwd" resultType="com.glls.mybatis.pojo.Manager">
            select mgr_id as id,mgr_name as name,mgr_pwd as password from t_managers 
    where mgr_id = #{id} and mgr_pwd=#{pwd}
        </select>

## 五、MyBatis处理关联关系-多表连接【`重点`】 ##

<table style="width:560px;"> 
 <tbody> 
  <tr> 
   <td style="width:557px;"> <p>实体间的关系：关联关系（拥有 has、属于 belong）</p> 
    <ul> 
     <li> <p>OneToOne：一对一关系（Passenger--- Passport）</p> </li> 
     <li> <p>OneToMany：一对多关系（Employee --- Department）</p> </li> 
     <li> <p>ManyToMany：多对多关系（Student --- Subject）</p> </li> 
    </ul></td> 
  </tr> 
 </tbody> 
</table>

![1631fd0bc30b417aa2cd4dccd9335f6e.png][]

### 1.一对一 ###

数据准备

DROP TABLE IF EXISTS `t_passenger`;
    
    CREATE TABLE `t_passenger` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(32) DEFAULT NULL,
      `sex` varchar(32) DEFAULT NULL,
      `birthday` date DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='乘客';
    
    /*Data for the table `t_passenger` */
    
    insert  into `t_passenger`(`id`,`name`,`sex`,`birthday`) values (1,'zs','男','2021-07-29'),(2,'lss','女','2021-07-28');
    
    
    DROP TABLE IF EXISTS `t_passport`;
    
    CREATE TABLE `t_passport` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `nationality` varchar(32) DEFAULT NULL,
      `expire` date DEFAULT NULL,
      `passenger_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
    
    /*Data for the table `t_passport` */
    
    insert  into `t_passport`(`id`,`nationality`,`expire`,`passenger_id`) values (1,'中国','2023-07-29',1),(2,'韩国','2023-07-28',2);

实体类

public class Passenger {
        private Integer id;
        private String name;
        private String sex;
        private Date birthday;
        private Passport passport;
    }
    
    public class Passport {
        private Integer id;
        private String nationality;
        private Date expire;
        private  Integer passenger_id;
    }

.xml中的sql和**手动的ORM**

<mapper namespace="com.glls.mybatis.mapper.PassengerMapper">
    
      	
        <resultMap id="passengerResultMap" type="passenger">
            <id property="id" column="id"/>
            <result property="name" column="name" />
            <result property="sex" column="sex" />
            <result property="birthday" column="birthday" />
    
          		 
            <association property="passport" javaType="passport">
                 <id property="id" column="passportId"></id>
                <result property="nationality" column="nationality"></result>
                <result property="expire" column="expire"></result>
                <result property="passenger_id" column="passenger_id"></result>
            </association>
        </resultMap>
    
      						  	
        <select id="selectPassengerById" resultMap="passengerResultMap">
            
            SELECT p1.id , p1.name , p1.sex , p1.birthday , p2.id as passportId , p2.nationality , p2.expire 			, p2.passenger_id
            FROM t_passengers p1 LEFT JOIN t_passports p2
            ON p1.id = p2.passenger_id
            WHERE p1.id = #{id}
        </select>
    </mapper>

注意：指定“一方”关系时（对象），使用 **< association javaType=" ">**

### 2.一对多（以部门员工为例，一部门多个员工） ###

数据准备

CREATE TABLE `t_departments` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(32) DEFAULT NULL,
      `location` varchar(32) DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
    
    /*Data for the table `t_departments` */
    
    insert  into `t_departments`(`id`,`name`,`location`) values (1,'研发部','杭州'),(2,'市场部','上海');
    
    CREATE TABLE `t_departments` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(32) DEFAULT NULL,
      `location` varchar(32) DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
    
    /*Data for the table `t_departments` */
    
    insert  into `t_departments`(`id`,`name`,`location`) values (1,'研发部','杭州'),(2,'市场部','上海');

实体类

public class Department {
        private Integer id;
        private String name;
        private String location;
        private List<Employee> empls;
    }
    public class Employee {
        private Integer eid;
        private String name;
        private Double salary;
        private Integer dept_id;
    }

.xml中的sql和**手动的ORM**

**指定“多方”关系时（集合），使用< collection ofType="" >**

<mapper namespace="com.zhp.mapper.DepartmentMapper">
    
        <resultMap id="deps" type="department">
            <id property="id" column="id"/>
            <result property="name" column="name"/>
            <result property="location" column="location"/>
            
                    
            <collection property="empls" ofType="employee">
                <id property="eid" column="eid"/>
                <result property="name" column="ename"/>
                <result property="salary" column="salary"/>
                <result property="dept_id" column="dept_id"/>
            </collection>
        </resultMap>
    
        
        <select id="selectDepartmentById" resultMap="deps">
            select td.*, eid, te.name as ename, salary, dept_id
            from t_departments td join t_employees te on td.id = te.dept_id
            where td.id = #{id}
        </select>
    </mapper>

### 3、多对多（学生、科目、中间表） ###

![1ef0602f9e1340818ae0f614a0c31deb.png][]

数据准备

CREATE TABLE `t_students` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(32) DEFAULT NULL,
      `sex` varchar(32) DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
    
    insert  into `t_students`(`id`,`name`,`sex`) values (1,'Tom','M'),(2,'Jack','M'),(3,'Marry','F'),(4,'Annie','F');
     
     
    CREATE TABLE `t_subjects` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(32) DEFAULT NULL,
      `grade` varchar(32) DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
    
    insert  into `t_subjects`(`id`,`name`,`grade`) values (1,'JavaSe','1'),(2,'JavaWeb','2'),(3,'FrameWork','3'),(4,'MicroService','4');
    
    -- 中间表 
    
    CREATE TABLE `t_stu_sub` (
      `student_id` int(11) DEFAULT NULL,
      `subject_id` int(11) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
    
    insert  into `t_stu_sub`(`student_id`,`subject_id`) values (1,1),(1,2),(2,1),(3,1);

实体类

@Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class Student {
        private Integer id;
        private String name;
        private String sex;
        //学科
        private List<Subject> subjects;
    }
    
    public class Subject {
        private Integer id;
        private String name;
        private String grade;
        private List<Student> students;
    }

.xml中的sql和**手动的ORM**

<?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.zhp.mapper.StudentMapper">
        
        <resultMap id="ss" type="student">
            <id property="id" column="id"></id>
            <result property="name" column="name"></result>
            <result property="sex" column="sex"></result>
            <collection property="subjects" ofType="subject">
                <id property="id" column="subjectId"></id>
                <result property="name" column="subjectName"></result>
                <result property="grade" column="grade"></result>
            </collection>
        </resultMap>
        
        <select id="findAll" resultMap="ss">
            select t1.*, t3.id as subjectId, t3.name as subjectName, t3.grade
            from t_students t1
                     join t_stu_sub t2 on t1.id = t2.student_id
                     join t_subjects t3 on t2.subject_id = t3.id
        </select>
    </mapper>

小结：

持有**对象关系**属性，使用 < association property="dept" javaType="department" >

持有**集合关系**属性，使用 < collection property="emps" ofType="employee" >

## 关于column的疑惑解答： ##

有人问：啊啊啊~**这column是个啥呀**，到底是对应我数据库表字段还是啥？

其实这个**column对应的是你SQL语句中select后面查询的字段名称**（不一定是表字段名称，你可以自己定义别名），所以这边的**column对应你查询出来的名字**就行了。

如图![1ca855a734174899955ad28aee60897b.png][]

[9621c7b52e384fcbb85c32e506f6b15e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/829bf8e542ab40cca9a71b8eb19c2274.png
[1631fd0bc30b417aa2cd4dccd9335f6e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/f20ce5bcb0114684aa5097c99a419240.png
[1ef0602f9e1340818ae0f614a0c31deb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/0780548f88054a6cbffd7a8b8608f560.png
[1ca855a734174899955ad28aee60897b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/778a23effc5b45b0ac0d958408033c6b.png