Can <session-factory> CREATE db with hbm2ddl.auto?

Per · **Joined:** Thu Mar 02, 2006 10:59 am **Posts:** 5

Is there any way to specify in hibernate.cfg.xml that the <session-factory> should simply CREATE the database if it does not exist, using e.g.

<property name="hbm2ddl.auto">whatever</property>

Possible values of 'whatever' seems to be:
create: drop and create
update: do not create, nor drop afterwards
validate: ? [documented where?]
create-drop: drop and create, drop afterwards

But all that I want is that the db should be created if it does not exist...
Why is there no value like 'just-create-dammit'?

In other words, what is the canonical way to write a webapp that, after deployment, automagically creates the db if it does not exist?

max · **Posted:** Fri Mar 31, 2006 11:52 am

so how would you decide that the database is "not there" ?

i think you are better of writing a custom initialization code that uses schemaexport to do what you want based on your definition of "not there"

Per · **Joined:** Thu Mar 02, 2006 10:59 am **Posts:** 5

OK, I understand that there currently is no such function for hbm2ddl.auto.

max wrote:

so how would you decide that the database is "not there" ?

Hibernate could decide this by asking the dbms if the database exists. See example code below.

max wrote:

i think you are better of writing a custom initialization code that uses schemaexport to do what you want based on your definition of "not there"

In most cases, if the database does not exist, I just want Hibernate to create it and the schema to be exported, just like with

<property name="hbm2ddl.auto">create</property>

for example

<property name="hbm2ddl.auto">create-if-not-exists</property>

All the pieces are already there (except, perhaps, db detection), so Hibernate could save the users from manually writing custom initialization code if the database just should be created without further ceremony.

This would be very useful. I have googled myself blue to find out if/how this can be done, and found no answer - but a lot of other folks with the same question.

Here's the example code. It does a little more than just create the database, but you'll see the interesting part.

Code:

private static Connection createConnection()
      throws SystemException
   {
      Statement stmt = null;

      try {
         Class.forName("com.mysql.jdbc.Driver");

         String jdbc_url = "jdbc:mysql://localhost/?user=fred";

         Connection conn = DriverManager.getConnection(jdbc_url);

         // Does dbName exist?

         DatabaseMetaData dbmd = conn.getMetaData();
         ResultSet rs = dbmd.getCatalogs();  // closed with stmt.close()
         boolean found = false;
         while (rs.next()) {
            String catalog = rs.getString(1);
            if (catalog.equals(dbName)) {
               found = true;
            }
         }

         // Nuts. Create it!

         if (!found) {
            stmt = conn.createStatement();
            stmt.execute("CREATE DATABASE " + dbName);
            stmt.close();

            conn.setCatalog(dbName);
            stmt = conn.createStatement();

            String sql =
               "CREATE TABLE " + tableName +
               " (user_name     VARCHAR(255) NOT NULL, " +
               "  setting_key   VARCHAR(255) NOT NULL, " +
               "  setting_value VARCHAR(255),  " +
               "  PRIMARY KEY k1 (user_name, setting_key) " +
               " ) TYPE=InnoDB;";

            stmt.execute(sql);
            stmt.close();
         }

         conn.setCatalog(dbName);
         return conn;
      }
      catch (ClassNotFoundException e) {
         throw new SystemException("No database driver", e);
      }
      catch (SQLException e) {
         throw new SystemException("SQL problem", e);
      }
      finally {
         try {
            if (stmt != null)
               stmt.close();
         }
         catch (SQLException e) {}
      }
   }

max · **Posted:** Mon Apr 03, 2006 6:05 am

this is very mysql specific.

and it won't work on other db's where you can't even login if the pointed to db/schema is not created so again; you can do this perfectly db/app-specific test in your own code.

Per · **Joined:** Thu Mar 02, 2006 10:59 am **Posts:** 5

max wrote:

and it won't work on other db's where you can't even login if the pointed to db/schema is not created so again; you can do this perfectly db/app-specific test in your own code.

Interesting. I just discovered (from experiment) that

<property name="hbm2ddl.auto">create</property>

requires that the db already exists. This isn't documented much.

So it seems that, although the code

Code:

Connection conn = DriverManager.getConnection(jdbc_url);
DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rs = dbmd.getCatalogs();

appears to be pure Java, it isn't portable between different dbms.
Perhaps there is no portable way to check if a database exists,
using JDBC?

In any case, a more detailed description of the possible alternatives to

<property name="hbm2ddl.auto">

in the Hibernate Reference Documentation would be most helpful.
For example, the value 'validate' exists, but not explained at all, as far as I can see. And that 'create' requires the db to exist beforehand would also be nice to know.

max · **Posted:** Mon Apr 03, 2006 7:02 am

no where in the documentation does it state that we will go out and create databases/schemas especially since we do not have any way of doing that (both because of portability issues and the fact we don't know how to idenfity the database)

mdean77 · **Joined:** Fri Mar 04, 2005 7:12 pm **Posts:** 34

Why not just use SchemaUpdate?