hibernate h2 embeddable list expected "identifier"

Question

I'm trying to associate a list of function (whom Embeddable) within my Employee Entity and H2 seems unhappy with this saying that it expected an "identifier"

Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement " CREATE TABLE EMPLOYEE_FUNCTIONS ( EMPLOYEE_EMPLOYEEID VARCHAR(255) NOT NULL, ACTIVE BOOLEAN NOT NULL, DEPARTMENTNUMBER INTEGER NOT NULL, DESCRIPTION VARCHAR(255), ORDER[*] INTEGER NOT NULL ) "; expected "identifier";

The thing is I already done that with an other project and I don't see why it doesn't work.

Employee.java

@Entity
public class Employee extends AbstractScheduleEntity<EmployeeSchedule> {
    public static final String ACOMBA_UNIQUE_FIELD = "acombaUnique";

    @Id
    @GenericGenerator(name = "sequence_id", strategy = 
    "ca.tecsar.core.sql.ServerSequenceGenerator")
    @GeneratedValue(generator = "sequence_id")
    @Column(name = "EmployeeID", unique = true, nullable = false)
    private String employeeID;
    @ElementCollection
    private List<Function> functions;

    //getter and setter
}

Function.java

@Embeddable
public class Function implements Serializable {
    private int order;
    private boolean active;
    private String description;
    private int departmentNumber;

    //getter and setter
}

I removed a few properties in Employee that wasn't necessary. What may cause this error? Is it because I have a String as identifier in my Employee? If so how can I tell to Hibernate to add Employee_EmployeeID as identifier? Thanks

score 71 · Accepted Answer · answered Oct 13 '17 at 20:34

71

Turns out I was being dumb and named a column "Order". Wonder why H2 wasn't happy :upside_down:

Changed the variable name to something else and it worked!

answered Oct 13 '17 at 20:34

Nicolas Sagala Beaucage

1,229
1
11
23

15

`ORDER` is a reserved keyword for H2DB - http://h2database.com/html/advanced.html#compatibility – Eugene Nov 14 '18 at 23:34
2

Yes I understood that. – Nicolas Sagala Beaucage Nov 16 '18 at 00:09
```@Enumerated(EnumType.ORDINAL) private RaceGroup group``` ```GROUP```also causes same problem. :P – baris.aydinoz Nov 17 '18 at 07:55
I also run into this problem but in my case reserved word was `offset`. Renaming column helped. – user1053510 Mar 14 '19 at 12:58
had same issue, in my case its the "from" field that is causing problem so I have to specify @Column(name = "from_date") to avoid conflict with reserved words. – mark ortiz Jan 12 '21 at 13:19
having a column named "value" will produce the same error, because its reserved as well – user1689987 Apr 14 '23 at 19:28

pearinthemountains · Answer 2 · 2019-04-25T13:39:42.090

9

I have the same problem while naming the fields: private String to and private String from , changed to ex. dayTo , dayFrom , and it worked.

edited Apr 25 '19 at 13:39

answered Apr 25 '19 at 10:59

pearinthemountains

115
2
11

1

Exactly my case, I had `from` and `until` dates, that broke my query. Renaming them to `fromDate` and `untilDate` solved the issue. – Tomas Lukac Feb 09 '20 at 13:22

score 4 · Answer 3 · answered Aug 12 '20 at 09:04

4

Got same issue with Order as entity. Changed table name to "orderz" and goes on.

answered Aug 12 '20 at 09:04

tarmogoyf

298
3
17

THIS! Took me one hour to figure out. – Jonathan R Feb 13 '21 at 18:26

score 2 · Answer 4 · answered Jun 10 '19 at 17:26

2

I had the same problem with Spring and H2 database for tests, My entity had the field name "interval", I renamed to "inter" and resolved the problem.

So, these errors happen due to a sql reserved names in entities.

answered Jun 10 '19 at 17:26

Caio

116
1
8

score 2 · Answer 5 · answered Jan 24 '21 at 06:12

I was facing a similar issue because of a field in my entity called "interval". The answers to this post help me identify the root cause, however changing the name of the field was not a solution for me because "interval" was perfect for my class.

The solution I used was to rename the table column by using hibernate annotation.

 @Column(name = "FREQUENCY_INTERVAL")
 private int interval;

This helped me keep the variable name as 'interval' while mapping to a column name that is acceptable by the database.

score 1 · Answer 6 · answered Feb 21 '21 at 03:42

I also had an issue with an entity field called row as it is also a keyword

I specified a Column name ("SEAT_ROW") to get around it.

@Entity
data class Seat (
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    val id :            Long,
    @Column(name="SEAT_ROW")
    val row :           Char,
    val num :           Int,
    val price :         BigDecimal,
    val description :   String
) {
    override fun toString(): String = "Seat $row-$num $$price ($description)"
}

H2 Compatibility and Keywords Table

score 1 · Answer 7 · answered May 06 '21 at 18:58

1

I was using order as table name in h2. Changed to something like _order and I get it!

As you can see here, order is a reserved word by h2.

answered May 06 '21 at 18:58

Brayan Farias

11
1

score 0 · Answer 8 · answered Oct 13 '17 at 20:20

0

try to put the ; before the " and test ?

answered Oct 13 '17 at 20:20

user641887

1,506
3
32
50

The query is generated by Hibernate not me – Nicolas Sagala Beaucage Oct 13 '17 at 20:21

Guram Kankava · Answer 9 · 2020-11-10T17:00:27.983

0

in my case problem cause was incorrect syntax in insert statement problem :

insert into 'table name missing' (column names...) values(values...);

after adding table name

fix:

insert into 'table name specified' (column names...) values(values...);

some times trivial mistakes are hard to spot :)

edited Nov 10 '20 at 17:00

answered Nov 10 '20 at 16:38

Guram Kankava

51
1
4

score 0 · Answer 10 · answered Mar 30 '23 at 05:37

For your script will be compatible with reserved words, it would be: "ORDER", see here:

CREATE TABLE EMPLOYEE_FUNCTIONS ( EMPLOYEE_EMPLOYEEID VARCHAR(255) NOT NULL, ACTIVE BOOLEAN NOT NULL, DEPARTMENTNUMBER INTEGER NOT NULL, DESCRIPTION VARCHAR(255), "ORDER" INTEGER NOT NULL );

using the tip reserved words by albertocavalcante user.

score 0 · Answer 11 · answered May 18 '23 at 18:26

0

Double check all your column names in your entity. Be sure you are not using reserved keywords, and if you are, add an alias using:

@Column(name = "<YOUR NOT RESERVED KEYWORD COLUMN NAME>")

To the Entity attribute you are suspecting may be a reserved keyword, i.e.

@Column(name = "_user")
private String user

answered May 18 '23 at 18:26

Eduardo Toural

59
5

Thanks for the answer but, how can I do if the column comes from a Oracle DB, so I can't change the name? I use H2 only for local testing purpose, and connect to Oracle DB in production env. – MSA Sep 01 '23 at 11:59
In my case I use: jdbc:h2:mem:db;DB_CLOSE_DELAY=-1;MODE=Oracle;NON_KEYWORDS=VALUE – MSA Sep 01 '23 at 13:42

score -1 · Answer 12 · answered Nov 10 '20 at 16:53

-1

I had the somewhat same problem but instead mine was missing a semi-colon.

answered Nov 10 '20 at 16:53

5adiljarotazz

15
2

1

This seem more like a comment than an answer. Can you provide an example with some code? – John Mercier Nov 10 '20 at 17:20

hibernate h2 embeddable list expected "identifier"

12 Answers12

Linked