Day21 - MyBatis映射、高级查询及注解

## 一、MyBatis映射

在映射文件中`mapper`的`parameterType`中填写输入参数类型。`resultType`中填写输出参数类型。

#### 1、基本数据类型

当输入参数为基本数据类型时，直接写数据的类型即可，且SQL语句的参数名可以随便填写一个符合要求的变量名。

```xml
<select id="getDeptById" resultType="Dept" parameterType="int">
    select * from DEPT where DEPTno=#{DEPTno}
</select>
```

#### 2、引用数据类型

当输入参数为引用数据类型时，若没有添加包别名，就需要写包全路径，若添加了包别名，需要写对应的类名。

```xml
<select id="getDeptById" resultType="Dept" parameterType="Dept">
    select * from DEPT where DEPTno=#{DEPTno}
</select>
```

**注意**：SQL语句中的参数名应与传入对象的对应对象名相同，不可随意写。

#### 3、Map类型

当输入参数为Map类型时，SQL参数名必须与Map的Key值相同。

##### 定义接口

```java
Dept getDeptByIdWithMap(Map<String,String> map);
```

##### 定义Mapper

```xml
<select id="getDeptByIdWithMap" resultType="Dept" parameterType="Map">
    select * from DEPT where DEPTno=#{DEPTno}
</select>
```

##### 调用

```java
Map<String,String> map = new HashMap<>();
map.put("DEPTno","10");
Dept dept = deptMapper.getDeptByIdWithMap(map);
System.out.println(dept);
```

**注意**：在这里因为需要传入的数据类型多变，所以一般Value用String类型。

#### 4、List类型

##### 定义接口

```java
List<Emp> listEmpByNames(List<String> list);
```

##### 定义Mapper

因为参数为List类型，所以使用`<foreach>`来承接参数，并定义`item`为遍历出来的属性名，`collection`需要填写List类型，`separator`为每两个成员之间的分隔符。

```xml
<select id="listEmpByNames" resultType="Emp">
    select * from EMP where EMP.ename in (
    <foreach item="ename" collection="list" separator=",">#{ename}</foreach>
    )
</select>
```

##### 调用

```java
List<String> list1 = new ArrayList<>();
List<Emp> list2 = new ArrayList<>();
list1.add("KING");
list2 = empMapper.listEmpByNames(list1);
System.out.println(list2);
```

**注意**：在使用`<foreach>`时，若外面有括号，则需要手动添加。

#### 5、转义符号

当查询语句中有特殊符号时，可能会与XML标签起冲突，所以要用转义符号代替原符号

| 原字符 | 转义字符 | 说明   | 示例（原SQL -> MyBatis XML）                |
| :----- | :------- | :----- | :------------------------------------------ |
| `<`    | `<`   | 小于号 | `column < 10` -> `column < 10`           |
| `>`    | `>`   | 大于号 | `column > 10` -> `column > 10`           |
| `&`    | `&`  | 和号   | `status = 'A&B'` -> `status = 'A&B'`    |
| `'`    | `'` | 单引号 | `name = 'O'Neil'` -> `name = 'O'Neil'` |
| `"`    | `"` | 双引号 | 在XML属性中常用，如`"value"`                |

#### 6、文本连接函数

在SQL语句中若有需要拼接的文本，不能直接使用`+`来拼接，可以使用SQL中自带的函数`CONCAT('','','',...)`进行拼接。

```xml
<select id="listEmpByName" resultType="Emp" parameterType="String">
    select *
    from EMP
    where ename like concat('%',#{ename},'%')
</select>
```

也可以使用`$`来拼接文本（不推荐）

```xml
<select id="listEmpByName" resultType="Emp" parameterType="String">
    select *
    from EMP
    where ename like '%${ename}%'
</select>
```

**注意**：使用`#`是默认使用JDBC中的`PreparedStatement`，没有SQL注入风险，较为安全；使用`$`是默认使用JDBC中的`Statement`，有SQL注入风险。

#### 7、ResultMap

当SQL语句中的字段名与实体对象中的字段名不一致时，可以使用ResultMap来显示的进行映射。

当某个字段为主键时，需要用`<id>`标签，若为普通字段，需要使用`<result>`标签

```xml
<resultMap id="empResultMap" type="Emp">
    <id column="EMPno" property="EMPno"/>
    <result column="ename" property="ename"/>
    <result column="job" property="job"/>
    <result column="mgr" property="mgr"/>
    <result column="hiredate" property="hiredate"/>
    <result column="sal" property="sal"/>
    <result column="comm" property="comm"/>
    <result column="DEPTno" property="DEPTno"/>
</resultMap>

<select id="listEmpByNameWithMap" resultMap="empResultMap" parameterType="String">
    select *
    from EMP
    where ename like concat('%', #{ename}, '%')
</select>
```

**注意**：ResultMap的`id`是区分ResultMap，`type`是传入对象的类型，需要写别名或者保全路径，`column`为数据库中的字段名，`property`为对象属性名一致。

## 二、事务操作

事务是数据库操作的最小单位，一个事务下的SQL语句要么都成功，要么都失败。

当使用数据增删改时，MyBatis会默认将sqlSession的事务提交方式设置为手动提交

需要设置事务类型为JDBC（在`sqlMapperConfig.xml`文件中）

```xml

<transactionManager type="JDBC"/>
```

#### 1、手动提交

使用`sqlSession.commit()`方法进行手动提交

