JPA Entity not creating table

Question

so i'm moving from JDBC to JPA, i removed the "create table" sql statement from my schema.sql file, so now the file looks like this:

INSERT INTO MEMBER (ID, LASTNAME, FIRSTNAME, PHONENUMBER, CREATED)
VALUES(1001, 'Max', 'Mad', 0547547547, sysdate());
INSERT INTO MEMBER (ID, LASTNAME, FIRSTNAME, PHONENUMBER, CREATED)
VALUES(1002, 'Boy', 'Johnny', 0547547547, sysdate());

my entity, Member.class, is like this:

@Entity
public class Member   {


    @Id
    @GeneratedValue
    private int id;

    private String lastName;
    private String firstName;
    private int phoneNumber;
    private Date created;


    public Member(int id, String firstName, String lastName, int phoneNumber, Date created) {
        super();
        this.id = id;
        this.firstName = firstName;
        this.lastName = lastName;
        this.phoneNumber = phoneNumber;
        this.created = created;
    }

    public Member(String firstName, String lastName, int phoneNumber, Date created) {
        super();
        this.firstName = firstName;
        this.lastName = lastName;
        this.phoneNumber = phoneNumber;
        this.created = created;
    }


    public Member(){  

    }

    public long getId() {
        return id;
    }
    public void setId(int userId) {
        this.id = userId;
    }
    public String getFirstName() {
        return firstName;
    }
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }
    public String getLastName() {
        return lastName;
    }
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
    public long getPhoneNumber() {
        return phoneNumber;
    }
    public void setPhoneNumber(int phoneNumber) {
        this.phoneNumber = phoneNumber;
    }
    public Date getCreated() {
        return created;
    }
    public void setCreated(Date created) {
        this.created = created;
    }

    @Override
    public String toString() {
        return "Member [id=" + id + ", firstName=" + firstName + ", lastName=" + lastName + ", phoneNumber="
                + phoneNumber + ", created=" + created + "]";
    }


}

