1

In Hibernate ORM & Spring Boot, during the SessionFactory creation, SQL DML statements can be executed to create tables and/or insert data via files such as import.sql defined in the javax.persistence.hibernate.hbm2ddl.import_files property. As I understand, using this feature is particularly useful for inserting referential data (e.g. ISO codes such as countries) on schema creation (create, create-drop, or update). Using Hibernate multi-tenancy also means that if new tenants are dynamically created, the import.sql will be run.

import.sql example:

INSERT INTO address_types (address_type_id, address_type) VALUES 
    (1, 'Home'), (2, 'Business') ON CONFLICT DO NOTHING;

AddressType.java:

@Entity
@Table(name = "address_types")
public class AddressType {
    @Id
    @SequenceGenerator(name = "address_types_id_seq", sequenceName = "address_types_id_seq")
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "address_types_id_seq")
    @Column(name = "address_type_id")
    private int id;
    @Column(name = "address_type", nullable = false, unique = true)
    private String type;

    // getters and setters ...
}

However, I noticed there are a couple limitations with using plain DML statements:

  1. If using sequences as opposed to a database autoincrement key (via GenerationType.IDENTITY) on the primary key column, it's easy to insert referential data and forget to either set the new sequence value or to specify the correct initial sequence number in the JPA annotation. This mistake may lead to DataIntegrityViolationExceptions if inserting data later within the application:

    org.springframework.dao.DataIntegrityViolationException: could not execute batch; 
    SQL [/* insert com.mypackage.model.AddressType */ 
    insert into address_types (address_type_id, address_type) values (?, ?)]; 
    constraint [address_types_pkey]; 
    nested exception is org.hibernate.exception.ConstraintViolationException: could not execute batch
    

    The fix for this limitation would be to carefully set the correct initialValue in the JPA entity:

    @SequenceGenerator(initialValue=2, name = "address_types_id_seq", sequenceName = "address_types_id_seq")`
    

    Or to run the SETVAL SQL DML statement:

    SELECT SETVAL(address_types_id_seq, (SELECT MAX(address_type_id) FROM address_types))
    

    Both of the above fixes seem less than ideal to me.

    Using the first fix, you have to either carefully keep track of the number of entities inserted or just leave some extra sequence space (e.g. set initialValue to some larger number).

    With the second fix, while it is valid SQL DML, it doesn't seem to run when I place it in my import.sql. Further, with multi-tenancy via schemas, there may be some confusion over which schema the SQL DML statement is running under. Is it the public/dbo schema or is it the tenant's schema?

    Overall, both approaches seem error-prone.

  2. Refactoring the JPA entity names means the import.sql needs to also be updated manually.

Alternatively, I thought why not simply create the referential data directly as Hibernate entities, persisting when the application starts for the first time? One such article I came across suggested exactly that by using a Spring ApplicationListener:

@Component
public class ExampleLoader implements ApplicationListener<ContextRefreshedEvent>, Ordered {
    public int getOrder() {
        return 1;
    }

    @Override
    public void onApplicationEvent(ContextRefreshedEvent event) {
        //generate data
    }
}

Are there any reasons against this approach? Or is there a best practice with regards to referential data setup?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.