```java
try {
    InputStream input = Resources.getResourceAsStream("sqlMapperConfig.xml");
    // 创建Session
    SqlSession sqlSession = new SqlSessionFactoryBuilder().build(input).openSession();
	 
    // 增删改的操作....
  
    // 提交事务
    sqlSession.commit();
    // 关闭Session
    sqlSession.close();
} catch (IOException e) {
    throw new RuntimeException(e);
}
```

#### 2、自动提交

若需要使用自动提交，应该在创建sqlSession时将`openSession()`中传入`true`

```java
try {
    InputStream input = Resources.getResourceAsStream("sqlMapperConfig.xml");
    // 创建Session，并设置为自动提交
    SqlSession sqlSession = new SqlSessionFactoryBuilder().build(input).openSession(true);
	 
    // 增删改的操作....
  
    // 关闭Session
    sqlSession.close();
} catch (IOException e) {
    throw new RuntimeException(e);
}
```

#### 3、获取自增主键

需要在Mapper中添加`<selectKey>`标签，并且使用`select LAST_INSERT_ID()`语句获取。

##### 定义接口

```java
int addEmp(Emp emp);
```

##### 定义Mapper

```xml
<insert id="addEmp" parameterType="Emp">
    <selectKey resultType="int" keyProperty="EMPno" order="AFTER">
        select LAST_INSERT_ID()
    </selectKey>
    insert into EMP values (default,#{ename},#{job},#{mgr},#{hiredate},#{sal},#{comm},#{DEPTno})
</insert>
```

##### 调用

```java
InputStream input = Resources.getResourceAsStream("sqlMapperConfig.xml");
// 创建Session
SqlSession sqlSession = new SqlSessionFactoryBuilder().build(input).openSession();
EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
Emp emp = new Emp();
emp.setEname("李四");
emp.setSal(2000.0);
int res =  empMapper.addEmp(emp);
System.out.println(res);
System.out.println(emp.getEMPno());
// 提交事务
sqlSession.commit();
// 关闭Session
sqlSession.close();
```

#### 4、删除记录

```xml
<delete id="deleteEmpById" parameterType="int">
    delete from EMP where EMPno=#{EMPno}
</delete>
```

#### 5、更新记录

```xml
<update id="updateEmpById" parameterType="Emp">
    update EMP set ename=#{ename},job=#{job},mgr=#{mgr},hiredate=#{hiredate},sal=#{sal},comm=#{comm},DEPTno=#{DEPTno} where EMPno=#{EMPno}
</update>
```

## 三、动态SQL

像更新操作这类不需要全部字段都改变的操作，可以使用动态SQL来改变查询时的语句

#### 1、`<if>`+`<where>`标签

根据Emp的属性动态设定查询条件

```xml
<select id="listEmpByCondition" parameterType="Emp" resultType="Emp">
    select *
    from EMP
    <where>
        <if test="ename!=null and ename!=''">
            and EMP.ename like concat('%',#{ename},'%')
        </if>
        <if test="job!=null and job!=''">
            and EMP.job like concat('%', #{job}, '%')
        </if>
        <if test="mgr!=null">
            and EMP.mgr=#{mgr}
        </if>
        <if test="hiredate!=null and hiredate!=''">
            and EMP.hiredate=#{hiredate}
        </if>
        <if test="sal!=null">
            and EMP.sal=#{sal}
        </if>
        <if test="comm!=null">
            and EMP.comm=#{comm}
        </if>
        <if test="DEPTno!=null">
            and EMP.DEPTno=#{DEPTno}
        </if>
    </where>
    order by EMP.EMPno;
</select>
```

**注意**：每个条件分支内的语句前必须要添加`and`，`<where>`标签会自动处理多出来的`and`；此处的条件判断虽然是字符串比较，但是需要使用`!=`来比较，是SQL语言，并不是Java中的`equals()`

#### 2、`<choose>`标签

根据Emp的属性动态设定查询条件

```xml
<select id="listEmpByCondition" parameterType="Emp" resultType="Emp">
    select *
    from EMP
    <where>
        <choose>
            <when test="ename!=null and ename!=''">
                and EMP.ename like concat('%',#{ename},'%')
            </when>
            <when test="job!=null and job!=''">
                and EMP.job like concat('%', #{job}, '%')
            </when>
            <when test="mgr!=null">
                and EMP.mgr=#{mgr}
            </when>
            <when test="hiredate!=null and hiredate!=''">
                and EMP.hiredate=#{hiredate}
            </when>
            <when test="sal!=null">
                and EMP.sal=#{sal}
            </when>
            <when test="comm!=null">
                and EMP.comm=#{comm}
            </when>
            <when test="DEPTno!=null">
                and EMP.DEPTno=#{DEPTno}
            </when>
            <otherwise>

</otherwise>
        </choose>
    </where>
    order by EMP.EMPno;
</select>
```

**注意**：若以上条件都不满足可以使用`<otherwise>`标签进行添加。

#### 3、`<foreach>`标签

例如上面的`getEmpByNames`

```xml
<select id="listEmpByNames" resultType="Emp">
    select * from EMP where EMP.ename in 
    <foreach item="ename" collection="list" separator="," open="(", close=")">
        #{ename}
    </foreach>
</select>
```

#### 4、`<trim>`标签

可以处理包含的内容中首尾字符，其属性`prefix`表示内容前包裹符号，`suffix`表示内容后包裹符号，`prefixOverrides`表示每条内容前的分隔符，`suffixOverrides`表示每条内容后的分隔符

