Hibernate: DDL Schema generation

Not so long time ago I have to work with in-memory database. This activity was related to integration testing. As you know, it is a normal practice to use in-memory data bases for the integration testing. There are several reasons for this: portability, well grained environment infrastructure, high performance, consistency of original data base.

The problem is a coppying of production DDL schemas into testing in-memory data base. The first one is MySQL, the second one is HSQLDB. A syntax of MySQL distinct from HSQL syntax. Therefore importing of MySQL table schemas into HSQLDB becomes impossible without an appropriate converting.

Frankly I’ve spent a lot of time in searching of a some solution which will help me to import MySQL DDL schemas in HSQL. The results were not so good as I wanted. All solutions were either commercial or not automatic, e.g. replacing of all MySQL specific code on HSQL.

Fortunately my project uses Hibernate as JPA implementation. All entities are decorated with appropriate Hibernate annotations. As you will see further that’s will be very helpful in translation of MySQL schemas. Hibernate provides mechanisms for generating of entities based on DDL and vice versa. Hence any entity decorated with Hibernate annotations can be represented as a table schema using that DB-dialect which Hibernate supports.

Here is a class, which solves my problem:

public class SchemaTranslator {
	private Configuration config = null;
	
	public SchemaTranslator() {
		config = new Configuration();
	}
	
	public SchemaTranslator setDialect(String dialect) {
		config.setProperty(AvailableSettings.DIALECT, dialect);
		return this;
	}
	
	/**
	 * Method determines classes which will be used for DDL generation. 
	 * @param annotatedClasses - entities annotated with Hibernate annotations.
	 */
	public SchemaTranslator addAnnotatedClasses(Class< ? >[] annotatedClasses) {
		for (Class< ? > clazz : annotatedClasses)
			config.addAnnotatedClass(clazz);
		return this;
	}
	
	/**
	 * Method performs translation of entities in table schemas.
	 * It generates 'CREATE' and 'DELETE' scripts for the Hibernate entities.
	 * Current implementation involves usage of {@link #write(FileOutputStream, String[], Formatter)} method.
	 * @param outputStream - stream will be used for *.sql file creation.
	 * @throws IOException
	 */
	public SchemaTranslator translate(FileOutputStream outputStream) throws IOException {
		Dialect requiredDialect = Dialect.getDialect(config.getProperties());
		String[] query = null;
		
		query = config.generateDropSchemaScript(requiredDialect);
		write(outputStream, query, FormatStyle.DDL.getFormatter());
		
		query = config.generateSchemaCreationScript(requiredDialect);
		write(outputStream, query, FormatStyle.DDL.getFormatter());
		
		return this;
	}
	
	/**
	 * Method writes line by line DDL scripts in the output stream.
	 * Also each line logs in the console.
	 * @throws IOException
	 */
	private void write(FileOutputStream outputStream, String[] lines, Formatter formatter) 
			throws IOException {
		String tempStr = null;
		
		for (String line : lines) {
			tempStr = formatter.format(line)+";";
			System.out.println(tempStr);
			outputStream.write(tempStr.getBytes());
		}
	}
	
	public static void main(String[] args) throws IOException {
		SchemaTranslator translator = new SchemaTranslator();
		Class< ? >[] entityClasses = {Smartphone.class};
		
		translator.setDialect("org.hibernate.dialect.HSQLDialect")
			.addAnnotatedClasses(entityClasses)
			.translate(new FileOutputStream(new File("db-schema.sql")));

	}

}

The code above is pretty verbose, but I commented it. So I hope it will be more or less easy for understanding. The code of the entire application can be found on GitHub. The SchemaTranslator class has following location in the project structure:

/src/test/java/com/mobapp/test/util/

With help of this class you can adopt your entities to any required data bases supported by Hibernate.

Good luck!