Thursday, March 25, 2010

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;
    }
    
    
    
}

Monday, March 15, 2010

Externalizing configuration properties

Generally, for deployment specific configurations we would like to move them out of spring specific configuration files and into a properties file.The purpose of the bean-wiring file is to define the relationships between dependent objects, and should not be used to define deployment configurations.

I've come across 2 ways to Externalize configuration properties and it really depends on the technologies you have at your disposal.

They both acheive the same result of externalizing configuration properties, except that Maven has the added benefit of using Profiles to select an appropriate build. For example, you can have one profile for creating a test build and another profile for a production build.

Preferrably, I like to use the Maven Resource filtering strategy, because it allows you to easily create a different build depending on the type of deployment you'd like to use based on Maven Profiles. Here's a good quote describing Maven resource filtering:

You can use Maven to perform variable replacement on project resources. When resource filtering is activated, Maven will scan resources for references to Maven property references surrounded by ${ and }. When it finds these references it will replace them with the appropriate value in much the same way the properties defined in the previous section can be referenced from a POM. This feature is especially helpful when you need to parameterize a build with different configuration values depending on the target deployment platform.