Elegant ways to handle database views on hibernate entities?

Question

One of the main reasons I use Hibernate is that it provides the flexibility to switch to another database without having to rewrite any code.

But until now I did not figure out a good way to define additional views on the tables to which my hibernate entities are matched; I am still using simple SQL scripts for that. Is there a more elegant way to define views on tables managed by hibernate?

Ideally I would like to use HQL or another generic method to do the job, so that I don't have to worry about my SQL scripts being incompatible with other kinds of databases.

If there's a way to do that, a second issue would then be to get 'synthetic' read-only instances from these views, which should make it much easier to feed the aggregated data into a UI.

EDIT:

It seems as if I didn't make the problem clear enough, so here's what i am trying to do: I want to write code that is independent of the used database. Since I use hibernate, I would just have to change the dialect configuration file and could then use another DBMS.

Question: how to create views on my hibernate entities without relying on a specific SQL dialect (to keep everything portable), or even HQL? And if that's possible, can I use HQL to also query these views, i.e. to create read-only aggregate entities? Is there any additional hibernate plug-in to help me with that? Haven't found anything so far... :-/

Answer 1

Hibernate will not automatically create the views for you, as each dialect supports only a limited subset of the data-definition language (DDL) of the underlying database. Basically, it supports enough DDL to generate a working schema, but not enough to handle creation of "extra" objects like views.

All is not lost, though. Hibernate does give you the ability to create (and drop) additional database objects yourself in the XML mapping files, and those objects can be scoped to a particular dialect. For example, I could have a mapping like this:

<hibernate-mapping>
  <class name='com.mycompany.myproduct.Customer' table='tbl_customer'>
    <id name='id' column='customer_id'>
      <generator class='native'/>
    </id>
    <property name='name' length='50' unique='true' not-null='true' />
  </class>

  <database-object>
    <create>create or replace view read_only_cust...</create>
    <drop>drop view read_only_cust</drop>
    <dialect-scope name='org.hibernate.dialect.Oracle9Dialect' />
  </database-object>
</hibernate-mapping>

You're free to create whatever additional views you want by adding more "database-object" sections. You have to write the SQL (DDL) yourself for each database you want to support, but since they're scoped to the dialect, Hibernate will only execute the SQL for the dialect chosen at schema export time.

Answer 2

Had the same problem and found the following solution in the hibernate doucmentation:

There is no difference between a view and a base table for a Hibernate mapping. This is transparent at the database level, although some DBMS do not support views properly, especially with updates. Sometimes you want to use a view, but you cannot create one in the database (i.e. with a legacy schema). In this case, you can map an immutable and read-only entity to a given SQL subselect expression:

<class name="Summary">
    <subselect>
        select item.name, max(bid.amount), count(*)
        from item
        join bid on bid.item_id = item.id
        group by item.name
    </subselect>
    <synchronize table="item"/>
    <synchronize table="bid"/>
    <id name="name"/>
    ...
</class>

https://docs.jboss.org/hibernate/stable/core/manual/en-US/html_single/#mapping-declaration