Programmatically Generating Database Schema with Hibernate 5

Today, I was working with a code base that was using Hibernate‘s schema generation facilities to create the database schema for a customer on the fly. The application creates separate database schema for each customer. Then, depending on the client specific identifier it connects to the correct database. This blog will not talk about how you should design your application with multi tenant database. I will cover that in a future post. In this post, I will share the code snippet that helped me generate database schema with Hibernate 5. In this post, I am using MySQL database as an example. The same code snippet should also work with other RDBMS as well.

The code shown below will generate schema in the testdb database. Make sure to create database manually using the create database testdb SQL script before running the code below.

import java.util.HashMap;
import java.util.Map;

import com.example.demo.domain.Task;
import com.example.demo.domain.User;
import org.hibernate.boot.MetadataSources;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.boot.spi.MetadataImplementor;
import org.hibernate.tool.hbm2ddl.SchemaExport;

public class MySQLDatabaseGeneratorCopy {

    public static void main(String[] args) {
        Map<String, String> settings = new HashMap<>();
        settings.put("connection.driver_class", "com.mysql.jdbc.Driver");
        settings.put("dialect", "org.hibernate.dialect.MySQL57InnoDBDialect");
        settings.put("hibernate.connection.url", "jdbc:mysql://localhost/testdb?useSSL=false");
        settings.put("hibernate.connection.username", "root");
        settings.put("hibernate.connection.password", "");
        settings.put("hibernate.hbm2ddl.auto", "create");
        settings.put("show_sql", "true");

        MetadataSources metadata = new MetadataSources(
                new StandardServiceRegistryBuilder()
                        .applySettings(settings)
                        .build());
        metadata.addAnnotatedClass(User.class);
        metadata.addAnnotatedClass(Task.class);
        SchemaExport schemaExport = new SchemaExport(
                (MetadataImplementor) metadata.buildMetadata()
        );
        schemaExport.setHaltOnError(true);
        schemaExport.setFormat(true);
        schemaExport.setDelimiter(";");
        schemaExport.setOutputFile("db-schema.sql");
        schemaExport.execute(true, true, false, true);
    }
}

In the code shown above, User and Task are custom domain classes. The code shown above does the following:

1. It first create a Map and populate it with the hibernate configuration properties. The properties include connectionUrl, database username and password, and hibernate’s hibernate.hbm2ddl.auto property that tells Hibernate to create the database schema. If you change its value to none then schema will not be generated.

2. Next, we create an instance of MetadataSources, which gives access to the metadata of the entity classes. Also, we add our domain classes manually to the MetadataSources object.

3. Then, we create the SchemaExport object passing it the metadata built from MetadataSources. We also configure exporter by setting few properties. All of the properties names are self explanatory. I set output file to db-schema.sql to ensure that schema is also written to a file in my application root folder.

4. Finally, we execute the SchemaExport passing it few boolean arguments. The four boolean arguments are:

   1. script: This boolean argument tells SchemaExport to print DDL scripts to the console. If it is false, then DDL scripts will not be printed to the console.

   2. export: This boolean argument tells SchemaExport to create the database schema. If this value is set to false, then nothing will happen in your database. I missed this and wasted couple of hours.

   3. justDrop: It will only run the drop scripts.

   4. justCreate: It will only run the create scripts.

If you want to create the schema, then you can also use the shorthand create function that only takes two parameters — script and export.

schemaExport.create(true, true);

The output produced is shown below.

02:59:45.906 [main] DEBUG org.hibernate.SQL - alter table users drop foreign key FK9ps89xpk18xf6pefpu1tg0nx

    alter table users 
        drop 
        foreign key FK9ps89xpk18xf6pefpu1tg0nx;
02:59:45.938 [main] DEBUG org.hibernate.SQL - drop table if exists hibernate_sequences

    drop table if exists hibernate_sequences;
02:59:45.942 [main] DEBUG org.hibernate.SQL - drop table if exists tasks

    drop table if exists tasks;
