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?

Daisey

What exception/ error does it throw? – Burdensome

No exceptions, it starts up and then runs perfectly fine until it attempts to interact with the db, then it throws an exception about there being no tables. No relevant warnings in the log either. – Daisey

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.

Leblanc

changing the dialect to org.hibernate.dialect.MySQL5InnoDBDialect did the trick. Thanks for the help! – Daisey

the number 1 works for me. What can i do, if i don't want to have my model in the same project with my main class. I added componentscan with the model package, but i didn't help me. – Sadism

@O.Badr, valid comment. I most likely pasted lines from a number of config files I had at a time. dialect and driver should match target database. – Leblanc

@borys86, so in your solution we should use org.hibernate.dialect.MySQL5InnoDBDialect instead, as the question was about MySQL! – Callus

@Callus this is correct org.hibernate.dialect.MySQL5InnoDBDialect is the correct property value here. I attempted to edit the answer but it was rejected by the moderators. – Daisey

how the F do we import @EnableAutoConfiguration – Sixtyfourmo

#2 worked for me, but spring.datasource.driverClassName=com.mysql.jdbc.Driver is not needed and gives a warning: Loading class com.mysql.jdbc.Driver'. This is deprecated. The new driver class is com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary. – Dalliance

if you have custom database @Configuration file, probably because you need to support multiple schemas or dbs, the prop file update will probably won't work since you might have explicitly set your jpa Properties in your LocalContainerEntityManagerFactoryBean. Also if you are using hibernate specific properties hibernate.hbm2ddl.auto will be the one taking effect. – Motorist

Worked when I changed dialect org.hibernate.dialect.MySQLDialect -> org.hibernate.dialect.MySQL8Dialect – Geisel

I had this: spring.jpa.hibernate.ddl-auto=update spring.datasource.url=jdbc:mysql://localhost:3306/littleNotes spring.datasource.username=root spring.datasource.password=****** spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect – Bumblebee

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

Ossian

Yeah, running it with those properties didn't work either. It's weird it doesn't even show the properties value in the jconsole: spring.jpa.CONFIGURATION_PROPERTIES={prefix=spring.jpa, properties={databasePlatform=null, database=MYSQL, generateDdl=false, showSql=false}} – Daisey

Just a long shot could it be that the user running the queries doesn't have permissions to create the tables? Are you getting any errors? – Ossian

The user is the same user that created the schema :/. No errors until I hit the Rest Controller which hits the database. – Daisey

This one worked for me <property name="hibernate.hbm2ddl.auto">create</property> – Postdiluvian

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

Jacquetta

It works for me, spring boot version:1.5.9.RELEASE. However, I find the root cause is I should put ApplicationConfig at the parent package of my entity package. So, it could automatically scan the entities in hierarchy. – Gabor

this worked for me. Thanks alot. Tooks me hours to fix this thing. The thing was, there was no error being thrown or whatsoever even just an info. Nothing at all, I've been blindly trying to search every keyword to this issue. However I still don't know why it wont automatically scan my entities. :/ – Convector

this helped me when my dao and repository , entity classes were in different maven module – Commingle

Answer 4

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

Bernetta

This is what I have been searching for a long time. Thanks, it works – Rhoea

Answer 5

Using the following two settings does work.

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

Sneeze

this belongs in application.properties file? – Sixtyfourmo

Yes you add these in applications.properties. Alternatively you can configure the properties in your main SpringBootApplication class. – Sneeze

Answer 6

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

MySQL5Dialect did trick, previously I was using 'MySQLDialect'

Linger

adding ``` spring.jpa.generate-ddl=true ``` did the trick. Only using ``` spring.jpa.hibernate.ddl-auto=update ```. Default seams to be false. – Averroism

In my case, removing spring.jpa.properties.hibernate.dialect or change its value to org.hibernate.dialect.MySQL8Dialect both worked. – Hydrostatic

Answer 7

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.

Bimbo

Answer 8

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

Lawmaker

Answer 9

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.

Venditti

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.

Cermet

Answer 11

I solved it by adding

spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.defer-datasource-initialization=true

Rotow

Answer 12

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

Saari

The question isn’t about creating a database, it’s about Spring JPA not creating tables in the database. The database might have been created but not seeding the tables – Rondo

Answer 13

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.

Erhard

Answer 14

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

Lazulite

Answer 15

Just add createDatabaseIfNotExist=true parameter in spring datasource url

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

Empirical

Answer 16

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.

Rideout

Answer 17

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


    }
}

Serrano

Answer 18

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.

Horseflesh

Answer 19

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

Deledda

Answer 20

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.

Frecklefaced

Answer 21

With Mysql-8.x use : spring.jpa.database-platform=org.hibernate.dialect.MySQL8Dialect instead MySQL5Dialect

Boor

Answer 22

In my case, it was caused by using a deprecated library. I found this in console:

Encountered deprecated setting [javax.persistence], use [jakarta.persistence] instead.

Changing imports from javax.persistence to jakarta.persistence resolved this issue.

Intentional

Answer 23

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

Brashy

Answer 24

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

Amelita

Answer 25

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

Apperception

Can you give some references which say this is a requirement? That will add more value to your answer. – Dietrich

Answer 26

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

Sacramentalism

Please describe in more detail what you are doing to achive this result and how you know it is correct :) – Nugent

Answer 27

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

Oily

Answer 28

2023 update Please use the database url for JDBC without any extra parameters

spring.datasource.url=jdbc:mysql://localhost:3306/yourdatabase

Pattani

Answer 29

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

Punctuate

Answer 30

i haved the same problem, but i add

spring.jpa.hibernate.ddl-auto = create

and everthing it is worked now

Handler

Answer 31

Add @EnableAutoConfiguration annotation this will surely work.

Fecundity

Answer 32

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

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

Ridgway

Postgres won't help with the MySQL database. – Jarboe

Answer 33

Just add

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

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

Tzong

Postgres won't help with the MySQL database. – Jarboe