mybatis的＜if＞标签使用

浅浅的花香味﹌ 2024-04-23 03:19 66阅读 0赞

**记录**：417

**场景**：使用MyBatis的<if></if>标签的test属性，对入参属性的值做非null(!=null)、非空(!='')判断，或者与常量做相等(==)或者不等判断(!=)。

**版本**：JDK 1.8,Spring Boot 2.6.3,mybatis-3.5.9。

**1.基础知识**

**1.1MyBatis的标签**

(1)查看MyBatis支持的标签

地址：http://mybatis.org/dtd/mybatis-3-mapper.dtd

(2)查看标签使用

以<mapper></mapper>标签元素为例，在mybatis-3-mapper.dtd中如下：

<!ELEMENT mapper(...)+>，表示这是一个标签元素mapper.

(..| insert\*| update\* | delete\* | select\*),表示在mapper元素中可以嵌套使用的元素清单。

<!ATTLIST mapper>，表示这是一个元素标签的支持的属性。

**1.2MyBatis的使用**

(1)在application.yml配置文件配置mybatis映射的xml文件位置。

mybatis:
      mapper-locations: classpath*:mapper/**/*.xml

(2)创建一个Java接口。在接口中添加方法。

(3)创建一个Java接口映射的xml文件。在xml中使用<mapper></mapper>标签的namespace属性指定Java接口的全路径。Java接口和xml映射文件就完成了绑定关系。

(4)在<mapper></mapper>标签内，使用<insert><update><delete><select>等标签的id属性指定Java的方法名称。Java接口的方法和xml映射文件的<mapper></mapper>内部的标签就完成了绑定关系。

**2.使用****<if></if>标签元素**

**场景**：<if></if>标签元素在<insert><update><delete><select>等标签元素内使用。

**示例功能**：示例使用<if></if>标签，根据不同条件组装不同查询的SQL。

**2.1Java接口**

@Repository
    public interface Label01IFMapper {
      List<CityLabelPO> queryCity(CityLabelDTO cityLabelDTO);
    }

**2.2Java接口映射的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.hub.example.mapper.Label01IFMapper">
      <select id="queryCity" parameterType="com.hub.example.domain.CityLabelDTO"
            resultType="com.hub.example.domain.CityLabelPO">
        select CITY_ID AS "cityId",
        <if test="flagColumn !=null and flagColumn !='' and flagColumn == '01' ">
            CITY_NAME AS "cityName",
            LAND_AREA AS "landArea",
            POPULATION AS "population",
            GROSS AS "gross",
            CITY_DESCRIBE AS "cityDescribe",
            DATA_YEAR AS "dataYear",
            UPDATE_TIME AS "updateTime"
        </if>
        <if test="flagColumn !=null and flagColumn !='' and flagColumn == '02' ">
            CITY_NAME AS "cityName",
            LAND_AREA AS "landArea",
            POPULATION AS "population",
            GROSS AS "gross"
        </if>
        from
        <if test="flagFrom !=null and flagFrom !='' and flagFrom == '01' ">
            t_city
        </if>
        <if test="flagFrom !=null and flagFrom !='' and flagFrom == '02' ">
            t_city_01
        </if>
        aa
        where
        <if test="cityId !=null and cityId !='' and flagWhere == '01' ">
            aa.CITY_ID = #{cityId}
        </if>
        <if test="cityName !=null and cityName !='' and flagWhere == '02' ">
            aa.CITY_NAME = #{cityName}
        </if>
      </select>
    </mapper>

**3.测试**

**3.1测试代码**

@Slf4j
    @RestController
    @RequestMapping("/hub/example/cityLabel")
    public class CityLabelController {
      @Autowired
      private Label01IFMapper label01IFMapper;
      @GetMapping("/load01")
      public Object load01() {
        log.info("测试开始...");
        // 示例一
        CityLabelDTO labelDTO = CityLabelDTO.builder()
                .cityId(1L).flagColumn("01")
                .flagFrom("01").flagWhere("01").build();
        List<CityLabelPO> labelPOList = label01IFMapper.queryCity(labelDTO);
        // 示例一
        labelDTO = CityLabelDTO.builder()
                .cityName("上海").flagColumn("02")
                .flagFrom("02").flagWhere("02").build();
        labelPOList = label01IFMapper.queryCity(labelDTO);
        log.info("测试结束...");
        return "执行成功";
      }
    }

**3.2测试请求**

URL：http://127.0.0.1:18080/hub-example/hub/example/cityLabel/load01

**3.3执行SQL**

示例使用<if></if>标签后，根据不同条件组装不同查询的SQL，适配不同业务场景。

示例一：

SELECT
      CITY_ID AS "cityId",
      CITY_NAME AS "cityName",
      LAND_AREA AS "landArea",
      POPULATION AS "population",
      GROSS AS "gross",
      CITY_DESCRIBE AS "cityDescribe",
      DATA_YEAR AS "dataYear",
      UPDATE_TIME AS "updateTime"
    FROM
      t_city aa
    WHERE aa.CITY_ID = ?;

示例二：

SELECT
      CITY_ID AS "cityId",
      CITY_NAME AS "cityName",
      LAND_AREA AS "landArea",
      POPULATION AS "population",
      GROSS AS "gross"
    FROM
      t_city_01 aa
    WHERE aa.CITY_NAME = ?;

**4.支撑**

**4.1实体对象**

(1)封装结果对象CityLabelPO

@Data
    @Builder
    @NoArgsConstructor
    @AllArgsConstructor
    public class CityLabelPO {
      private Long cityId;
      private String cityName;
      private Double landArea;
      private Long population;
      private Double gross;
      private String cityDescribe;
      private String dataYear;
      private Date updateTime;
    }

(2)封装参数CityLabelDTO

@Data
    @Builder
    @NoArgsConstructor
    @AllArgsConstructor
    public class CityLabelDTO {
      private Long cityId;
      private String cityName;
      private Double landArea;
      private Long population;
      private Double gross;
      private String cityDescribe;
      private String dataYear;
      private Date updateTime;
      // 标记查询的字段
      private String flagColumn;
      // 标记查询的表
      private String flagFrom;
      // 标记where条件
      private String flagWhere;
    }

**4.2建表语句**

CREATE TABLE t_city (
      CITY_ID BIGINT(16) NOT NULL COMMENT '唯一标识',
      CITY_NAME VARCHAR(64) COLLATE utf8_bin NOT NULL COMMENT '城市名',
      LAND_AREA DOUBLE DEFAULT NULL COMMENT '城市面积',
      POPULATION BIGINT(16) DEFAULT NULL COMMENT '城市人口',
      GROSS DOUBLE DEFAULT NULL COMMENT '生产总值',
      CITY_DESCRIBE VARCHAR(512) COLLATE utf8_bin DEFAULT NULL COMMENT '城市描述',
      DATA_YEAR VARCHAR(16) COLLATE utf8_bin DEFAULT NULL COMMENT '数据年份',
      UPDATE_TIME DATETIME DEFAULT NULL COMMENT '更新时间'
    ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='城市信息表';

以上，感谢。

2023年4月23日