SQL Schema syntax Error chapters 3-4
aasmc opened this issue · 1 comments
When trying to run tests in chapters 3 and 4, the Application context can't be loaded.
This is the exception that I get.
Caused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'personServiceImpl' defined in file [/IdeaProjects/pivotal-certified-pro-spring-dev-exam-02/chapter04/aop/build/classes/java/main/com/apress/cems/aop/service/PersonServiceImpl.class]: Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'jdbcPersonRepo' defined in com.apress.cems.aop.test.TestDbConfig: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [com.apress.cems.repos.PersonRepo]: Factory method 'jdbcPersonRepo' threw exception; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'jdbcTemplate' defined in com.apress.cems.aop.test.TestDbConfig: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.jdbc.core.JdbcTemplate]: Factory method 'jdbcTemplate' threw exception; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in com.apress.cems.aop.test.TestDbConfig: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method 'dataSource' threw exception; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #2 of class path resource [db/schema.sql]: CREATE TABLE PERSON ( ID BIGINT IDENTITY PRIMARY KEY , USERNAME VARCHAR2(50) NOT NULL , FIRSTNAME VARCHAR2(50) , LASTNAME VARCHAR2(50) , PASSWORD VARCHAR2(50) NOT NULL , HIRINGDATE TIMESTAMP , VERSION INT , CREATED_AT TIMESTAMP NOT NULL , MODIFIED_AT TIMESTAMP NOT NULL , UNIQUE(USERNAME) ); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "CREATE TABLE PERSON ( ID BIGINT IDENTITY[*] PRIMARY KEY , USERNAME VARCHAR2(50) NOT NULL , FIRSTNAME VARCHAR2(50) , LASTNAME VARCHAR2(50) , PASSWORD VARCHAR2(50) NOT NULL , HIRINGDATE TIMESTAMP , VERSION INT , CREATED_AT TIMESTAMP NOT NULL , MODIFIED_AT TIMESTAMP NOT NULL , UNIQUE(USERNAME) )"; expected "ARRAY, INVISIBLE, VISIBLE, NOT, NULL, AS, DEFAULT, GENERATED, ON, NOT, NULL, AUTO_INCREMENT, DEFAULT, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, COMMENT, PRIMARY, UNIQUE, NOT, NULL, CHECK, REFERENCES, AUTO_INCREMENT, ., )"; SQL statement: CREATE TABLE PERSON ( ID BIGINT IDENTITY PRIMARY KEY , USERNAME VARCHAR2(50) NOT NULL , FIRSTNAME VARCHAR2(50) , LASTNAME VARCHAR2(50) , PASSWORD VARCHAR2(50) NOT NULL , HIRINGDATE TIMESTAMP , VERSION INT , CREATED_AT TIMESTAMP NOT NULL , MODIFIED_AT TIMESTAMP NOT NULL , UNIQUE(USERNAME) ) [42001-206] at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:798)
I got the same error in chapter 05.
It's a syntax error in H2 INDENTITY.
Just change the id definition in schema.sql
from:
ID BIGINT IDENTITY PRIMARY KEYto
ID BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY