How do I add a column to a JPA entity, and still keep my data?

Question

During the development of a JPA application, I added a field "type" to an entity. This caused the application to fail to start with

Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.2.v20100323-r6872): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: Column 'T1.TYPE' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'T1.TYPE' is not a column in the target table.
Error Code: -1

during a selection.

Is there a way to get it to ALTER TABLE on startup, to match the current entity definitions? I notice that when the app starts up it calls CREATE TABLE for each table, fails and carries on.

In the past when I've added fields to entities I just delete the database and start again. This is ok for me now but what if I want to add a field to an entry when developing the next release of the software, how do I upgrade my clients without losing all there data?

Thanks in advance - Phil.

Answer 1

Generally speaking, the database generators of JPA providers are not reliable for creating and updating production databases. Suppose that in version 1 you had a field named name for your User entity and in version 2 you replace that with firstName and lastName. In such cases the DB generator is unable to migrate your schema correctly.

In other words, the JPA provider's automatic DB generation is only useful for development and not for production.

Instead, you can consider using a database migration tool like LiquiBase.

Having said that, while EclipseLink does not support a way for updating, rather than re-creating, tables using the eclipselink.ddl-generation property, some other JPA providers (e.g. Hibernate) support updating of the database tables. However, they can not be relied on for production use as they fail in cases like the given example.

Answer 2

You can replace the tables in EclipseLink using,

"eclipselink.ddl-generation" = "drop-and-create-tables"

EclipseLink does not support altering existing tables, please log an enhancement request for this and vote for it.

In general it is normally better to have a DBA define a script to define the production schema and handle migration.

Answer 3

For reference, if you did the same with DataNucleus and specified a default value for the new field (column) and you had the relevant persistence property set to allow schema updates then it would issue an "ALTER TABLE ADD COLUMN ..." and be fine.

Answer 4

Do you use it with JSF?

Its very very easy then. The persistence unit (persistence.xml) can drop and create tables for you.

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
  <persistence-unit name="JobbingPU" transaction-type="JTA">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <jta-data-source>job</jta-data-source>
    <exclude-unlisted-classes>false</exclude-unlisted-classes>
    <properties>
      <property name="eclipselink.ddl-generation" value="drop-and-create-tables"/>
    </properties>
  </persistence-unit>
</persistence>