JPA自定义sql参数为空和postgresql遇到问题

问题描述

今天使用jpa自定义sql时，发现判空处理有问题

less

体验AI代码助手

代码解读

复制代码

@Query(value = "select d.user_id from device_grant_auth_item_info d inner join device_grant_auth " + " g on d.device_grant_auth_id = g.id inner join sys_device device on d.device_id = device.id" + " left join sys_user u on d.user_id = u.id where ((g.grant_auth_type = 'PERPETUAL') or (g.grant_auth_type = 'NORMAL' " + " and d.start_time >= CAST(:time AS timestamp) and d.end_time <= CAST(:time AS timestamp)) or (g.grant_auth_type = 'TEMPORALITY' and d.start_time >= CAST(:time AS timestamp) and d.end_time <= CAST(:time AS timestamp) " + " and (d.use_count = 0 or d.device_lock_count < d.use_count ) ))" + " and u.user_type in (:userTypeList)) and d.user_id = :userId) " + "and (d.create_id = :createId) group by d.user_id order by d.user_id asc ", nativeQuery = true) Page<Long> page(@Param("time") LocalDateTime localDateTime, @Param("userTypeList") List<UserType> userTypeList, @Param("userId") Long userId, @Param("createId") Long createId, Pageable pageable); }

发现userId等传入null会报错,抛出的异常如下

sql

体验AI代码助手

代码解读

复制代码

ERROR: COALESCE types bytea and character varying in PostgreSQL

后面使用hql语句解决

less

体验AI代码助手

代码解读

复制代码

@Query(value = "select d.userId from DeviceGrantAuthItemInfo d inner join DeviceGrantAuth " + " g on d.deviceGrantAuthId = g.id inner join SaasDevice device on d.deviceId = device.id" + " left join User u on d.userId = u.id where (g.grantAuthType = 'PERPETUAL' " + " or (g.grantAuthType = 'NORMAL' and d.startTime <= :time and d.endTime >= :time) " + " or (g.grantAuthType = 'TEMPORALITY' and d.startTime <= :time and d.endTime >= :time and (d.useCount = 0 or d.deviceLockCount < d.useCount ) ))" + " and (:userTypeList IS NULL OR u.userType in (:userTypeList)) and (:userId IS NULL or d.userId = :userId) " + "and (:createId IS NULL or d.createId = :createId) group by d.userId order by d.userId asc ") Page<Long> page(@Param("time") LocalDateTime localDateTime, @Param("userTypeList") List<UserType> userTypeList, @Param("userId") Long userId, @Param("createId") Long createId, Pageable pageable);

本文使用的是JDK14以及Springboot2.3.8版本