H2 index name uniqueness

Question

I have a small problem with the uniqueness of index names in h2 database. With mysql/mariadb it is possible to define index named "X" for table A and table B at the same time. With h2 database it is not possible, since name of the index should be unique per database.

It is a problem for me, since I have a base JPA entity class with the following property defined:

@org.hibernate.annotations.Index(name = "X")
protected String x;

It is inherited by class A and B and index creation fails for class B with the following error:

ERROR [main] o.h.tool.hbm2ddl.SchemaUpdate   - HHH000388: Unsuccessful: create index X on B(x)
ERROR [main] o.h.tool.hbm2ddl.SchemaUpdate   - Index "X" already exists

Is it possible to tell hibernate to automatically create index name or somehow create an adapter for H2 that will prefix such index names with table names?

Answer 1

Although you should always have the database drive the schema evolution and use FlywayDB to migrate schema versions, you can override the @Index as you want.

I added a test on GitHub to prove this.

The classes look like this:

@Entity(name = "Base")
@Table(name="Base")
@Inheritance(strategy = InheritanceType.JOINED)
public static abstract class Base {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Transient
    protected String x;

    public Long getId() {
        return id;
    }

    public String getX() {
        return x;
    }

    public void setX(String x) {
        this.x = x;
    }
}

@Entity(name = "ChildY")
@Table(name="ChildY")
@DiscriminatorValue("Y")
public static class ChildY extends Base {

    private String y;

    @Override
    @org.hibernate.annotations.Index(name = "xy")
    @Access(AccessType.PROPERTY)
    public String getX() {
        return x;
    }
}

@Entity(name = "ChildZ")
@Table(name="ChildZ")
@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorValue("Z")
public static class ChildZ extends Base {

    private String z;

    @Override
    @org.hibernate.annotations.Index(name = "xz")
    @Access(AccessType.PROPERTY)
    public String getX() {
        return x;
    }
}

The schema is generated like this:

create table Base (id bigint generated by default as identity (start with 1), primary key (id))
create table ChildY (x varchar(255), y varchar(255), id bigint not null, primary key (id))
create table ChildZ (x varchar(255), z varchar(255), id bigint not null, primary key (id))
create index xy on ChildY (x)
create index xz on ChildZ (x)

This way:

1. You retain the base class property in the Domain Model (field, getter and setter)
2. Each table gets its own x column with an associated index

The problem is that you can't have the field in the base class, as Hibernate will attempt to create it twice. You can file a Jira issue on Hibernate and mention that the HBM schema generation should skip the index if it's already created.

The most elegant solution is to simply use a proper database schema and remove the HBM-DDL schema generation.