```xml
<insert id="addEmpWithTrim" parameterType="Emp">
    <selectKey resultType="int" keyProperty="EMPno" order="AFTER">
        select LAST_INSERT_ID()
    </selectKey>
    insert into EMP
    <trim prefix="(" suffix=")" suffixOverrides=",">
    <if test="EMPno!=null">
        EMPno,
    </if>
    <if test="ename!=null and ename!=''">
        ename
    </if>
    <if test="job!=null and job!=''">
        job,
    </if>
    <if test="mgr!=null">
        mgr,
    </if>
    <if test="hiredate!=null and hiredate!=''">
        hiredate,
    </if>
    <if test="sal!=null">
        sal,
    </if>
    <if test="comm!=null">
        comm,
    </if>
    <if test="DEPTno!=null">
        DEPTno,
    </if>
    values
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="EMPno!=null">
            #{EMPno},
        </if>
        <if test="ename!=null and ename!=''">
            #{ename},
        </if>
        <if test="job!=null and job!=''">
            #{job},
        </if>
        <if test="mgr!=null">
            #{mgr},
        </if>
        <if test="hiredate!=null and hiredate!=''">
            #{hiredate},
        </if>
        <if test="sal!=null">
            #{sal},
        </if>
        <if test="comm!=null">
            #{comm},
        </if>
        <if test="DEPTno!=null">
            #{DEPTno},
        </if>
    </trim>
</insert>
```

#### 5、`<set>`标签

使用`<set>`标签可以自动拼接`update`的条件

```xml
<select id="updateEmpByCondition" parameterType="Emp">
    update EMP
    <set>
        <if test="ename!=null and ename!=''">
            EMP.ename=#{ename},
        </if>
        <if test="job!=null and job!=''">
            EMP.job=#{job},
        </if>
        <if test="mgr!=null">
            EMP.mgr=#{mgr},
        </if>
        <if test="hiredate!=null and hiredate!=''">
            EMP.hiredate=#{hiredate},
        </if>
        <if test="sal!=null">
            EMP.sal=#{sal},
        </if>
        <if test="comm!=null">
            EMP.comm=#{comm},
        </if>
        <if test="DEPTno!=null">
            EMP.DEPTno=#{DEPTno},
        </if>
    </set>
    where EMP.EMPno=#{EMPno}
</select>
```

## 四、多表查询

#### 1、导入数据库

