1. DeviceMapper.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.bmc.cloud.mapper.DeviceMapper">
<resultMap id="BaseResultMap" type="com.bmc.cloud.model.ExtDevice">
<id column="id" property="id" jdbcType="INTEGER" />
<result column="pairing_status" property="pairingStatus" jdbcType="VARCHAR"/>
<result column="create_id" property="createId" jdbcType="INTEGER"/>
<result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
<result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
</resultMap>
<select id="getDeviceListByCondition" resultMap="BaseResultMap">
select c.*, d.patient_hcp_id
from (
SELECT a.*, b.patient_id
from sys_device as a inner join sys_patient_device as b
on a.sn=b.sn and b.status = 1 // 左连查询带条件
where a.create_id=#{createId, jdbcType=INTEGER}
<if test="condition.sn != null and condition.sn != ''">
and a.sn like CONCAT('%',#{condition.sn, jdbcType=VARCHAR},'%')
</if>
<if test="condition.model != null and condition.model != ''">
and a.model like CONCAT('%',#{condition.model, jdbcType=VARCHAR},'%')
</if>
<if test="condition.type != null and condition.type != ''">
and a.type = #{condition.type, jdbcType=VARCHAR}
</if>
<if test="condition.status != null and condition.status != ''">
and a.status = #{condition.status, jdbcType=VARCHAR}
</if>
<if test="condition.pairingStatus != null and condition.pairingStatus != ''">
and a.pairing_status = #{condition.pairingStatus, jdbcType=VARCHAR}
</if>
<if test="condition.startTime != null and condition.startTime != ''">
and a.create_time >= STR_TO_DATE(#{condition.startTime, jdbcType=VARCHAR},'%Y-%m-%d %H:%i:%s')
</if>
<if test="condition.endTime != null and condition.endTime != ''">
and a.create_time <= STR_TO_DATE(#{condition.endTime, jdbcType=VARCHAR},'%Y-%m-%d %H:%i:%s')
</if>
) as c
left JOIN sys_patient as d
ON c.patient_id=d.id
order by c.create_time desc
</select>
</mapper>
2. DeviceMapper.java
public interface DeviceMapper extends BaseMapper<Device> {
// 多表符合查询列表
@Select("select c.*, d.patient_hcp_id from (SELECT a.*, b.patient_id from sys_device as a LEFT JOIN sys_patient_device as b on a.create_id=#{createId} AND a.sn=b.sn) as c left JOIN sys_patient as d ON c.patient_id=d.id")
Page<ExtDevice> getPageDevicePatientVo(Integer createId, Page<ExtDevice> iPage);
// 多条件搜索功能
Page<ExtDevice> getDeviceListByCondition(Integer createId, Page<ExtDevice> iPage,@Param("condition") SearchDeviceRequest request);
}
3. DeviceServiceImpl
public class DeviceServiceImpl extends ServiceImpl<DeviceMapper, Device> implements IDeviceService {
@Resource
DeviceMapper deviceMapper;
@Override
public Page<ExtDevice> searchDeviceList(Account account, SearchDeviceRequest request) {
//获取指定分页的设备列表
Long page = Long.valueOf(request.getPage());
Long size = Long.valueOf(request.getSize());
Page<ExtDevice> ipage = new Page<ExtDevice>(page, size);
Page<ExtDevice> result = deviceMapper.getDeviceListByCondition(account.getId(), ipage, request);
return result;
}
}
4. DeviceService
public interface IDeviceService extends IService<Device> {
public Page<ExtDevice> getDeviceList(Account account, Page<ExtDevice> ipage);
public Page<ExtDevice> searchDeviceList(Account account, SearchDeviceRequest request);
}
5. DeviceController
@ApiOperation(value = "按条件获取设备列表", notes = "post请求")
@ApiImplicitParams({
@ApiImplicitParam(name = "page", value = "页码", required = true, dataType = "java.lang.String"),
@ApiImplicitParam(name = "size", value = "单页容量", required = true, dataType = "java.lang.String"),
@ApiImplicitParam(name = "startTime", value = "设备添加开始时间", required = true, dataType = "java.lang.String"),
@ApiImplicitParam(name = "endTime", value = "设备添加结束时间", required = true, dataType = "java.lang.String")
})
@PostMapping("/search")
BaseResponse searchDeviceList(@RequestBody SearchDeviceRequest requestBody) {
Page<ExtDevice> deviceList = deviceService.searchDeviceList(account, requestBody);
return success(deviceList);
}
6. 日期过滤简化：入参 before_date， after_date为String
and v1.record_date BETWEEN #{before_date, jdbcType=DATE} AND #{after_date, jdbcType=DATE}
order by v1.record_date
7. 日期属于某个集合
<select id="selectEventData" resultMap="BaseWithEventsResultMap">
select
v1.*,
<include refid="Table_Event_Column_List"></include>
from ventilator_summaries v1
left join ventilator_event_pressures v2
on v1.id = v2.summary_id
left join ventilator_event_leaks v3
on v1.id = v3.summary_id
left join ventilator_event_apns v4
on v1.id = v4.summary_id
left join ventilator_event_hyps v5
on v1.id = v5.summary_id
left join ventilator_event_csas v6
on v1.id = v6.summary_id
where v1.serial = #{condition.serialNumber, jdbcType=VARCHAR}
and v1.record_date in
<foreach item = "recordDate" collection="condition.recordDateList" open="(" separator="," close=")">
#{recordDate, jdbcType=DATE}
</foreach>
order by v1.record_date
</select>
8. case when a then x when b then y end （参考： https://blog.csdn.net/qq_44858608/article/details/125735498）
select pd.p_date as pDate, pd.unp_date as unpDate, p.pa_id as paId,
(case when a.type = 2 then (select pr.`name` from prov pr where pr.ac_id = pd.op_id) when a.type = 3 then a.name END) as opName
from pa_d pd
left join pa p on p.id = pd.pa_id
left join ac a on a.id = pd.op_id
where pd.`status` = 0 and pd.sn = 'E110' ORDER BY pd.update_time DESC;
