Springboot project failing to run. SQL BeanCreationException error in logs-CodePudding

Im trying to make a REST api using springboot. Normally I would use PHPMyAdmin for the database but trying H2 out for the first time.

Havnt seen these issues come up in my old projects because I make the SQL database outside the project using PHPMyAdmin.

Any help would be extremely grateful.

This is the Maven Output Maven output

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourceScriptDatabaseInitializer' defined in class path resource [org/springframework/boot/autoconfigure/sql/init/DataSourceInitializationConfiguration.class]: Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #2 of URL [file:/C:/Users/Brendan Ahern/Desktop/takehomeproject/api_java_project/api_java_project/target/classes/schema.sql]: CREATE TABLE accounts ( accountnumber int(9) NOT NULL, pin int(4) NOT NULL, balance int(11) NOT NULL, overdraft int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "CREATE TABLE ACCOUNTS ( ACCOUNTNUMBER INT(9) NOT NULL, PIN INT(4) NOT NULL, BALANCE INT(11) NOT NULL, OVERDRAFT INT(11) NOT NULL ) ENGINE=[*]INNODB DEFAULT CHARSET=LATIN1"; expected "identifier"; SQL statement:
CREATE TABLE accounts ( accountnumber int(9) NOT NULL, pin int(4) NOT NULL, balance int(11) NOT NULL, overdraft int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 [42001-200]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1804) ~[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.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:322) ~[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.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:145) ~[spring-boot-2.6.6.jar:2.6.6]
    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.example.api_java_project.ApiJavaProjectApplication.main(ApiJavaProjectApplication.java:15) ~[classes/:na]

This is my schema.sql

DROP TABLE IF EXISTS accounts;

CREATE TABLE accounts (
  accountnumber int(9) NOT NULL,
  pin int(4) NOT NULL,
  balance int(11) NOT NULL,
  overdraft int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS atm;

CREATE TABLE atm (
  note_value String NOT NULL,
  note_amount int(11) NOT NULL


) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This is my Account object class

@Entity
public class Account {

    @Id
    private int accountNumber;
    private int pin;
    private double balance;
    private double overdraft;

    public Account() {

    }

    public Account(int accountNumber, int pin, double balance, double overdraft) {
        super();
        this.accountNumber = accountNumber;
        this.pin = pin;
        this.balance = balance;
        this.overdraft = overdraft;
    }

    public Integer getAccountNumber() {
        return accountNumber;
    }
    public void setAccountNumber(Integer accountNumber) {
        this.accountNumber = accountNumber;
    }
    public int getPin() {
        return pin;
    }
    public void setPin(int pin) {
        this.pin = pin;
    }
    public Double getBalance() {
        return balance;
    }
    public void setBalance(Double balance) {
        this.balance = balance;
    }
    public Double getOverdraft() {
        return overdraft;
    }
    public void setOverdraft(Double overdraft) {
        this.overdraft = overdraft;
    }
}

CodePudding user response：

[*] mark in exceptions from H2 indicates a position of parser error:

… ) ENGINE=[*]INNODB …

You have two problems here.

You cannot use ENGINE=InnoDB etc. in H2 unless MySQL or MariaDB compatibility mode was enabled, you need to append the related flags (;MODE=MySQL and possibly others) to JDBC URL: https://h2database.com/html/features.html#compatibility You also need to upgrade H2 to a more recent version, because old unsupported H2 1.4.200 is more restrictive here and cannot parse latin1 as charset name (or you can replace it with UTF8).
There is no such data types as STRING in H2 and in the real MySQL too, you need to use some valid data type, for example, VARCHAR(100).

CodePudding user response：

The error message actually tells you the cause of your error.

expected "identifier";

You must specify a PRIMARY KEY value for the tables you want to create.

Additionally, if you do not want to assign the PRIMARY KEY value manually, do not forget to add the AUTO_INCREMENT definition for automatic assignment.