Unable to get spring boot to automatically create database schema

Question

I'm unable to get spring boot to automatically load my database schema when I start it up.

Here is my application.properties:

spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=test
spring.datasource.password=
spring.datasource.driverClassName = com.mysql.jdbc.Driver

spring.jpa.database = MYSQL

spring.jpa.show-sql = true

spring.jpa.hibernate.ddl-auto = create
spring.jpa.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
spring.jpa.hibernate.naming_strategy = org.hibernate.cfg.ImprovedNamingStrategy

Here is my Application.java:

@EnableAutoConfiguration
@ComponentScan
public class Application {
    public static void main(final String[] args){
        SpringApplication.run(Application.class, args);
    }
}

Here is a sample entity:

@Entity
@Table(name = "survey")
public class Survey implements Serializable {

    private Long _id;

    private String _name;

    private List<Question> _questions;

    /**
     * @return survey's id.
     */
    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(name = "survey_id", unique = true, nullable = false)
    public Long getId() {
        return _id;
    }

    /**
     * @return the survey name.
     */
    @Column(name = "name")
    public String getName() {
        return _name;
    }


    /**
     * @return a list of survey questions.
     */
    @OneToMany(mappedBy = "survey")
    @OrderBy("id")
    public List<Question> getQuestions() {
        return _questions;
    }

    /**
     * @param id the id to set to.
     */
    public void setId(Long id) {
        _id = id;
    }

    /**
     * @param name the name for the question.
     */
    public void setName(final String name) {
        _name = name;
    }

    /**
     * @param questions list of questions to set.
     */
    public void setQuestions(List<Question> questions) {
        _questions = questions;
    }
}

Any ideas what I'm doing wrong?

Answer 1

There are several possible causes:

1. Your entity classes are in the same or in a sub-package relative one where you have you class with @EnableAutoConfiguration. If not then your spring app does not see them and hence will not create anything in db

2. Check your config, it seems that you are using some hibernate specific options, try to replace them with:

   spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
   spring.jpa.hibernate.ddl-auto=update
   spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
   spring.datasource.url=jdbc:mysql://localhost:3306/test
   spring.datasource.username=test
   spring.datasource.password=
   

**note that the manual loading of the driver class is unnecessary because it's automatically registered, so don't bother yourself with it

3. Your application.properties must be in src/main/resources folder.

If you did not specify dialect correctly it might try to default to bundled together with boot in-memory database and (as it was with me) I could see that it tries to connect to local HSQL (see console output) instance and fail at updating the schema.

Answer 2

Did you try running it with:

spring.jpa.generate-ddl=true

and then

spring.jpa.hibernate.ddl-auto = create

By default the DDL execution (or validation) is deferred until the ApplicationContext has started. There is also a spring.jpa.generate-ddl flag, but it is not used if Hibernate autoconfig is active because the ddl-auto settings are more fine-grained.

see spring-boot-features

Answer 3

@SpringBootApplication
@EnableConfigurationProperties
@EntityScan(basePackages = {"com.project.ppaa.model"})  // scan JPA entities
public class Application {

  private static ConfigurableApplicationContext applicationContext;

  public static void main(String[] args) {
    Application.applicationContext = SpringApplication.run(Application.class, args);
  }
}

it should work automatically but if it does not, you can enter the base package

@EntityScan(basePackages = {"com.project.ppaa.model"})  // scan JPA entities manually

Answer 4

Using the following two settings does work.

spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=create

Answer 5

If your entity class isn't in the same package as your main class, you can use @EntityScan annotation in the main class, specifying the Entity you want to save or package too. Like your model package.

About:

spring.jpa.hibernate.ddl-auto = create

You can use the option update. It won't erase any data, and will create tables in the same way.

Answer 6

You just add createDatabaseIfNotExist=true like this

spring.datasource.url=jdbc:mysql://localhost:3306/test?createDatabaseIfNotExist=true&useUnicode=true&characterEncoding=utf-8&autoReconnect=true

to your application.properties file

Answer 7

I solved my case with this solution. Did just insert a new parameter createDatabaseIfNotExist=true on a spring.datasource.url property on an application.properties file, like this:

spring.datasource.url=jdbc:mysql://localhost:3306/minhasenha?autoReconnect=true&useSSL=false&createDatabaseIfNotExist=true

I have the src/main/resources/Schema.sql with DDL to create the database schema. And I did use flyaway to create and maintain the tables.

I founded this solution here: original answer

