JPA: create database then connect

Question

I want to create database at the first time of an application deploying process. I am using JPA. So I consider two ways.

1) I have a full connection description in my persistence.xml:

<property name="mysql.jdbc.user" value="root"/>
<property name="mysql.jdbc.password" value=""/>
<property name="mysql.jdbc.url" value="jdbc:mysql://localhost:3306/DB"/>
<property name="mysql.jdbc.driver" value="com.mysql.jdbc.Driver"/>

When an application is started, it cannot connect to DB and reports about this trouble advising to go to the install page. In this install page I launch a DB creation with explicit SQL queries.

2) I have a connection to a database server:

<property name="mysql.jdbc.user" value="root"/>
<property name="mysql.jdbc.password" value=""/>
<property name="mysql.jdbc.url" value="jdbc:mysql://localhost:3306/"/>
<property name="mysql.jdbc.driver" value="com.mysql.jdbc.Driver"/>

An application informs me that it is a first conneciton and redirect me to the install page. During a DB installation persistence.xml should be modified. The property mysql.jdbc.url must be changed a new value assignment which declares a database name.

What do you think about this? How to do this?

If you're testing, you can use H2 to create a database and generate the schema when the test case starts up. If this is what you're interested in doing, let me know and I'll post a more detailed solution. — Fil, Nov 28 '11 at 15:09

score 3 · Answer 1 · answered Nov 28 '11 at 14:40

3

I'm almost sure you can't use JPA to create the database itself.

You need to either create it manually or look into creating it using a build tool like Maven.

answered Nov 28 '11 at 14:40

Gerard

4,818
5
51
80

score 1 · Answer 2 · answered Nov 28 '11 at 15:15

1

May be you should create database using you persistence.xml. For instance for JBoss this property look like following:

<property name="hibernate.hbm2ddl.auto" value="create-drop"/>

Also please look for other possible values.

answered Nov 28 '11 at 15:15

Nulldevice

3,926
3
31
37

1

That's a property specific to Hibernate. If it works in JBoss, that's simply because it uses Hibernate as JPA implementation. It can work anytime you use Hibernate. Your answer is correct, just making sure it's clear this isn't JBoss-specific. – G_H Nov 28 '11 at 15:18
You remark is correct. My idea was that frameworks have a property for that in persistence.xml, I just gave an example. – Nulldevice Nov 28 '11 at 15:24

score 0 · Accepted Answer · edited May 23 '17 at 10:34

What you could do is add a servlet that is configured to run (once) at application startup. This servlet could check whether the database already exists and if not, create it programmatically. Check this question's answers to see how this can be achieved for MySQL. If you have a running server, you can obtain a connection and run the SQL to create the database. After that, the necessary tables and the like can be auto-created using the right property for your JPA implementation (like Nulldevice showed for Hibernate).

You could even have the servlet locate the persistence.xml file, since it's supposed to be in a particular location anyway, and parse it to find out several properties like URL to make sure they're only defined in one place.

What I'm not certain about is whether JPA would only try connecting (and possibly creating the tables) after the servlet is run, or how this could be forced. For this, you might have to check the lifecycle of JavaEE applications. It could depend on the implementation by the Application Server vendor too.

JPA: create database then connect

3 Answers3