Spring Boot SQL syntax error when creating table; expected "identifier"

Question

I have a class in my Java Spring Boot project:

@Entity
@Table(name = "attribute_table")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class Attribute {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;
    @Column(name = "name")
    private String name;
    @Column(name = "attribute_type")
    private AttributeType attributeType;
    @Column(name = "value")
    private byte[] value;

    @ManyToOne
    @JoinColumn(name = "asset_category_id")
    private AssetCategory assetCategory;
}

After running I get an exception:

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "create table test1234 (attribute_type tinyint check (attribute_type between 0 and 3), asset_category_id bigint, id bigint generated by default as identity, name varchar(255), [*]value varbinary(255), primary key (id))"; expected "identifier";

I found that this type of error occurs when the name of the table is the same as reserved sql keyword, but I tried few names for @Table annotation and it is clearly not the reason.

I don't know if it is important, but in my Attribute class I have value field which is byte[], because it's type depends on AttributeType enum. Also there is BaseClass categoryOf in AssetCategory which is not clearly defined, because it can be mapped with other classes that inherit from BaseClass. Here are other classes in my project:

@Entity
@Table(name = "asset_categories")
@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
public class AssetCategory {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name = "name")
    private String name;
    @Lob
    @Column(name = "description")
    private String description;

    @OneToMany(mappedBy = "assetCategory")
    private List<Attribute> attributes;

    @OneToOne(mappedBy = "assetCategory")
    private BaseClass categoryOf;

    public void addAttribute(Attribute attribute) {
        attributes.add(attribute);
    }
}

@Getter
@Setter
@Entity
public abstract class BaseClass {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    protected Long id;
    @Column(name = "name")
    protected String name;
    @OneToOne
    @JoinColumn(name = "asset_category", referencedColumnName = "id")
    protected AssetCategory assetCategory;
    @Column(name = "archived")
    protected Boolean archived;

}

public enum AttributeType {
    NUMBER,
    STRING,
    DATE,
    BOOL
}

Answer 1

VALUE is a reserved word is the SQL Standard and it is a keyword in H2, you cannot use it as unquoted identifier.

It will be better to choose some different name for your column, but you can quote this name with name = "\"VALUE\"" or you can force quotation of all identifiers by Hibernate ORM with spring.jpa.properties.hibernate.globally_quoted_identifiers=true.

In the worst case you can add ;NON_KEYWORDS=VALUE to JDBC URL (if you use H2 for unit tests you will also need to disable their automatic configuration, otherwise your custom connection URL will be ignored), but this setting doesn't cover all possible cases, for example in ALTER TABLE … ADD … UNIQUE(VALUE) definition VALUE will still have a special meaning (unique constraint on entire row).