Generating Database Schema using Hibernate

0

Hibernate provides a tool to automatically generate the database schema from the mapping files. The generated schema includes ‘create table’, ‘alter table’, referential integrity constraints, primary and foreign keys.

  1. Create some POJO persistent classes.
  2. Create mapping files.
  3. Configure the Dialect.
  4. Customize your mapping files to improve the generated schema.
  5. Run SchemaExport tool to convert mapping files to DDL statements.

Let’s begin with the setup.

Below are my setup details:

  1. Maven 3.2.3
  2. Hibernate 5.0.0.CR1 RELEASE
  3. Eclipse  as the IDE, version Luna 4.4.1.
  4. Ant support from Maven to run the SchemaExportTask

Add the following dependencies:

  1. hibernate-core the core hibernate library
  2. mysql-connector-java for database driver
  3. slf4j-log4j12 for logging
  4. javassist hibernate uses this for Java bytecode manipulation
  5. hibernate-tools hibernate uses this for generating schema
  6. maven-antrun-plugin plugin to call ant tasks from Maven

pom.xml:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.javarticles.spring.hibernate</groupId>
	<artifactId>hibernateExample</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<dependencies>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.26</version>
		</dependency>
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-core</artifactId>
			<version>${hibernate.version}</version>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-api</artifactId>
			<version>1.7.12</version>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-log4j12</artifactId>
			<version>1.7.12</version>
		</dependency>
		<dependency>
			<groupId>javassist</groupId>
			<artifactId>javassist</artifactId>
			<version>3.12.1.GA</version>
		</dependency>
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-tools</artifactId>
			<version>4.3.1.CR1</version>
		</dependency>
	</dependencies>
	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-antrun-plugin</artifactId>
				<version>1.8</version>
				<configuration>
					<target>
						<property name="compile_classpath" refid="maven.compile.classpath" />
						<ant antfile="build.xml">
							<target name="schemaexportTarget" />
						</ant>
					</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
	<properties>
		<hibernate.version>5.0.0.CR1</hibernate.version>
	</properties>

</project>

Create a model

Let’s first create a simple business model which we will map to database tables and then generate schema.

The model is chosen such that we have a bit of everything, indexes, foreign keys, uniqueness, check constraint, a variety of data types like boolean, long, string and entity reference.

We have an order which is meant for a customer and deals with a product.

Simple Order Model

Simple Order Model

Let’s create the POJO classes.

Order:

package com.javarticles.hibernate;

import java.util.Date;

public class Order {
    private Long orderId;
    private String orderNbr;
    private Date orderDate;
    private String orderDesc;
    private Long orderQty;
    private Double totalPrice;
    private String orderPaymentMethod;
    private Customer orderCustomer;
    private Product orderProduct;

    public Long getOrderId() {
        return orderId;
    }      

    public String getOrderNbr() {
        return orderNbr;
    }

    public void setOrderNbr(String orderNbr) {
        this.orderNbr = orderNbr;
    }

    public Date getOrderDate() {
        return orderDate;
    }

    public void setOrderDate(Date orderDate) {
        this.orderDate = orderDate;
    }

    public String getOrderDesc() {
        return orderDesc;
    }

    public void setOrderDesc(String orderDesc) {
        this.orderDesc = orderDesc;
    }

    public Long getOrderQty() {
        return orderQty;
    }

    public void setOrderQty(Long orderQty) {
        this.orderQty = orderQty;
    }       

    public Double getTotalPrice() {
        return totalPrice;
    }

    public void setTotalPrice(Double totalPrice) {
        this.totalPrice = totalPrice;
    }

    public String getOrderPaymentMethod() {
        return orderPaymentMethod;
    }

    public void setOrderPaymentMethod(String orderPaymentMethod) {
        this.orderPaymentMethod = orderPaymentMethod;
    }

    public Customer getOrderCustomer() {
        return orderCustomer;
    }

    public void setOrderCustomer(Customer orderCustomer) {
        this.orderCustomer = orderCustomer;
    }

    public Product getOrderProduct() {
        return orderProduct;
    }

    public void setOrderProduct(Product orderProduct) {
        this.orderProduct = orderProduct;
    }

    public String toString() {
        return "Order: nbr[" + orderNbr + "] date [" + orderDate + "] desc["
                + orderDesc + "] qty[" + orderQty + "]";
    }
}

Product:

package com.javarticles.hibernate;


public class Product {
    private Long productId;
    private String productName;
    
    public Long getProductId() {
        return productId;
    }
    public void setProductId(Long productId) {
        this.productId = productId;
    }
    public String getProductName() {
        return productName;
    }
    public void setProductName(String productName) {
        this.productName = productName;
    }       
}

Customer:

package com.javarticles.hibernate;


public class Customer {
    private Long customerId;
    private String customerFirstName;
    private String customerLastName;
    private String customerMobile;
    private String customerEmailId;
   
