how to bind null value in native query in spring

Question

@Query(value = "SELECT TRUNC(p.CREATION_DATE), SUM(p.AMOUNT)" +
            " FROM PAYMENT  p" +
            " INNER JOIN FACTOR f" +
            " ON p.PAYMENT_ID = f.FACTOR_ID" +
            " JOIN VEHICLE_GATEWAY v" +
            " ON v.FACTOR_ID = f.FACTOR_ID" +
            " WHERE :debitTypes IS null OR f.FACTOR_TYPE IN (:debitTypes)" +
            " AND p.CREATION_DATE >= :fromPaymentDate" +
            " AND p.CREATION_DATE <= :toPaymentDate" +
            " GROUP BY TRUNC(p.CREATION_DATE)" +
            " ORDER BY TRUNC(p.CREATION_DATE)", nativeQuery = true)
    Object[] getPaidDebtSummary(@Param("debitTypes") List<Integer> debitTypes,
                                @Param("fromPaymentDate") Date fromPaymentDate,
                                @Param("toPaymentDate") Date toPaymentDate);

Hi

When I run this query through spring boot app I get this error : "java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected NUMBER got BINARY"

but when I run it in database console in intellij it returns the result set!

Does Anyone have an idea how to solve this problem?

Not the cause of the error, but a further problem that would lead to unexpected results is that you need to wrap that OR condition in brackets, otherwise all those AND conditions will not apply when:debitTypes is null. — racraman, Jun 11 '23 at 11:56

score 0 · Answer 1 · answered Jun 11 '23 at 11:18

0

it is because :debitTypes . instead of :debitTypes use your column name

answered Jun 11 '23 at 11:18

mamJavad

49
8

but I want to check whether 'debitTypes' is null or not! not the FACTOR_TYPE! – masoome Jun 11 '23 at 11:49
when you pass your parameter and it is null it's like null is null and it isn't correct. – mamJavad Jun 11 '23 at 13:38

score 0 · Answer 2 · answered Jun 11 '23 at 14:39

The JDBC driver doesn't know how to convert a List<> into the parameter of the IN() that's why you get a BINARY as fallback. ORACLE driver doesn't support anonymous type in that context: createArrayOf of the JDBC standard is not implemented.

f.FACTOR_TYPE IN (:debitTypes)

should be

f.FACTOR_TYPE IN ( select * from table(:debitTypes) )

and you should pass a java.sql.Array not a List, that you create with connection.createARRAY, and you need to define a TYPE in your DB, (here a TABLE OF NUMBER), to pass its name to connection.createARRAY and second argument will be an Integer[]. You may need to unwrap your java.sql.Connection into an oracle.jdbc.driver.OracleConnection.

Mustafa Khalil · Answer 3 · 2023-06-12T15:52:41.487

It is not possible to use :debitTypes IS null condition, it should be a column name. instead, in order to check if debitTypes list is null, do this check in a higher level (the method that calls getPaidDebtSummary), and use another query for it.

@Query(value = "SELECT TRUNC(p.CREATION_DATE), SUM(p.AMOUNT)" +
        " FROM PAYMENT  p" +
        " INNER JOIN FACTOR f" +
        " ON p.PAYMENT_ID = f.FACTOR_ID" +
        " JOIN VEHICLE_GATEWAY v" +
        " ON v.FACTOR_ID = f.FACTOR_ID" +
        " WHERE p.CREATION_DATE >= :fromPaymentDate" +
        " AND p.CREATION_DATE <= :toPaymentDate" +
        " GROUP BY TRUNC(p.CREATION_DATE)" +
        " ORDER BY TRUNC(p.CREATION_DATE)", nativeQuery = true)
Object[] getPaidDebtSummaryNoDebitTypes(@Param("fromPaymentDate") Date fromPaymentDate,
                            @Param("toPaymentDate") Date toPaymentDate);

Update: my answer was not very clear, my idea was to not to add the condition here but in a higher level, adding the condition is correct, thanks for correcting me.

It is perfectly normal to use an expression like `:bindvar IS NULL` in a SQL statement. If the bound value is translated by the database adapter as an Oracle `NULL`, then `NULL IS NULL` will work as intended. — Paul W, Jun 11 '23 at 13:25
what i need is something like this: `where (:field1 is null or table.field1 in :field1)` but when i write something like this, i get error — masoome, Jun 11 '23 at 13:35

how to bind null value in native query in spring

3 Answers3