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.

8 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

for ict 99 said...

The effectiveness of IEEE Project Domains depends very much on the situation in which they are applied. In order to further improve IEEE Final Year Project Domains practices we need to explicitly describe and utilise our knowledge about software domains of software engineering Final Year Project Domains for CSE technologies. This paper suggests a modelling formalism for supporting systematic reuse of software engineering technologies during planning of software projects and improvement programmes in Final Year Project Centers in Chennai.

Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining the authors explore the idea of using Java in Big Data platforms.
Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai

madin said...

For over 80 years Ray-Ban has been forging its name as the most iconic eyewear brand. Its glasses tells a story through decades, Cheap Ray-Ban Sunglasses uk trends and the people that wear it. It’s about seeing and being seen, cheap raybans caravan and ownership of one’s own individuality – of dictating who we are instead of being told. But most of all it’s about sharing what it is to be you, with people who understand it. It’s about that feeling of belonging, without conforming.