Recently I encountered a situation with a client Grails Application that required a read-only domain attribute. The application had a backend table where one column (call it
A) was populated strictly by a batch database process. The table needed to be mapped to a Grails Domain class. Certain columns (call them
B through D) were to be updated from the UI. Attribute
A should not be writable, but needed to be accessed for read-only operations.
The catch here is when an update occurs on a mapped domain class, GORM with Hibernate issues an update on all columns,
regardless of which columns have a value change. So even if attribute
B is changed, an update will be issued on all columns
A through D. If the database is configured with a specific column as read-only, you will get an error along the lines of: “failure to insert into a read-only column”.
We propose two solutions:
- Derived Properties
- Hibernate’s Dynamic update.
Let’s contrive a simple example with a domain class to understand the issue. Perhaps there is a
Team with a
coach are read-write attributes while
nationalRank is the attribute which is read-only.
It is updated strictly by a database batch process.
Given a row already exists, we then issue an update with the statement:
Hibernate will generate the log shown below. As you can see
nationalRank is included in the statement, which will end up in an error due to the database constraint of read-only.
To prevent hibernate from including an attribute in the update statement, we can use the
formula mapping for
nationalRank. This means that GORM will allow us to access the property as though it were a normal column, but will ignore it for update operations (because formulas are, by definition, not updateable). In this case, the formula simply returns the value of the column, but the net effect gives us the desired behavior.
Now upon updates, the following hibernate log is generated (note the omission of
A derived property is one that takes its value from a SQL expression. E.g.
tax formula: 'PRICE * TAX_RATE'. The formula expressed in the
ORM DSL references to NATIONAL_RANK instead nationalRank. We are echoing a value which must be set
elsewhere and cannot be inserted through GORM. So returning the column value directly achieves a read-only effect. DerivedProperties Reference
An alternate way of achieving the same result is to use hibernate’s
Hibernate whether to include unmodified properties in the SQL UPDATE statement. This excludes any attribute from the update
statement which is not dirty.