mybatis的＜sql＞标签使用

记录：420

场景：使用MyBatis的<sql></sql>标签元素，把重复的SQL语句集中到<sql>标签，使用标签的id属性指定唯一标识。在<insert><update><delete><select>等标签中使用<include></include>标签引用<sql></sql>标签。

版本：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 (cache-ref | cache | resultMap* | parameterMap* | sql* | insert* | update* | delete* | select* )+>
<!ATTLIST mapper
namespace CDATA #IMPLIED
>

<!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.使用<sql></sql>标签元素

场景：<sql></sql>标签元素在<mapper>标签元素中定义，使用<include></include>标签引用<sql>标签。

2.1Java接口

@Repository
public interface Label04SqlMapper {List<CityLabelPO> queryCity01(CityLabelPO cityPO);List<CityLabelPO> queryCity02(CityLabelPO cityPO);List<CityLabelPO> queryCity03(CityLabelPO cityPO);
}

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.Label04SqlMapper"><sql id="selectSql">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</sql><select id="queryCity01" resultType="com.hub.example.domain.CityLabelPO"><include refid="selectSql"></include>WHERE aa.CITY_ID = #{cityId}</select><select id="queryCity02" resultType="com.hub.example.domain.CityLabelPO"><include refid="selectSql"></include>WHERE aa.CITY_NAME = #{cityName}</select><select id="queryCity03" resultType="com.hub.example.domain.CityLabelPO"><include refid="selectSql"></include><where><if test="cityName !=null and cityName !='' ">aa.CITY_NAME = #{cityName}</if></where></select>
</mapper>

3.测试

3.1测试代码

@Slf4j
@RestController
@RequestMapping("/hub/example/cityLabel")
public class CityLabelController {@Autowiredprivate Label04SqlMapper label04SqlMapper;@GetMapping("/load04")public Object load04() {log.info("测试开始...");CityLabelPO cityPO = CityLabelPO.builder().cityId(3L).build();// 示例一List<CityLabelPO> list01 = label04SqlMapper.queryCity01(cityPO);// 示例二cityPO = CityLabelPO.builder().cityName("上海").build();list01 = label04SqlMapper.queryCity02(cityPO);// 示例三cityPO = CityLabelPO.builder().cityName("杭州").build();list01 = label04SqlMapper.queryCity03(cityPO);log.info("测试结束...");return "执行成功";}
}

3.2测试请求

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

3.3执行SQL

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

示例一：

SELECTCITY_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"
FROMt_city aa
WHERE aa.CITY_ID = ?

示例二：

SELECTCITY_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"
FROMt_city aa
WHERE aa.CITY_NAME = ?

示例三：

SELECTCITY_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"
FROMt_city 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;
}

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日