Unable to get spring boot to automatically create database schema
JavaMysqlSpringHibernateSpring BootJava Problem Overview
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?
Java Solutions
Solution 1 - Java
There are several possible causes:
-
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 -
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
- Your
application.properties
must be insrc/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.
Solution 2 - Java
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.
Solution 3 - Java
@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
Solution 4 - Java
You just add createDatabaseIfNotExist=true
like this
spring.datasource.url=jdbc:mysql://localhost:3306/test?createDatabaseIfNotExist=true&amp;useUnicode=true&amp;characterEncoding=utf-8&amp;autoReconnect=true
to your application.properties file
Solution 5 - Java
Using the following two settings does work.
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=create
Solution 6 - Java
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.
Solution 7 - Java
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'
Solution 8 - Java
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
Solution 9 - Java
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.
Solution 10 - Java
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.
Solution 11 - Java
Abderrahmane response is correct: add ?createDatabaseIfNotExist=true
in the url property.
It seems that ddl-auto
won't do anything.
Solution 12 - Java
This is what I did after reading all of the answers above.
- Add
spring.jpa.hibernate.ddl-auto=update
with other simple properties to application.properties - run
- 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.
- Copy that SQL code and paste it separately into your DBMS to create the table.
- After that, run the app again.
Solution 13 - Java
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
Solution 14 - Java
Just add createDatabaseIfNotExist=true parameter in spring datasource url
Example: spring.datasource.url= jdbc:mysql://localhost:3306/test?createDatabaseIfNotExist=true
Solution 15 - Java
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.
Solution 16 - Java
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);
}
}
Solution 17 - Java
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.
Solution 18 - Java
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.
Solution 19 - Java
Simple we are adding semicolon after spring.jpa.hibernate.ddl-auto = create;
which is wrong spring.jpa.hibernate.ddl-auto = create
enough
Solution 20 - Java
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
Solution 21 - Java
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
Solution 22 - Java
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
Solution 23 - Java
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
Solution 24 - Java
In my case I had to rename the table with name user
. I renamed it for example users
and it worked.
Solution 25 - Java
i haved the same problem, but i add
spring.jpa.hibernate.ddl-auto = create
and everthing it is worked now
Solution 26 - Java
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
Solution 27 - Java
I had same problem and solved it with only this add:
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect
Solution 28 - Java
Just add
spring.jpa.databaseplatform=org.hibernate.dialect.PostgreSQLDialect
at the end. This will solve your issue. Only this was missing