3

I would like to know how to use prepared statement for insert query. Generally for select query I am using in following way. 

Query query = JPA.em()
    .createNativeQuery("select item_status from item_details where box_id=:boxnumber");
query.setParameter("boxnumber", boxNumber);

But when I am using insert query I am unable to use in the above way. 

Query query = JPA.em()
    .createNativeQuery("insert into item_details values(':item_status')");
query.setParameter("item_status", itemstat);

I am getting error like

java.lang.IllegalArgumentException: 
    org.hibernate.QueryParameterException: could not locate named parameter [item_status]
    at org.hibernate.ejb.QueryImpl.setParameter(QueryImpl.java:368) ~[hibernate-entitymanager-3.6.9.Final.jar:3.6.9.Final]
    at org.hibernate.ejb.QueryImpl.setParameter(QueryImpl.java:72) ~[hibernate-entitymanager-3.6.9.Final.jar:3.6.9.Final]

please any one help me to sort out this issue. Thanks in advance

default locale
  • 13,035
  • 13
  • 56
  • 62
Kathirvel Appusamy
  • 227
  • 3
  • 7
  • 16
  • 1
    possible duplicate of [JPA/Hibernate Native Queries do not recognize Parameters](http://stackoverflow.com/questions/3144235/jpa-hibernate-native-queries-do-not-recognize-parameters) – default locale Dec 12 '14 at 14:44
  • its not similar. My doubt regarding insert query. select query is working perfectly for me. issues is with insert query – Kathirvel Appusamy Dec 12 '14 at 14:48
  • Have you tried to use solutions suggested there? Did they work? If they didn't you might want to specify what exactly didn't work as expected. P.S. is select query also native? – default locale Dec 12 '14 at 14:51
  • Hold on a second. You error message says: `could not locate named parameter [boxnumber]` and your query doesn't even have `boxnumber` as a parameter. Are you sure that you posted exactly the problematic query? – default locale Dec 12 '14 at 14:55
  • Yes select query also native. Select query is working properly.Also the post you have mentioned not even have one insert example. I tried with some example which are available but its also not working. – Kathirvel Appusamy Dec 12 '14 at 15:00
  • My error message according to above post is could not locate named parameter [item_status]. I have cut shorted my question when I am posting but the error I forgot to change according to question. – Kathirvel Appusamy Dec 12 '14 at 15:02
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/66760/discussion-between-default-locale-and-kathirvel-appusamy). – default locale Dec 12 '14 at 15:04
  • 1
    Turns out the actual problem was the quoted query parameter. So, the real duplicate is [here](http://stackoverflow.com/questions/4144660/problem-with-positional-parameters-in-jpa-native-query) – default locale Dec 12 '14 at 15:44

1 Answers1

1

I could't tested but could you try this;

Query query = JPA.em().createNativeQuery("insert into item_details(item_status) values(?)") .setParameter(1, itemstat).executeUpdate();
Semih Eker
  • 2,389
  • 1
  • 20
  • 29