记录:421
场景:使用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 {
@Autowired
private 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,适配不同业务场景。
示例一:
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",
CITY_DESCRIBE AS "cityDescribe",
DATA_YEAR AS "dataYear",
UPDATE_TIME AS "updateTime"
FROM
t_city aa
WHERE aa.CITY_NAME = ?
示例三:
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_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='城市信息表';
以上,感谢。文章来源:https://www.toymoban.com/news/detail-813511.html
2023年4月23日文章来源地址https://www.toymoban.com/news/detail-813511.html
到了这里,关于mybatis的<sql>标签使用的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!