JPA and toplink create-table on if they don't already exist?

Question

Looks like jpa is something which makes me ask a lot of questions.

Having added this

<property name="toplink.ddl-generation" value="create-tables"/>

my JPA application always creates tables when running, which results in exceptions in case the tables already exist. I would like JPA to check if the tables already exist and if not create them, however I could not find a value for the property above which does this.

So if I just turn it off, is there a way to tell JPA manually at some point to create all the tables?

Update here's the exception I get

Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'tags' already exists
Error Code: 1050
Call: CREATE TABLE tags (ID BIGINT AUTO_INCREMENT NOT NULL, NAME VARCHAR(255), OCCURRENCE INTEGER, PRIMARY KEY (ID))

MySQLSyntaxErrorException?! Now that's wrong for sure

Answer 1

According to http://www.oracle.com/technology/products/ias/toplink/JPA/essentials/toplink-jpa-extensions.html#Java2DBSchemaGen toplink does not have an option to update exiting tables, I'm not sure if I would trust it to do the right thing anyway. You could configure toplink to generate a sql script that you then would have to execute manually to create all tables. The filenames and location can be configured like this:

<property name="toplink.ddl-generation" value="create-tables"/>
<property name="toplink.ddl-generation.output-mode" value="sql-script"/>
<property name="toplink.create-ddl-jdbc-file-name" value="createDDL.sql"/>
<property name="toplink.drop-ddl-jdbc-file-name" value="dropDDL.sql"/>
<property name="toplink.application-location" value="/tmp"/>

Answer 2

I would like [my] JPA [provider] to check if the tables already exist and if not create them, however I could not find a value for the property above which does this.

Weird, according to the TopLink Essentials documentation about the toplink.ddl-generation extension, create-table should leave existing table unchanged:

TopLink JPA Extensions for Schema Generation

Specify what Data Descriptor Language (DDL) generation action you want for your JPA entities. To specify the DDL generation target, see toplink.ddl-generation.output-mode.

Valid values: oracle.toplink.essentials.ejb.cmp3.EntityManagerFactoryProvider

• none - do not generate DDL; no schema is generated.
• create-tables - create DDL for non-existent tables; leave existing tables unchanged (see also toplink.create-ddl-jdbc-file-name).
• drop-and-create-tables - create DDL for all tables; drop all existing tables (see also toplink.create-ddl-jdbc-file-name and toplink.drop-ddl-jdbc-file-name).

If you are using persistence outside the EJB container and would like to create the DDL files without creating tables, additionally define a Java system property INTERACT_WITH_DB and set its value to false.

Answer 3

Liquibase (http://www.liquibase.org) is good at this. It takes some time to get fully used to it, but I think it's worth the effort.

The Liquibase-way is independent of which JPA persistence provider you use. Actually, it's even database agnostic.