SQL Zone is brought to you in partnership with:

Gabriel Jeremiah Campbell has a Bachelor of Science Honours Degree in Applied Mathematics and Computer Science from the National University of Science and Technology (NUST). I am a Sun Certified Java Programmer(SCJP) and Sun Certified Web Component Developer(SCWCD). I have worked as Junior Lecturer in Computer Science for 3 years. I have been actively developing software in Java for 4 years. I have also coded in C/C++. When not at a computer (or thinking about a computer) I enjoy sci-fi movies, cycling, nature walks and photography. Gabriel Jeremiah is a DZone MVB and is not an employee of DZone and has posted 9 posts at DZone. You can read more from them at their website. View Full User Profile

Liquibase and Hibernate

11.17.2010
| 12899 views |
  • submit to reddit
I have been examining the liquibase project and analyzing it’s benefits as a database management tool.

We create an application called LiquibaseTryOuts.  The structure of the projects is as follows:

The dependencies are listed below

I have noticed that there is not alot of documentation about setting up liquibase with ant scripts. So I created an ant script for our application. Here is the ant script:

<?xml version="1.0" encoding="UTF-8"?>

<project name="liquibase-sample">

<property file="liquibase.properties"/>

<path id="lib.path" >
<fileset dir="lib1" />
</path>

<target name="update-database">
<taskdef name="updateDatabase" classname="liquibase.ant.DatabaseUpdateTask"
classpathref="lib.path" />
<updateDatabase
changeLogFile="${changeLogFile}"
driver="${driver}"
url="${url}"
username="${username}"
password="${password}"
dropFirst="${dropfirst}"
classpathref="lib.path"/>
</target>

<target name="rollback-database">
<taskdef name="rollbackDatabase" classname="liquibase.ant.DatabaseRollbackTask"
classpathref="lib.path" />
<rollbackDatabase
changeLogFile="${changeLogFile}"
driver="${driver}"
url="${url}"
username="${username}"
password="${password}"
classpathref="lib.path"
rollbackTag="${tag}"
>
</rollbackDatabase>
</target>

<target name="tag">
<taskdef name="tagDatabase" classname="liquibase.ant.TagDatabaseTask"
classpathref="lib.path" />
<tagDatabase
changeLogFile="${changeLogFile}"
driver="${driver}"
url="${url}"
username="${username}"
password="${password}"
classpathref="lib.path"
tag="${tag}"
>
</tagDatabase>
</target>

<target name="generateChangeLog">
<taskdef name="generateChangeLogDatabase" classname="liquibase.ant.GenerateChangeLogTask"
classpathref="lib.path" />
<tagDatabase
outputFile="${outputFile}"
driver="${driver}"
url="${url}"
username="${username}"
password="${password}"
classpathref="lib.path"
>
</tagDatabase>
</target>

<target name="diff-database">
<taskdef name="diffDatabase" classname="liquibase.ant.DiffDatabaseTask"
classpathref="lib.path" />
<tagDatabase
driver="${driver}"
url="${url}"
username="${username}"
password="${password}"

baseUrl="${url}"
baseUsername="${username}"
baseUassword="${password}"

outputDiffFile="${outputDiffFile}"
classpathref="lib.path"
>
</tagDatabase>
</target>


</project>

Next we use hibernate now like my blog on the 30 May we use hibernate with JPA annotations. Here is the configuration file:

<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>

<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="connection.url">jdbc:mysql://localhost:3306/liquibasetestdb</property>
<property name="connection.username">monty</property>
<property name="connection.password">some_pass</property>

<!– JDBC connection pool (use the built-in) –>
<property name="connection.pool_size">1</property>

<!– SQL dialect –>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>


<!–<mapping package="liquibase.database.pojo"/>–>
<mapping class="liquibase.database.pojo.Users"/>


<!– <mapping resource="test/animals/orm.xml"/>–>
</session-factory>
</hibernate-configuration>

Now the class files for this project are the same as the files used for the blog in the 30 May with one or two extra files. Here is the UML diagram:

Now we need some change logs  that follow the liquibase specifications these will be as follows

included.changelog.xml

<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.7"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.7 http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.7.xsd">
<preConditions>
<dbms type="mysql"/>
</preConditions>

<changeSet id="1" author="gabriel">
<createTable tableName="news">
<column name="id" type="int" autoIncrement="true">
<constraints primaryKey="true" nullable="false"/>
</column>
<column name="title" type="varchar(50)"/>
</createTable>
</changeSet>

<changeSet id="2" author="gabriel" context="test">
<insert tableName="news">
<column name="title" value="Liquibase 0.8 Released"/>
</insert>
<insert tableName="news">
<column name="title" value="Liquibase 0.9 Released"/>
</insert>
</changeSet>

<changeSet id="3" author="gabriel" context="demo">
<insert tableName="news">
<column name="title" value="Liquibase 1.0 Released"/>
</insert>
</changeSet>
</databaseChangeLog>

Next included2.changelog.xml

<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.7"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.7 http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.7.xsd">
<preConditions>
<dbms type="mysql"/>
</preConditions>

<changeSet id="1" author="gabriel">
<createTable tableName="records">
<column name="id" type="int" autoIncrement="true">
<constraints primaryKey="true" nullable="false"/>
</column>
<column name="title" type="varchar(50)"/>
</createTable>
</changeSet>

