Java 使用 JPA 指定索引（非唯一键）

Specifying an Index (Non-Unique Key) Using JPA

提问by Jacob

How do you define a field, eg emailas having an index using JPA annotations. We need a non-unique key on emailbecause there are literally millions of queries on this field per day, and its a bit slow without the key.

您如何定义字段，例如email使用 JPA 注释具有索引。我们需要一个非唯一键，email因为每天在这个字段上有数百万次查询，没有键它有点慢。

@Entity
@Table(name="person", 
       uniqueConstraints=@UniqueConstraint(columnNames={"code", "uid"}))
public class Person {
    // Unique on code and uid
    public String code;
    public String uid;

    public String username;
    public String name;
    public String email;
}

I have seen a hibernate specific annotation but I am trying to avoid vendor specific solutions as we are still deciding between hibernate and datanucleus.

我看到了一个特定于休眠的注释，但我试图避免供应商特定的解决方案，因为我们仍在决定休眠和 datanucleus 之间。

UPDATE:

更新：

As of JPA 2.1, you can do this. See: The annotation @Index is disallowed for this location

从 JPA 2.1 开始，您可以执行此操作。请参阅：此位置不允许使用注释 @Index

采纳答案by borjab

With JPA 2.1 you should be able to do it.

使用 JPA 2.1，您应该能够做到。

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Index;
import javax.persistence.Table;

@Entity
@Table(name = "region",
       indexes = {@Index(name = "my_index_name",  columnList="iso_code", unique = true),
                  @Index(name = "my_index_name2", columnList="name",     unique = false)})
public class Region{

    @Column(name = "iso_code", nullable = false)
    private String isoCode;

    @Column(name = "name", nullable = false)
    private String name;

} 

Update: If you ever need to create and index with two or more columns you may use commas. For example:

更新：如果您需要使用两列或更多列创建和索引，您可以使用逗号。例如：

@Entity
@Table(name    = "company__activity", 
       indexes = {@Index(name = "i_company_activity", columnList = "activity_id,company_id")})
public class CompanyActivity{

回答by Tassos Bassoukos

As far as I know, there isn't a cross-JPA-Provider way to specify indexes. However, you can always create them by hand directly in the database, most databases will pick them up automatically during query planning.

据我所知，没有一种跨 JPA-Provider 的方式来指定索引。但是，您始终可以直接在数据库中手动创建它们，大多数数据库会在查询计划期间自动获取它们。

回答by Thierry-Dimitri Roy

It's not possible to do that using JPA annotation. And this make sense: where a UniqueConstraint clearly define a business rules, an index is just a way to make search faster. So this should really be done by a DBA.

使用 JPA 注释不可能做到这一点。这是有道理的：UniqueConstraint 明确定义了业务规则，而索引只是一种加快搜索速度的方式。所以这真的应该由 DBA 来完成。

回答by Pascal Thivent

I'd really like to be able to specify database indexes in a standardized way but, sadly, this is not part of the JPA specification (maybe because DDL generation support is not required by the JPA specification, which is a kind of road block for such a feature).

我真的很希望能够以标准化的方式指定数据库索引，但遗憾的是，这不是 JPA 规范的一部分（可能是因为 JPA 规范不需要 DDL 生成支持，这是一种障碍这样的功能）。

So you'll have to rely on a provider specific extension for that. Hibernate, OpenJPA and EclipseLink clearly do offer such an extension. I can't confirm for DataNucleus but since indexes definition is part of JDO, I guess it does.

因此，您必须为此依赖提供者特定的扩展。Hibernate、OpenJPA 和 EclipseLink 显然提供了这样的扩展。我无法确认 DataNucleus 但由于索引定义是 JDO 的一部分，我想它确实如此。

I really hope index support will get standardized in next versions of the specification and thus somehow disagree with other answers, I don't see any good reason to not include such a thing in JPA (especially since the database is not always under your control) for optimal DDL generation support.

我真的希望索引支持在规范的下一个版本中得到标准化，因此在某种程度上不同意其他答案，我认为没有什么好的理由不在 JPA 中包含这样的东西（特别是因为数据库并不总是在你的控制之下）以获得最佳的 DDL 生成支持。

By the way, I suggest downloading the JPA 2.0 spec.

顺便说一下，我建议下载 JPA 2.0 规范。

回答by expert

OpenJPA allows you to specify non-standard annotation to define index on property.

OpenJPA 允许您指定非标准注释来定义属性索引。

Details are here.

详细信息在这里。

回答by Nathan

EclipseLink provided an annotation (e.g. @Index) to define an index on columns. There is an exampleof its use. Part of the example is included...

EclipseLink 提供了一个注释（例如@Index）来定义列的索引。有一个使用它的例子。部分示例包括...

The firstName and lastName fields are indexed, together and individually.

firstName 和 lastName 字段被一起和单独编入索引。

@Entity
@Index(name="EMP_NAME_INDEX", columnNames={"F_NAME","L_NAME"})  // Columns indexed together
public class Employee{
    @Id
    private long id;

