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.

score 13 · Accepted Answer · answered Apr 11 '11 at 16:31

13

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.

answered Apr 11 '11 at 16:31

Behrang

46,888
25
118
160

The property is "hibernate.hbm2ddl.auto" with Hibernate and its values can be {validate|update|create|create-drop}. Again, not recommended for production purposes. Despite of the fact that in production your connection most probably won't have privileges to do such changes. – Edwin Dalorzo Apr 11 '11 at 18:56
6

EclipseLink 2.4 supports a new option to alter existing table, "eclipselink.ddl-generation" ="create-or-extend-tables" – James Aug 27 '12 at 14:38
James, can we use this new option while in production or does what @Behrang is mentioning about reliability still apply. – Warz Aug 30 '12 at 14:35

score 4 · Answer 2 · answered Apr 12 '11 at 12:34

4

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.

answered Apr 12 '11 at 12:34

James

17,965
11
91
146

6

EclipseLink 2.4 supports a new option to alter existing table, "eclipselink.ddl-generation" ="create-or-extend-tables" – James Aug 27 '12 at 14:38

score 1 · Answer 3 · answered Aug 27 '12 at 13:28

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>

drop and create tables is fine, but what about the data already stored in the database? — dendini, May 26 '13 at 16:21
That option is only for development, not for upgrading existing tables... And what does it has to do with JSF? — Ville Myrskyneva, Dec 29 '16 at 09:29

score 1 · Answer 4 · answered Apr 11 '11 at 18:41

1

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.

answered Apr 11 '11 at 18:41

DataNucleus

15,497
3
32
37

Even so, there are certain changes that can not be migrated without manual human intervention. As such it's better to opt for a reliable strategy rather than depending on automatic schema updates. Even diffing can fail in some scenarios. – Behrang Apr 11 '11 at 19:22
A change like the one you mention is one of the very few that can't be made automatically; since your class doesn't contain information on what was the old column(s) and what is the new column(s). No reason there not to use the auto features for other situations. – DataNucleus Apr 12 '11 at 06:21
1

So you use auto-generation when it works and rely on scripts, LiquiBase, or something like that when that doesn't work, in the same application? Sounds like a maintenance nightmare to me. – Behrang Apr 12 '11 at 06:35

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

4 Answers4

Linked