    public Long getCustomerId() {
        return customerId;
    }
    public void setCustomerId(Long customerId) {
        this.customerId = customerId;
    }
    public String getCustomerFirstName() {
        return customerFirstName;
    }
    public void setCustomerFirstName(String customerFirstName) {
        this.customerFirstName = customerFirstName;
    }
    public String getCustomerLastName() {
        return customerLastName;
    }
    public void setCustomerLastName(String customerLastName) {
        this.customerLastName = customerLastName;
    }
    public String getCustomerMobile() {
        return customerMobile;
    }
    public void setCustomerMobile(String customerMobile) {
        this.customerMobile = customerMobile;
    }
    public String getCustomerEmailId() {
        return customerEmailId;
    }
    public void setCustomerEmailId(String customerEmailId) {
        this.customerEmailId = customerEmailId;
    }
}

Create mapping files

Before we start generating schema, its important we customize the schema. By customizing, we mean to come up with a schema which is more explicit and is not driven by the default rules. This way, the DDL statements will contain just the stuff we want.

In the mapping files below, we have used the following attributes:

  1. length this defined the column length.
  2. precision total number of digits.
  3. scale number of digits after the decimal.
  4. not-null specifies that the column should be non-nullable.
  5. unique specifies that the column should have a unique constraint.
  6. index specifies the name of a (multi-column) index.
  7. unique-key specifies the name of a multi-column unique constraint.
  8. foreign-key specifies the name of the foreign key constraint generated for an association.
  9. sql-type specify explicitly SQL column type when we know the type is not the default column type.
  10. default specify a default value for the column.
  11. check create an SQL check constraint on either column or table.

customer.hbm.xml:

<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.javarticles.hibernate">
	<class name="com.javarticles.hibernate.Customer" table="customer">
		<id name="customerId" column="customer_id">
			<generator class="native" />
		</id>
		<property name="customerFirstName" column="first_name" type="string" length="30" unique-key="cust_name" not-null="true"/>
		<property name="customerLastName" column="last_name" type="string" length="30" unique-key="cust_name" not-null="true"/>
		<property name="customerMobile" column="mobile_nbr" type="string" length="30" index="mobile" not-null="true"/>
		<property name="customerEmailId" column="email_id" type="string" length="30" index="mobile" not-null="false"/>						
	</class>
</hibernate-mapping>

product.hbm.xml:

<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.javarticles.hibernate">
	<class name="com.javarticles.hibernate.Product" table="product">
		<id name="productId" column="product_id">
			<generator class="native" />
		</id>
		<property name="productName" column="product_name" type="string" length="30" unique="true" not-null="true"/>		
	</class>
</hibernate-mapping>

orders.hbm.xml:

<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.javarticles.hibernate">
	<class name="com.javarticles.hibernate.Order" table="orders">
		<id name="orderId" column="order_id">
			<generator class="native" />
		</id>
		<property name="orderNbr" column="order_nbr" type="string"
			length="30" unique="true" not-null="true"/>
		<property name="orderDesc" column="order_desc" type="string"
			length="60" not-null="false"/>
		<property name="orderDate" type="timestamp" column="order_date" not-null="true"/>
		<property name="orderQty" type="long" not-null="true">
			<column name="qty" default="1"/> 
		</property>
		<property name="totalPrice" type="double" not-null="true" precision="6" scale="2">
			<column name="total_price" sql-type="decimal(6,3)" check="total_price > 1000" />
		</property>
		<!--  
		<property name="orderPaymentMethod" type="string" length="20">
			<column name="payment_method" default="'BY_CARD_ON_DELIVERY'"
				not-null="true" />
		</property>
		-->
		<many-to-one name="orderProduct" column="product"
			class="com.javarticles.hibernate.Product" foreign-key="FK_ORDER_PRODUCT" not-null="true"/>
	</class>
</hibernate-mapping>

Define hibernate configuration file

You must specify a SQL Dialect using the hibernate.dialect property as the dialect drives the DDL statements.

We are using MySQL so the dialect class is org.hibernate.dialect.MySQLDialect. You can also see the database connection properties and the mapping resources.

hibernate.cfg.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
		"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
		"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
	<session-factory>
		<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
		<property name="hibernate.connection.password">mnrpass</property>
		<property name="hibernate.connection.url">jdbc:mysql://localhost/test</property>
		<property name="hibernate.connection.username">root</property>
		<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
		<property name="hbm2ddl.auto">create-drop</property>
		<property name="show_sql">true</property>				
		<property name="current_session_context_class">thread</property>
		<mapping resource="customer.hbm.xml" />
		<mapping resource="product.hbm.xml" />
		<mapping resource="orders.hbm.xml" />
	</session-factory>
</hibernate-configuration>

Generate the Schema

hbm2ddl is distributed with the core Hibernate package so one can also call it programatically. When you run it, it will create the DDL statements in the SQL file passed in to schemaExport.setOutputFile(). In our case, it is hbm2schema.sql.