these are the exceptions i get:

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'inMemoryDatabaseShutdownExecutor' defined in class path resource [org/springframework/boot/devtools/autoconfigure/DevToolsDataSourceAutoConfiguration.class]: Unsatisfied dependency expressed through method 'inMemoryDatabaseShutdownExecutor' parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in class path resource [org/springframework/boot/autoconfigure/jdbc/DataSourceConfiguration$Hikari.class]: Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerInvoker': Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/C:/Users/apodga/javaworkspace2/springbootdb/target/classes/schema.sql]: INSERT INTO MEMBER (ID, LASTNAME, FIRSTNAME, PHONENUMBER, CREATED) VALUES(1001, 'Max', 'Mad', 0547547547, sysdate()); nested exception is org.h2.jdbc.JdbcSQLException: Table "MEMBER" not found; SQL statement:
INSERT INTO MEMBER (ID, LASTNAME, FIRSTNAME, PHONENUMBER, CREATED) VALUES(1001, 'Max', 'Mad', 0547547547, sysdate()) [42102-196]
    at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:723) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:458) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1249) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1098) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:545) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:502) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:312) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:310) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:200) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:304) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:200) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1083) ~[spring-context-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:858) ~[spring-context-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:549) ~[spring-context-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:122) ~[spring-boot-2.0.0.M5.jar:2.0.0.M5]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:750) [spring-boot-2.0.0.M5.jar:2.0.0.M5]
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:386) [spring-boot-2.0.0.M5.jar:2.0.0.M5]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:327) [spring-boot-2.0.0.M5.jar:2.0.0.M5]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1245) [spring-boot-2.0.0.M5.jar:2.0.0.M5]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1233) [spring-boot-2.0.0.M5.jar:2.0.0.M5]
    at com.recweb.springbootdb.SpringbootdbApplication.main(SpringbootdbApplication.java:29) [classes/:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_131]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_131]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_131]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_131]
    at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49) [spring-boot-devtools-2.0.0.M5.jar:2.0.0.M5]
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in class path resource [org/springframework/boot/autoconfigure/jdbc/DataSourceConfiguration$Hikari.class]: Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerInvoker': Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/C:/Users/apodga/javaworkspace2/springbootdb/target/classes/schema.sql]: INSERT INTO MEMBER (ID, LASTNAME, FIRSTNAME, PHONENUMBER, CREATED) VALUES(1001, 'Max', 'Mad', 0547547547, sysdate()); nested exception is org.h2.jdbc.JdbcSQLException: Table "MEMBER" not found; SQL statement:
INSERT INTO MEMBER (ID, LASTNAME, FIRSTNAME, PHONENUMBER, CREATED) VALUES(1001, 'Max', 'Mad', 0547547547, sysdate()) [42102-196]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:591) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:502) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:312) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:310) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:200) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:251) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1133) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1060) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:809) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:715) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    ... 26 common frames omitted
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerInvoker': Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/C:/Users/apodga/javaworkspace2/springbootdb/target/classes/schema.sql]: INSERT INTO MEMBER (ID, LASTNAME, FIRSTNAME, PHONENUMBER, CREATED) VALUES(1001, 'Max', 'Mad', 0547547547, sysdate()); nested exception is org.h2.jdbc.JdbcSQLException: Table "MEMBER" not found; SQL statement:
INSERT INTO MEMBER (ID, LASTNAME, FIRSTNAME, PHONENUMBER, CREATED) VALUES(1001, 'Max', 'Mad', 0547547547, sysdate()) [42102-196]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1704) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:583) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:502) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:312) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:310) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:225) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveNamedBean(DefaultListableBeanFactory.java:1011) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.getBean(DefaultListableBeanFactory.java:340) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.getBean(DefaultListableBeanFactory.java:335) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerPostProcessor.postProcessAfterInitialization(DataSourceInitializerPostProcessor.java:57) ~[spring-boot-autoconfigure-2.0.0.M5.jar:2.0.0.M5]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyBeanPostProcessorsAfterInitialization(AbstractAutowireCapableBeanFactory.java:438) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1708) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:583) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    ... 36 common frames omitted
Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/C:/Users/apodga/javaworkspace2/springbootdb/target/classes/schema.sql]: INSERT INTO MEMBER (ID, LASTNAME, FIRSTNAME, PHONENUMBER, CREATED) VALUES(1001, 'Max', 'Mad', 0547547547, sysdate()); nested exception is org.h2.jdbc.JdbcSQLException: Table "MEMBER" not found; SQL statement:
INSERT INTO MEMBER (ID, LASTNAME, FIRSTNAME, PHONENUMBER, CREATED) VALUES(1001, 'Max', 'Mad', 0547547547, sysdate()) [42102-196]
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:492) ~[spring-jdbc-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:240) ~[spring-jdbc-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:48) ~[spring-jdbc-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.runScripts(DataSourceInitializer.java:186) ~[spring-boot-autoconfigure-2.0.0.M5.jar:2.0.0.M5]
    at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.createSchema(DataSourceInitializer.java:102) ~[spring-boot-autoconfigure-2.0.0.M5.jar:2.0.0.M5]
    at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerInvoker.afterPropertiesSet(DataSourceInitializerInvoker.java:64) ~[spring-boot-autoconfigure-2.0.0.M5.jar:2.0.0.M5]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1763) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1700) ~[spring-beans-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    ... 49 common frames omitted
Caused by: org.h2.jdbc.JdbcSQLException: Table "MEMBER" not found; SQL statement:
INSERT INTO MEMBER (ID, LASTNAME, FIRSTNAME, PHONENUMBER, CREATED) VALUES(1001, 'Max', 'Mad', 0547547547, sysdate()) [42102-196]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.message.DbException.get(DbException.java:179) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.message.DbException.get(DbException.java:155) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.readTableOrView(Parser.java:5552) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.readTableOrView(Parser.java:5529) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.parseInsert(Parser.java:1062) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.parsePrepared(Parser.java:417) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.parse(Parser.java:321) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.parse(Parser.java:293) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.prepareCommand(Parser.java:258) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.engine.Session.prepareLocal(Session.java:578) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.engine.Session.prepareCommand(Session.java:519) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1204) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:176) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:164) ~[h2-1.4.196.jar:1.4.196]
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95) ~[HikariCP-2.7.2.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-2.7.2.jar:na]
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:471) ~[spring-jdbc-5.0.0.RELEASE.jar:5.0.0.RELEASE]
    ... 56 common frames omitted

as you can see, it says table MEMBER not found. if i bring back the "create table MEMBER" sql statement back to the schema.sql file, there is no exception & the table is getting created, but the "insert" statements are not doing anything. i just get an empty table. but anyway, i dont want to create a table through a sql statement, i want that the entity annotation will do it.

I am using Spring Boot on Eclipse.

Thanks!

Answer 1

The data.sql gets initialized first in spring boot. So, it tries to insert the data before creating the table by @Entity... To solve this issue, add this code to your application.properties. spring.jpa.defer-datasource-initialization = true

Answer 2

One of the reason could be, if your entity classes are in a completely different package than your main application package.

This could prevent your entities from being scanned. As there would be no scanned entities, no tables would be inserted to your database, meaning you wouldn't even see any obvious exceptions or error messages. Then the missing tables could cause the exception you pasted here.

If that is the case, you shall use @EntityScan annotation in your main application. For example

@EntityScan("your.package.with.annotated.entities")
public class SomeClassInOtherPackage{
    // ...
}

Answer 3

• Add this hibernate property in your application.properties file:

  spring.jpa.hibernate.ddl-auto=create-drop
  

  Or

  spring.jpa.hibernate.ddl-auto=create
  

From documentation :

create-drop

Drop the schema and recreate it on SessionFactory startup. Additionally, drop the schema on SessionFactory shutdown.

create

Database dropping will be generated followed by database creation.

You may think twice before using hibernate.ddl-auto in production.

• Rename schema.sql to import.sql or to data.sql, so spring boot will initialize the database (with data, by running your sql file) after its creation.

Answer 4

The thing that worked for me was adding this line to application.properties:

spring.datasource.initialize=false