CODEWITHSUNDEEP
hibernatespring-bootkotlinspring-data
UncleBob
UncleBob

Reputation: 1391

Spring @Query cannot handle positional parameters when doing native query

I'm trying to do a native query with spring data. The query has subqueries where JPQL doesn't support them, so I have to use a native query. The final query I need looks like this:

@Query( value = """select count(*)
    from (select * from image a where a.table_id = ?1) as img
    inner join
        (select b.id from image_set b where b.camera_id = ?2 and b.time between ?3 and ?4) as imgset
    on img.image_set_id = imgset.id""",
        nativeQuery = true)

fun countByTableIdAndImageSetTimeBetween(tableId: Long, cameraId: Long, fromDateTime: LocalDateTime, toDateTime: LocalDateTime): Long

This didn't provide a result, so for the time being I substituted a simpler query to try to locate the problem. In the following I will be refering to this query, until I can get at least that to run:

@Query( value = "select count(*) from image_set b where b.camera_id = ?2", nativeQuery = true)

fun countByTableIdAndImageSetTimeBetween(tableId: Long, cameraId: Long, fromDateTime: LocalDateTime, toDateTime: LocalDateTime): Long

The first problem I encountered is that I got an exception:

org.hibernate.QueryException: Named parameter [2] not set

After some searching, I found something that while JPA's positional parameters are 1-indexed, Hibernate's are 0-indexed. I figured that the error might come from trying to assign the LocalDateTime to a place in the query where it can't be used, and I should be using 0-indices when doing a native query. This seems highly questionable, but changing ?2 to ?1 did resolve the exception. It did not, however, resolve the problem. I'm always getting a result of 0 now, which should not be the case. I noticed that in the hibernate log the parameter value wasn't filled in:

Hibernate: 
/* dynamic native SQL query */ select
    count(*) 
from
    image_set b 
where
    b.camera_id=?

However, this seems to be the case for other queries too, which work, so I guess hibernate doesn't log the actual query it sends? In any case, by now I'm pretty much lost. I have no idea what I'm doing wrong here, any help would be appreciated.

IMPORTANT ADDENDUM: My questionable assumption that positional arguments are 0-based when doing a native query is in fact incorrect. I just tried using ?0, and I'm getting ArrayIndexOutOfBoundsException: -1. So it would appear that spring subtracts 1 from the index by itself.

This changes the problem: The reason why I'm getting back 0 when using ?1 is indeed because I am using the wrong value. 0 is the correct response under the circumstances. So what I need to figure out is why I get the error when using ?2.

Upvotes: 1

Views: 2132

Answers (1)

UncleBob
UncleBob

Reputation: 1391

Solved it, there's a bit to unpack here. Mostly stupidity on my part.

So let's start with the query I actually wanted, that always returned 0 as a result:

@Query( value = """select count(*)
    from (select * from image a where a.table_id = ?1) as img
    inner join
        (select b.id from image_set b where b.camera_id = ?2 and b.time between ?3 and ?4) as imgset
    on img.image_set_id = imgset.id""",
        nativeQuery = true)

fun countByTableIdAndImageSetTimeBetween(tableId: Long, cameraId: Long, fromDateTime: LocalDateTime, toDateTime: LocalDateTime): Long

The reason this didn't work wasn't the positional parameter. It was the time arguments. It's painfully obvious in hindsight, but it took me a while to see: Of course you can't use LocalDateTime as a parameter in a native query. You have to convert it to java.util.Date.

So that fixes the problem I actually wanted to fix. The rest was pretty much a wild goose chase. Because I thought the only reasonable assumption that the query doesn't work was that the parameter got passed wrong I made the second, simplified query on the same method, because that was the least effort:

@Query( value = "select count(*) from image_set b where b.camera_id = ?2", nativeQuery = true)

fun countByTableIdAndImageSetTimeBetween(tableId: Long, cameraId: Long, fromDateTime: LocalDateTime, toDateTime: LocalDateTime): Long

The problem is that this query does not query for the same entity! It was in the wrong repository because I just put it over the other method, and the reason it didn't work was again not due to positional parameters. Once I moved that query to the correct repository, it actually worked without issue.
Which is what finally led me to the conclusion that maybe the parameters weren't my problem after all, and after a bit of sternly looking at my original query I realised the problem.

Two additional things for the record: One, No, you don't need zero-based indices for positional parameters when using nativeQuery. Spring subtracts 1 from the index by itself.

Two, No, the method name does in fact not matter at all when using the @Query annotation. You can name your method fooBar and execute whatever query with however many arguments you want with it, it doesn't make a difference.

Thanks for the effort, everyone!

Upvotes: 2

Related Questions