02:59:45.947 [main] DEBUG org.hibernate.SQL - drop table if exists users

    drop table if exists users;
02:59:45.952 [main] DEBUG org.hibernate.SQL - create table hibernate_sequences (sequence_name varchar(255) not null, next_val bigint, primary key (sequence_name))

    create table hibernate_sequences (
        sequence_name varchar(255) not null,
        next_val bigint,
        primary key (sequence_name)
    );
02:59:45.965 [main] DEBUG org.hibernate.SQL - create table tasks (id bigint not null, name varchar(255), primary key (id))

    create table tasks (
        id bigint not null,
        name varchar(255),
        primary key (id)
    );
02:59:45.980 [main] DEBUG org.hibernate.SQL - create table users (id bigint not null, name varchar(255), roleName varchar(255), taskOfUser_id bigint, primary key (id))

    create table users (
        id bigint not null,
        name varchar(255),
        roleName varchar(255),
        taskOfUser_id bigint,
        primary key (id)
    );
02:59:45.996 [main] DEBUG org.hibernate.SQL - alter table users add constraint FK9ps89xpk18xf6pefpu1tg0nx foreign key (taskOfUser_id) references tasks (id)

    alter table users 
        add constraint FK9ps89xpk18xf6pefpu1tg0nx 
        foreign key (taskOfUser_id) 
        references tasks (id);
02:59:46.048 [main] INFO org.hibernate.tool.hbm2ddl.SchemaExport - HHH000230: Schema export complete

Specifying the naming strategy

Prior to version 5 of Hibernate, there used to be a single property org.hibernate.cfg.NamingStrategy that defined naming strategy. This got changed in Hibernate 5. Hibernate now defines two properties:

1. hibernate.implicit_naming_strategy: This is used to determine a proper logical name from the domain model mapping. A logical name can be either explicitly specified by the user (using @Column or @Table e.g.) or it can be implicitly determined by Hibernate through anImplicitNamingStrategy contract.
2. hibernate.physical_naming_strategy: This resolves logical name to a physical name which is defined by the PhysicalNamingStrategy contract.

You can read more about in the documentation.

If you look at the create table users statement above you will notice that it followed camelCase convention for fields. For foreign key, it appended _id after the taskOfUser field. You can define you own custom hibernate.physical.naming_strategy to define your custom naming strategy. Let’s suppose we want to write all fields in snake case(i.e. role_name, task_of_user_id) format then either we can write our own naming strategy or if you are Spring JPA then we can use the following setting.

settings.put("hibernate.physical_naming_strategy", "org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy");

Now, when you will run the schema generation again you will see column name in snake case as shown in the output below.

03:21:50.183 [main] DEBUG org.hibernate.SQL - create table hibernate_sequences (sequence_name varchar(255) not null, next_val bigint, primary key (sequence_name))

    create table hibernate_sequences (
        sequence_name varchar(255) not null,
        next_val bigint,
        primary key (sequence_name)
    );
03:21:50.213 [main] DEBUG org.hibernate.SQL - create table tasks (id bigint not null, name varchar(255), primary key (id))

    create table tasks (
        id bigint not null,
        name varchar(255),
        primary key (id)
    );
03:21:50.231 [main] DEBUG org.hibernate.SQL - create table users (id bigint not null, name varchar(255), role_name varchar(255), task_of_user_id bigint, primary key (id))

    create table users (
        id bigint not null,
        name varchar(255),
        role_name varchar(255),
        task_of_user_id bigint,
        primary key (id)
    );
03:21:50.244 [main] DEBUG org.hibernate.SQL - alter table users add constraint FKndlunlnl4e224d00wspe10bq foreign key (task_of_user_id) references tasks (id)

    alter table users 
        add constraint FKndlunlnl4e224d00wspe10bq 
        foreign key (task_of_user_id) 
        references tasks (id);
03:21:50.260 [main] INFO org.hibernate.tool.hbm2ddl.SchemaExport - HHH000230: Schema export complete

That’s it for today. I hope you enjoyed the post.