    @Index                      // F_NAME column indexed
    @Column(name="F_NAME")
    private String firstName;

    @Index                      // L_NAME column indexed
    @Column(name="L_NAME")
    private String lastName;
    ...
}

回答by Alvin Thompson

JPA 2.1 (finally) adds support for indexes and foreign keys! See this blogfor details. JPA 2.1 is a part of Java EE 7, which is out .

JPA 2.1（终于）增加了对索引和外键的支持！有关详细信息，请参阅此博客。JPA 2.1 是 Java EE 7 的一部分，它在 .

If you like living on the edge, you can get the latest snapshot for eclipselink from their maven repository(groupId:org.eclipse.persistence, artifactId:eclipselink, version:2.5.0-SNAPSHOT). For just the JPA annotations (which should work with any provider once they support 2.1) use artifactID:javax.persistence, version:2.1.0-SNAPSHOT.

如果你喜欢生活在边缘，你可以从他们的maven 存储库中获取 eclipselink 的最新快照（groupId:org.eclipse.persistence, artifactId:eclipselink, version:2.5.0-SNAPSHOT）。对于 JPA 注释（一旦它们支持 2.1 就应该与任何提供程序一起使用）使用 artifactID:javax.persistence, version:2.1.0-SNAPSHOT。

I'm using it for a project which won't be finished until after its release, and I haven't noticed any horrible problems (although I'm not doing anything too complex with it).

我将它用于一个直到发布后才会完成的项目，而且我没有注意到任何可怕的问题（尽管我没有用它做任何太复杂的事情）。

UPDATE (26 Sep 2013): Nowadays release and release candidate versions of eclipselink are available in the central (main) repository, so you no longer have to add the eclipselink repository in Maven projects. The latest release version is 2.5.0 but 2.5.1-RC3 is also present. I'd switch over to 2.5.1 ASAP because of issues with the 2.5.0 release (the modelgen stuff doesn't work).

更新（2013 年 9 月 26 日）：现在 eclipselink 的发布和发布候选版本在中央（主）存储库中可用，因此您不再需要在 Maven 项目中添加 eclipselink 存储库。最新的发布版本是 2.5.0，但 2.5.1-RC3 也存在。由于 2.5.0 版本的问题（modelgen 的东西不起作用），我会尽快切换到 2.5.1。

回答by Hyman

To sum up the other answers:

总结其他答案：

• Hibernate: org.hibernate.annotations.Index
• OpenJPA: org.apache.openjpa.persistence.jdbc.Index
• EclipseLink: org.eclipse.persistence.annotations.Index

• 休眠： org.hibernate.annotations.Index
• 打开JPA： org.apache.openjpa.persistence.jdbc.Index
• Eclipse链接： org.eclipse.persistence.annotations.Index

I would just go for one of them. It will come with JPA 2.1 anyway and should not be too hard to change in the case that you really want to switch your JPA provider.

我只想选择其中之一。无论如何，它将随 JPA 2.1 一起提供，并且在您确实想切换 JPA 提供程序的情况下不应该太难更改。

回答by Joe Almore

This solution is for EclipseLink 2.5, and it works (tested):

此解决方案适用于 EclipseLink 2.5，并且有效（已测试）：

@Table(indexes = {@Index(columnList="mycol1"), @Index(columnList="mycol2")})
@Entity
public class myclass implements Serializable{
      private String mycol1;
      private String mycol2;
}

This assumes ascendant order.

这假设升序。

回答by James Clayton

In JPA 2.1 you need to do the following

在 JPA 2.1 中，您需要执行以下操作

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Index;
import javax.persistence.Table;

@Entity(name="TEST_PERSON")
@Table(
    name="TEST_PERSON", 
    indexes = {
       @Index(name = "PERSON_INDX_0", columnList = "age"),
       @Index(name = "PERSON_INDX_1", columnList = "fName"),
       @Index(name = "PERSON_INDX_1", columnList = "sName")  })
public class TestPerson {

    @Column(name = "age", nullable = false)
    private int age;

    @Column(name = "fName", nullable = false)
    private String firstName;

    @Column(name = "sName", nullable = false)
    private String secondName;

    @Id
    private long id;

    public TestPerson() {
    }
}

In the above example the table TEST_PERSON will have 3 indexes:

在上面的例子中，表 TEST_PERSON 将有 3 个索引：

• unique index on the primary key ID

• index on AGE

• compound index on FNAME, SNAME

• 主键 ID 上的唯一索引

• 年龄指数

• FNAME、SNAME 上的复合索引

Note 1: You get the compound index by having two @Index annotations with the same name

注 1：您可以通过使用两个同名的 @Index 注释来获得复合索引

Note 2: You specify the column name in the columnList not the fieldName

注 2：您在 columnList 中指定列名而不是 fieldName