[Hibernate-issues] [Hibernate-JIRA] Created: (HB-581) Add ability to use hibernate syntax to do an "update"

[<leg...@at...>]

Message:

  A new issue has been created in JIRA.

---------------------------------------------------------------------
View the issue:

  http://opensource.atlassian.com/projects/hibernate/secure/ViewIssue.jspa?key=HB-581


Here is an overview of the issue:
---------------------------------------------------------------------
        Key: HB-581
    Summary: Add ability to use hibernate syntax to do an "update"
       Type: Improvement

     Status: Unassigned
   Priority: Minor

    Project: Hibernate2
 Components: 
             core
   Versions:
             2.1.1

   Assignee: 
   Reporter: Will Gayther

    Created: Sat, 27 Dec 2003 12:03 AM
    Updated: Sat, 27 Dec 2003 12:03 AM

Description:
Hibernate has several ways of writing abstract queries in a query language - native sql, the new psuedo-sql query through session.createSQLQuery(), and hql. Changes to the database are typically made by loading the object(s) to change, changing them, and resaving them to the database.

However, there are a few cases where this is incredibly inefficient. For example, I have a large number of "Link" objects in the database. Each link has a "displayIndex" - a long value that is used to determine which Links are displayed first. Naturally, I allow the user to change this ordering themselves. However, if there are a large number of links, and the user wants to move a link from the bottom of the ordering to the top, it would be extremely inefficient to load all of the links in-between the two spots, change all of their displayIndex's, then resave them, when sql like:
"update linkTable set displayIndex = displayIndex - 1 where displayIndex <= ? and displayIndex > ?"
would do the job much more efficiently with much less database traffic.

Of course, you can do this with straight sql. But that requires you to look up table names, column names, etc which breaks the abstraction which is the reason for using Hibernate in the first place.

What I propose is that you add a method, Session.createSQLUpdate(), that allows you to create and execute an sql update using the same syntax you that is used for Session.createSQLQuery(), so that the above statement could be written as:
"update linkTable set {link.displayIndex} = {link.displayIndex} - 1 where {link.displayIndex} <= ? and {link.displayIndex} > ?"
I know that in this example, since the property names and the column names are identical, it doesn't seem to do much good. But they can be different, and to write the query as sql requires you to stop thinking about objects, and start thinking "Are the property names the same as the column names?". This, together with my post on abstracting the table name rather than hard coding it (http://forum.hibernate.org/viewtopic.php?p=2183636#2183636) I think this would help keep the hibernate abstraction from being broken.

I would think that you could reuse the exact same parser used for Session.createSQLQuery(), so it wouldn't require any significant extra work for you guys (otherwise I wouldn't bring it up because it probably wouldn't be worth the time to do). Perhaps I'm wrong, or there's already a better way - what do you think?


---------------------------------------------------------------------
JIRA INFORMATION:
This message is automatically generated by JIRA.

If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa

If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira