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
– JB Nizet, Commented Sep 18, 2018 at 6: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: spring.io/blog/2014/07/15/… — JB Nizet
– JB Nizet, Commented Sep 18, 2018 at 6:54
thanks a lot! i ve tried a bunch of ways but could not integrate a simple string. updated the question — Orkun
– Orkun, Commented Sep 18, 2018 at 7: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 — user3973283
– user3973283, Commented Sep 18, 2018 at 7:36

Jens Schauder · Accepted Answer · 2018-09-18 10:13:31Z

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.

Collectives™ on Stack Overflow

Native query with parameters using @Query in Hibernate

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related