如何在Spring数据存储库nativeQuery中使用oracle NVL函数

Question

我正在尝试在Spring Data Repository的nativeQuery中使用oracle的NVL函数。

当我在programId参数中传递null值时，它会抛出异常（ORA-00932：不一致的数据类型：预期的数字是二进制的）如果我在“programId”中传递有效值，那么它工作正常。

public interface ProgramRulesRepository
{
  public static final String FIND_PROGRAM_RULES_BY_PARTICIPANT_ID_AND_ROLE_OR_PROGRAM = " SELECT DISTINCT pr.id , pr.program_id , prgm.display_name , pr.group_id , pr.type , pr.cmmo_key FROM  program prgm , program_rule pr , program_audience pa , participant_audience paa WHERE prgm.id = pa.program_id AND pr.program_id = pa.program_id AND pa.audience_id = paa.audience_id AND pr.type = :roleType  AND paa.participant_id = :participantId "
      + " AND pr.program_id = NVL ( :programId ,pr.program_id )";

  @Query( value = FIND_PROGRAM_RULES_BY_PARTICIPANT_ID_AND_ROLE_OR_PROGRAM, nativeQuery = true )
List<Object[]> findByParticipantIdAndRoleTypeOrProgramId( @Param( "participantId" ) Long participantId, @Param( "roleType" ) String roleType, @Param( "programId" ) Long programId );


}

例外情况：

Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

Answer 1

我在MongoDB上遇到过这个问题。但是我可以通过使用mongoTemboard来解决这个问题，

 Query query = new Query();
 Criteria criteria = new Criteria();
 List<Criteria> orCriterias = new ArrayList<>();
 if( dto.getId() != null) {    
   orCriterias.add(Criteria.where("id").is(Integer.parseInt(dto.getId())));
 }
 ... so on for other fields
criteria.orOperator(orCriterias.toArray(new Criteria[orCriterias.size()]));
query.addCriteria(criteria);
List<StudentDTO> recordsList = mongoTemplate.find(query, StudentDTO.class, 
"student_collection");

Answer 2

解决这个问题的好办法是：

" AND (:programId IS NULL OR pr.program_id = :programId)"

这样做，如果您的参数为null，则这句话将导致TRUE并且不会丢弃寄存器，如果它不是null，则将与存储在其字段中的值进行比较。

Answer 3

使用Hibernate时避免使用NVL和COALESCE。COALESCE函数需要具有相同类型的所有参数。NVL使用隐式转换，当有BINARY或VARBINARY时效果不佳。这个BINARY来自哪里？好吧，Hibernate将NULL值设置为BINARY类型，并忽略Java支持的真实数据类型。当您将日志记录级别设置为跟踪时，您可以在输出中看到：

binding parameter [1] as [VARBINARY] - [null]

因此，当其他类型的in-COALESCE或NVL函数是例如NUMBER时，您将得到错误ORA-00932。