Answer 8

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=update

MySQL5Dialect is did trick, previously I was using 'MySQLDialect'

Answer 9

This is what I did after reading all of the answers above.

1. Add spring.jpa.hibernate.ddl-auto=update with other simple properties to application.properties
2. run
3. In the console, you can see the error. At one place in the error, you can find the SQL code generated by this software to create your entity table.
4. Copy that SQL code and paste it separately into your DBMS to create the table.
5. After that, run the app again.

Answer 10

You need to provide configurations considering your Spring Boot Version and the version of libraries it downloads based on the same.

My Setup: Spring Boot 1.5.x (1.5.10 in my case) downloads Hibernate v5.x

Use below only if your Spring Boot setup has downloaded Hibernate v4.

spring.jpa.hibernate.naming_strategy=org.hibernate.cfg.ImprovedNamingStrategy

Hibernate 5 doesn't support above.

If your Spring Boot Setup has downloaded Hibernate v5.x, then prefer below definition:

spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

IMPORTANT: In your Spring Boot application development, you should prefer to use annotation: @SpringBootApplication which has been super-annotated with: @SpringBootConfiguration and @EnableAutoConfiguration

NOW If your entity classes are in different package than the package in which your Main Class resides, Spring Boot won't scan those packages.

Thus you need to explicitly define Annotation: @EntityScan(basePackages = { "com.springboot.entities" })
This annotation scans JPA based annotated entity classes (and others like MongoDB, Cassandra etc)

NOTE: "com.springboot.entities" is the custom package name.

Following is the way I had defined Hibernate and JPA based properties at application.properties to create Tables:-

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3333/development?useSSL=true spring.datasource.username=admin
spring.datasource.password=

spring.jpa.open-in-view=false
spring.jpa.hibernate.ddl-auto=create
spring.jpa.generate-ddl=true
spring.jpa.hibernate.use-new-id-generator-mappings=true
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
spring.jpa.hibernate.naming.strategy=org.hibernate.cfg.ImprovedNamingStrategy
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
spring.jpa.properties.hibernate.format_sql=true

I am able to create tables using my above mentioned configuration.

Refer it and change your code wherever applicable.

Answer 11

Abderrahmane response is correct: add ?createDatabaseIfNotExist=true in the url property. It seems that ddl-auto won't do anything.

Answer 12

Sadly for me none of the answer given above worked because I later discovered that the issue came from my pom file. I used spring boot starter project and I added another kind of spring jpa which did not work. Initially I had this,

    <dependency>
        <groupId>org.springframework.data</groupId>
        <artifactId>spring-data-jpa</artifactId>
    </dependency> 

I replaced it with this:

   <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
   </dependency> 

Take note of the spring-boot-starter-data-jpa. Hopefully this may help someone. Check your pom file and ensure your dependencies match.

Answer 13

In my case the tables were not getting created automatically even though I was using JPArepository. After adding the below property in my springboot app application.properties file the tables are now getting created automatically. spring.jpa.hibernate.ddl-auto=update

Answer 14

I also have the same problem. Turned out I have the @PropertySource annotation set on the main Application class to read a different base properties file, so the normal "application.properties" is not used anymore.

Answer 15

I had the same problem before. My problem was the Entity relationship I was trying to establish by using a "List". I knew it was the cause because the program ran fine without the list variable. In your case, I think the problem is:

private List<Question> _questions;

I am assuming you already have a class named Question. So, try having:

@OneToMany
private Question _questions;

But the thing is, in your method, you are going to handle it so it return a list. I used Spring Data JPA with CrudRepository. So, if you decide to use it, yours may look like this:

public List<Question> findById( Long _id );

There are more changes you will have to do, but these are pretty easy and straightforward. Refer to this Java Brains video to have a better grasp and see what else needs to be modified.

Answer 16

Just add createDatabaseIfNotExist=true parameter in spring datasource url

Example: spring.datasource.url= jdbc:mysql://localhost:3306/test?createDatabaseIfNotExist=true

Answer 17

I ran into a similar problem. I'm using spring boot 2.x and I missed to add Postgres dependency at spring initializer. I added the dependency manually

<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>

and here is what I was getting- INFO org.hibernate.dialect.Dialect - HHH000400: Using dialect: org.hibernate.dialect.PostgreSQLDialect instead of

**INFO  org.hibernate.dialect.Dialect - HHH000400: Using 
dialect:org.hibernate.dialect.PostgreSQL10Dialect**

This connected me to the DB

