8

Morning.

I need to add indexing in hibernate entity. As I know it is possible to do using @Index annotation to specify index for separate column but I need an index for several fields of entity.

I've googled and found jboss annotation @Table, that allows to do this (by specification). But (I don't know why) this functionality doesn't work. May be jboss version is lower than necessary, or maybe I don't understant how to use this annotation, but... complex index is not created.

Why index may not be created?

jboss version 4.2.3.GA

Entity example:

package somepackage;
import org.hibernate.annotations.Index;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

@Entity
@org.hibernate.annotations.Table(appliesTo = House.TABLE_NAME,
    indexes = {
            @Index(name = "IDX_XDN_DFN",
                    columnNames = {House.XDN, House.DFN}
            )
    }
)

public class House {
    public final static String TABLE_NAME = "house";
    public final static String XDN = "xdn";
    public final static String DFN = "dfn";

    @Id
    @GeneratedValue
    private long Id;

    @Column(name = XDN)
    private long xdn;

    @Column(name = DFN)
    private long dfn;

    @Column
    private String address;

    public long getId() {
        return Id;
    }

    public void setId(long id) {
        this.Id = id;
    }

    public long getXdn() {
        return xdn;
    }

    public void setXdn(long xdn) {
        this.xdn = xdn;
    }

    public long getDfn() {
        return dfn;
    }

    public void setDfn(long dfn) {
        this.dfn = dfn;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

When jboss/hibernate tries to create table "house" it throws following exception:

Reason: org.hibernate.AnnotationException: @org.hibernate.annotations.Table references an unknown table: house
Pascal Thivent
  • 562,542
  • 136
  • 1,062
  • 1,124
Zaur_M
  • 737
  • 3
  • 11
  • 18
  • 1
    btw, you are supposed to mark answers as accepted (the tick below the votes), if they suit you. – Bozho Feb 02 '10 at 09:34
  • @foobar - did you resolve your issue? – Bozho Feb 23 '10 at 20:52
  • I've desided to create indexes manually by using SQL scripts. Your suggestsion is correct but I suppose that it's like a hack that users use as a hibernate aproach. And this method can't be eligible for another persistence provider. The problem is resolved but in future I want to spend a little more time to realise how to do this task in the best way. I'll write about result here... Thanks a lot. – Zaur_M Mar 03 '10 at 14:15
  • look at @Pascal Thivent answer: it shows that an annotation is missing in your mapping. (from the reference doc: This annotation [@org.hibernate.annotations.Table] is expected where @javax.persistence.Table occurs.) – Thierry May 01 '10 at 19:31

3 Answers3

16

Please try the following:

@Entity
@org.hibernate.annotations.Table(appliesTo = House.TABLE_NAME,
    indexes = {
            @Index(name = "IDX_XDN_DFN",
                    columnNames = {House.XDN, House.DFN}
            )
    }
)
@Table(name="house")
public class House {
    ...
}

Note that this should also allow you to create a multi-column index (based on the index name):

@Index(name = "index1")
public String getFoo();

@Index(name = "index1")
public String getBar();

P.S.: What version of Hibernate are you using BTW? What database/dialect?

Pascal Thivent
  • 562,542
  • 136
  • 1,062
  • 1,124
1

You have to have hibernate.hbm2ddl.auto set to create in persistence.xml. When set to update hibernate won't create indexes.

hibernate.hbm2ddl.auto = create

fraktalek
  • 151
  • 1
  • 3
-1

You'd better go with a composite primary key.

This article explains how to do it with JPA annotations. It uses @Embeddable and @EmbeddedId

Bozho
  • 588,226
  • 146
  • 1,060
  • 1,140
  • Thank you. Use JPA specific API is right thing but ... it's still not clear why it doesn't work on jboss. I've tried to use the same class specifying @Index annotation for separate fields but it doesn't work too. – Zaur_M Feb 02 '10 at 09:42
  • It's not necessary to make entity embeddable in my business logic. There must be more simplest decision. But don't know yet how to do it .... Yes, I've read that article. – Zaur_M Feb 02 '10 at 09:54
  • 1
    this is the simplest and best solution. Since your primary key is composite, you better abstract it a a new object. – Bozho Feb 02 '10 at 10:08
  • Bozho, I totally agree with U, but I want to do less redesign of business domain classes (entities), because it's already used by the application. – Zaur_M Feb 02 '10 at 10:17
  • Anyway thanks for sharing you knowledge. I'll note this approach for the future. – Zaur_M Feb 02 '10 at 10:24
  • 3
    Note: In Oracle a composite primary key cannot contain columns with null values, but a composite unique index can contain columns with null values. Therefore this answer does not make sense in some contexts (specifically when you want to allow one or more of your columns to contain null values and be composite...) – KyleM Feb 12 '13 at 01:11
  • terrible advice, what if it is not a primary key? – Enerccio Nov 29 '16 at 15:53