How to fix org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement expected "identifier"

Question

I am using H2 in-memory database for my springboot application. Where I have enabled hibernate.ddl-auto. I am getting below exception while hibernate is creating a schema

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException:
Syntax error in SQL statement "CREATE TABLE PRODUCT_OFFSET_INFO (ID BIGINT NOT NULL, MODIFIED_TIMESTAMP TIMESTAMP, OFFSET[*] BIGINT, TOPIC_NAME VARCHAR(255), PRIMARY KEY (ID))"; expected "identifier"; SQL statement:
create table PRODUCT_OFFSET_INFO (ID bigint not null, MODIFIED_TIMESTAMP timestamp, OFFSET bigint, TOPIC_NAME varchar(255), primary key (ID)) [42001-200]
'''

Below is the Entity class:

@Entity
@Table(name="PRODUCT_OFFSET_INFO")
public class ProductOffsetInfo implements Serializable
{
   private static final long serialVersionUID = -2147468513335906679L;
    
   @Id
   @Column(name="ID")
   private Long ProductId;
    
   @Column(name="TOPIC_NAME")
   private String topic_name;
    
   @Column(name="OFFSET")
   private Long offset;
    
   @Column(name = "MODIFIED_TIMESTAMP")
   private Date modified;
    
   public Long getProductId() {
       return ProductId;
   }
   public void setProductId(Long ProductId) {
       this.ProductId = ProductId;
   }
    
   public String getTopic_name() {
       return topic_name;
   }
   public void setTopic_name(String topic_name) {
       this.topic_name = topic_name;
   }
    
   public Long getOffset() {
       return offset;
   }
   public void setOffset(Long offset) {
       this.offset = offset;
   }
    
   public Date getModified() {
       return modified;
   }
    
   public void setModified(Date modified) {
       this.modified = modified;
   }
}

Below is the database config file:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
    entityManagerFactoryRef = "productEntityManagerFactory",
    transactionManagerRef = "productTransactionManager",
    basePackages = {"com.product.repository.product"}
)
@ComponentScan({"com.product.repository.product.impl"})
@EntityScan({"com.product.commons.entities.product.models","com.product.models.product"})
public class ProductDbConfig {

        @Autowired
        private JpaProperties jpaProperties;
        @Primary
        @Bean("productHikariConfig")
        @ConfigurationProperties(prefix = "spring.datasource")
        public HikariConfig hikariConfig() {
            return new HikariConfig();
        }
    
        @Primary
        @Bean(name = "productDataSource")
        @DependsOn("productHikariConfig")
        public DataSource dataSource(@Qualifier("productHikariConfig") HikariConfig hikariConfig) {
            return new HikariDataSource(hikariConfig);
        }
    
        @Primary
        @Bean(name = "productEntityManagerFactory")
        @PersistenceContext(unitName = "product")
        public LocalContainerEntityManagerFactoryBean entityManagerFactory(
                EntityManagerFactoryBuilder builder, @Qualifier("productDataSource") DataSource datasource) {
            return builder
                    .dataSource(datasource).properties(jpaProperties.getProperties())
                    .packages("com.product.commons.entities.product.models","com.product.models.product")
                    .persistenceUnit("product")
                    .build();
        }
    
        @Primary
        @Bean(name = "productTransactionManager")
        public PlatformTransactionManager transactionManager(
                @Qualifier("productEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
            return new JpaTransactionManager(entityManagerFactory);
        }
    }

Below is the application.yml content for DB

    spring:
      profiles: mock
      jpa:
        database-platform: org.hibernate.dialect.H2Dialect
        generate-ddl: true
        hibernate:
          ddl-auto: create
      datasource:
        jdbcUrl: jdbc:h2:mem:PRODUCT
        driver-class-name: org.h2.Driver
        maximumPoolSize: 10
        minimumIdle: 5
        idleTimeout: 60000
        maxLifetime: 120000
        leakDetectionThreshold: 180000
        poolName: "product"

Answer 1

Try to correct this:

@Column(name="OFFSET")
private Long offset;

to

@Column(name="`OFFSET`")
private Long offset;

As OFFSET is reserved keyword you should force Hibernate to quote an identifier in the generated SQL by enclosing the column name in backticks in the mapping document. See this section of hibernate documentation.

Answer 2

I faced this same but none of the solutions worked, but if i did the below it worked fine.

Updated the dialect from " spring.jpa.database-platform=org.hibernate.dialect.H2Dialect"

to "spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect"

Answer 3

Apart from the accepted answer, I want to highlight an alternate way.

We can add "NON_KEYWORDS=OFFSET" in the spring.datasource.url property in properties file.

This works for the reserved keywords used as column name but also in table name. Like, User table name.