Hibernate Spring Data native query not able to UPDATE set NULL with MERGE INTO

Question

First of all, I have a working query for MERGE INTO if I directly run it on database. I want to SET NULL to some columns with MERGE INTO WHEN MATCHED THEN part. It simply doesn't work when I use it with Hibernate.

This is how my method looks like :

@Modifying
@Transactional
@Query(nativeQuery = true,
        value = "MERGE INTO APP_TOKEN t " +
                "USING DUAL " +
                "ON (  t.TYPE      = :appTokenType " +
                "  AND t.TOKEN     = :token " +
                "  AND t.IS_DELETE = 0 " +
                ") " +
                "WHEN MATCHED THEN " +
                "  UPDATE SET " +
                "    t.APP_USER_ID   = :appUserId, " +
                "    t.APP_DEVICE_ID = :appDeviceId, " +
                "    t.EXPIRY_DATE   = :expiryDate, " +
                "    t.UPDATED_DT    = :updatedDt, " +
                "    t.UPDATED_BY    = :updatedBy " +
                "WHEN NOT MATCHED THEN " +
                "  INSERT (APP_USER_ID, APP_DEVICE_ID, TYPE, TOKEN, EXPIRY_DATE, IS_DELETE, STATUS, CREATED_BY, CREATED_DT, UPDATED_BY, UPDATED_DT) " +
                "  VALUES (:appUserId, :appDeviceId, :appTokenType, :token, :expiryDate, :isDelete, :status, :createdBy, :createdDt, :updatedBy, :updatedDt) ")
int upsert(@Param(value = "appUserId")        Long         appUserId,
           @Param(value = "appDeviceId")      Long         appDeviceId,
           @Param(value = "appTokenType")     String       appTokenType,
           @Param(value = "token")            String       token,
           @Param(value = "expiryDate")       Date         expiryDate,
           @Param(value = "isDelete")         Boolean      isDelete,
           @Param(value = "status")           String       status,
           @Param(value = "createdBy")        Long         createdBy,
           @Param(value = "createdDt")        Date         createdDt,
           @Param(value = "updatedBy")        Long         updatedBy,
           @Param(value = "updatedDt")        Date         updatedDt);

The appUserId (first parameter) of the method may be NULL, and I want to update this column to NULL (in database) if the parameter is NULL (in Java).

The following exception is thrown when the appUserId is NULL :

java.sql.sqlsyntaxerrorexception ora-00932 inconsistent datatypes expected number got binary

Framework/ Library I used :

Spring MVC 5.0.4.RELEASE
Spring Data JPA 2.0.0.RELEASE
Hibernate 5.2.16.Final

Database : Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

Is Hibernate able to handle setting NULL with UPDATE ?

EDIT : I have tried with simple UPDATE ... SET ... statement to set some column to NULL, but still not successful

Hibernate Spring Data native query not able to UPDATE set NULL with MERGE INTO

0 Answers0