hibernate.hbm2ddl.auto -2

In the last article I used the SchemaExporter tool to create two sample tables. Here I shall explore the tool in more detail.The tool can be enabled by setting the below property in our cfg file:

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

Or if we are using hibernate.properties

hibernate.hbm2ddl.auto=create-drop

The above property tells hibernate to use the hbm2ddl tool.
The different values available for the tool are :

• validate: validate the schema, makes no changes to the database. 
• update: update the schema. 
• create: creates the schema, destroying previous data. 
• create-drop: drop the schema at the end of the session. 

I shall run the tool on our previous tables (Item and Shelf) for the available different configurations.

Java Code:

public static void main(String[] args) {
    Configuration configuration = new Configuration();
    configuration = configuration.configure();
    sessionFactory = configuration.buildSessionFactory();
}

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

On running the code for create-drop mode, the logs are as below:

5797 [main] INFO  org.hibernate.tool.hbm2ddl.SchemaExport  - Running hbm2ddl sch
ema export
5797 [main] DEBUG org.hibernate.tool.hbm2ddl.SchemaExport  - import file not fou
nd: /import.sql
5797 [main] INFO  org.hibernate.tool.hbm2ddl.SchemaExport  - exporting generated
 schema to database
...
...
5797 [main] DEBUG org.hibernate.tool.hbm2ddl.SchemaExport  - alter table ITEM dr
op foreign key Item_FK_Constraint_1
6000 [main] DEBUG org.hibernate.tool.hbm2ddl.SchemaExport  - drop table if exist
s ITEM
6016 [main] DEBUG org.hibernate.tool.hbm2ddl.SchemaExport  - drop table if exist
s SHELF
6047 [main] DEBUG org.hibernate.tool.hbm2ddl.SchemaExport  - create table ITEM (
ID bigint not null auto_increment, NAME varchar(255) not null, COST numeric(19,2
) default 0, BEST_PRICE decimal(13,3) check (BEST_PRICE > 0), shelfId bigint not
 null, SERIAL_NO varchar(255) not null, primary key (ID), unique (NAME, shelfId)
, unique (SERIAL_NO))
6062 [main] DEBUG org.hibernate.tool.hbm2ddl.SchemaExport  - create table SHELF
(ID bigint not null auto_increment, primary key (ID))
6078 [main] DEBUG org.hibernate.tool.hbm2ddl.SchemaExport  - create index search
Index on ITEM (NAME)
6094 [main] DEBUG org.hibernate.tool.hbm2ddl.SchemaExport  - alter table ITEM ad
d index Item_FK_Constraint_1 (shelfId), add constraint Item_FK_Constraint_1 fore
ign key (shelfId) references SHELF (ID)
6109 [main] INFO  org.hibernate.tool.hbm2ddl.SchemaExport  - schema export compl
ete

As seen in the logs above, when the application is started, Hibernate will drop the tables if they already exist and then recreate them. If the program closes the sessionFactory at the end of the code, then (and only then) Hibernate will drop the tables created as a part of this execution.

6156 [main] INFO  org.hibernate.tool.hbm2ddl.SchemaExport  - 
Running hbm2ddl schema export 
6156 [main] DEBUG org.hibernate.tool.hbm2ddl.SchemaExport  - 
import file not fou nd: /import.sql 
6156 [main] INFO  org.hibernate.tool.hbm2ddl.SchemaExport  - 
exporting generated  schema to database ... ... 
6297 [main] DEBUG org.hibernate.tool.hbm2ddl.SchemaExport  -  
alter table ITEM dr op foreign key Item_FK_Constraint_1 
6328 [main] DEBUG org.hibernate.tool.hbm2ddl.SchemaExport  -  
drop table if exist s ITEM 
6344 [main] DEBUG org.hibernate.tool.hbm2ddl.SchemaExport  - 
drop table if exist s SHELF 
6344 [main] INFO  org.hibernate.tool.hbm2ddl.SchemaExport  -  
schema export complete

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

