CODEWITHSUNDEEP
javahibernate
Kathir
Kathir

Reputation: 33

Hibernate createSQLQuery() - list() and executeUpdate() - delete and select statement

We are using the following SQL Statement and execute via createSQLQuery() - list() instead of executeUpdate()

a) delete from employee where nbr in ('1', '2', '3')
b) delete from emp_details where empNbr=(select nbr from employee where name = 'Somu')

Both the queries works fine in SQL Client but throws the following exception 

org.hibernate.exception.SQLStateConverter.handledNonSpecificException
(SQLStateConverter.java:126), org.hibernate.exception.SQLStateConverter.
convert(SQLStateConverter.java:114),org.hibernate.exception.
JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66),  
org.hibernate.loader.Loader.doList(Loader.java:2235), 
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129), 
org.hibernate.loader.Loader.list(Loader.java:2124), 
org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312), 
org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1723), 
org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165), 
org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)

There is no issue with the query as the query works fine in SQL client.

Question:

1) Can we pass or execute deletion script via createSQLQuery() - list() or not?
2) Is it wrong to use createSQLQuery() - list() for deletion script?

Upvotes: 2

Views: 9711

Answers (2)

Sagar Panda
Sagar Panda

Reputation: 561

It is a wrong practice to use .list() for any update or delete script.

As mentioned above by Andre, you need to use .executeUpdate which has a return type of int, returning the number of rows affected by the executeUpdate command.

SQLQuery sqlQuery = session.createSQLQuery("delete from employee where nbr in (1,2,3)"); sqlQuery.executeUpdate();

Also its a good practice to use named parameters for passing your parameters in the query. (Read this for reference)

Upvotes: 3

André R.
André R.

Reputation: 1647

you have to call executeUpdate() instead of list on the query you get back from createSQLQuery() if you want to execute a delete statement !

Upvotes: 4

Related Questions