It's not so weird because Springboot does version dependency by itself and reduces the development work. On the flip side, if Springboot chooses incorrect dependency, it wastes a lot many hours.

Answer 18

Use this Sample code

application.properties
# DataSource settings: set here your own configurations for the database 
# connection. In this example we have "dojsb" as database name and 
# "root" as username and password.
spring.datasource.url =jdbc:postgresql://localhost:5432/usman
spring.datasource.username = postgres
spring.datasource.password = 12345

# Keep the connection alive if idle for a long time (needed in production)
spring.datasource.testWhileIdle = true
spring.datasource.validationQuery = SELECT 1

# Show or not log for each sql query
spring.jpa.show-sql = true

# Hibernate ddl auto (create, create-drop, update)
spring.jpa.hibernate.ddl-auto = create

# Naming strategy
spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy

# Use spring.jpa.properties.* for Hibernate native properties (the prefix is
# stripped before adding them to the entity manager)

# The SQL dialect makes Hibernate generate better SQL for the chosen database
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect

server.port = 8963



Entity Class:



import java.sql.Timestamp;
import java.util.UUID;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

import org.hibernate.annotations.Type;


@Entity
@Table(name = "QUEUERECORDS")
public class QueuesRecords {
    @Id
    private UUID id;

    @Column(name="payload", nullable = true)
    @Type(type="text")
    private String payload;


    @Column(name="status", nullable = true)
    @Type(type="text")
    private String status;

    private Timestamp starttime;

    private Timestamp endtime;

    @Column(name="queueid",nullable= true)
    @Type(type="text")
    private String queueid;

    public UUID getId() {
        return id;
    }

    public void setId(UUID id) {
        this.id = id;
    }

    public String getPayload() {
        return payload;
    }

    public void setPayload(String payload) {
        this.payload = payload;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    public Timestamp getStarttime() {
        return starttime;
    }

    public void setStarttime(Timestamp starttime) {
        this.starttime = starttime;
    }

    public Timestamp getEndtime() {
        return endtime;
    }

    public void setEndtime(Timestamp endtime) {
        this.endtime = endtime;
    }

    public String getQueueid() {
        return queueid;
    }

    public void setQueueid(String queueid) {
        this.queueid = queueid;
    }



}



Main class



import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;


@SpringBootApplication
public class Test{

    public static void main(String[] args) {

        SpringApplication.run(Test.class, args);


    }
}

Answer 19

Simple we are adding semicolon after
spring.jpa.hibernate.ddl-auto = create;
which is wrong
spring.jpa.hibernate.ddl-auto = create
enough

Answer 20

if your DB is MySQL:

spring.jpa.hibernate.ddl-auto=update
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/your_database
spring.datasource.username=root
spring.datasource.password=root

if your DB is PostgreSQL:

spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true
spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:postgresql://localhost:3306/your_database
spring.datasource.username=root
spring.datasource.password=root

Answer 21

The configurations below worked for me:

spring.jpa.properties.javax.persistence.schema-generation.database.action=create
spring.jpa.properties.javax.persistence.schema-generation.create-database-schemas=true
spring.jpa.properties.javax.persistence.schema-generation.create-source=metadata
spring.jpa.properties.javax.persistence.schema-generation.drop-source=metadata
spring.jpa.properties.javax.persistence.schema-generation.connection=jdbc:mysql://localhost:3306/your_database

Answer 22

If you had this problem on Spring Boot, double check your package names which should be exactly like:

com.example.YOURPROJECTNAME - consists main application class
com.example.YOURPROJECTNAME.entity - consists entities

Answer 23

to connect to mysql with springboot as well as creating table automatically into database: spring.datasource.url=jdbc:mysql://localhost:3306/solace spring.datasource.username=root spring.datasource.password=root spring.jpa.generate-ddl=true spring.jpa.hibernate.ddl-auto=update

Answer 24

In my case I had to rename the table with name user. I renamed it for example users and it worked.

Answer 25

i haved the same problem, but i add

spring.jpa.hibernate.ddl-auto = create

and everthing it is worked now

Answer 26

this one helped me

i assume you have INNODB engine:

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL8Dialect

spring.jpa.properties.hibernate.dialect.storage_engine=innodb

Answer 27

I had same problem and solved it with only this add:

spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect

Answer 28

Just add

spring.jpa.databaseplatform=org.hibernate.dialect.PostgreSQLDialect  

at the end. This will solve your issue. Only this was missing