I've a spring boot application which run sql on H2 to create database table during startup. The project is in github here.
I've Entity called Movie.java
The sql that I'm running is below and on github here -
DROP TABLE IF EXISTS "movie";
CREATE TABLE "movie" AS SELECT * FROM CSVREAD('classpath:movie.csv');
The csv file that I'm inserting is here
id,title,year,rating,genre
1,The Shawshank Redemption,1994,9.3,DRAMA
2,The godfather,1972,9.2,DRAMA
3,Pulp Fiction,1994,8.9,CRIME
4,The good the bad and the ugly,1966,8.8,WESTERN
5,Inception,2010,8.8,ACTION
6,Se7en,1995,8.6,CRIME
7,City of god,2002,8.06,CRIME
8,Django unchained,2012,8.4,WESTERN
9,Oldboy,2003,8.4,ACTION
When I run the spring application I get below error -
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table movie (id integer not null, genre varchar(255), rating double not null, title varchar(255), year integer not null, primary key (id))" via JDBC Statement at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.6.7.Final.jar:5.6.7.Final] at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlString(SchemaCreatorImpl.java:458) ~[hibernate-core-5.6.7.Final.jar:5.6.7.Final] at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlStrings(SchemaCreatorImpl.java:442) ~[hibernate-core-5.6.7.Final.jar:5.6.7.Final] at org.hibernate.tool.schema.internal.SchemaCreatorImpl.createFromMetadata(SchemaCreatorImpl.java:325) ~[hibernate-core-5.6.7.Final.jar:5.6.7.Final] at org.hibernate.tool.schema.internal.SchemaCreatorImpl.performCreation(SchemaCreatorImpl.java:169) ~[hibernate-core-5.6.7.Final.jar:5.6.7.Final] at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:138) ~[hibernate-core-5.6.7.Final.jar:5.6.7.Final] at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:124) ~[hibernate-core-5.6.7.Final.jar:5.6.7.Final] at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:168) ~[hibernate-core-5.6.7.Final.jar:5.6.7.Final] at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:85) ~[hibernate-core-5.6.7.Final.jar:5.6.7.Final] at org.hibernate.internal.SessionFactoryImpl.(SessionFactoryImpl.java:335) ~[hibernate-core-5.6.7.Final.jar:5.6.7.Final] at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:471) ~[hibernate-core-5.6.7.Final.jar:5.6.7.Final] at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1498) ~[hibernate-core-5.6.7.Final.jar:5.6.7.Final] at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58) ~[spring-orm-5.3.17.jar:5.3.17] at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) ~[spring-orm-5.3.17.jar:5.3.17] at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:409) ~[spring-orm-5.3.17.jar:5.3.17] at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:396) ~[spring-orm-5.3.17.jar:5.3.17] at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341) ~[spring-orm-5.3.17.jar:5.3.17] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1863) ~[spring-beans-5.3.18.jar:5.3.18] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1800) ~[spring-beans-5.3.18.jar:5.3.18] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:620) ~[spring-beans-5.3.18.jar:5.3.18] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:542) ~[spring-beans-5.3.18.jar:5.3.18] at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335) ~[spring-beans-5.3.18.jar:5.3.18] at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.3.18.jar:5.3.18] at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333) ~[spring-beans-5.3.18.jar:5.3.18] at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.18.jar:5.3.18] at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1154) ~[spring-context-5.3.18.jar:5.3.18] at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:908) ~[spring-context-5.3.18.jar:5.3.18] at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:583) ~[spring-context-5.3.18.jar:5.3.18] at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:740) ~[spring-boot-2.6.6.jar:2.6.6] at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:415) ~[spring-boot-2.6.6.jar:2.6.6] at org.springframework.boot.SpringApplication.run(SpringApplication.java:303) ~[spring-boot-2.6.6.jar:2.6.6] at org.springframework.boot.SpringApplication.run(SpringApplication.java:1312) ~[spring-boot-2.6.6.jar:2.6.6] at org.springframework.boot.SpringApplication.run(SpringApplication.java:1301) ~[spring-boot-2.6.6.jar:2.6.6] at com.grpcflix.movie.MovieApplication.main(MovieApplication.java:9) ~[main/:na] Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "create table movie (id integer not null, genre varchar(255), rating double not null, title varchar(255), []year integer not null, primary key (id))"; expected "identifier"; SQL statement: create table movie (id integer not null, genre varchar(255), rating double not null, title varchar(255), year integer not null, primary key (id)) [42001-210] at org.h2.message.DbException.getJdbcSQLException(DbException.java:521) ~[h2-2.1.210.jar:2.1.210] at org.h2.message.DbException.getJdbcSQLException(DbException.java:496) ~[h2-2.1.210.jar:2.1.210] at org.h2.message.DbException.getSyntaxError(DbException.java:265) ~[h2-2.1.210.jar:2.1.210] at org.h2.command.Parser.readIdentifier(Parser.java:5759) ~[h2-2.1.210.jar:2.1.210] at org.h2.command.Parser.parseTableColumnDefinition(Parser.java:9355) ~[h2-2.1.210.jar:2.1.210] at org.h2.command.Parser.parseCreateTable(Parser.java:9298) ~[h2-2.1.210.jar:2.1.210] Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "create table movie (id integer not null, genre varchar(255), rating double not null, title varchar(255), []year integer not null, primary key (id))"; expected "identifier"; SQL statement:
at org.h2.command.Parser.parseCreate(Parser.java:6779) ~[h2-2.1.210.jar:2.1.210] at org.h2.command.Parser.parsePrepared(Parser.java:746) ~[h2-2.1.210.jar:2.1.210] at org.h2.command.Parser.parse(Parser.java:674) ~[h2-2.1.210.jar:2.1.210] at org.h2.command.Parser.parse(Parser.java:644) ~[h2-2.1.210.jar:2.1.210] at org.h2.command.Parser.prepareCommand(Parser.java:551) ~[h2-2.1.210.jar:2.1.210] at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:615) ~[h2-2.1.210.jar:2.1.210] at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:553) ~[h2-2.1.210.jar:2.1.210] at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1116) ~[h2-2.1.210.jar:2.1.210] at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:237) ~[h2-2.1.210.jar:2.1.210] at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:223) ~[h2-2.1.210.jar:2.1.210] at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94) ~[HikariCP-4.0.3.jar:na] at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-4.0.3.jar:na] at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54) ~[hibernate-core-5.6.7.Final.jar:5.6.7.Final] ... 33 common frames omitted
I think the error is columns are in different order than it is defined in the entity from the SQL to create table.
create table movie (id integer not null, genre varchar(255), rating double not null, title varchar(255), [*]year integer not null, primary key (id))
Any idea how to ix this error?
CodePudding user response：
The main problem is probably "movie" as table-name incl the "
So this should work
schema.sql:
DROP TABLE IF EXISTS movie;
CREATE TABLE movie AS SELECT * FROM CSVREAD('classpath:movie.csv');
here is a working basic example: example on github
Note for the example
schema.sqlfor initialization of the DB howto.data-initializationspring.jpa.hibernate.ddl-auto=noneto disable Hibernate automatic schema creation