Update table on the basis of multiple columns using grails domain

Question

I am trying to update one column in a table on the basis of multiple columns(in where clause) using grails domain.

My domain class is as follows:

class Objectattributestrans { String id String attrvalue // 50 // String transid // 50 int attribid

   static mapping = {
          id column: 'trans_id'  //['transid', 'attribid']
          table name: 'object_attributes_trans'
          attrvalue column: 'value'
          attribid column:'oa_attrib_id'
          version  false
          }

   static constraints = {
          attribid blank:false, nullable:false
          id blank:false, nullable:false
          attrvalue maxSize: 50, nullable:true
   }

}

and I am using this domain as follows for update:

 Objectattributestrans.findAllByIdAndAttribid(transacId, queryKey).each {

         it.attrvalue = updateVal.toString()
         it.save(flush:true) ; // this will perform "update"

 }

But,whatever the query is building is given below.

update object_attributes_trans set oa_attrib_id=?, value=? where trans_id=?

But,I want the update statement as

update object_attributes_trans set value=? where trans_id=? and oa_attrib_id=?

How can I do this using above domain?

Thanks.

Andrew Andrew · Accepted Answer · 2014-10-14T05:35:20

Your code sample is a bit unclear. I can't quite tell if you have a compound primary key.

That aside, when using a dynamic finder (findAllBy...) as you have in your example, GORM/Hibernate will retrieve a collection of all matching objects. If you then loop through them, updating each and calling save(), GORM/Hibernate will issue an UPDATE statement for each object. The UPDATE statements will reference only the primary key(s) / id field(s) rather than your original criteria.

It would be more efficient (and closer to what you are after?) to use a where query along with updateAll or a similar HQL statement.

1) Using a where query

def query = Objectattributestrans.where {
    transid == myTransid && attribid == myAttribid 
} 
int total = query.updateAll(attrvalue: updateVal)

2) Hibernate HQL method (note that this will not work with non-RDBMS data stores for which HQL support does not exist)

Objectattributestrans.executeUpdate(
    "update Objectattributestrans o set o.attrvalue = :newAttrvalue " +
    "where o.transid = :transid and o.attribid = :attribid",
    [newAttrvalue: updateVal, transid: myTransid, attribid: myAttribid])

Update table on the basis of multiple columns using grails domain

1 Answers