模糊查詢遇到null情況的小坑

String medicalId = pbSearch.getMedicalId()==""?null:pbSearch.getMedicalId(); 		
String identityId = pbSearch.getIdentityId()==""?null:pbSearch.getIdentityId();
String fullname = pbSearch.getFullname()==""?"":pbSearch.getFullname();
String mode = pbSearch.getMode();
PageRequest pageRequest =  size == 0 ? null:  PageRequest.of(--page, size, Sort.by("sysTime").descending());	
PagepatientBasicList = patientBasicRepo.findVoByDomainAndMedicalIdOrIdentityIdOrFullnameOrMode(domain
				,medicalId, identityId, fullname,mode,pageRequest);
                
@Query("select new com.ICU.ICUmodule.vo.caseManage.PatientBasicVo(pb.id as id, pb.medicalId as medicalId, pb.identityId as identityId, pb.fullname as fullname, "
+ "pb.telephone as telephone, pb.localDate as localDate, pb.treatment as treatment, pr.mode as mode) "
+ "from patient_basic pb left join patient_returned pr on pb.id=pr.patientBasic.id "
+ "where pb.domain=:doamin and (:medicalId is null or pb.medicalId =:medicalId) and (:identityId is null or pb.identityId =:identityId) and (:fullname is '' or pb.fullname LIKE '%' || :fullname||'%') and (:mode is null or pr.mode =:mode)")
Page findVoByDomainAndMedicalIdOrIdentityIdOrFullnameOrMode(String doamin, String medicalId,
			String identityId, String fullname, String mode, Pageable pageRequest);

在一般情況下, 若是要判斷是否為 null 再拉進JPQL裡面做is null or object.xxx = :xxx

然而在模糊查詢的時候, 這裡若為 null 帶入到JPQL LIKE語法, 會產生判斷JPQL語法錯誤問題

因此在這裡LIKE語法是不能帶null, 而要給""字串來做判斷, 這樣JPQL就不會run出問題, 也可以進行模糊查詢及一般查詢