Thursday, May 14, 2009

What would Hibernate do? Avoidance of hbm2ddl.auto=update in production

For the past 9 months I have been using Hibernate Annotations (along with the Spring Framework) to build my development database. Although I have a reasonably strong grasp of SQL I would still not consider myself fluent. Hibernate does all the hard work for me. Truth be told, there is a substantial learning curve to mastering mapping entity bean associations/relationships with Hibernate annotations. However once you finally develop an understanding (or find enough examples of what you want) it is very easy to continually evolve a data model throughout system development. Using annotations in conjunction with a suitably crafted import.sql file I can go from zero to system in no time. So after several months' development I am on the brink of having a production ready database containing precious real data that I do not want to loose. Should I take the risk of using "hbm2ddl.auto=update" to incorporate last minute database changes? I think not. I would not do this and this is not because I don't trust Hibernate but more because I do not trust myself. If I accidentally used hbm2ddl.auto=create (as I do regularly in my development instance) then bang goes my production database and all the data it contains.

So I wrote a small program to let me see what Hibernate would do if I ran an update without the associated risk of actually performing an update. I have a Spring applicationContext.xml which contains something like the following (note the complete absence of hbm2ddl.auto property setting):


<bean class="org.springframework.beans.factory.config.PreferencesPlaceholderConfigurer">
<property name="location" value="jdbc.prod.properties" />
</bean>
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName">
<value>${jdbc.driverClassName}</value>
</property>
<property name="url">
<value>${jdbc.url}</value>
</property>
<property name="username">
<value>${jdbc.username}</value>
</property>
<property name="password">
<value>${jdbc.password}</value>
</property>
</bean>
<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">${hibernate.dialect}
</prop>
</props>
</property>
<property name="annotatedClasses">
<list>
<value>edu.bristol.entity.Tom</value>
<value>edu.bristol.entity.Dick</value>
<value>edu.bristol.entity.Harry</value>
</list>
</property>
<property name="dataSource" ref="dataSource" />
</bean>

Now for my program, one thing that I have noticed is that update scripts that Hibernate would create will add new fields to a database but will not delete existing columns (even if these have been removed from the annotated entity). It makes a good deal of sense not to delete columns from a production database without knowing exactly what you are doing so this is a good thing!


import java.sql.Connection;
import java.sql.SQLException;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.dialect.Dialect;
import org.hibernate.tool.hbm2ddl.DatabaseMetadata;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.orm.hibernate3.LocalSessionFactoryBean;

public class AcquireScripts {

public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
// ampersand is very significant - it means get me an
// instance of the beanfactory and not give me a bean
LocalSessionFactoryBean sfb = (LocalSessionFactoryBean) context.getBean("&sessionFactory");
Configuration cfg = sfb.getConfiguration();
Dialect dialect = Dialect.getDialect(cfg.getProperties());
//printDropSchemaScript(cfg, dialect);
//printSchemaCreationScript(cfg, dialect);
printSchemaUpdateScript((SessionFactory) sfb.getObject(), cfg, dialect);
}

private static void printSchemaCreationScript(final Configuration cfg, final Dialect dialect) {
String[] schemaCreationScript = cfg.generateSchemaCreationScript(dialect);
for (String stmt : schemaCreationScript) {
System.out.println(stmt + ";");
}
}

private static void printDropSchemaScript(final Configuration cfg, final Dialect dialect) {
String[] dropSchemaScript = cfg.generateDropSchemaScript(dialect);
for (String stmt : dropSchemaScript) {
System.out.println(stmt + ";");
}
}

private static void printSchemaUpdateScript(final SessionFactory sf, final Configuration cfg, final Dialect dialect) {
HibernateTemplate hibernateTemplate = new HibernateTemplate(sf);
hibernateTemplate.setFlushMode(HibernateTemplate.FLUSH_NEVER);
hibernateTemplate.execute(
new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException, SQLException {
Connection con = session.connection();
Dialect dialect = Dialect.getDialect(cfg.getProperties());
DatabaseMetadata metadata = new DatabaseMetadata(con, dialect);
String[] schemaUpdateScript = cfg.generateSchemaUpdateScript(dialect, metadata);
for (String stmt : schemaUpdateScript) {
System.out.println(stmt + ";");
}
return null;
}
});
}
}

Notice the cunning use of the ampersand to acquire an instance of the LocalSessionFactoryBean rather than the actual SessionFactory itself. So there we have it, all of the cleverness of Hibernate annotations working out what SQL we might need to update our database but none of the risk.

6 comments:

ismjml said...

This could be quite useful. The only problem I find is this "jdbc.prod.properties" - what is that? I tried running your tool, but it crashes with exception:



java.io.FileNotFoundException: class path resource [jdbc.prod.properties] cannot be opened because it does not exist



I would be very greatful if you could explain me a little what is this jdbc.prod.properties.
Note: Comment imported. Original by Saulius at 2009-06-27 00:31

ismjml said...

Hi Saulius,



jdbc.prod.properties is my database properties file. Containing something like (precise details will vary depending on your database):





jdbc.driverClassName=oracle.jdbc.OracleDriver

jdbc.url=jdbc:oracle:thin:@localhost:1521:oracle

jdbc.username=scott

jdbc.password=tiger





I hope this helps.


Note: Comment imported. Original by markmc website: http://content.mark-mclaren.info/ at 2009-06-27 10:11

ismjml said...

Is there any option to work with import.sql with update, as when I am working with import.sql using create, it deletes the existing table creates a new table for each deploymnet of my application
Note: Comment imported. Original by Mahesh Yamsnai at 2009-09-05 06:17

ismjml said...

Hi Mahesh,



I do not think there is an option to run import.sql with an update in the default Hibernate distribution. In fact, I think that even the name "import.sql" is hard coded somewhere. For my own applications I created a class called ScriptRunner.java which I use to run scripts when the application starts up. It is configured in the Spring applicationContext.xml





<bean id="scriptRunner" class="edu.bristol.ScriptRunner">

<property name="sessionFactory" ref="sessionFactory" />

<property name="scripts">

<list>

<value>/importDEVDrops.sql</value>

<value>/importDEV.sql</value>

<value>/dummydevdata.sql</value>

</list>

</property>

</bean>





HTH



Mark
Note: Comment imported. Original by markmc website: http://content.mark-mclaren.info/ at 2009-09-05 10:56

ismjml said...

Hi Mark,



Thank you for the post. It was very useful. Unfortunately I couldn't print the update script. Do you know about any reason why it whould not work with Hibernate 3 and Oracle?



Thank you very much
Note: Comment imported. Original by Renato at 2009-10-06 21:54

ismjml said...

Hi Renato,



I wrote this script to use with Hibernate 3 and Oracle. Did you get any exceptions?



Mark
Note: Comment imported. Original by markmc website: http://content.mark-mclaren.info/ at 2009-10-08 22:27