This works in a very similar manner to the create-drop, but without the drop facility.

<property name="hibernate.hbm2ddl.auto">validate</property>

This validates the mapping file contents with the values present in the database. If any mismatch is observed, the application throws an org.hibernate.HibernateException.

<property name="hibernate.hbm2ddl.auto">update</property>

This is used to fire ddl update statements on the mapped tables. To test this mode, I made some changes in my hbm file.

<property name="cost" precision="6" scale="2" type="big_decimal">
    <column name="UNIT_COST" default="10" />
</property>

I modified my Item mapping file to include these changes modified the name of cost column to UNIT_COST and set its default value as 10.
The resultant output is as follows:

2141 [main] INFO  org.hibernate.tool.hbm2ddl.SchemaUpdate  - updating schema
...
...
2219 [main] INFO  org.hibernate.tool.hbm2ddl.TableMetadata  - table found: menag
erieDb.item
2219 [main] INFO  org.hibernate.tool.hbm2ddl.TableMetadata  - columns: [id, best
_price, name, shelfid, cost, serial_no]
2219 [main] INFO  org.hibernate.tool.hbm2ddl.TableMetadata  - foreign keys: [ite
m_fk_constraint_1]
2219 [main] INFO  org.hibernate.tool.hbm2ddl.TableMetadata  - indexes: [searchin
dex, name, primary, item_fk_constraint_1, serial_no]
2250 [main] INFO  org.hibernate.tool.hbm2ddl.TableMetadata  - table found: menag
erieDb.shelf
2250 [main] INFO  org.hibernate.tool.hbm2ddl.TableMetadata  - columns: [id]
2250 [main] INFO  org.hibernate.tool.hbm2ddl.TableMetadata  - foreign keys: []
2250 [main] INFO  org.hibernate.tool.hbm2ddl.TableMetadata  - indexes: [primary]
2250 [main] DEBUG org.hibernate.tool.hbm2ddl.SchemaUpdate  - alter table ITEM ad
d column UNIT_COST numeric(19,2) default 10 
2266 [main] INFO  org.hibernate.tool.hbm2ddl.SchemaUpdate  - schema update compl
ete

From the logs it can be seen that Hibernate added a new column to the table, instead of modifying the existing column. So now there are two columns in the table, COST and UNIT_COST.
Hibernate thus ensured that there were no data losses on any kind. However you would probably want to remove the old column manually.

Hibernate also provides the option to execute some custom scripts using this tool. These scripts are executed however only after the operations of the SchemaExporter tool are complete. The scripts needed to be placed in a file named import.sql and they must be available on the class path.

import.sql:

Insert into shelf (ID) values (1);
Insert into item (ID,NAME,COST,BEST_PRICE,SHELFID,SERIAL_NO) 
values (1,'Item1',100.05,90,1,'S1234');

Output logs:

2109 [main] INFO  org.hibernate.tool.hbm2ddl.SchemaExport  - Executing import sc
ript: /import.sql
2109 [main] DEBUG org.hibernate.tool.hbm2ddl.SchemaExport  - Insert into shelf (
ID) values (1)
2125 [main] DEBUG org.hibernate.tool.hbm2ddl.SchemaExport  - Insert into item (I
D,NAME,COST,BEST_PRICE,SHELFID,SERIAL_NO) values (1,'Item1',100.05,90,1,'S1234')
2141 [main] INFO  org.hibernate.tool.hbm2ddl.SchemaExport  - schema export compl
ete

This option is available with only the create-drop and the create option.

That concludes my study of the tool. From what I read in other blogs the suggestion is that the tool is a good option to speed up development. But when it comes to production it is preferable to have your sql scripts handwritten. A compromise formula for me would be to export the sql scripts thus created by Hibernate during the development process and have them modified to fill up missing gaps if any.