Native query with parameters using @Query in Hibernate

Question

I m trying to parametrize a @Query method against MariaDb 10.3 using a sequence named F0001

On this tutorial, section 5.2 there s this example

5.2. Native Indexed parameters for the native queries work exactly in the same way as for JPQL:

@Query(
  value = "SELECT * FROM Users u WHERE u.status = ?1", 
  nativeQuery = true)
User findUserByStatusNative(Integer status);

But when I try to do the same (using a sequence)

@Query(value = "SELECT NEXTVAL(?1)", nativeQuery = true)
Long getNextSequenceByFleetId(String fleetId);

It does not work for me, although the sequence is correctly set in the DB.

SELECT NEXTVAL(F0001)  --> returns nextval 2

What am i missing here?

Thanks.

PS: I saw this post but the examples are not using the @Query annotation.

UPDATE :

Following the suggestions by @JB Nizet in the comments, i ve tried using the SpEL:

https://spring.io/blog/2014/07/15/spel-support-in-spring-data-jpa-query-definitions

@Query("select u from User u where u.age = ?#{[0]}")
List<User> findUsersByAge(int age);

I ve tried the following:

@Query(value = "SELECT NEXTVAL(?#{[0]})", nativeQuery = true)
Long getNextSequenceByFleetId(String fleetId);

but alas...

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''F0001')' at line 1

You can pass values as parameters to a query. But not table, column or sequence names. The database needs that information in order to prepare the plan for the query. — JB Nizet, Sep 18 '18 at 06:51
If I remember correctly, you can use SpEL, though, to simulate that by concatenating the parameter value. Google for it. Done it for you: https://spring.io/blog/2014/07/15/spel-support-in-spring-data-jpa-query-definitions — JB Nizet, Sep 18 '18 at 06:54
thanks a lot! i ve tried a bunch of ways but could not integrate a simple string. updated the question — Orkun, Sep 18 '18 at 07:35
FWIW JPQL is not a "native" query. SQL is a native query. JPQL is a totally different query language. Suggest that you fix the title of this — , Sep 18 '18 at 07:36
You can only use parameters in the WHERE clause not in the part before that. — M. Deinum, Sep 18 '18 at 08:20
@M.Deinum that is not correct. You could use a parameter as part of the select clause or in the order by or having clause. The constraint is that you can use it only where an expression is expected not in positions where an identifier is required. — Jens Schauder, Sep 18 '18 at 10:16
Trying to use it in the SELECT never worked for me but maybe I'm writing the wrong queries. The order, where and having did. But my comment was maybe a bit to short for that. — M. Deinum, Sep 18 '18 at 10:19

score 5 · Answer 1 · answered Sep 18 '18 at 10:13

JB Nizet gave you the correct answer.

You can pass values as parameters to a query. But not table, column or sequence names. The database needs that information in order to prepare the plan for the query.

Unfortunately, the "trick" with a SpEL doesn't work. SpELs get translated into bind parameters so the same constraints apply. There is one exception to the rule which is when the SpEL expression just uses the entity name as in this example taken from Spring Data JPAs integration tests:

@Query("update #{#entityName} u set u.active = :activeState where u.id in :ids")
void updateUserActiveState(@Param("activeState") boolean activeState, @Param("ids") Integer... ids);

This is intended for use in interfaces that get inherited for multiple repositories and probably doesn't really help you.

What you probably could do is to invoke a stored procedure, which performs the query to the sequence based on a parameter.

Native query with parameters using @Query in Hibernate

1 Answers1