Home » Java » Enterprise Java » Hibernate: DDL Schema generation

About Alexey Zvolinskiy

Alexey Zvolinskiy
Alexey is a test developer with solid experience in automation of web-applications using Java, TestNG and Selenium. He is so much into QA that even after work he provides training courses for junior QA engineers.

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!
 

Reference: Hibernate: DDL Schema generation from our JCG partner Alexey Zvolinskiy at the Fruzenshtein’s notes blog.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

One comment

  1. thanks a lot for an example

Leave a Reply

Your email address will not be published. Required fields are marked *

*


+ 5 = seven

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Do you want to know how to develop your skillset and become a ...

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!
Get ready to Rock!
To download the books, please verify your email address by following the instructions found on the email we just sent you.

THANK YOU!

Close