1

I have an application in Java Spring using HSQLDB database.

For an evolution, i have to add a column to an existing table. So, I have done what I have to do (adding the attribute in the entity class with the info) but when i deploy my new app in tomcat, the database is not updated and i can't start my app.

Is it possible to add the column automatically?

Here is the configuration of the database in the applicationContext.xml:

<bean id="myEmf"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSourceDb" />
    <property name="jpaVendorAdapter">
        <bean
            class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
            <property name="generateDdl" value="true" />
        </bean>
    </property>
    <property name="jpaProperties">
        <props>
            <prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>
            <prop key="hibernate.show_sql">true</prop>
            <prop key="hibernate.format_sql">false</prop>
            <prop key="hibernate.hbm2ddl.auto">update</prop>
        </props>
    </property>
</bean>

Thank you for your help.

Ryoku
  • 35
  • 7
  • are you using Hibernate? see http://stackoverflow.com/questions/306806/hibernate-automatically-creating-updating-the-db-tables-based-on-entity-classes – Michael Pralow Apr 02 '15 at 08:25
  • Hi, thanks for your help. I saw this and have update my spring configuration with hbm2ddl.auto to "update" but it does not work :( I have edited my question with the configuration – Ryoku Apr 02 '15 at 09:00

2 Answers2

2

Of course, if you using Hibernate, you can make use of auto-generation from object model. But this way is not good for a production use. I suggest you to use database migration tool. It provide you means for auto execution of your sql scripts in certain order. Look at flyway, liquibase.

retroq
  • 572
  • 2
  • 7
  • Thank you for your answer but this needs to install an external tool, is there a good way to do it without install anything? – Ryoku Apr 02 '15 at 09:02
  • It's not automatically the installation of an external tool. Flyway at least can be a dependency in your application, and can be run on application startup, before Hibernate is stared. – Rich Apr 02 '15 at 09:10
  • Oh ok ! I will see it and will come back here to inform about the progress. Thanks a lot ! – Ryoku Apr 02 '15 at 09:31
  • Hi, i have added flyway to my project through maven and it does not create the column automatically, so i have a question: is it required to add a migration file in db/migration folder? I wanted flyway to base on my classes declaration and do the comparison with database automatically, is it possible? Thanks for your help. – Ryoku Apr 03 '15 at 07:21
  • No, flyway uses your scripts. This approach is more clear for production. – retroq Apr 03 '15 at 13:41
  • But you can take ddl scripts generated by hibernate (change hbm2ddl.auto to "create", show_sql to "true" and run project with db which data can be lost). – retroq Apr 03 '15 at 13:50
2

Thank you very much for your help. This is all the actions I did to update the database structure with flyway. It may help someone, one day.

  1. Add Flyway dependency to my pom.xml : http://flywaydb.org/documentation/api/

    <dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
    <version>3.2.1</version>
</dependency>

  2. Add the Flyway configuration to my application context:

    <bean id="flyway" class="org.flywaydb.core.Flyway" init-method="migrate">
    <property name="baselineOnMigrate" value="true" />
    <property name="dataSource" ref="dataSourceDb"/>
</bean>

<bean id="persistenceUnitManager" depends-on="flyway"
   class="org.springframework.orm.jpa.persistenceunit.DefaultPersistenceUnitManager">
   <property name="defaultDataSource" ref="dataSourceDb" />
</bean>

I have added the property baselineOnMigrate because I had an error, and the error message said that set this property to true will fix the problem.

  1. Create a script under the folder src/main/resources/db/migration named V2__add_datatype_column.sql

    ALTER TABLE PUBLICATION ADD COLUMN datatype varchar(6);

  2. Build, it works.

    Thanks a lot to retroq, Rich and Michael Pralow for your answers.

Ryoku
  • 35
  • 7
  • Hi, it's me again, just a information, these actions will work for migrate from an existing database, don't forget to create the creation script, which creates the database if it does not exist. I faced this problem, but it is not really complicate to fix. – Ryoku Apr 08 '15 at 14:28