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

Flyblow

Answer 1

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!

Flyblow

ORDER is a reserved keyword for H2DB - h2database.com/html/advanced.html#compatibility – Harmaning

Yes I understood that. – Flyblow

@Enumerated(EnumType.ORDINAL) private RaceGroup group GROUPalso causes same problem. :P – Mapel

I also run into this problem but in my case reserved word was offset. Renaming column helped. – Uniformitarian

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. – Kuibyshev

having a column named "value" will produce the same error, because its reserved as well – Leatherworker

Answer 2

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

Lundy

Exactly my case, I had from and until dates, that broke my query. Renaming them to fromDate and untilDate solved the issue. – Follett

Answer 3

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

Barfuss

THIS! Took me one hour to figure out. – Meshwork

Answer 4

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.

Halloo

Answer 5

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.

Thenna

Answer 6

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

Refresh

Answer 7

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.

Recommend

Answer 8

try to put the ; before the " and test ?

Jemimah

The query is generated by Hibernate not me – Flyblow

Answer 9

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

Weakfish

Answer 10

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.

Unfledged

Answer 11

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

Pasteurism

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. – Chalmer

In my case I use: jdbc:h2:mem:db;DB_CLOSE_DELAY=-1;MODE=Oracle;NON_KEYWORDS=VALUE – Chalmer

I think order attribute from your Function class is an Oracle Reserved Word, double check that here in the Oracle official docs. <docs.oracle.com/cd/A97630_01/appdev.920/a42525/apb.htm> If that is the case, consider an alias for that order column, or refactor to a different name. i.e. employee_order – Pasteurism

Answer 12

There is no need to change the column name like the other answers suggest! If you are using Spring JPA, you can just put this in your test/resources/applicaiton.properties:

spring.jpa.properties.hibernate.globally_quoted_identifiers=true

As the name suggests, this will put column names in quotes and avoid problems with reserved keywords.

Underfoot

Answer 13

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

Histopathology

This seem more like a comment than an answer. Can you provide an example with some code? – Haynie