One of the more useful tools associated with Hibernate is the hbm2ddl tool. Depending on how this is configured in your application, you can use this to validate, update or create your database schema on application startup. This is controlled using the hibernate.hbm2ddl.auto property, which can be set to one of the following values: validate | update | create | create-drop (more here). In an development environment I’ve found it very useful to set this property to update. Doing so means that changes you make to your entities are immediately reflected in the database when you restart your application. The same is true for changes to entities made by your team members, restarting your local application following updates from your code repository will automatically update your database with your teams changes.

On a project I’ve recently been working on we had a requirement that a number of views be created and maintained as part of our application. Try as I might I haven’t been able to find any built in support for maintaining a set of views using hibernate/hibernate annotations (please correct me if I’ve missed something obvious). So we decided that we would build a little tool which would fulfil a similar job as hbm2dll on update but for views. What we were basically after was a tool that would run once at application startup, which would create a number of preset database views. 

We’re using Spring as our core application framework and we already use Quartz for scheduled jobs. The approach we decided to take was to create a new bean which was responsible for executing any number SQL CREATE VIEW statements. This bean would in turn be executed using a Quartz SimpleTriggerBean implementation that would be set to run once at application startup. Cool. Lets see a simple example of this in practice: 

1. The Execute Views Bean - UpdateRequiredViews

First we create a bean whose job it is to execute each of the view scripts at startup. This is an abridged and simplified version to get the idea across (for the sake of brevity I’ve excluded error handling etc, you get the idea…), which just executes a list of SQL strings:

import java.util.List;

import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

public class UpdateRequiredViews extends HibernateDaoSupport
{
/**
* List of SQL create view statements to execute.
*/
private List<String> viewScripts;

public void createOrUpdateDatabaseViews()
{
for (String script : this.viewScripts)
{
this.getSession().createSQLQuery(script).executeUpdate();
}
}

public void setViewScripts(List<String> viewScripts)
{
this.viewScripts = viewScripts;
}
}

2. The Bean Config

We’re using 3 bean definitions here. The first is for our UpdateRequiredViews bean, which we define as a simple bean. We inject a reference to the hibernate session factory along with a list of SQL strings to execute - obviously embedding SQL in bean definitions isn’t really a viable technique in a production system, in our finished version we actually injected a list of paths to files, each containing blocks of SQL statements that needed to be executed.

	<bean name="updateRequiredViews" class="org.antech.cwms.core.tools.UpdateRequiredViews">
		<property name="sessionFactory" ref="sessionFactory"/>
		<property name="viewScripts">
	        <list>
	        <value>
	        	CREATE OR REPLACE VIEW salesaccounts AS
			SELECT c.coid AS coid, a.accountcode
			FROM company c, companyaccount a
			WHERE c.accountid = a.id;
		</value>
	        <value>
			CREATE OR REPLACE VIEW useraccount AS
			SELECT u.userid, p.name
			FROM user u, person p
			WHERE u.userid = p.userid;
		</value>
	      </list>
	    </property>
	</bean>

Next we define a SimpleTriggerBean which we use to set the scheduling of this job. Remember we only want this to execute once at application startup, so we set the ’startdelay’ field to 0 so execution occurs immediately and the ‘repeatCount’ to 0 so that task execute just once.

	<bean id="simpleTriggerBean" class="org.springframework.scheduling.quartz.SimpleTriggerBean">
		<property name="jobDetail" ref="methodInvokingJob"/>
		<property name="startDelay" value="0"/>
		<property name="repeatInterval" value="0"/>
		<property name="repeatCount" value="0"/>
	</bean>

  

Finally we need to link these two beans together, so we declare one more bean which tells the SimpleTriggerBean which bean and which method to execute.

	<bean id="methodInvokingJob" class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean">
	  	<property name="targetObject" ref="updateRequiredViews" />
	  	<property name="targetMethod" value="createOrUpdateDatabaseViews" />
	</bean>

 

So there we have it, providing you follow the above set up then everytime your application starts up your database views will be re-created each time. Remember to use the CREATE OR REPLACE syntax (using just CREATE will obviously not work if a view with that name already exists). Also, if you’re using hibernate then it’s probably a while since you’ve hand-cranked SQL statements, don’t forget that SQLinForm is a really useful tool for beautifying your scripts.

I think this approach has a number of advantages. Firstly each developer in your team and each system deployment will always automatically have the most recent versions of the database views required by the application. Secondly, embedding the view scripts into the bean definitions (or preferably into external files) means that the view scripts become automatically source controlled (providing you’re using some kind of source control software). You then get all the advantages of source control applied to your view scripts - revision control etc.

Of course the implementation I’ve shown above is (purposefully) quite simplistic and there any number of areas where this would need improvement before making it into a production system. As mentioned above, moving your SQL statements to external files is an important step. As is proper error handling (my real implementation of this keeps a tally of the scripts and statements run and e-mails notifications of any errors to the administrator). You might want to include validation of SQL statements to prevent abuse - maybe ensuring that only SQL statements that start with ‘CREATE OR REPLACE VIEW’ for example are executed. Similarly, if you opt to shift your scripts to external files then make sure that the external files are either part of your source control system or properly protected - the potential for trouble with unprotected SQL scripts is enough to give most security professionals cold sweats!