Hello, need to support something like this:

I have a database table T and a database view of T - TV. I would like to configure hibernate to read from the view (TV), but write to the table (T). Writes can be strictly limited to T (e.g. none of the joined tables need to be updated).

So what I don't understand is how this could be accomplished with the class tag:

<class name="TClass" table="T" >
...
</class>

Thanks!
John

If your database supports updatable views, then just use the view all the time.

One hibernate-only approach is to drop the view entirely, and use the join tag to implement the view in your mapping file. The join tag goes after your proprty/many-to-one/one-to-many... tags in your class element. If you never want to modify the joined columns, don't forget to add insert="false" and update="false" so that hibernate can optimize things. This works well if you're getting several columns from one other table.

Finally, you can use the formula attribute of the property element to implement pure derived columns. This works well if there's only one column from another table.

tenwit -

Thanks for your reply! I am prototyping a replacement of our existing OR framework with Hibernate, so I'm trying to keep as much the same as makes sense. The read-from-view/write-to-table is pretty engrained in the existing framework, so I'm worried that having to ditch the views might invalidate the use of Hibernate all together to the people that will determine if the prototype is a success. We are using Oracle which does support writable views, however a quick experiment seemed to indicate our views aren’t structured in such a way that allows them to be written to at present. I appreciate your suggestions though.

Would it be possible to extend EntityPersister to swap the view/table? I guess its naïve, but at some level it seems like all I want to do is query/load from one table and insert/update to another 

I don't know too much about the internals, so I can't answer your EntityPersister question. Are you sure that your DBS doesn't handle updatable views? They're becoming more common, maybe you just need to update the version of your DB.

If modelling your views as join elements in your mapping file isn't going to cut it with the powers that be in your company, then I'd probably go with using two classes, one read-only (using the view), and one normal using the table. Then write factory save methods which instantiate instances of the table-based classes from instances of the view-based classes. If the views that you use always add the same columns (something from an auditing table, maybe?) then you can do it all in base classes, so it won't be too hard.

Reading from a view/writing to a table is an implementation detail. If you're choosing your DAL based on an implementation detail like that, you have to expect that not all DALs are going to work for you.

So write a custom <loader> query for that class mapping that executes SQL against your view, and map your class to the base table for all DML.

Thanks Christian,

I tried this today, the load works as expected but the problem is that * queries no longer work because the view-only property is included in the select against the table, which results in a sql error. I'm looking into subselect or lazy fetching for the view-only fields, think this could work?

Thanks!

E.g.

<hibernate-mapping>

<class name="B" table="T">
...
<property name="x" column="X" />
<property name="view_y" column="VIEW_Y" insert="false" update="false"/> <-- this is returned for all queries which occur against the table T but it only exists on the view
<loader query-ref="loadFromView"/>
</class>

<sql-query name="loadFromView">
<return alias="v" class="B" lock-mode="upgrade"/>
SELECT {v.*}
FROM V v
WHERE ID=?
</sql-query>

<hibernate-mapping>

Oracle at least is very restrictive about how the views are structured, some of our views are too complicated to write to.



That's a pretty good idea, if I can't figure out how to do this with a configuration only, that's probably what I'll do.



I have to consider "implementation details" like this because they have a huge impact on the amount of work we need to do during the migration.

oracle support updatabale views and if you can't do it with oracle it is hard with hibernate, too

you have to write instead of trigger on view and trigger write to table instead to view (in hibernate you use view complete) - you want this and i don't know how hibernate or what ever can do it better

It must be better to map view as is and to change DML (opposite way as it was sugested) it will work as client side "INSTED OF" trigger.

I figured this out today, a working solution when you want to read from a view but write to a table is simply to map the class against the view, and impement <sql-insert>, <sql-update> and <sql-delete> against the table. This way load, and all queries go against the view, and updates, inserts and deletes against the table.

E.g.

<class name="MyClass" table="A_VIEW">
...
<sql-insert>INSERT INTO MY_TABLE VALUES ...</sql-insert>
<sql-update>UPDATE MY_TABLE SET X=?,Y=?...</sql-update>
<sql-delete>DELETE FROM MY_TABLE WHERE ID=?</sql-delete>

</class>