Why I cannot define an column named "index" in jpa?

Question

I use jpa to persist an object to mysql databse.

When I define a column named "index" like below, the table will not be generated in database.

When I delete it or rename it, the jpa works fine.

import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import play.db.jpa.GenericModel;

@Entity
@Table(name="QA_ASSETVM")
public class QAAssetVM extends GenericModel{

     private static final long serialVersionUID = 1L;

     @Id 
     @Column(name="vmassetname")
     public String vmassetname;

     @Column(name="assetvm_index")  //this works fine!
     public int index;
     /*
     @Column(name="index")  //When I add this, the table "QA_ASSETVM" will not be generated by jpa in database.
     public int index;
     */
}

Can't test here, but can you declare public int index twice? — antonicelli, Aug 20 '13 at 02:35
`index` and other keywords cannot be used as for column name.. — Daniel Robertus, Aug 20 '13 at 02:37
Obviously they can be used if they are quoted, and that is exactly what some other JPA implementations (e.g DataNucleus JPA) do for the user ... — DataNucleus, Aug 20 '13 at 04:42

score 3 · Answer 1 · edited May 23 '17 at 12:20

As Hannibal pointed out, index is a reserved keyword in MySQL therefore you cannot use it directly as a column name, but you have to escape it.

If you are using Hibernate 3.5+, try with hibernate.globally_quoted_identifiers=true, which will quote all database indentifiers (source: Automatic reserved word escaping for Hibernate tables and columns)
In JPA 2.0, you can use the annotation: @Column(name="\"index\""), which is the new standardize way to escape a single column

score 2 · Accepted Answer · edited Aug 20 '13 at 10:11

2

I haven't tried using mysql, but 'index' seems to be a reserved word.

check this out.

edited Aug 20 '13 at 10:11

Aaron Digulla

321,842
108
597
820

answered Aug 20 '13 at 02:36

Hannibal

48
5

score 1 · Answer 3 · answered Sep 19 '17 at 10:49

1

Because index is the reserved word. In order to use index as a column name you have to use write it within []. like this :

@Column(name="[index]")

answered Sep 19 '17 at 10:49

Akshay Pethani

2,390
2
29
42

Why I cannot define an column named "index" in jpa?

3 Answers3