When we are developing Spring Boot applications it is a very common need to create tables and load into its data. Spring Boot provides an easy way to load initial SQL queries to insert registries in our database. Here we are going to understand how to initialize data with SQL files that Spring can load when it is starting.
Spring can load two files schema.sql and data.sql. These two files have to be in the resources folder.
The schema.sql file
We know that Spring can create tables using the entities class.
Please take a look at this article https://gustavopeiretti.com/spring-boot-database-connection-config/
But If we want to create tables using SQL sentences we can do it using the file schema.sql
Assume you have a User Entity
@Entity
public class User {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
private Long id;
private String name;
private String surname;
private LocalDate birthDate;
public User() {
}
// getters.. setters
}
For this entity, we need a table user with these attributes: id, name, surname, birth_date
So, ¿what do we have to do to create this table on our own?
We have to create a resources/schema.sql with the columns, type of data, and other definitions you need.
File resources/schema.sql
create table if not exists user
(
id bigint not null primary key,
birth_date date not null,
name varchar(255) not null,
surname varchar(255) not null
);
The data.sql file
Spring Boot uses the data.sql file to run SQL queries when starting the application.
For example, we could have these queries in our resources/data.sql file to insert registries and populate the table.
INSERT INTO user (id, birth_date, name, surname)
VALUES (1, '1960-06-10', 'Paul', 'Hewson');
INSERT INTO user (id, birth_date, name, surname)
VALUES (2, '1960-03-13', 'Adam', 'Clayton');
INSERT INTO user (id, birth_date, name, surname)
VALUES (3, '1961-08-08', 'David', 'Evans');
Disable auto creation of tables
Be careful to add this property spring.jpa.hibernate.ddl-auto=none. Spring won’t use User.java class to create the table with this property with the value of none .
You can understand more about this property by visiting https://gustavopeiretti.com/spring-boot-database-connection-config/
With this property, the User.java entity class will not be used to create the tables. Our responsibility will be to create them manually or with the initialization using the SQL files.
The property indicates that we don’t want to use entity class to automatically create tables.
spring.jpa.hibernate.ddl-auto=none
Database initialization works with embedded databases like H2.
So, we need to add this property too, if we are using it for other non-embedded databases.
Enable SQL data source initialization
spring.datasource.initialization-mode=always
Starting our Spring Boot application
ok. Now, we are going to start our application to see the console.
Add this property to see data source logs with details.
logging.level.org.springframework.jdbc.datasource.init=DEBUG
As we begin our application, we are able to see this:
Conclusion:
Spring Boot provides an easy way to create schemas and populate tables. We can perform this data source creation and initialization with two files, schema, and data.
Check the source code of this example here