Schema is not dropped on hbmddl.auto = create.drop

Question

I am using hbmddl.auto set to create in the hibernate configuration file and using it to connect to the derby database in network mode (not embedded, don't know if that is relevant).

Here is my hibernate.cfg.xml

    <!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>

    <session-factory>

        <!-- Database connection settings -->
        <property name="connection.driver_class">org.apache.derby.jdbc.ClientDriver</property>
        <property name="connection.url">jdbc:derby://localhost:1527/HibernateDb;create=true</property>
        <property name="connection.username">admin</property>
        <property name="connection.password">admin</property>

        <!-- JDBC connection pool (use the built-in) -->
        <property name="connection.pool_size">1</property>

        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.DerbyDialect</property>

        <!-- Disable the second-level cache  -->
        <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>

        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>

        <!-- Drop and re-create the database schema on startup -->
        <property name="hbm2ddl.auto">create-drop</property>

        <!-- Names the annotated entity class -->
        <mapping class="org.asif.javabrains.dto.UserDetails"/>

    </session-factory>

</hibernate-configuration>

But when i run the java program that is using this aka the service class, i doesn't seem to drop the database and recreate it. as the comment says ( i took the config file from the hibernate examples).

~~~~UPDATE~~~~

I concluded that hbm2ddl was not dropping the schema based on the following behaviour

I have 2 classes that look like as follows

@Entity
public class UserDetails {

    @Id @GeneratedValue(strategy=GenerationType.AUTO)
    private int userId;
    private String userName;

    @OneToMany
    @JoinTable(name="USER_VEHICLE",
               joinColumns=@JoinColumn(name="USER_ID"),
               inverseJoinColumns = @JoinColumn(name="VEHICLE_ID")
    )    
    private Collection<Vehicle> vehicles = new ArrayList<Vehicle>() ;

        /* getters and setters */
}

and

@Entity
public class Vehicle {
    @Id @GeneratedValue
    private int vehicleId;
    private String vehicleName;
    @ManyToOne
    @JoinColumn(name="USER_ID")
    private UserDetails user;

        /* getters and setters */
}

and the corresponding hibernate config is

 <!--boiler plate-->

   <!-- Drop and re-create the database schema on startup -->
    <property name="hbm2ddl.auto">create</property>

    <!-- Names the annotated entity class -->
    <mapping class="org.asif.javabrains.dto.UserDetails"/>
    <mapping class="org.asif.javabrains.dto.Vehicle"/>

  <!--boiler plate-->

Now when i change the above classes to

  @Entity
    public class UserDetails {

        @Id @GeneratedValue(strategy=GenerationType.AUTO)
        private int userId;
        private String userName;

        @OneToMany(mappedBy="user")
        private Collection<Vehicle> vehicles = new ArrayList<Vehicle>() ;
    //rest is same

and

@Entity
public class Vehicle {
    @Id @GeneratedValue
    private int vehicleId;
    private String vehicleName;
    @ManyToOne
    @JoinColumn(name="USER_ID")
    private UserDetails user;
      //rest is same

The following is printed to the console

SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
Hibernate: insert into UserDetails (userId, userName) values (default, ?)
Hibernate: values identity_val_local()
Hibernate: insert into Vehicle (vehicleId, USER_ID, vehicleName) values (default, ?, ?)
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not insert: [org.asif.javabrains.dto.Vehicle]
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.id.insert.AbstractSelectingDelegate.performInsert(AbstractSelectingDelegate.java:64)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2345)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2852)
    at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:71)
    at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273)
    at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:320)
    at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:203)
    at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:129)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:210)
    at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:56)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:195)
    at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:50)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:93)
    at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:713)
    at org.hibernate.impl.SessionImpl.save(SessionImpl.java:701)
    at org.hibernate.impl.SessionImpl.save(SessionImpl.java:697)
    at org.asif.javabrains.hibernate.HibernateTest.main(HibernateTest.java:33)
Caused by: java.sql.SQLSyntaxErrorException: 'USER_ID' is not a column in table or VTI 'ADMIN.VEHICLE'.
    at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
    at org.apache.derby.client.am.Connection.prepareStatement(Unknown Source)
    at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534)
    at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:116)
    at org.hibernate.id.insert.AbstractSelectingDelegate.performInsert(AbstractSelectingDelegate.java:54)
    ... 16 more
Caused by: org.apache.derby.client.am.SqlException: 'USER_ID' is not a column in table or VTI 'ADMIN.VEHICLE'.
    at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown Source)
    at org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown Source)
    at org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown Source)
    at org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Unknown Source)
    at org.apache.derby.client.am.PreparedStatement.readPrepareDescribeInputOutput(Unknown Source)
    at org.apache.derby.client.am.PreparedStatement.flowPrepareDescribeInputOutput(Unknown Source)
    at org.apache.derby.client.am.PreparedStatement.prepare(Unknown Source)
    at org.apache.derby.client.am.Connection.prepareStatementX(Unknown Source)
    ... 20 more

This seems to go away when i manually delete the tables and execute again. What could be causing this behavior.

Answer 1

That comment is actually incorrect. Since quite a long time ago, the true behavior of "create-drop" as compared to just "create" is that the former drops the schema when the SessionFactory is closed. "create" itself does what the comment says, which is to drop the schema and recreate it on startup. For verification, setting "org.hibernate" logging to trace will show that with either "create" or "create-drop", the schema is dropped and then created at startup:

INFO - Running hbm2ddl schema export
DEBUG - import file not found: /import.sql
INFO - exporting generated schema to database
TRACE - total checked-out connections: 0
TRACE - using pooled JDBC connection, pool size: 0
DEBUG - alter table Bar drop constraint FK103F39E150191
DEBUG - Unsuccessful: alter table Bar drop constraint FK103F39E150191
DEBUG - Table "BAR" not found; SQL statement:
alter table Bar drop constraint FK103F39E150191 [42102-149]
DEBUG - drop table Bar if exists
DEBUG - drop table Foo if exists
DEBUG - create table Bar (id integer generated by default as identity, foo_id integer, primary key (id))
DEBUG - create table Foo (id integer generated by default as identity, primary key (id))
DEBUG - alter table Bar add constraint FK103F39E150191 foreign key (foo_id) references Foo
INFO - schema export complete

However, on shutdown (SessionFactory.close()), the "create" gives

INFO - closing
INFO - cleaning up connection pool: jdbc:h2:file:D:/dev/projects/testbed/test-db-for-hibernate-create-drop

whereas with "create-drop", you'll see

INFO - closing
INFO - Running hbm2ddl schema export
DEBUG - import file not found: /import.sql
INFO - exporting generated schema to database
TRACE - total checked-out connections: 0
TRACE - using pooled JDBC connection, pool size: 0
DEBUG - alter table Bar drop constraint FK103F39E150191
DEBUG - drop table Bar if exists
DEBUG - drop table Foo if exists
INFO - schema export complete
TRACE - returning connection to pool, pool size: 1
INFO - cleaning up connection pool: jdbc:h2:file:D:/dev/projects/testbed/test-db-for-hibernate-create-drop

You can try it yourself:

git clone [email protected]:zzantozz/testbed tmp
cd tmp
mvn compile exec:java -Dexec.mainClass=rds.hibernate.Main -pl hibernate-create-drop