联查——Mybatis
<?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.milo.mapper.TankMapper">
<!--resultMap记录主表,并为连表字段赋值-->
<resultMap id="TankResult" type="com.milo.entity.TankEntity">
<result property="TANK_NAME" column="TANK_NAME"/>
<result property="TANK_NUM" column="TANK_NUM"/>
<result property="PRE_WARNING_NUMBER" column="PRE_WARNING_NUMBER"/>
<collection property="FLAMMABLE_GAS_NUM1"
column="{FLAMMABLE_GAS_NUM=FLAMMABLE_GAS_NUM1,PRE_WARNING_STATUS=PRE_WARNING_NUMBER}"
select="getTankGroup"/>
<collection property="FLAMMABLE_GAS_NUM2"
column="{FLAMMABLE_GAS_NUM=FLAMMABLE_GAS_NUM2,PRE_WARNING_STATUS=PRE_WARNING_NUMBER}"
select="getTankGroup"/>
<collection property="FLAMMABLE_GAS_NUM3"
column="{FLAMMABLE_GAS_NUM=FLAMMABLE_GAS_NUM3,PRE_WARNING_STATUS=PRE_WARNING_NUMBER}"
select="getTankGroup"/>
</resultMap>
<resultMap id="FlammableGasResult" type="com.milo.entity.FlammableGasEntity">
<result property="FLAMMABLE_GAS_NAME" column="FLAMMABLE_GAS_NAME"/>
<result property="FLAMMABLE_GAS_NUM" column="FLAMMABLE_GAS_NUM"/>
</resultMap>
<!--查询X的通用数据+XX类型的通用数据-->
<select id="getTankList" parameterType="java.util.Map" resultMap="TankResult">
<bind name="PRE_WARNING_NUMBER" value="_parameter.param3"/>
<bind name="TABLE_NAME_L" value="_parameter.param4"/>
SELECT
t.TANK_NAME,
t.TANK_NUM,
t.FLAMMABLE_GAS_NUM1,
t.FLAMMABLE_GAS_NUM2,
t.FLAMMABLE_GAS_NUM3,
#{PRE_WARNING_NUMBER} AS PRE_WARNING_NUMBER
FROM tank_mov t
WHERE 1=1
<choose>
<when test="TANK_NAME!=null and TANK_NAME!=''">and t.TANK_NAME = #{TANK_NAME}</when>
<otherwise>
and t.TANK_NAME = 'T101'
</otherwise>
</choose>
</select>
<!--查询XXXX临时表数据+XXXX通用表数据-->
<select id="getTankGroup" parameterType="java.util.Map" resultType="com.milo.entity.FlammableGasEntity"
resultMap="FlammableGasResult">
SELECT f.FLAMMABLE_GAS_NAME,
f.FLAMMABLE_GAS_NUM,
(
SELECT DISTINCT(e.GZ_Value)
FROM ${PRE_WARNING_STATUS} e
WHERE e.GZ_Status = f.FLAMMABLE_GAS_NUM
AND e.GZ_Name LIKE "%E_EHI"
) AS FLAMMABLE_GAS_EHI,
(
SELECT DISTINCT(e.GZ_Value)
FROM ${PRE_WARNING_STATUS} e
WHERE e.GZ_Status = f.FLAMMABLE_GAS_NUM
AND e.GZ_Name LIKE "%E_CV"
) AS FLAMMABLE_GAS_ECV,
(
SELECT DISTINCT(e.GZ_Value)
FROM ${PRE_WARNING_STATUS} e
WHERE e.GZ_Status = f.FLAMMABLE_GAS_NUM
AND e.GZ_Name LIKE "%E_HI"
) AS FLAMMABLE_GAS_HI,
(
SELECT DISTINCT(e.GZ_Value)
FROM ${PRE_WARNING_STATUS} e
WHERE e.GZ_Status = f.FLAMMABLE_GAS_NUM
AND e.GZ_Name LIKE "%E_HIHI"
) AS FLAMMABLE_GAS_HIHI
FROM flammable_gas f
WHERE f.FLAMMABLE_GAS_NUM = #{FLAMMABLE_GAS_NUM}
</select>
<select id="getTankUsualMsg" resultType="java.lang.String" statementType="STATEMENT">
SELECT DISTINCT(GZ_Value)
FROM ${TABLE_NAME}
WHERE GZ_Status like "%${TANK_NAME}"
AND GZ_Name like "%${GZ_Name}"
</select>
<insert id="insertTank" parameterType="com.milo.entity.TankEntity">
</insert>
<update id="updateTank" parameterType="com.milo.entity.TankEntity">
</update>
</mapper>
collection标签
-
property:与实体类中字段名称保持一致
-
column:关联的字段
- 是一个map类型,可映射(传递)多个值
- 一般{映射表字段=本表字段}
- {FLAMMABLE_GAS_NUM=FLAMMABLE_GAS_NUM1,…}
bind标签
- name:随意命名
- value:_parameter
- _parameter:取出dao层接口函数,对应的传参,形式为[参数1,参数2,param1,param2]
- 取值方式:_parameter.param1 / _parameter.参数1
重点看:#{PRE_WARNING_NUMBER} AS PRE_WARNING_NUMBER
实体类:
@Data
@Accessors(chain = true)
public class MovEntity {
// String valveName; //XX名称
String valveRunningStatus; //XX运行状态
String valveControlModel; //XX模式
String valveScramStatus; //XX状态
}
Controller层:
@GetMapping("getMovMsg")
public GlobalResponse getMovMsg(@RequestParam(required = false) String movName) {
List<MovEntity> movList = movService.getMovList(movName,
getColumnList(MovEntity.class.getDeclaredFields(), "E_CV", "A_TAG", "F_AUTO"));
return GlobalResponse.success(movList);
}
工具类:
public class GlobalUtil {
public static Map<String, String> getColumnList(Field[] fields, String... columns) {
Map<String, String> map = new HashMap<>();
List<String> arrayList = Arrays.asList(columns);
for (int i = 0; i < fields.length; i++) {
map.put(fields[i].getName(), arrayList.get(i));
}
return map;
}
}
Mapper接口:
//@Param("columnMap") 可有可无
List<MovEntity> getMovList(String MovNum, String TABLE_NAME, @Param("columnMap") Map<String, String> columnMap);
mybatis-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.milo.mapper.MovMapper">
<!--查询XX的临时表数据-->
<select id="getMovList" resultType="com.milo.entity.MovEntity">
SELECT
mov0.GZ_Status AS valveName,
<foreach collection="columnMap" index="columnName" item="value" open="" separator="," close="">
${value}.GZ_Value AS ${columnName}
</foreach>
FROM
(SELECT DISTINCT(GZ_Status),GZ_Value FROM ${TABLE_NAME} WHERE GZ_Name LIKE "%A_DESC" ) mov0
<foreach collection="columnMap" index="columnName" item="value" open="" separator="" close="">
LEFT JOIN
(SELECT DISTINCT(GZ_Status),GZ_Value FROM ${TABLE_NAME} WHERE GZ_Name LIKE "%${value}") ${value}
ON mov0.GZ_Status = ${value}.GZ_Status
</foreach>
WHERE 1=1
<choose>
<when test="MovNum!=null and MovNum!=''">and mov0.GZ_Status = #{MovNum}</when>
</choose>
</select>
</mapper>
foreach标签
- collection:
- foreach(遍历)的对象,入参是list、array类型对象时,collection属性值分别默认用"list"、"array"代替,Map对象没有默认的属性值。
- 作为入参时可以使用@Param(“columnMap”)注解来设置自定义collection属性值,设置@Param后,list、array会失效(此时用参数名称即可)。
- index:在list、array中,index为元素的序号索引。但是在Map中,index为遍历元素的key值,该参数为可选项。
- item:集合元素迭代时的别名称,该参数为必选项。
- open:遍历集合时的开始符号,通常与close=")"搭配使用。使用场景IN(),values()时,为每次遍历的开头赋值(任意字符),该参数为可选项。
- close:遍历集合时的结束符号,通常与open="("搭配使用,为每次遍历的结尾赋值(任意字符),该参数为可选项。
- separator:元素之间的分隔符,类比在IN()的时候,若separator=“,”,则最终所有遍历的元素将会以设定的(,)逗号符号隔开,该参数为可选项。
foreach的collection属性情况:
- 如果传入的参数类型为List时,collection的默认属性值为list,同样可以使用@Param注解自定义keyName;
- 如果传入的参数类型为array时,collection的默认属性值为array,同样可以使用@Param注解自定义keyName;
- 如果传入的参数类型为Map时,collection的属性值可为三种情况:(1.遍历map.keys;2.遍历map.values;3.遍历map.entrySet()),稍后会在代码中示例;