How to create/call a sql view in Hibernate

Question

Here is the view created in document.hbm.xml

<database-object>
    <create><![CDATA[CREATE VIEW docView
     AS
     SELECT * from document;
     GO]]></create>
    <drop>DROP VIEW docView</drop>
    <dialect-scope name='org.hibernate.dialect.SQLServerDialect' />
</database-object> 

Now how to call this view in my method

Tried calling like this

Session session = sessFactory.openSession();
Query query = session.createSQLQuery("docView");
List<?> list = query.list();

Ended up with

Caused by: java.sql.SQLException: The request for procedure 'docView' failed because 'docView' is a view object.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)

Any Idea or any otherway to call sql view in hibernate?

In short is there a way like Is there any way to call view just like a stored procedure??, without creating a new entity??

Solution 1

You can work with a DB view as if it were a regular entity table. Define an entity class, either with the @Entity annotation or an equivalent XML and an arbitrary subset of the view's columns as fields.

An important point is that you should not change the values in the entity, as the view is read-only.

EDIT: I am not aware of a way to use a view like a stored procedure. If the purpose of your stored procedure is querying over multiple entities as implied in your comment, you could either:

• make the view 'broad' enough to contain all the necessary attributes of the needed entities
• relate to the relevant entities using foreign key columns in the view and regular @*To* annotations for the entity that is mapped to the view.

I am afraid this does not bring you very far, since you still have to either use native SQL or define an entity.

Solution 2

Create an entity to map it to your view, then use it for querying your view

@Entity
@Table(name = "docView")
public class DocView {

    // Put all fields that you use in your view
    documentField1;
    documentField2;
    .
    .
}

Then you could make your query like this:

Session session = sessFactory.openSession();
Query query = session.createSQLQuery("from DocView");
List<?> list = query.list();

Solution 3

Hibernate view is not a named query. You can create the view in you DB and then create the entity pojos with the view in mind. Hibernate will treat these entities as mapped to a view and then you can perform you operation as you normally do for a hibernate entity.

1. You are writing a create DDL query to create the view which will only be called if the hbm2ddl property is set correctly.
2. Create view is a DDl query and it does not return a list of values. You cannot call the create view query as if its a named sql query
3. Once the view is created you can write your named sql queries which can retrieve data from the view. For that all you need is a POJO which maps to the view and the named query to get the data.

Comments

[Suresh Atta]

Can you please refer this once ? stackoverflow.com/questions/901537/… , Am I missing something here ?

[Pratik Shelar]

You can write that and if your hbm2ddl property is correctly set hibernate will create the view but you cannot call the view query as a SQL named query because its a DDL query and not a query which returns a list.

[Suresh Atta]

Is there any way to call view just like a stored procedure??