0

When you read in a result set in Groovy it comes in a collection of maps.

Seems like you should be able to update values inside those maps and write them back out, but I can't find anything built into groovy to allow me to do so.

I'm considering writing a routine that allows me to write a modified map by iterating over the fields of one of the result objects, taking each key/value pair and using them to create the appropriate update statement, but it could be annoying so I was wondering if anyone else had done this or if it'sa vailable already in groovy.

It seems like just a few lines of code so I'd rather not bring in hibernate for this. I'm just thinking a little "update" method that would allow:

def rows=sql.rows(query)
rows[0].name="newName"
update(sql, rows[0])

to update the first guy's name in the database. Anyone seen/created such a monster, or is something like this already built into Groovy Sql and I'm just missing it?

(I suppose you may have to point out to the update method which field is the key field, but that's doable...)

Bill K
  • 62,186
  • 18
  • 105
  • 157

1 Answers1

3

Using the rows method will actually read out all of the values into a List of GroovyRowResult so it's not really possible to update the data without creating an update method like the one you mention.

It's not really possible to do that in the generic case because your query can contain joins or a column reference that is an aggregate, etc.

If you're selecting from a single table use the Sql.eachRow method however and set the ResultSet to be an updatable one, you can use the underlying ResultSet interface to update as you iterate through:

sql.resultSetConcurrency = ResultSet.CONCUR_UPDATABLE
sql.resultSetType = ResultSet.TYPE_FORWARD_ONLY
sql.eachRow(query) { row ->
    row.updateString('name', 'newName')
    row.updateRow()
}

Depending on the database/driver you use, you may not be able to create an updatable ResultSet.

Phuong LeCong
  • 1,834
  • 16
  • 19
  • I really like that, didn't know you could do it that way. It doesn't quite fit my problem (I'm really wanting to update something I queried a while ago), but it's going to be very useful in the future. – Bill K Feb 09 '13 at 00:52
  • Actually if one used this pattern and knew the unique key he could iterate over the same exact query searching for identical keys to the original results, copy over the modified fields and then write each modified record back out--this would be really close to what I want... – Bill K Feb 09 '13 at 01:11