跨时间节点、模糊查询(JPA)

跨时间节点的查询

以下是xxxRepository.java的代码，注意sql语句的写法

package com.siid.webapi.device.repository;

import com.siid.webapi.device.domain.DevicePropertyEntity;
import com.siid.webapi.device.domain.DevicePropertyLogEntity;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.sql.Timestamp;
import java.util.List;


public interface DevicePropertyLogRepository extends JpaRepository<DevicePropertyEntity, String> {
    List<DevicePropertyLogEntity> findAllById(List<Integer> id);

    List<DevicePropertyLogEntity> findAllByDeviceId(List<Integer> id);

    @Query("select p from DevicePropertyLogEntity as p where (p.deviceId = ?1)" +
            " and (p.name like %?2% or p.dataKey like %?2%)" +
            " and (p.lastTime between ?3 and ?4) order by p.lastTime desc")//将查询结果按降序排列
    Page<DevicePropertyLogEntity> findByDeviceIdAndKeyword(int deviceId, String queryStr, Timestamp from, Timestamp to, Pageable pageable);

}

以下是在xxxServiceImpl中应用xxxRepository中的方法的代码

@Override
    public PagedList<DevicePropertyModel> getHistoryByDeviceId(int deviceId, String queryStr, int page, int pageSize, LocalDateTime from, LocalDateTime to) {
        if(from == null && to == null){
            from = LocalDateTime.of(2000,1,1,0,0);
            to = LocalDateTime.now();
        }
        if(from.isEqual(to)){
            from = LocalDateTime.of(from.getYear(),from.getMonth(), from.getDayOfMonth(),0,0);
            to = LocalDateTime.of(from.getYear(),from.getMonth(), from.getDayOfMonth(),23,59);
        }
        Pageable pageable = PageRequest.of(page-1, pageSize);
        Page<DevicePropertyLogEntity> devicePropertyLogEntities = devicePropertyLogRepository.findByDeviceIdAndKeyword(deviceId, queryStr, Timestamp.valueOf(from), Timestamp.valueOf(to), pageable);
        List<DevicePropertyModel> devicePropertyModels = devicePropertyLogEntities.stream().map(p -> {
            DevicePropertyModel dpm = new DevicePropertyModel();
            dpm.setId(p.getId());
            dpm.setDeviceId(p.getDeviceId());
            dpm.setDataKey(p.getDataKey());
            dpm.setDataValue(p.getDataValue());
            dpm.setLastTime(p.getLastTime());
            dpm.setName(p.getName());
            dpm.setPropertyId(p.getPropertyId());
            return dpm;
        }).collect(Collectors.toList());
        PagedList<DevicePropertyModel> devicePropertyPages = new PagedList<>(devicePropertyLogEntities.getTotalElements(), page, pageSize);
        devicePropertyPages.setItems(devicePropertyModels);
        return devicePropertyPages;
    }

以下是xxxService在xxxController服务接口中应用，注意怎么传递时间参数的

@ApiOperation(value = "获取设备历史数据", response = DevicePropertyModel.class, responseContainer = "PagedList")
@GetMapping(value = "/history/{deviceId}", produces = "application/json")
    public PagedList<DevicePropertyModel> getHistoryProperty(@PathVariable("deviceId") int deviceId,
                                                                   @RequestParam(required = false, defaultValue = "") String queryStr,
                                                             @RequestParam(required = false, defaultValue = "1") int page,
                                                             @RequestParam(required = false, defaultValue = "20") int pageSize,
                                                             @RequestParam(required = false) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME) LocalDateTime from,
                                                             @RequestParam(required = false) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME) LocalDateTime to) {
        return devicePropertyService.getHistoryByDeviceId(deviceId, queryStr, page, pageSize, from, to);
    }

模糊查询——like %?2%

以下是xxxRepository.java的代码

package com.siid.webapi.device.repository;

import com.siid.webapi.device.domain.DeviceDetailEntity;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Optional;

public interface DeviceRepository extends JpaRepository<DeviceDetailEntity, Integer> {
//    Page<DeviceDetailEntity> findByDeviceTypeId(int positionTypeId, Pageable pageable);
    Page<DeviceDetailEntity> findByModelId(int modelId, Pageable pageable);

    Optional<DeviceDetailEntity> findOneByModelIdAndName(int modelId, String name);

    @Query(value = "select p from DeviceDetailEntity as p where (p.modelId=?1 or ?1=0) and (p.name like %?2% or p.description like %?2%)")
    Page<DeviceDetailEntity> findByModelIdAndKeyword(int modelId, String keyword, Pageable pageable);

    DeviceDetailEntity getByName(String name);
}

？2是表示应该带入方法参数列表中传递的第二个参数。