Wrong HQL query?

herbatniczek · **Joined:** Tue Nov 22, 2005 6:55 am **Posts:** 41

Whats wrong with this code?

Code:

createQuery(
                        "UPDATE CustomerActionModel as customerAction " +
                        "set customerAction.customer = (select Customer as customer where " +
                        "customer.customerId = customerAction.customerId) where customerAction.userTask.objectId = ?")
                        .setLong(0,userTask.getObjectId().longValue()).list();
                        

it says, that the query must start with select or from word....
so what is wrong with it?

snpesnpe · **Joined:** Sat Jun 12, 2004 4:49 pm **Posts:** 915

you can use only SELECT (no update) in hql query

herbatniczek · **Joined:** Tue Nov 22, 2005 6:55 am **Posts:** 41

thanks, I use hibernate 2.1

Now I want to do the same thing, but using SQL

THE_QUERY_STRING="UPDATE WKM_CUSTOMER_ACTION customerAction set customerAction.customer = (select customer.objectId FROM WKM_CUSTOMER customer where customer.customerId
customerAction.customerId) where customerAction.userTask = 15608"

customerAction - (it can be done without this alias) but should it?

With current THE_QUERY_STRING
two parameters are missing...
session.createSQLQuery(UPDATE_CUSTOMER_SQL, , );

What should I put here?

LaLiLuna · **Posted:** Wed Jan 11, 2006 7:56 am

list() is false

You must use executeUpdate

Regards Sebastian

herbatniczek · **Joined:** Tue Nov 22, 2005 6:55 am **Posts:** 41

executeUpdate is not resolved in my hibernate Query class. I use Hibernate 2.

And what about this SQL ? Could u repair it and add last two parameters to CreateSQLQuery? I need it only once.

LaLiLuna · **Posted:** Wed Jan 11, 2006 9:55 am

have a look in the reference. May be there is another query type available.
if not use sql in this case

you try to set a customer object but you select an id
HQL
UPDATE WKM_CUSTOMER_ACTION customerAction set customerAction.customer = (select customer FROM WKM_CUSTOMER customer where customer.customerId
missing = here ?
customerAction.customerId) where customerAction.userTask = 15608
SQL
update WKM_CUSTOMER_ACTION customerAction set customerAction.customer_field_in_db = (select id_field_in_db FROM WKM_CUSTOMER customer where ...)
where ...
Regards Sebastian and please rate.

CWitherow · **Posted:** Wed Jan 11, 2006 1:23 pm

UPDATE & DELETE are not supported for H2 HQL-- they are supported in H3 for batch operations. You must use direct JDBC and SQL in H2 in order to use UPDATE & DELETE.

I would recommend:
1. Store the query as a named query in a mapping file.
<sql-queryname="updateQuery1">
<![CDATA[
UPDATE table1 SET x = ? WHERE y = ?
]]>
</sql-query>
I found that I had to use position parameters (?) -- I'm using DB2/400.
2. Get the connection from Hibernate, get the SQL from mapping, set parameters and execute the SQL statement:
Connection con = getSession().connection();
Query qry = getSession().getNamedQuery("updateQuery1");
PreparedStatement stmt = con.prepareStatement(qry.getQueryString());
stmt.setString(1, 'xxxx');
stmt.setString(2, 'yyyy');
updateCount = stmt.executeUpdate();

I've used this several times and it works fine. Keep in mind that mixing direct JDBC/SQL alongside various Hibernate data access methods will cause inconsistencies between the database and the Hibernate session. So, use sparingly and with caution.
Curtis ...