development

JPA를 사용하여 인덱스 (비 고유 키) 지정

big-blog 2020. 9. 17. 08:23
반응형

JPA를 사용하여 인덱스 (비 고유 키) 지정

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

최대 절전 모드 특정 주석을 보았지만 여전히 최대 절전 모드와 데이터 핵 사이를 결정하고 있으므로 공급 업체별 솔루션을 피하려고합니다.

최신 정보:

JPA 2.1부터이 작업을 수행 할 수 있습니다. 참조 : 이 위치에 @Index 주석이 허용되지 않습니다.

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;

}

업데이트 : 두 개 이상의 열을 만들고 색인을 생성해야하는 경우 쉼표를 사용할 수 있습니다. 예를 들면 :

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

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

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.

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).

A unique hand-picked collection of Index annotations

= Specifications =

= ORM Frameworks =

= ORM for Android =

= Other (difficult to categorize) =

  • Realm - Alternative DB for iOS / Android: Annotation io.realm.annotations.Index;
  • Empire-db - a lightweight yet powerful relational DB abstraction layer based on JDBC. It has no schema definition through annotations;
  • Kotlin NoSQL (GitHub) - a reactive and type-safe DSL for working with NoSQL databases (PoC): ???
  • Slick - Reactive Functional Relational Mapping for Scala. It has no schema definition through annotations.

Just go for one of them.

In JPA 2.1 you need to do the following

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:

  • unique index on the primary key ID

  • index on AGE

  • compound index on FNAME, SNAME

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

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

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).

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.

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.

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

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.

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

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

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

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

Details are here.

To sum up the other answers:

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.

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.

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

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

This assumes ascendant order.

참고URL : https://stackoverflow.com/questions/3405229/specifying-an-index-non-unique-key-using-jpa

반응형

'development' 카테고리의 다른 글

목록에없는 가장 작은 정수 찾기  (0) 2020.09.17
jquery 변수 구문  (0) 2020.09.17
터미널에서 내 Git 사용자 이름을 변경하는 방법은 무엇입니까?  (0) 2020.09.17
자바 문자열 : "String s = new String ("silly ");"  (0) 2020.09.17
.NET의 문자열에서 큰 따옴표 제거  (0) 2020.09.17

'development'의 다른글

  • 현재글JPA를 사용하여 인덱스 (비 고유 키) 지정

관련글

댓글

티스토리툴바