Java开发笔记10(简单的JPA VO查询)

1.Controller：

//获取屏幕表信息
@GetMapping("/pisplanstatus")
public Result pisPlanStatus(String queryType) {
    Station station = getStation();
    return Result.ok(planBriService.pisplanstatus(queryType,station.getStationName()));
}

 

2.Service：

Object pisplanstatus(String queryType, String stationName);

3.ServiceImpl：

    @Override
    public Object pisplanstatus(String queryType, String stationName) {
//        String sql = "SELECT device_name,ip,station,`status`,fault_state_code,device_address as region,modify_time "+
//                "FROM `guide_screen_monitoring` WHERE station=:stationName ORDER BY modify_time DESC  ";
        String sql = "SELECT gsm.device_name,gsm.ip,gsm.station,gsm.`status`,gsm.fault_state_code,gsm.device_address AS region,gsm.modify_time,rd.s_device_id "+
                " FROM guide_screen_monitoring gsm LEFT JOIN rec_device rd on gsm.ip = rd.s_ip WHERE station=:stationName ORDER BY modify_time DESC  ";
        Map<String, Object> param = new HashMap<>();
        param.put("stationName", stationName);
        List<GuideScreenTest> listBySql = planBrightnessDao.findListBySql(GuideScreenTest.class, sql, param);
        //Map<String, Object> param = new HashMap<>();
        /*String sql = "select zhu.led_name,zhu.ip,zhu.station_name,zhu.baspis_id,zhu.fault_state_code,zhu.modify_time,zhu.s_id,CASE WHEN zi.start_work_time <= NOW() and zi.stop_work_time >=NOW() THEN 1 ELSE 0 END AS newstatus from (select pi.led_name,pi.ip,pi.station_name,pi.baspis_id,gsm.fault_state_code,gsm.modify_time,rd.s_id from pis_info pi,guide_screen_monitoring gsm,rec_device  rd where pi.station_name = :stationName and led_type = '3' and pi.ip = gsm.ip and pi.ip = rd.s_ip) zhu LEFT JOIN (select * from (select pa.start_work_time,pa.stop_work_time,pa.baspis_id,pa.run_state,pa.plan_state from pis_app_plan pa where pa.station_name = :stationName and pa.stop_work_time> NOW() ORDER BY pa.stop_work_time ASC) t GROUP BY t.baspis_id)zi on zhu.baspis_id = zi.baspis_id  ORDER BY zhu.led_name,zhu.baspis_id";
        param.put("stationName", stationName);
        //FaultStatus 0正常  1端口冲突   2节能模式  3连接失败
        //RunStatus  0不在上屏    1正在上屏
        List<PlanStatusVo> listBySql = planBrightnessDao.findListBySql(PlanStatusVo.class, sql, param);
        for (PlanStatusVo planStatusVo : listBySql) {
            if (planStatusVo.getFaultStatus().equals("0") && planStatusVo.getRunStatus() == 0) {
                planStatusVo.setFaultStatus("2");
            }
        }
        Date date = new Date();
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String time = df.format(date);
        //1执行中,0无状态
        for (int i = 0; i < listBySql.size(); i++) {
            try {
                if (listBySql.get(i).getStartWorkTime() != null && listBySql.get(i).getStartWorkTime().getTime() < df.parse(time).getTime() &&
                        listBySql.get(i).getStopWorkTime().getTime() > df.parse(time).getTime()) {
                    listBySql.get(i).setRunStatus(1);
                } else {
                    listBySql.get(i).setRunStatus(0);
                }
            } catch (ParseException e) {
                e.printStackTrace();
            }

        }*/
        return listBySql;
    }

 

4.entity：

package com.cars.ict.rbpsems.vo.controlpisled;

import com.fasterxml.jackson.annotation.JsonFormat;
import org.springframework.format.annotation.DateTimeFormat;

import java.util.Date;

public class GuideScreenTest {
    private String ledName;

    private String ip;

    private String stationName;

    private String status;

    private String faultStatus;

    private String region;

    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date modifyTime;

    private String deviceId;

    public String getLedName() {
        return ledName;
    }

    public void setLedName(String ledName) {
        this.ledName = ledName;
    }

    public String getIp() {
        return ip;
    }

    public void setIp(String ip) {
        this.ip = ip;
    }

    public String getStationName() {
        return stationName;
    }

    public void setStationName(String stationName) {
        this.stationName = stationName;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    public String getFaultStatus() {
        return faultStatus;
    }

    public void setFaultStatus(String faultStatus) {
        this.faultStatus = faultStatus;
    }

    public String getRegion() {
        return region;
    }

    public void setRegion(String region) {
        this.region = region;
    }

    public Date getModifyTime() {
        return modifyTime;
    }

    public void setModifyTime(Date modifyTime) {
        this.modifyTime = modifyTime;
    }

    public String getDeviceId() {
        return deviceId;
    }

    public void setDeviceId(String deviceId) {
        this.deviceId = deviceId;
    }

    public GuideScreenTest(String ledName, String ip, String stationName, String status, String faultStatus, String region, Date modifyTime, String deviceId) {
        this.ledName = ledName;
        this.ip = ip;
        this.stationName = stationName;
        this.status = status;
        this.faultStatus = faultStatus;
        this.region = region;
        this.modifyTime = modifyTime;
        this.deviceId = deviceId;
    }
}