Constructing table views with Hibernate

Database views are convenient to use especially for a view that represents a complex query or one that is used repetitively.

Here I will show you how to construct a database view using Hibernate.

In the HBM mapping file you can declare your view using the element subselect.
The SQL used should be native to the database you are using and should not be HQL.

You use the synchronize elements to identify which tables the SQL query uses to construct the view.

Below is an example:

   <!-- VIEW -->
    <class name="au.edu.apf.phenomebank.report.inventory.mouse.StrainCryo" >
        <!-- Postgres specific SQL call -->
        <subselect>
            SELECT '' || cs.id || s.id  AS id, s.id AS strain_id, cs.id AS cryosession_id 
            FROM cryo_session cs 
            JOIN cryo_session_result csr ON(cs.id=csr.cryo_session_id) 
            JOIN mouse m ON (csr.mouse_id=m.id) 
            JOIN strains s ON (m.strain_id=s.id) 
            GROUP BY cs.date_of_session, s.id, cs.id 
            ORDER BY cs.date_of_session
        </subselect>
        <synchronize table="cryo_session"/>        
        <synchronize table="cryo_session_result"/>
        <synchronize table="mouse"/>
        <synchronize table="strains"/>
        
        <id name="id" type="java.lang.Long" />

                
        <many-to-one name="strain" column="strain_id" cascade="none" not-null="true" />
        <many-to-one name="cryoSession" column="cryosession_id" cascade="none" not-null="true" />
        
    </class>

I combine two foreign key ID's to construct a unique key for the table view as follows:

'' || cs.id || s.id

The entity representing the view is a simple POJO as follows:

public class StrainCryo {

    /**
     * Arbitrary ID to create the view in Hibernate
     */
    private Long id;
    private Strain strain;
    private CryoSession cryoSession;
    
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public Strain getStrain() {
        return strain;
    }
    public void setStrain(Strain strain) {
        this.strain = strain;
    }
    public CryoSession getCryoSession() {
        return cryoSession;
    }
    public void setCryoSession(CryoSession cryoSession) {
        this.cryoSession = cryoSession;
    }
    
    
    
}