title: Db migrate - Hibernate/JPA - hbm2ddl - diff tools
body: <p>I use:</p>
  
  <ul>
  <li>EJB3 / JPA (Hibernate)</li>
  <li>MySQL 5</li>
  </ul>
  
  <p>I have to set up a system to help database migrations. Tried to use LiquiBase but it seems not enough mature yet to be used with Hibernate.</p>
  
  <p>What I would like to do is:</p>
  
  <ul>
  <li>I have version 1.0 of the application in production</li>
  <li>I have version 2.0 of the application developed and tested</li>
  <li>I want to update the application database in production without losing data</li>
  </ul>
  
  <p>Actually I would like to use the new <code>persistence.xml</code> and the production database to be able to generate the "delta" between the old database and the new. I would like to be able to retrieve the SQL code that is executed when hbm2ddl is on "update" mode.</p>
  
  <p>This SQL code will be modified (drop+create=rename etc...) to avoid losing data that could happen with <code>hbm2ddl.auto=update</code>.</p>
  
  <p>I guess it is possible because Hibernate does it when deploying a new version with <code>hbm2ddl.auto=update</code>. But I want to be able to do it in an Ant task with <code>hibernatetool</code>.</p>
  
  <p>I don't find many informations about this on internet so i wonder if someone already done something like that here and could help me.</p>
  
  <p>I've done the following:</p>
  
  <pre><code>&lt;hibernatetool destdir="${dist}"&gt;
              &lt;!--
              &lt;jdbcconfiguration propertyfile="hibtest.properties"&gt;&lt;/jdbcconfiguration&gt;
              --&gt;
              &lt;jpaconfiguration persistenceunit="server-pu" /&gt;
              &lt;classpath&gt;
                  &lt;fileset dir="${core.lib.server}" includes="*.jar" /&gt;
                  &lt;fileset dir="${core.lib.runtime}" includes="*.jar" /&gt;
                  &lt;fileset dir="${core.lib.build}" includes="*.jar" /&gt;
                  &lt;pathelement location="${core.class}" /&gt;
                  &lt;pathelement location="${core.etc}" /&gt;
              &lt;/classpath&gt;
              &lt;hbm2ddl outputfilename="schema-delta.sql" format="true"
                  export="false" update="true" /&gt;
  &lt;/hibernatetool&gt;
  </code></pre>
  
  <p>I don't really know how to do, i was able to get the creation sql file, but i just want the delta. 
  I have to put update="true" ? 
  Can i use the jpaconfig with persistence unit outside of application server (the db settings are set on JNDI ressource on Glassfish). (tried and it just can't find the db)</p>
  
  <p>I tried also with <code>&lt;jdbcconfiguration propertyfile="hibtest.properties"&gt;&lt;/jdbcconfiguration&gt;</code> or setting properties of the database in <code>persistence.xml</code> instead of using JNDI resource.</p>
  
  <p>I got the following error:</p>
  
  <pre><code>BUILD FAILED
  /home/slorber/workspace/build/build.xml:899: org.hibernate.exception.JDBCConnectionException: Getting database metadata
      at org.hibernate.tool.ant.HibernateToolTask.reportException(HibernateToolTask.java:226)
      at org.hibernate.tool.ant.HibernateToolTask.execute(HibernateToolTask.java:189)
      at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:288)
      at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:597)
      at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:105)
      at org.apache.tools.ant.Task.perform(Task.java:348)
      at org.apache.tools.ant.Target.execute(Target.java:357)
      at org.apache.tools.ant.Target.performTasks(Target.java:385)
      at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1329)
      at org.apache.tools.ant.Project.executeTarget(Project.java:1298)
      at org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:41)
      at org.eclipse.ant.internal.ui.antsupport.EclipseDefaultExecutor.executeTargets(EclipseDefaultExecutor.java:32)
      at org.apache.tools.ant.Project.executeTargets(Project.java:1181)
      at org.eclipse.ant.internal.ui.antsupport.InternalAntRunner.run(InternalAntRunner.java:423)
      at org.eclipse.ant.internal.ui.antsupport.InternalAntRunner.main(InternalAntRunner.java:137)
  Caused by: org.hibernate.exception.JDBCConnectionException: Getting database metadata
      at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:97)
      at org.hibernate.cfg.reveng.dialect.AbstractMetaDataDialect.getMetaData(AbstractMetaDataDialect.java:64)
      at org.hibernate.cfg.reveng.dialect.AbstractMetaDataDialect.caseForSearch(AbstractMetaDataDialect.java:163)
      at org.hibernate.cfg.reveng.dialect.JDBCMetaDataDialect.getTables(JDBCMetaDataDialect.java:22)
      at org.hibernate.cfg.reveng.JDBCReader.processTables(JDBCReader.java:476)
      at org.hibernate.cfg.reveng.JDBCReader.readDatabaseSchema(JDBCReader.java:74)
      at org.hibernate.cfg.reveng.JDBCReader.readDatabaseSchema(JDBCReader.java:860)
      at org.hibernate.cfg.JDBCBinder.readDatabaseSchema(JDBCBinder.java:115)
      at org.hibernate.cfg.JDBCBinder.readFromDatabase(JDBCBinder.java:88)
      at org.hibernate.cfg.JDBCMetaDataConfiguration.readFromJDBC(JDBCMetaDataConfiguration.java:42)
      at org.hibernate.tool.ant.JDBCConfigurationTask.doConfiguration(JDBCConfigurationTask.java:81)
      at org.hibernate.tool.ant.ConfigurationTask.getConfiguration(ConfigurationTask.java:55)
      at org.hibernate.tool.ant.HibernateToolTask.getConfiguration(HibernateToolTask.java:302)
      at org.hibernate.tool.ant.Hbm2DDLExporterTask.createExporter(Hbm2DDLExporterTask.java:51)
      at org.hibernate.tool.ant.ExporterTask.execute(ExporterTask.java:39)
      at org.hibernate.tool.ant.HibernateToolTask.execute(HibernateToolTask.java:186)
      ... 15 more
  Caused by: java.sql.SQLException: No suitable driver found for jdbc:mysql:3306//localhost/db
      at java.sql.DriverManager.getConnection(DriverManager.java:602)
      at java.sql.DriverManager.getConnection(DriverManager.java:154)
      at org.hibernate.connection.DriverManagerConnectionProvider.getConnection(DriverManagerConnectionProvider.java:133)
      at org.hibernate.cfg.reveng.dialect.AbstractMetaDataDialect.getConnection(AbstractMetaDataDialect.java:122)
      at org.hibernate.cfg.reveng.dialect.AbstractMetaDataDialect.getMetaData(AbstractMetaDataDialect.java:61)
      ... 29 more
  --- Nested Exception ---
  org.hibernate.exception.JDBCConnectionException: Getting database metadata
      at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:97)
      at org.hibernate.cfg.reveng.dialect.AbstractMetaDataDialect.getMetaData(AbstractMetaDataDialect.java:64)
      at org.hibernate.cfg.reveng.dialect.AbstractMetaDataDialect.caseForSearch(AbstractMetaDataDialect.java:163)
      at org.hibernate.cfg.reveng.dialect.JDBCMetaDataDialect.getTables(JDBCMetaDataDialect.java:22)
      at org.hibernate.cfg.reveng.JDBCReader.processTables(JDBCReader.java:476)
      at org.hibernate.cfg.reveng.JDBCReader.readDatabaseSchema(JDBCReader.java:74)
      at org.hibernate.cfg.reveng.JDBCReader.readDatabaseSchema(JDBCReader.java:860)
      at org.hibernate.cfg.JDBCBinder.readDatabaseSchema(JDBCBinder.java:115)
      at org.hibernate.cfg.JDBCBinder.readFromDatabase(JDBCBinder.java:88)
      at org.hibernate.cfg.JDBCMetaDataConfiguration.readFromJDBC(JDBCMetaDataConfiguration.java:42)
      at org.hibernate.tool.ant.JDBCConfigurationTask.doConfiguration(JDBCConfigurationTask.java:81)
      at org.hibernate.tool.ant.ConfigurationTask.getConfiguration(ConfigurationTask.java:55)
      at org.hibernate.tool.ant.HibernateToolTask.getConfiguration(HibernateToolTask.java:302)
      at org.hibernate.tool.ant.Hbm2DDLExporterTask.createExporter(Hbm2DDLExporterTask.java:51)
      at org.hibernate.tool.ant.ExporterTask.execute(ExporterTask.java:39)
      at org.hibernate.tool.ant.HibernateToolTask.execute(HibernateToolTask.java:186)
      at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:288)
      at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:597)
      at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:105)
      at org.apache.tools.ant.Task.perform(Task.java:348)
      at org.apache.tools.ant.Target.execute(Target.java:357)
      at org.apache.tools.ant.Target.performTasks(Target.java:385)
      at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1329)
      at org.apache.tools.ant.Project.executeTarget(Project.java:1298)
      at org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:41)
      at org.eclipse.ant.internal.ui.antsupport.EclipseDefaultExecutor.executeTargets(EclipseDefaultExecutor.java:32)
      at org.apache.tools.ant.Project.executeTargets(Project.java:1181)
      at org.eclipse.ant.internal.ui.antsupport.InternalAntRunner.run(InternalAntRunner.java:423)
      at org.eclipse.ant.internal.ui.antsupport.InternalAntRunner.main(InternalAntRunner.java:137)
  Caused by: java.sql.SQLException: No suitable driver found for jdbc:mysql:3306//localhost/db
      at java.sql.DriverManager.getConnection(DriverManager.java:602)
      at java.sql.DriverManager.getConnection(DriverManager.java:154)
      at org.hibernate.connection.DriverManagerConnectionProvider.getConnection(DriverManagerConnectionProvider.java:133)
      at org.hibernate.cfg.reveng.dialect.AbstractMetaDataDialect.getConnection(AbstractMetaDataDialect.java:122)
      at org.hibernate.cfg.reveng.dialect.AbstractMetaDataDialect.getMetaData(AbstractMetaDataDialect.java:61)
      ... 29 more
  </code></pre>
  
  <p>I use MySQL, and my JDBC properties are:</p>
  
  <pre><code>hibernate.dialect=org.hibernate.dialect.MySQLInnoDBDialect
  hibernate.connection.driver_class=com.mysql.jdbc.Driver
  hibernate.connection.url=jdbc:mysql:3306//localhost/db
  hibernate.connection.username=root
  hibernate.connection.password=root
  hibernate.connection.show_sql=true
  </code></pre>
  
  <p>My JDBC MySQL driver is in <code>${core.lib.server}: mysql-connector-java-5.1.7-bin.jar</code>.
  (I also tried to put in classpath <code>&lt;fileset dir="/home/slorber/workspace/core/lib/server" includes="*.jar" /&gt;</code>.)</p>
  
  <p>I also added it to the run configuration classpath of Ant (Eclipse plugin).</p>
  
  <p>So my questions are:</p>
  
  <ul>
  <li>Have I done something wrong to do what I want?</li>
  <li>Is this the way you would do a db migrate with a Hibernate framework? (And what else would you do if not writing all db changes in hand-made SQL files?)</li>
  </ul>