NewInstance

I never used - and liked - so much database views, but after last experience I had to change my mind. Now I think that database views can be very helpful in application development, and system integration.

Our last application is a sort of web based Console to an heterogeneous systems, in which great part of the job is done by the Console itself.
The Console is also responsible to generate reports on its behaviour and external systems states. Systems communicate thru sockets and RPC calls and share data thru database tables: this can be implemented by database links and other tricky stuff that most of dba knows very well.
Systems receiving informations from users write data to some tables with many fields, and those tables are visible to our Console application.

We used Hibernate as persistent layer since the start of the development and it has been a great experience. But, as far as I know, Hibernate does not support natively database views. To solve the problem, we simply mapped our objects to handly created views, and used them as they were common tables. Using hibernate query language we filter and aggregate informations according system administrator needs. It worked greatly.
A big advantage on using database views, is that you can use proprietary DB functions to manipulate data to be used, whitout being locked to the specific database. For example, we needed a way to group by date, but in this table we had timestamps:

CREATE TABLE ORIGINAL_TABLE (
  name varchar,
  surname varchar,
  reg_time timestamp
);


CREATE VIEW OUR_VIEW AS
    SELECT
        name,
        surname,
        TRUNC(reg_time) AS reg_date,
        reg_time
    FROM
        ORIGINAL_TABLE;

In this way we can create reports thru Hibernate using OUR_VIEW and grouping by reg_date without having to call or reference the trunc function in our source code. Trunc is supported by hibernate natively - it is translated to various db dialects - but this approach could be also more useful to avoid calling proprietary db functions, in particular when the db function is not supported by hibernate. Let's suppose that trunc was not supported by hibernate, in this case the only reference to trunc is inside the DDL of the view; nothing in our code. Using another DB we have just to create the view using the appropriate function to truncate dates, and no code intervention will be needed.

The only problem is that we are accustomed to let hibernate generate the schema for the tables using the schema generator. But, as we mapped our views as tables, it also tries to generate the DDL for our views as "CREATE TABE ...", so that we have to manually remove those DDL and replace them with "CREATE VIEW ...". I consider this a problem, as doing that manually could be the occasion for possible errors.

It would be nice if hibernate was supporting views. Unfortunately, AFAIK, it does not do that. (If I am wrong, I would be happy to have some advice in comments of this post.)
I think it would be trivial to implement this: we've seen that Tables and Views works in the same manner, for hibernate (at least when used in read-only): it's just only a matter of the schema-generation, then. A view could be mapped with XML as in following:

<class name="ClassName" view="VIEW_NAME">

   <view-hql>HQL query to generate the view</view-hql>
   <property name="propertyName" column="VIEW_COLUMN" type="..." />

...
</class>

In this way, the schema generator should only translate the hql query to specific sql and append "CREATE VIEW <view-name> AS ..." in front of the generated query.
Maybe it could be even be better to let the programmer to write a raw SQL, so that we can map views using proprietary function. The problem, in this case, is that we are going to lock our mapping XMLs to a specific vendor. But it's not that problem comparing to the flexibility gained.

I know, nothing is easy as it intitially seems. There could be some reason why hibernate does not implement this, but I didn't find this reason until now.