SQl/HQL Update Troubles

Leesy · **Posted:** Fri Dec 01, 2006 7:19 am

Hibernate version: 3.1

Name and version of the database you are using: MySQL 5.*

I was just wondering if somebody could give me some guidance with my SQL/HQL. I've got a number of rows in a table that I would like to update. I need to be able to use a join in the query as I only want to update certain fields.

According to the Hibernate In Action book, it would be best to do this using SQL. Fair enough. So I have my tried and tested SQL statement. But can I call this without having to implement my own JDBC connection? Thinks like session.createSQLQuery("...").executeUpdate() or session.createSQLQuery("...").uniqueResult() just aren't working.

Here's the query incase that helps:

Code:

UPDATE entry
INNER JOIN entries_index USING (index_id)
INNER JOIN collection USING (index_id)
SET state = -1
WHERE entry.state = 0 
AND collection.name = 'harvest'";

Any ideas on what I should be doing to execute this query with a minimum or fuss and without having to create an object for item I want to change?

andresgr · **Posted:** Fri Dec 01, 2006 7:28 am

Why do you say createSQLQuery() is not working? It should work...

Leesy · **Posted:** Fri Dec 01, 2006 7:33 am

The createSQLQuery itself works but what do I call on the return SQLQuery object to execute the update?

.executeUpdate() returns an error starting "Update queries only supported through HQL"
.list() or .updateResults() returns an error stating "Can not issue data manipulation statements with executeQuery()"

Here's the code I am trying to use. Still having no luck creating 1 HQL statement to do this either :(

Code:

    final Session session = HibernateUtil.getSession();
    Transaction transaction = null;
    try {
      String query = "update entry"
          + "inner join entries_index using (index_id)"
          + "inner join collection using (index_id) set state = -1"
          + "where entry.state = 0" + "and collection.name = 'harvest'";
      
      // Won't work :(
      session.createSQLQuery(query).executeUpdate();
      
      String hqlQuery = "update Entry e"
        + "inner join Index as i"
        + "inner join Collection as c"
        + "set e.state = -1"
        + "where e.state = 0 and c.name = :collectionId";
      
      int result = session.createQuery(hqlQuery).setString("collectionId", collectionId).executeUpdate();
      System.out.println(result);
    } catch (Exception e) {
      LOGGER.error("Error marking entries as gathered.", e);
      if (transaction != null && transaction.isActive()) {
        transaction.rollback();
      }
    } finally {
      if (session != null && session.isConnected()) {
        session.close();
      }
    }

Leesy · **Posted:** Fri Dec 01, 2006 7:48 am

I see what you mean Andresgr - it should be working. Another developer has used the same code in the same project and it works there. There must be something throwing things off.

andresgr · **Posted:** Fri Dec 01, 2006 8:12 am

Sorry, i was wrong.

Hibernate does not support update queries in CreateSQLQuery.

You should use plain JDBC to execute that update...

wykoff · **Posted:** Fri Dec 01, 2006 12:21 pm

I was able to execute an SQL query like this:

Code:

SQLQuery query = (SQLQuery)getSession().getNamedQuery("hce.updateFactorValue");

        query.setParameter("item", dp.getItemInternalId());
        query.setParameter("ts", new Timestamp(dp.getUtcTimestamp()));
        query.setParameter("degrad", dp.getDegradationFactor());
        query.setParameter("severity", dp.getComputedSeverity().ordinal());
        query.setParameter("id", dp.getInternalId());
        
        return query.executeUpdate();

where my named query looks like:

Code:

     <sql-query name="hce.updateFactorValue">
              update SHM_HEALTHFACTORVALUE 
              set degradationfactor=:degrad
              , HEALTHFACTORITEM_ID=:item
              , timestamp=:ts
              , severity=:severity
              where id = :id
      </sql-query>

I'm using Spring's HibernateDaoSupport class.

max · **Posted:** Sat Dec 02, 2006 3:39 am

nothing to do with spring etc. You just need to use Hibernate 3.2 to get executeUpdate support for createSQLQuery

batmat · **Posted:** Tue Dec 12, 2006 6:44 am

max wrote:

Nothing to do with spring etc. You just need to use Hibernate 3.2 to get executeUpdate support for createSQLQuery

Are you sure this feature is present. I just tried to do something a bit different with 3.2.1.ga : call a named sql-query, this sql-query tries and do an update, but this is rejected.

max · **Posted:** Tue Dec 12, 2006 6:56 am

what do you mean by rejected ?

And yes it is present as you can see in the unittest and that wykoff verified it.

batmat · **Posted:** Tue Dec 12, 2006 8:03 am

Arf, forget about it. I just realised I was using the wrong "user lib", so 3.1.3 instead of 3.2.1.ga. OK, it works. I can't make the <synchronize> tag work to refresh the session, though, but that's another problem :). Thanks and sorry for the noise in this thread that was not mine :-/