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) {
? ? ? ? //獲取指定分頁的設(shè)備列表
? ? ? ? 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 = "按條件獲取設(shè)備列表", 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 = "設(shè)備添加開始時間", required = true, dataType = ????????????"java.lang.String"),
? ? ? ? ? ? @ApiImplicitParam(name = "endTime", value = "設(shè)備添加結(jié)束時間", required = true, dataType = ????????????????"java.lang.String")
? ? })
? ? @PostMapping("/search")
? ? BaseResponse searchDeviceList(@RequestBody SearchDeviceRequest requestBody) {
? ? ? ? Page<ExtDevice> deviceList = deviceService.searchDeviceList(account, requestBody);
? ? ? ? return success(deviceList);
? ? }
6. 日期過濾簡化：入?yún)?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;