<changeSet id="2" author="gabriel" context="test">
<insert tableName="records">
<column name="title" value="Liquibase 0.8 Released"/>
</insert>
<insert tableName="records">
<column name="title" value="Liquibase 0.9 Released"/>
</insert>
</changeSet>

<changeSet id="3" author="gabriel" context="demo">
<insert tableName="records">
<column name="title" value="Liquibase 1.0 Released"/>
</insert>
</changeSet>
</databaseChangeLog>

included3.changelog.xml

<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.7"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.7 http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.7.xsd">
<preConditions>
<dbms type="mysql"/>
</preConditions>

<changeSet id="1" author="gabriel">
<createTable tableName="register">
<column name="id" type="int" autoIncrement="true">
<constraints primaryKey="true" nullable="false"/>
</column>
<column name="title" type="varchar(50)"/>
<column name="name" type="varchar(100)"/>
</createTable>
</changeSet>

<changeSet id="2" author="gabriel">
<dropTable tableName="register"/>
<rollback changeSetId="1" changeSetAuthor="gabriel"/>
</changeSet>

</databaseChangeLog>

And included4.changelog.xml

And we need a liquibase.properties file:

changeLogFile=included4.changelog.xml
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/liquibasetestdb
username=monty
password=some_pass

#for diff between two databases
baseUrl=jdbc:mysql://localhost:3306/liquibasetestdb
baseUsername=monty
basePassword=some_pass

dropFirst=false
tag=version 1.4
outputFile=outputFile.xml
outputDiffFile=outputFile.txt

When we add the file in our eclipse ant editor This will be the view:

Now running rollback-database,as an example, we get:

Buildfile: /home/gabriel/eclipse/eclipse/opensource-workspace/LiquibaseTryOuts/build.xml
rollback-database:
[rollbackDatabase] Lock Database
[rollbackDatabase] Successfully acquired change log lock
[rollbackDatabase] included4.changelog.xml is using schema version 1.7 rather than version 1.9
[rollbackDatabase] Reading from `DATABASECHANGELOG`
[rollbackDatabase] Release Database Lock
[rollbackDatabase] Successfully released change log lock
BUILD SUCCESSFUL
Total time: 6 seconds

Now in my project(which has similar classes to the project of my last blog 30 May) I have added the two extra classes HibernateSchemaTests and LiquibaseSchemaTests.

Here is the full listing of HibernateSchemaTests:

Here is the full listing of HibernateSchemaTests:

/**

*

*/

package liquibase.database.tests;


import java.util.ArrayList;

import java.util.List;


import junit.framework.TestCase;


import org.hibernate.HibernateException;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

import org.hibernate.cfg.AnnotationConfiguration;

import org.hibernate.cfg.Configuration;

import org.hibernate.dialect.Dialect;

import org.hibernate.tool.hbm2ddl.DatabaseMetadata;


/**

* @author gabriel

*

*/

public abstract class HibernateSchemaTests extends TestCase {


private AnnotationConfiguration ac;


public void assertDatabaseSchema() throws Exception {

String[] script = generateScript();

List differences = getSignificantDifferences(script);

assertTrue(differences.toString(), differences.isEmpty());

}


private String[] generateScript() throws Exception {

// Configuration cfg = getConfiguration();

// SessionFactory sessionFactory = cfg.buildSessionFactory();

// Session session = sessionFactory.openSession();

ac = new AnnotationConfiguration().configure();

SessionFactory sessionFactory = ac.buildSessionFactory();

Session session = sessionFactory.openSession();

try {

Dialect dialect = getDatabaseDialect();

DatabaseMetadata dbm = new DatabaseMetadata(session.connection(),

dialect);


String[] existingScript = ac.generateSchemaCreationScript(dialect);


for (String s : existingScript)

System.out.println(s);


String[] script = ac.generateSchemaUpdateScript(dialect, dbm);

return script;

} finally {

session.close();

}

}


protected Dialect getDatabaseDialect() throws Exception {

return (Dialect) Class.forName(

getConfiguration().getProperty("hibernate.dialect"))

.newInstance();

}


private List getSignificantDifferences(String[] script) {

List differences = new ArrayList();

for (int i = 0; i < script.length; i++) {

String line = script[i];

if (line.indexOf("add constraint") == -1)

differences.add(line);

}

return differences;

}


protected Configuration getConfiguration() throws HibernateException {

return ac;

}

}

Now we have the listing of LiquibaseSchemaTests:

/**

*

*/

package liquibase.database.tests;


/**

* @author gabriel

*

*/

public class LiquibaseSchemaTest extends HibernateSchemaTests {


/**

*

*/

public LiquibaseSchemaTest() {

// TODO Auto-generated constructor stub

}


public void test() throws Exception {

assertDatabaseSchema();

}


}

Now running these tests can also give us a diff-log between the schema from our mapping files(where we used annotations) and the actually database.

So what is the actual difference in the two methods.? Well Liquibase gives an xml output and can tag the database. it is open source. Using hibernate is powerful but only for developers what about DBA’s and other users? Also when using liquibase the developers will have to liaise with the DBA to ensure smooth running. Liquibase has the advantage of alot of stable built in functionality.

Integrating ant scripts for liquibase into maven can yield some nice functionalitySmile

See this post on Gabriel Jeremiah Campbell's blog:
http://gabrieljeremiahcampbell.wordpress.com/2010/06/06/liquibase-and-hibernate/

Published at DZone with permission of Gabriel Jeremiah Campbell, author and DZone MVB.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)