one more day is over @ ceylan's office

Hello,

I felt the need to generate a dynamic import sql for a project that was based on hibernate.

The task proved to be a bit more advanced then a trivial thing. So here I share the way I hacked it to convince hibernate to use an import script generated on the fly.

Requirement, the project I am currently working on is a “service provision application”  which designates the application instances by the domain requests are destined to. The application therefore requires to fill the db in with some generated data as it kicks off for the first time. The solution is also handy any scenario that needs to alter import.sql based on some conditions.

First thing is to create a manager class to undertake the task. In my instance it was a spring based application therefore I needed to extend LocalSessionFactoryBean provided by spring. If you need to create a standalone / adhoc hibernate container you may embed the necessary code to your session factory class or so called HibernateUtil class.

Since we will be using our own SchemaExport, we must block hibernate.hbm2ddl.auto somehow in the properties file and set it to none. In my case since I was using spring, I just put hardcoded hibernate.hbm2ddl.auto = none in my applicationContext.xml file.

Then comes the generation of the sql on the fly. My sql was not a huge file therefore I chose to regenerate it with a FileStream accessed via BufferedReader and appended the altered SQL lines to my StringBuffer.

The next thing is to create a SchemaExport and set the importSQL. Here hibernate implementation wierdly only allows classpath relative import.sql references and removes the leading “/” which makes it impossible to refer a generated file residing somewhere in the file system (err, in the tmp path of the application server, runtime or OS, to be a good boy). What we do here is to replace the thread’s classloader with a fake classloader. We feed in a fake name for the import script and as we see the fake name back in the classloader requested as a stream via getResourceAsStream, we just return an input stream that reads whatever (or wherever) we prepared the sql.

By issuing a create request to SchemExport,  we are done. And we cleanup by restoring the original classloader of the thread we are executing on.

Below is the code that I used. By modifying as required, it should be good to use in stand alone / ad hoc / Teneo driven, etc environments. By the way, for those that are not aware of Eclipse EMF and Eclipse Teneo,  with the new sister project Texo, I highly recommend you get your hands on EMF and Teneo, I am sure you will love it.

package com.example.session;

import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.InetAddress;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.hibernate.HibernateException;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.classic.Session;
import org.hibernate.tool.hbm2ddl.SchemaExport;
import org.hibernate.util.ConfigHelper;
import org.springframework.orm.hibernate3.LocalSessionFactoryBean;

public class ExampleSessionFactory extends LocalSessionFactoryBean {

	private static final String	FAKE_SCRIPT	= "__auto_generated_import.sql__";	//$NON-NLS-1$
	private StringBuffer		sql;

	/*
	 * (non-Javadoc)
	 *
	 * @see org.springframework.orm.hibernate3.LocalSessionFactoryBean#newSessionFactory(org.hibernate.cfg.Configuration)
	 */
	@Override
	protected SessionFactory newSessionFactory(Configuration config)
		throws HibernateException {
		final SessionFactory sessionFactory = config.buildSessionFactory();

		final String httpPort = CommonUtils.getHTTPPort();

		if ( this.schemaInitRequired(sessionFactory) ) {
			this.prepareSchema(config, httpPort);
		}

		return sessionFactory;
	}

	private void prepareSchema(Configuration config, final String httpPort) {
		try {
			final InputStream is = ConfigHelper.getResourceAsStream("/import.sql"); //$NON-NLS-1$
			final InputStreamReader importFileReader = new InputStreamReader(is);

			this.sql = new StringBuffer();

			this.logger.info("Preparing import script..."); //$NON-NLS-1$
			final BufferedReader reader = new BufferedReader(importFileReader);
			for ( String raw = reader.readLine(); raw != null; raw = reader.readLine() ) {
				final String replaced1 = raw.replaceAll("%host%", InetAddress.getLocalHost().getHostName()); //$NON-NLS-1$
				final String replaced2 = replaced1.replaceAll("%port%", httpPort); //$NON-NLS-1$
				this.sql.append(replaced2);
				this.sql.append("\n"); //$NON-NLS-1$
			}

			final ClassLoader oldClassLoader = Thread.currentThread().getContextClassLoader();
			this.swapClassLoader(oldClassLoader);

			try {
				final SchemaExport export = new SchemaExport(config);
				export.setImportFile(ExampleSessionFactory.FAKE_SCRIPT);
				export.create(false, true);
			}
			finally {
				Thread.currentThread().setContextClassLoader(oldClassLoader);
			}
		}
		catch ( final Exception e ) {
			this.logger.fatal("Error while initializing database", e);
		}
	}

	private boolean schemaInitRequired(SessionFactory factory) {
		final Session session = factory.openSession();
		try {
			final Connection connection = session.connection();

			try {
				final Statement statement = connection.createStatement();
				try {
					final ResultSet resultSet = statement.executeQuery("SELECT * from APPLICATION");
					try {
						return ( !resultSet.next() );
					}
					finally {
						resultSet.close();
					}
				}
				finally {
					statement.close();
				}
			}
			catch ( final SQLException e ) {
				return true;
			}
		}
		finally {
			session.close();
		}
	}

	/**
	 * Swapping with a *fake* class loader to trick hibernate
	 *
	 * @param oldClassLoader
	 */
	private void swapClassLoader(ClassLoader oldClassLoader) {
		Thread.currentThread().setContextClassLoader(new ClassLoader() {

			@Override
			public InputStream getResourceAsStream(String name) {
				if ( ExampleSessionFactory.FAKE_SCRIPT.equals(name) ) {
					return new ByteArrayInputStream(ExampleSessionFactory.this.sql.toString().getBytes());
				}

				return super.getResourceAsStream(name);
			}
		});
	}
}

Enjoy your dynamic import.sql…

Popularity: 79%