A new SchemaExport object is created from the Metadata. The Metadata is created using hibernate configuration XML hibernate.cfg.xml. The schemaExport.create(true, true) call triggers the DDL creation process and not only writes the generated SQL to file but also prints them to console.

HibernateSchemaGenerationExample:

package com.javarticles.hibernate;

import java.io.IOException;

import org.hibernate.MappingException;
import org.hibernate.boot.MetadataSources;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.boot.registry.internal.StandardServiceRegistryImpl;
import org.hibernate.boot.spi.MetadataImplementor;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.tool.hbm2ddl.SchemaExport;

public class HibernateSchemaGenerationExample {

    public static void main(String[] args) throws MappingException, IOException {
        ServiceRegistry serviceRegistry = buildCfg();
        MetadataImplementor metadata = (MetadataImplementor) new MetadataSources(serviceRegistry).buildMetadata();
        SchemaExport schemaExport = new SchemaExport(metadata);
        schemaExport.setOutputFile("hbm2schema.sql");
        schemaExport.create(true, true);
        ( (StandardServiceRegistryImpl) serviceRegistry ).destroy();
    }
    
    public static StandardServiceRegistryImpl buildCfg() {
        return (StandardServiceRegistryImpl) new StandardServiceRegistryBuilder()
                .configure("hibernate.cfg.xml")
                .build();
    }
}

hbm2schema.sql:

alter table orders drop foreign key FK_ORDER_PRODUCT
drop table if exists customer
drop table if exists orders
drop table if exists product
create table customer (customer_id bigint not null auto_increment, first_name varchar(30) not null, last_name varchar(30) not null, mobile_nbr varchar(30) not null, email_id varchar(30), primary key (customer_id))
create table orders (order_id bigint not null auto_increment, order_nbr varchar(30) not null, order_desc varchar(60), order_date datetime not null, qty bigint default 1, total_price decimal(6,3) check (total_price > 1000), product bigint not null, primary key (order_id))
create table product (product_id bigint not null auto_increment, product_name varchar(30) not null, primary key (product_id))
create index mobile on customer (mobile_nbr, email_id)
alter table customer add constraint cust_name  unique (first_name, last_name)
alter table orders add constraint UK_88c5du0f4ysw599c7fpfvl2cq  unique (order_nbr)
alter table orders add constraint FK_ORDER_PRODUCT foreign key (product) references product (product_id)
alter table product add constraint UK_383i0awxqlq7pc33hil7afbgo  unique (product_name)

SchemaExport Ant Task

Ant task to generate the schema.

build.xml:

<project name="hbm2ddl" default="schemaexportTarget">
	<target name="schemaexportTarget">
		<taskdef name="schemaexport"
			        classname="org.hibernate.tool.hbm2ddl.SchemaExportTask"
			        classpath="${compile_classpath}"/>

				<schemaexport
					config="src/main/resources/hibernate.cfg.xml"
			        quiet="no"
			        text="no"
			        drop="no"
			        delimiter=";"
			        output="schema-export.sql"/>
	</target>

</project>

Call the above ant task from Maven.

Below is the maven-antrun-plugin snippet from pom.xml.

pom.xml:

<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-antrun-plugin</artifactId>
				<version>1.8</version>
				<configuration>
					<target>
						<property name="compile_classpath" refid="maven.compile.classpath" />
						<ant antfile="build.xml">
							<target name="schemaexportTarget" />
						</ant>
					</target>
				</configuration>
			</plugin>
		</plugins>
	</build>

Now to run the above maven-antrun-plugin, run the below command.

mvn antrun:run

schema-export.sql:

alter table orders drop foreign key FK_ORDER_PRODUCT;
drop table if exists customer;
drop table if exists orders;
drop table if exists product;
create table customer (customer_id bigint not null auto_increment, first_name varchar(30) not null, last_name varchar(30) not null, mobile_nbr varchar(30) not null, email_id varchar(30), primary key (customer_id));
create table orders (order_id bigint not null auto_increment, order_nbr varchar(30) not null, order_desc varchar(60), order_date datetime not null, qty bigint default 1, total_price decimal(6,3) check (total_price > 1000), product bigint not null, primary key (order_id));
create table product (product_id bigint not null auto_increment, product_name varchar(30) not null, primary key (product_id));
create index mobile on customer (mobile_nbr, email_id);
alter table customer add constraint cust_name  unique (first_name, last_name);
alter table orders add constraint UK_88c5du0f4ysw599c7fpfvl2cq  unique (order_nbr);
alter table orders add constraint FK_ORDER_PRODUCT foreign key (product) references product (product_id);
alter table product add constraint UK_383i0awxqlq7pc33hil7afbgo  unique (product_name);

Download the source code

This was an example about generating schema from the mapping files using hibernate SchemaExport tool.

You can download the source code here: hibernateSchemaGenerationExample.zip
Share.

Comments are closed.