H2索引名称唯一性 [英] H2 index name uniqueness

问题描述

我对h2数据库中索引名称的唯一性有一个小问题.使用mysql/mariadb，可以同时为表A和表B定义名为"X"的索引.对于h2数据库，这是不可能的，因为索引的名称在每个数据库中都应该是唯一的.

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.

这对我来说是个问题，因为我有一个定义了以下属性的基本JPA实体类:

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;

它被类A和B继承，并且类B的索引创建失败，并出现以下错误:

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

是否可以告诉hibernate自动创建索引名称，或者以某种方式为H2创建一个适配器，该适配器将在表名称的前面添加此类索引名称?

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?

推荐答案

尽管您应该始终使用

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.

这些类如下:

@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;
    }
}

模式是这样生成的:

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)

这种方式:

1. 您将在域模型(字段，getter和setter)中保留基类属性
2. 每个表都有自己的带有关联索引的x列

问题是您不能在基类中包含该字段，因为Hibernate会尝试创建该字段两次.您可以在Hibernate上提交一个Jira问题，并提及如果已创建了HBM模式，则应该跳过该索引.

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.

最优雅的解决方案是仅使用适当的数据库架构并删除HBM-DDL架构生成.

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

这篇关于H2索引名称唯一性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！