导入新的数据库`db_01`：[香肠狗网盘](https://pan.soki.me/s/leF5)

```sql
/*
SQLyog Ultimate v8.4 
MySQL - 8.0.31 : Database - db_01
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`db_01` /*!40100 DEFAULT CHARACTER SET utf8mb3 */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `db_01`;

/*Table structure for table `orders` */

DROP TABLE IF EXISTS `orders`;

CREATE TABLE `orders` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `ordertime` date DEFAULT NULL,
  `total` double(10,2) DEFAULT NULL,
  `uid` smallint unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_orders` (`uid`),
  CONSTRAINT `FK_orders` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=1100008 DEFAULT CHARSET=utf8mb3;

/*Data for the table `orders` */

insert  into `orders`(`id`,`ordertime`,`total`,`uid`) values (1100001,'2022-05-02',300.00,1001),(1100002,'2022-05-02',200.00,1002),(1100003,'2022-05-02',100.00,1003),(1100004,'2022-05-03',88.00,1004),(1100005,'2022-05-04',21.00,1004),(1100006,'2022-05-04',33.00,1004),(1100007,'2022-05-04',10.00,1005);

/*Table structure for table `role` */

DROP TABLE IF EXISTS `role`;

CREATE TABLE `role` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `rolename` varchar(40) DEFAULT NULL,
  `roleDesc` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3;

/*Data for the table `role` */

insert  into `role`(`id`,`rolename`,`roleDesc`) values (1,'院长','负责全面工作'),(2,'研究员','课程研发工作'),(3,'讲师','授课工作'),(4,'助教','协助解决学生的问题'),(5,'班主任','负责学生的生活'),(6,'就业指导','负责学生的就业工作');

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `uid` smallint unsigned NOT NULL AUTO_INCREMENT,
  `uname` varchar(20) NOT NULL,
  `upassword` varchar(20) NOT NULL,
  `usex` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=2001 DEFAULT CHARSET=utf8mb3;

/*Data for the table `user` */

insert  into `user`(`uid`,`uname`,`upassword`,`usex`) values (1001,'孙尚香','2000','女'),(1002,'裴擒虎','3500','男'),(1003,'李白','123','男'),(1004,'钟馗','123','男'),(1005,'大乔','123','女'),(1006,'孙悟空','123','男'),(1016,'梦琪','123','男'),(1018,'王昭君','111','女');

/*Table structure for table `user_role` */

DROP TABLE IF EXISTS `user_role`;

CREATE TABLE `user_role` (
  `user_id` smallint unsigned NOT NULL,
  `orle_id` int unsigned NOT NULL,
  KEY `FK_user_role` (`orle_id`),
  KEY `FK_user_role_02` (`user_id`),
  CONSTRAINT `FK_user_role` FOREIGN KEY (`orle_id`) REFERENCES `role` (`id`),
  CONSTRAINT `FK_user_role_02` FOREIGN KEY (`user_id`) REFERENCES `user` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

/*Data for the table `user_role` */

insert  into `user_role`(`user_id`,`orle_id`) values (1001,1),(1001,2),(1002,2),(1002,3);

/*Table structure for table `user_orders_view` */

DROP TABLE IF EXISTS `user_orders_view`;

/*!50001 DROP VIEW IF EXISTS `user_orders_view` */;
/*!50001 DROP TABLE IF EXISTS `user_orders_view` */;

/*!50001 CREATE TABLE  `user_orders_view`(
 `uid` smallint unsigned ,
 `uname` varchar(20) ,
 `upassword` varchar(20) ,
 `usex` varchar(10) ,
 `id` int unsigned ,
 `ordertime` date ,
 `total` double(10,2) 
)*/;

/*Table structure for table `user_role_view` */

DROP TABLE IF EXISTS `user_role_view`;

/*!50001 DROP VIEW IF EXISTS `user_role_view` */;
/*!50001 DROP TABLE IF EXISTS `user_role_view` */;

/*!50001 CREATE TABLE  `user_role_view`(
 `uid` smallint unsigned ,
 `uname` varchar(20) ,
 `upassword` varchar(20) ,
 `usex` varchar(10) ,
 `id` int unsigned ,
 `rolename` varchar(40) ,
 `roleDesc` varchar(255) ,
 `user_id` smallint unsigned ,
 `orle_id` int unsigned 
)*/;

/*Table structure for table `user_view01` */

DROP TABLE IF EXISTS `user_view01`;

/*!50001 DROP VIEW IF EXISTS `user_view01` */;
/*!50001 DROP TABLE IF EXISTS `user_view01` */;

/*!50001 CREATE TABLE  `user_view01`(
 `uid` smallint unsigned ,
 `uname` varchar(20) ,
 `upassword` varchar(20) ,
 `usex` varchar(10) 
)*/;

/*View structure for view user_orders_view */

/*!50001 DROP TABLE IF EXISTS `user_orders_view` */;
/*!50001 DROP VIEW IF EXISTS `user_orders_view` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `user_orders_view` AS select `u`.`uid` AS `uid`,`u`.`uname` AS `uname`,`u`.`upassword` AS `upassword`,`u`.`usex` AS `usex`,`o`.`id` AS `id`,`o`.`ordertime` AS `ordertime`,`o`.`total` AS `total` from (`user` `u` join `orders` `o`) where (`u`.`uid` = `o`.`uid`) */;

/*View structure for view user_role_view */

/*!50001 DROP TABLE IF EXISTS `user_role_view` */;
/*!50001 DROP VIEW IF EXISTS `user_role_view` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `user_role_view` AS select `u`.`uid` AS `uid`,`u`.`uname` AS `uname`,`u`.`upassword` AS `upassword`,`u`.`usex` AS `usex`,`r`.`id` AS `id`,`r`.`rolename` AS `rolename`,`r`.`roleDesc` AS `roleDesc`,`ur`.`user_id` AS `user_id`,`ur`.`orle_id` AS `orle_id` from ((`user` `u` join `role` `r`) join `user_role` `ur`) where ((`u`.`uid` = `ur`.`user_id`) and (`r`.`id` = `ur`.`orle_id`)) */;

/*View structure for view user_view01 */

/*!50001 DROP TABLE IF EXISTS `user_view01` */;
/*!50001 DROP VIEW IF EXISTS `user_view01` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `user_view01` AS select `user`.`uid` AS `uid`,`user`.`uname` AS `uname`,`user`.`upassword` AS `upassword`,`user`.`usex` AS `usex` from `user` where (`user`.`usex` = '男') */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

```

#### 2、创建持久层对象

`cn.doooog.po.Orders.java`

```java
package cn.doooog.po;

import java.util.List;

public class Orders {
    private Integer id;
    private String ordertime;
    private Double total;
    private Integer uid;
    private User user;

public Orders() {
    }

public Orders(Integer id, String ordertime, Double total, Integer uid) {
        this.id = id;
        this.ordertime = ordertime;
        this.total = total;
        this.uid = uid;
    }

@Override
    public String toString() {
        return "Orders{" +
                "id=" + id +
                ", ordertime='" + ordertime + '\'' +
                ", total=" + total +
                ", uid=" + uid +
                '}';
    }

public User getUser() {
        return user;
    }

public void setUser(User user) {
        this.user = user;
    }

public Integer getId() {
        return id;
    }

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

public String getOrdertime() {
        return ordertime;
    }

public void setOrdertime(String ordertime) {
        this.ordertime = ordertime;
    }

public Double getTotal() {
        return total;
    }

public void setTotal(Double total) {
        this.total = total;
    }

public Integer getUid() {
        return uid;
    }

public void setUid(Integer uid) {
        this.uid = uid;
    }
}
```

`cn.doooog.po.User.java`

```java
package cn.doooog.po;

import java.util.List;

public class User {
    private Integer uid;
    private String uname;
    private String upassword;
    private String usex;
    private List<Orders> ordersList;

public User() {
    }

public User(Integer uid, String uname, String upassword, String usex) {
        this.uid = uid;
        this.uname = uname;
        this.upassword = upassword;
        this.usex = usex;
    }

@Override
    public String toString() {
        return "User{" +
                "uid=" + uid +
                ", uname='" + uname + '\'' +
                ", upassword='" + upassword + '\'' +
                ", usex='" + usex + '\'' +
                '}';
    }

public Integer getUid() {
        return uid;
    }

public void setUid(Integer uid) {
        this.uid = uid;
    }

public String getUname() {
        return uname;
    }

public void setUname(String uname) {
        this.uname = uname;
    }

public String getUpassword() {
        return upassword;
    }

public void setUpassword(String upassword) {
        this.upassword = upassword;
    }

public String getUsex() {
        return usex;
    }

public void setUsex(String usex) {
        this.usex = usex;
    }
}
```

`cn.doooog.po.Role.java`

```java
package cn.doooog.po;

public class Role {
    private Integer id;
    private String rolename;
    private String roleDesc;

public Role() {
    }

public Role(Integer id, String rolename, String roleDesc) {
        this.id = id;
        this.rolename = rolename;
        this.roleDesc = roleDesc;
    }

@Override
    public String toString() {
        return "Role{" +
                "id=" + id +
                ", rolename='" + rolename + '\'' +
                ", roleDesc='" + roleDesc + '\'' +
                '}';
    }

public Integer getId() {
        return id;
    }

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

public String getRolename() {
        return rolename;
    }

public void setRolename(String rolename) {
        this.rolename = rolename;
    }

public String getRoleDesc() {
        return roleDesc;
    }

public void setRoleDesc(String roleDesc) {
        this.roleDesc = roleDesc;
    }
}

```

#### 3、创建Mapper接口

`cn.doooog.mapper.OrdersMapper.java`

```java
package cn.doooog.mapper;

public interface OrdersMapper {
}
```

`cn.doooog.mapper.UserMapper.java`

```java
package cn.doooog.mapper;

public interface UserMapper {
}

```

#### 4、创建映射文件

`cn/doooog/mapper/OrdersMapper.xml`

```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="cn.doooog.mapper.OrdersMapper">

</mapper>
```

`cn/doooog/mapper/UserMapper.xml`

</mapper>
```

#### 5、测试文件

`cn.doooog.Test.java`

```java
package cn.doooog;

public class Test {
    public static void main(String[] args) {
        try {
            InputStream input = Resources.getResourceAsStream("sqlMapperConfig.xml");
            SqlSession sqlSession = new SqlSessionFactoryBuilder().build(input).openSession();

sqlSession.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
}
```

#### 6、一对一(多对一)查询

`OrdersMapper.java`接口

```java
List<Orders> listOrdersAll();
```

`OrdersMapper.xml`映射文件

```xml
<resultMap id="ordersMap" type="Orders">
    <id property="id" column="id"/>
    <result property="ordertime" column="ordertime"/>
    <result property="total" column="total"/>
    <result property="uid" column="uid"/>
    <result property="user.uid" column="uid"/>
    <result property="user.uname" column="uname"/>
    <result property="user.upassword" column="upassword"/>
    <result property="user.usex" column="usex"/>
</resultMap>
<select id="listOrdersAll" resultMap="ordersMap">
    select * from orders join user u on u.uid = orders.uid
</select>
```

或者使用`<association>`标签

```xml
<resultMap id="ordersMap" type="Orders">
    <id property="id" column="id"/>
    <result property="ordertime" column="ordertime"/>
    <result property="total" column="total"/>
    <result property="uid" column="uid"/>
    
    <association property="user" javaType="User">
        <id property="uid" column="uid"/>
        <result property="uname" column="uname"/>
        <result property="upassword" column="upassword"/>
        <result property="usex" column="usex"/>
    </association>
</resultMap>
<select id="listOrdersAll" resultMap="ordersMap">
    select * from orders join user u on u.uid = orders.uid
</select>
```

`Test.java`测试文件

```java
OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
List<Orders> ordersList = ordersMapper.listOrdersAll();
for (Orders o : ordersList){
    System.out.println(o);
    System.out.println(o.getUser());
}
```

#### 7、一对多查询

`UserMapper.java`接口

```java
User getUserById(int uid);
```

`UserMapper.xml`映射文件

```xml
<resultMap id="userMap" type="User">
    <id property="uid" column="uid"/>
    <result property="uname" column="uname"/>
    <result property="upassword" column="upassword"/>
    <result property="usex" column="usex"/>
    <collection property="ordersList" ofType="Orders">
        <id property="id" column="id"/>
        <result property="ordertime" column="ordertime"/>
        <result property="total" column="total"/>
        <result property="uid" column="uid"/>
    </collection>
</resultMap>
<select id="getUserById" parameterType="int" resultMap="userMap">
    select *
    from user
    join db_01.orders o on user.uid = o.uid
    where user.uid = #{uid}
</select>
```

`Test.java`测试文件

```java
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.getUserById(1001);
System.out.println(user);
System.out.println(user.getOrdersList());
```

#### 8、多对多查询

一般用三个表进行维护，一般把多对多的关系转化成一对多的关系。

`UserMapper.java`接口

```java
List<User> listUserWithRoles();
```

`UserMapper.xml`映射文件

```xml
<resultMap id="userRoleMap" type="User">
    <id property="uid" column="uid"/>
    <result property="uname" column="uname"/>
    <result property="upassword" column="upassword"/>
    <result property="usex" column="usex"/>
    <collection property="roles" ofType="Role">
        <id property="id" column="id"/>
        <result property="rolename" column="rolename"/>
        <result property="roleDesc" column="roleDesc"/>
    </collection>
</resultMap>
<select id="listUserWithRoles" resultMap="userRoleMap">
    select *
    from user,role,user_role
    where user.uid = user_role.user_id
    and role.id = user_role.orle_id
</select>
```

`Test.java`测试文件

```java
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> list = userMapper.listUserWithRoles();
for (User u : list){
    System.out.println(u);
    System.out.println(user.getRoles());
}
```

## 五、注解形式实现映射

#### 1、常用注解

| 类别             | 注解名称          | 作用                                       |
| ---------------- | ----------------- | ------------------------------------------ |
| **SQL语句注解**  | `@Select`         | 执行查询操作                               |
|                  | `@Insert`         | 执行插入操作                               |
|                  | `@Update`         | 执行更新操作                               |
|                  | `@Delete`         | 执行删除操作                               |
| **参数处理注解** | `@Param`          | 给参数命名，用于多参数方法                 |
|                  | `@Options`        | 配置SQL执行选项（如主键回填）              |
|                  | `@ResultMap`      | 引用XML中定义的ResultMap                   |
| **结果映射注解** | `@Results`        | 定义结果集映射关系（类似XML中的ResultMap） |
|                  | `@Result`         | 在`@Results`内定义单个字段映射             |
|                  | `@One`            | 配置一对一关联查询（嵌套查询）             |
|                  | `@Many`           | 配置一对多关联查询（嵌套查询）             |
| **动态SQL注解**  | `@SelectProvider` | 通过Provider类动态生成SQL                  |
|                  | `@InsertProvider` | 动态生成Insert语句                         |
|                  | `@UpdateProvider` | 动态生成Update语句                         |
|                  | `@DeleteProvider` | 动态生成Delete语句                         |
| **缓存注解**     | `@CacheNamespace` | 在接口上声明使用二级缓存                   |
|                  | `@Property`       | 配合`@CacheNamespace`设置缓存属性          |

#### 2、查询

修改`UserMapper.java`接口文件

```java
@Results(id = "userResults", value = {
    @Result(id = true, property = "uid", column = "uid"),
    @Result(property = "uname", column = "uname"),
    @Result(property = "upassword", column = "upassword"),
    @Result(property = "usex", column = "usex"),
    @Result(property = "ordersList", column = "uid", javaType = Orders.class,
            many = @Many(select = "cn.doooog.mapper.OrdersMapper.getOrdersByIdLazy"))
})
@Select("select * from user join db_01.orders o on user.uid = o.uid where user.uid = #{uid}")
User getUserById(int uid);

@Results({
    @Result(id = true, property = "uid", column = "uid"),
    @Result(property = "uname", column = "uname"),
    @Result(property = "upassword", column = "upassword"),
    @Result(property = "usex", column = "usex")
})
@Select("select * from user where user.uid = #{uid}")
User getUserByIdLazy(int uid);
```

修改`OrdersMapper.java`接口文件

```java
@Results(id = "ordersResults", value = {
    @Result(id = true, property = "id", column = "id"),
    @Result(property = "ordertime", column = "ordertime"),
    @Result(property = "total", column = "total"),
    @Result(property = "uid", column = "uid"),
    @Result(property = "user",column = "uid",javaType = User.class,one = @One(select = "cn.doooog.mapper.UserMapper.getUserByIdLazy"))
})
@Select("select * from orders")
List<Orders> listOrdersAll();

@Results({
    @Result(id = true, property = "id", column = "id"),
    @Result(property = "ordertime", column = "ordertime"),
    @Result(property = "total", column = "total"),
    @Result(property = "uid", column = "uid")
})
@Select("select * from orders where uid=#{uid}")
Orders getOrdersByIdLazy(int uid);
```

## 一、MyBatis映射

在映射文件中`mapper`的`parameterType`中填写输入参数类型。`resultType`中填写输出参数类型。

#### 1、基本数据类型

当输入参数为基本数据类型时，直接写数据的类型即可，且SQL语句的参数名可以随便填写一个符合要求的变量名。

```xml
<select id="getDeptById" resultType="Dept" parameterType="int">
    select * from DEPT where DEPTno=#{DEPTno}
</select>
```

#### 2、引用数据类型

当输入参数为引用数据类型时，若没有添加包别名，就需要写包全路径，若添加了包别名，需要写对应的类名。

```xml
<select id="getDeptById" resultType="Dept" parameterType="Dept">
    select * from DEPT where DEPTno=#{DEPTno}
</select>
```

**注意**：SQL语句中的参数名应与传入对象的对应对象名相同，不可随意写。

#### 3、Map类型

当输入参数为Map类型时，SQL参数名必须与Map的Key值相同。

##### 定义接口

```java
Dept getDeptByIdWithMap(Map<String,String> map);
```

##### 定义Mapper

```xml
<select id="getDeptByIdWithMap" resultType="Dept" parameterType="Map">
    select * from DEPT where DEPTno=#{DEPTno}
</select>
```

##### 调用

```java
Map<String,String> map = new HashMap<>();
map.put("DEPTno","10");
Dept dept = deptMapper.getDeptByIdWithMap(map);
System.out.println(dept);
```

**注意**：在这里因为需要传入的数据类型多变，所以一般Value用String类型。

#### 4、List类型

##### 定义接口

```java
List<Emp> listEmpByNames(List<String> list);
```

##### 定义Mapper

```xml
<select id="listEmpByNames" resultType="Emp">
    select * from EMP where EMP.ename in (
    <foreach item="ename" collection="list" separator=",">#{ename}</foreach>
    )
</select>
```

##### 调用

```java
List<String> list1 = new ArrayList<>();
List<Emp> list2 = new ArrayList<>();
list1.add("KING");
list2 = empMapper.listEmpByNames(list1);
System.out.println(list2);
```

**注意**：在使用`<foreach>`时，若外面有括号，则需要手动添加。

#### 5、转义符号

当查询语句中有特殊符号时，可能会与XML标签起冲突，所以要用转义符号代替原符号

| 原字符 | 转义字符 | 说明   | 示例（原SQL -> MyBatis XML）                |
| :----- | :------- | :----- | :------------------------------------------ |
| `<`    | `&lt;`   | 小于号 | `column < 10` -> `column &lt; 10`           |
| `>`    | `&gt;`   | 大于号 | `column > 10` -> `column &gt; 10`           |
| `&`    | `&amp;`  | 和号   | `status = 'A&B'` -> `status = 'A&amp;B'`    |
| `'`    | `&apos;` | 单引号 | `name = 'O'Neil'` -> `name = 'O&apos;Neil'` |
| `"`    | `&quot;` | 双引号 | 在XML属性中常用，如`"value"`                |

#### 6、文本连接函数

在SQL语句中若有需要拼接的文本，不能直接使用`+`来拼接，可以使用SQL中自带的函数`CONCAT('','','',...)`进行拼接。

```xml
<select id="listEmpByName" resultType="Emp" parameterType="String">
    select *
    from EMP
    where ename like concat('%',#{ename},'%')
</select>
```

也可以使用`$`来拼接文本（不推荐）

```xml
<select id="listEmpByName" resultType="Emp" parameterType="String">
    select *
    from EMP
    where ename like '%${ename}%'
</select>
```

**注意**：使用`#`是默认使用JDBC中的`PreparedStatement`，没有SQL注入风险，较为安全；使用`$`是默认使用JDBC中的`Statement`，有SQL注入风险。

#### 7、ResultMap

当SQL语句中的字段名与实体对象中的字段名不一致时，可以使用ResultMap来显示的进行映射。

当某个字段为主键时，需要用`<id>`标签，若为普通字段，需要使用`<result>`标签

<select id="listEmpByNameWithMap" resultMap="empResultMap" parameterType="String">
    select *
    from EMP
    where ename like concat('%', #{ename}, '%')
</select>
```

**注意**：ResultMap的`id`是区分ResultMap，`type`是传入对象的类型，需要写别名或者保全路径，`column`为数据库中的字段名，`property`为对象属性名一致。

## 二、事务操作

事务是数据库操作的最小单位，一个事务下的SQL语句要么都成功，要么都失败。

当使用数据增删改时，MyBatis会默认将sqlSession的事务提交方式设置为手动提交

需要设置事务类型为JDBC（在`sqlMapperConfig.xml`文件中）

```xml

<transactionManager type="JDBC"/>
```

#### 1、手动提交

使用`sqlSession.commit()`方法进行手动提交

#### 2、自动提交

若需要使用自动提交，应该在创建sqlSession时将`openSession()`中传入`true`

#### 3、获取自增主键

需要在Mapper中添加`<selectKey>`标签，并且使用`select LAST_INSERT_ID()`语句获取。

##### 定义接口

```java
int addEmp(Emp emp);
```

##### 定义Mapper

##### 调用

#### 4、删除记录

```xml
<delete id="deleteEmpById" parameterType="int">
    delete from EMP where EMPno=#{EMPno}
</delete>
```

#### 5、更新记录

## 三、动态SQL

像更新操作这类不需要全部字段都改变的操作，可以使用动态SQL来改变查询时的语句

#### 1、`<if>`+`<where>`标签

根据Emp的属性动态设定查询条件

#### 2、`<choose>`标签

根据Emp的属性动态设定查询条件

</otherwise>
        </choose>
    </where>
    order by EMP.EMPno;
</select>
```

**注意**：若以上条件都不满足可以使用`<otherwise>`标签进行添加。

#### 3、`<foreach>`标签

例如上面的`getEmpByNames`

#### 4、`<trim>`标签

#### 5、`<set>`标签

使用`<set>`标签可以自动拼接`update`的条件

## 四、多表查询

#### 1、导入数据库

导入新的数据库`db_01`：[香肠狗网盘](https://pan.soki.me/s/leF5)

```sql
/*
SQLyog Ultimate v8.4 
MySQL - 8.0.31 : Database - db_01
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

USE `db_01`;

/*Table structure for table `orders` */

DROP TABLE IF EXISTS `orders`;

/*Data for the table `orders` */

/*Table structure for table `role` */

DROP TABLE IF EXISTS `role`;

/*Data for the table `role` */

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

/*Data for the table `user` */

/*Table structure for table `user_role` */

DROP TABLE IF EXISTS `user_role`;

/*Data for the table `user_role` */

insert  into `user_role`(`user_id`,`orle_id`) values (1001,1),(1001,2),(1002,2),(1002,3);

/*Table structure for table `user_orders_view` */

DROP TABLE IF EXISTS `user_orders_view`;

/*!50001 DROP VIEW IF EXISTS `user_orders_view` */;
/*!50001 DROP TABLE IF EXISTS `user_orders_view` */;

/*Table structure for table `user_role_view` */

DROP TABLE IF EXISTS `user_role_view`;

/*!50001 DROP VIEW IF EXISTS `user_role_view` */;
/*!50001 DROP TABLE IF EXISTS `user_role_view` */;

/*Table structure for table `user_view01` */

DROP TABLE IF EXISTS `user_view01`;

/*!50001 DROP VIEW IF EXISTS `user_view01` */;
/*!50001 DROP TABLE IF EXISTS `user_view01` */;

/*!50001 CREATE TABLE  `user_view01`(
 `uid` smallint unsigned ,
 `uname` varchar(20) ,
 `upassword` varchar(20) ,
 `usex` varchar(10) 
)*/;

/*View structure for view user_orders_view */

/*!50001 DROP TABLE IF EXISTS `user_orders_view` */;
/*!50001 DROP VIEW IF EXISTS `user_orders_view` */;

/*View structure for view user_role_view */

/*!50001 DROP TABLE IF EXISTS `user_role_view` */;
/*!50001 DROP VIEW IF EXISTS `user_role_view` */;

/*View structure for view user_view01 */

/*!50001 DROP TABLE IF EXISTS `user_view01` */;
/*!50001 DROP VIEW IF EXISTS `user_view01` */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

```

#### 2、创建持久层对象

`cn.doooog.po.Orders.java`

```java
package cn.doooog.po;

import java.util.List;

public class Orders {
    private Integer id;
    private String ordertime;
    private Double total;
    private Integer uid;
    private User user;

public Orders() {
    }

public Orders(Integer id, String ordertime, Double total, Integer uid) {
        this.id = id;
        this.ordertime = ordertime;
        this.total = total;
        this.uid = uid;
    }

public User getUser() {
        return user;
    }

public void setUser(User user) {
        this.user = user;
    }

public Integer getId() {
        return id;
    }

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

public String getOrdertime() {
        return ordertime;
    }

public void setOrdertime(String ordertime) {
        this.ordertime = ordertime;
    }

public Double getTotal() {
        return total;
    }

public void setTotal(Double total) {
        this.total = total;
    }

public Integer getUid() {
        return uid;
    }

public void setUid(Integer uid) {
        this.uid = uid;
    }
}
```

`cn.doooog.po.User.java`

```java
package cn.doooog.po;

import java.util.List;

public class User {
    private Integer uid;
    private String uname;
    private String upassword;
    private String usex;
    private List<Orders> ordersList;

public User() {
    }

public User(Integer uid, String uname, String upassword, String usex) {
        this.uid = uid;
        this.uname = uname;
        this.upassword = upassword;
        this.usex = usex;
    }

public Integer getUid() {
        return uid;
    }

public void setUid(Integer uid) {
        this.uid = uid;
    }

public String getUname() {
        return uname;
    }

public void setUname(String uname) {
        this.uname = uname;
    }

public String getUpassword() {
        return upassword;
    }

public void setUpassword(String upassword) {
        this.upassword = upassword;
    }

public String getUsex() {
        return usex;
    }

public void setUsex(String usex) {
        this.usex = usex;
    }
}
```

`cn.doooog.po.Role.java`

```java
package cn.doooog.po;

public class Role {
    private Integer id;
    private String rolename;
    private String roleDesc;

public Role() {
    }

public Role(Integer id, String rolename, String roleDesc) {
        this.id = id;
        this.rolename = rolename;
        this.roleDesc = roleDesc;
    }

public Integer getId() {
        return id;
    }

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

public String getRolename() {
        return rolename;
    }

public void setRolename(String rolename) {
        this.rolename = rolename;
    }

public String getRoleDesc() {
        return roleDesc;
    }

public void setRoleDesc(String roleDesc) {
        this.roleDesc = roleDesc;
    }
}

```

#### 3、创建Mapper接口

`cn.doooog.mapper.OrdersMapper.java`

```java
package cn.doooog.mapper;

public interface OrdersMapper {
}
```

`cn.doooog.mapper.UserMapper.java`

```java
package cn.doooog.mapper;

public interface UserMapper {
}

```

#### 4、创建映射文件

`cn/doooog/mapper/OrdersMapper.xml`

</mapper>
```

`cn/doooog/mapper/UserMapper.xml`

</mapper>
```

#### 5、测试文件

`cn.doooog.Test.java`

```java
package cn.doooog;

sqlSession.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
}
```

#### 6、一对一(多对一)查询

`OrdersMapper.java`接口

```java
List<Orders> listOrdersAll();
```

`OrdersMapper.xml`映射文件

或者使用`<association>`标签

```xml
<resultMap id="ordersMap" type="Orders">
    <id property="id" column="id"/>
    <result property="ordertime" column="ordertime"/>
    <result property="total" column="total"/>
    <result property="uid" column="uid"/>
    
    <association property="user" javaType="User">
        <id property="uid" column="uid"/>
        <result property="uname" column="uname"/>
        <result property="upassword" column="upassword"/>
        <result property="usex" column="usex"/>
    </association>
</resultMap>
<select id="listOrdersAll" resultMap="ordersMap">
    select * from orders join user u on u.uid = orders.uid
</select>
```

`Test.java`测试文件

#### 7、一对多查询

`UserMapper.java`接口

```java
User getUserById(int uid);
```

`UserMapper.xml`映射文件

`Test.java`测试文件

```java
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.getUserById(1001);
System.out.println(user);
System.out.println(user.getOrdersList());
```

#### 8、多对多查询

一般用三个表进行维护，一般把多对多的关系转化成一对多的关系。

`UserMapper.java`接口

```java
List<User> listUserWithRoles();
```

`UserMapper.xml`映射文件

`Test.java`测试文件

## 五、注解形式实现映射

#### 1、常用注解

#### 2、查询

修改`UserMapper.java`接口文件

修改`OrdersMapper.java`接口文件

最后修改：2025 年 10 月 27 日

👆请简单粗暴地爱我。

Day21 - MyBatis映射、高级查询及注解

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

Spring框架依赖注入

MySQL知识扩展

Day12 - JDBC驱动安装及其简单使用方法

Java常用注解表

Electron的简介

Docker镜像与容器使用

Java文件读写操作

Vue-Cli构建项目

Tomcat9.0安装与IDEA配置

JavaEE基础

Day21 - MyBatis映射、高级查询及注解

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

Day21 - MyBatis映射、高级查询及注解

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款