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

Answer 1

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.