[java] Unable to get spring boot to automatically create database schema

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?

Using the following two settings does work.

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

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

Just add createDatabaseIfNotExist=true parameter in spring datasource url

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

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.

@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

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

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

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'

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.

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);


    }
}

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

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

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

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

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

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.

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.

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

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.

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.

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.

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

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

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

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

Just add

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

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