Updating rows through a cursor in Hibernate

Bugmaster · **Joined:** Fri Jun 02, 2006 1:14 am **Posts:** 30

Hi All,

I'm currently using the following pattern in my JDBC code:

Code:

Connection conn = obtain a DB connection;
String sql = "some complicated SQL query";
PreparedStatement s = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = s.executeQuery();
while(rs.next()) {
  String foo = rs.getString("foo");
  Integer bar = rs.getInt("bar");
  Boolean baz = rs.getBoolean("baz");

  // Complicated logic goes here...

  rs.updateString("foo", foo);
  rs.updateInteger("bar", bar);
  rs.updateRow();
}

My application goes through hundreds of thousands of rows in this fashion, and it needs to do it fairly quickly. Is there a way to reproduce this pattern in Hibernate ? I.e., is there a way to tell Hibernate to update rows through the DB cursor, as opposed to issuing individual update statements for each row ?

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

No. This is a good situation to continue to use JDBC. That code probably shouldn't be migrated to hibernate.. except to use session.connection() to get the connection, instead of getting a whole new connection.

Bugmaster · **Joined:** Fri Jun 02, 2006 1:14 am **Posts:** 30

tenwit wrote:

No. This is a good situation to continue to use JDBC. That code probably shouldn't be migrated to hibernate.. except to use session.connection() to get the connection, instead of getting a whole new connection.

Thanks, that's a good point about connections. I'm not sure how to proceed, however.

The "complicated logic goes here" part of my JDBC-based code really is fairly complicated. I would love to replace it with a simpler POJO abstraction; I.e., I'd like to replace this:

Code:

 String foo = rs.getString("foo"); 
  Integer bar = rs.getInt("bar"); 
  Boolean baz = rs.getBoolean("baz"); 

  // Complicated logic goes here... 

  rs.updateString("foo", foo); 
  rs.updateInteger("bar", bar); 
  rs.updateRow();

With something like this:

Code:

  LineItem pojo = loadLineItem(rs);
  doComplicatedLogic(pojo);
  updatePojo(rs, pojo);

The problem is that, when I change my data model, I need to change my code in at least three places: the SQL query that retrieves the rows, the code that maps recordset columns to local variables, and the code that updates recordset columns with newly calculated values.

Is there a way I can use Hibernate's mapping layer to somehow simplify this process ? I.e., can I somehow get Hibernate to supply me with the loadLineItem and updatePojo methods, as well as (hopefully) a portion of the SQL statement ?

(EDIT: typos)

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Not as far as I'm aware. The best you can do is make use of intelligent flushing and transaction commiting to optimize your database writes to near-rowset performance, and maybe also using a stateless session, if it's appropriate. Check out sections 13.2 and 13.3 of the ref docs.

Bugmaster · **Joined:** Fri Jun 02, 2006 1:14 am **Posts:** 30

Well, I've found the ClassMetadata class ( http://www.hibernate.org/hib_docs/v3/re ... e-metadata ) . Originally, I thought about using that to build up my SQL queries and column mappings. I got as far as the Type class, but I got stuck there. Apparently, I need a Mapping class to go from Type to a SQL column and datatype... but... how do I obtain an instance of the Mapping ?

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

From the Configuration class, either getClassMappings() or getClassMapping(nameOfMapping). You get the Configration instance only during initial configuration, when you load the config file. It isn't retained by the SessionFactory, because it's big and heavyweight.

Bugmaster · **Joined:** Fri Jun 02, 2006 1:14 am **Posts:** 30

tenwit wrote:

From the Configuration class, either getClassMappings() or getClassMapping(nameOfMapping). You get the Configration instance only during initial configuration, when you load the config file. It isn't retained by the SessionFactory, because it's big and heavyweight.

Doh... I'm using Spring to manage my mappings for me. Is there any way to retrieve the Configuration instance after initialization ?

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

No, I don't think so. However, this situation is handled by Spring. I don't know exactly how (we're only slowly migrating to Spring here where I work), but I know that people have done what you need to. I presume that you can register a callback, or get the hibernate configuration object from a Spring object, or something.

I know that there are a couple of topics on the hibernate wiki about configuration with Spring. Start your search there.

scarface · **Joined:** Sun Jun 04, 2006 1:58 am **Posts:** 136

trust me this not the one of the best use cases for hibernate . I tried to do something on the same lines for the comfort of using pojos but i ended up with a big fat outofmemoryerror i tried everything from using stateless sessions to manually evicting objects ...but ended up using jdbc in the end.

Bugmaster · **Joined:** Fri Jun 02, 2006 1:14 am **Posts:** 30

Well, here are some snippets of my Spring based JDBC code:

Code:

    private static final String[] REVIEWABLE_ITEM_COLUMNS = {
            "id", "version", "subclass", "mount_point_id",
            "review_anchor_id", "dedup_key", "parent_id", "parent_email_id", "label", ...

...

// Inserting an item
                Object[] args = new Object[REVIEWABLE_ITEM_COLUMNS.length];
                int col = 0;
                args[col++] = i.getId();
                args[col++] = i.getVersion();
                args[col++] = i.getClass().getName();
                args[col++] = i.getMountPoint().getId();

...

// Reading an item
        String subclass = rs.getString(prefix + "subclass");
        try {
            Class clazz = Class.forName(subclass);
            ReviewableItem item = (ReviewableItem)clazz.getConstructor().newInstance();
            item.setId(rs.getLong(prefix + "id"));
            item.setVersion(rs.getInt(prefix + "version"));
            item.setMountPoint(new MountPointProxy(rs.getLong(prefix + "mount_point_id")));
            item.setLabel(rs.getString(prefix + "label"));
...

This gets really old really quickly, especially when I want to add or remove a column. I was hoping to use Hibernate to parse the HBM mapping files, and turn the above code into something like this:

Code:

StringBuffer itemColumns;
for(int i=0; i<props.length; i++) {
  itemColumns.append(props[i]).append(", ");
}

...

for(int i=0; i<props.length; i++) {
  args[i] = getBeanProperty(item, props[i]);
}

This way, I could modify the HBM mapping file, and not worry about changing my code in 4 places. The problem is, for this approach to work correctly, I need to know the SQL types of each property, and this is what I'm trying to do right now.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Found it. Apparently the session factory also has getClassMetadata(Class). So you can use session.getSessionFactory().getClassMetadata(Class).

Bugmaster · **Joined:** Fri Jun 02, 2006 1:14 am **Posts:** 30

tenwit wrote:

Found it. Apparently the session factory also has getClassMetadata(Class). So you can use session.getSessionFactory().getClassMetadata(Class).

I know, but I need an instance of a Mapping in order to retrieve the proper SQL data type for each Hibernate Type in the class metadata. How do I do that ?