4

I have to run the following Oracle query using Spring Data JPA native queries.

create user "kshitij" identified by "qwert123"

I am trying this :

@Query(value="create user \"?1\" identified by \"qwert123\"",nativeQuery=true)
    void createUser(String userName);

However when I wrap ?1 by "" then it Hibernate stops treating ?1 as variable to be substituted by userName, instead as actual string. How would I accomplish running the above mentioned Oracle query in spring data jpa?

Alongwith create I would also like to check if user exists.

Current not working approach : 

@Query(value="select username from all_users where username = \"?1\"", nativeQuery=true)
Kshitij Kohli
  • 4,055
  • 4
  • 19
  • 27
  • Either use @Rohan Shah's answer or remove the double quotes around `?1`. Replace your query by `@Query(value="create user ?1 identified by \"qwert123\"",nativeQuery=true) void createUser(String userName);` Also, add the `@Modifying` modifier. – Naanavanalla Jan 02 '20 at 13:34
  • why you want double quote "" ...... it can be CREATE USER myuser IDENTIFIED BY password – DEBENDRA DHINDA Jan 09 '20 at 06:09
  • you can check https://stackoverflow.com/questions/56310935/queryexception-jpa-style-positional-param-was-not-an-integral-ordinal/56315857#56315857 – DEBENDRA DHINDA Jan 09 '20 at 06:10

4 Answers4

1

Have you tried to use parameters? There is an annotation for telling the native queries what the parameters are and it is like this,

@Query(value="create user :userName identified by \"qwert123\"",nativeQuery=true)
    void createUser(@Param("userName")String userName);
dxjuv
  • 879
  • 1
  • 8
  • 28
  • Using that will give out the same issue as ?1 since after wrapping :userName in quotes, ie ":userName" it again becomes a string literal. What I want to accomplish is have variable getting substituted alongwith quote wrapping around them. – Kshitij Kohli Dec 19 '19 at 04:23
  • But he wants quotes in his Query @SternK – dxjuv Dec 19 '19 at 08:59
  • If I don't wrap the parameter in quotes I'm running into the following : https://stackoverflow.com/questions/59409574/parameterized-queries-to-create-a-user-throwing-missing-user-or-role-name-except – Kshitij Kohli Dec 19 '19 at 12:42
0

Can you try usiig this code?

@Modifying
@Transactional
@Query(value="create user :userName identified by \"qwert123\"",nativeQuery=true)
void createUser(@Param("userName") String userName);
sud007
  • 5,824
  • 4
  • 56
  • 63
Jaganath Kamble
  • 506
  • 2
  • 10
0

It's quiet easy, but I also needed some time to figure it out!

When you are using postgres, there's a function provided to achieve double quotes (there is also one for single quotes). The JPA-Native-Query will execute it on the parameter-value!

quote_ident(:param)

This works with named query parameters. Haven't tried it yet with unnamed, but that doesn't make a huge difference at all!

olinator98
  • 1
  • 1
-1

Place this on top of class or entity.

@NamedQueries({
    @NamedQuery(name = "EntityName.getUserName", query = "SELECT u FROM User u WHERE u.username = :username")
})

Named Query

Atul Jain
  • 1,035
  • 2
  • 16
  • 24
  • How would solve this issue? The query wold still run without the double quotes surrounded on it. From what I have observed when spring data jpa replaces params and generates query on the fly it treats the param surrounded by single quoes. This would work in case of select * where query, but for create user / drop user query this is an invalid oracle query, running it directly on Oracle fails too, that's why it is important for me to enclose them in double quotes. – Kshitij Kohli Jan 08 '20 at 10:48
  • Are you setting a parameter value username? – Atul Jain Jan 08 '20 at 11:31