Seeding Data in the DB

Overview

In this lab, you will:

• Create a data.sql file to seed the DB with Data
• Update application.properties to make sure that the Item table is created before seeding it

You will use project from the previous lab springboot-rest-demo.

Create data.sql

Create a file called data.sql in the src/main/resources folder (same location as application.properties)

Insert the following SQL into the file:

Click here to view the example solution

INSERT INTO item (name, price) VALUES ('Apple', 0.99);
INSERT INTO item (name, price) VALUES ('Banana', 0.59);
INSERT INTO item (name, price) VALUES ('Orange', 1.29);
INSERT INTO item (name, price) VALUES ('Grapes', 2.49);
INSERT INTO item (name, price) VALUES ('Mango', 1.99);

Update application.properties

Make sure that the application.properties file has the following content (will use a H2 database).

Click here to view the example solution

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.h2.console.enabled=true
spring.jpa.defer-datasource-initialization=true

Key Configuration Explained

The last line spring.jpa.defer-datasource-initialization=true is important for the purposes of seeding the Item table. It tells Hibernate to make sure that the table is created before calling data.sql (stops trying to seed a table that doesn't exist).

Additional Information

It is also possible to create a file called schema.sql that can be used to generate a setup using the schema(s) defined in this file.

File Structure:

src/main/resources/
├── application.properties
├── data.sql          # Contains INSERT statements for initial data
└── schema.sql        # (Optional) Contains CREATE TABLE statements

How Data Seeding Works

1. Spring Boot starts up and reads the configuration
2. JPA/Hibernate creates tables based on your Entity classes
3. Spring executes schema.sql (if present) for additional schema setup
4. Spring executes data.sql to insert initial data
5. Application is ready with pre-populated data

Testing the Data Seeding

To test that the seeding has worked use Postman to call getAllItems. You should see that several items of fruit have been returned.

Expected Response:

[
    {
        "id": 1,
        "name": "Apple",
        "price": 0.99
    },
    {
        "id": 2,
        "name": "Banana",
        "price": 0.59
    },
    {
        "id": 3,
        "name": "Orange",
        "price": 1.29
    },
    {
        "id": 4,
        "name": "Grapes",
        "price": 2.49
    },
    {
        "id": 5,
        "name": "Mango",
        "price": 1.99
    }
]

Alternative Testing Methods

1. Using H2 Console:

   • Navigate to http://localhost:8080/h2-console
   • Use JDBC URL: jdbc:h2:mem:testdb
   • Username: sa, Password: (leave empty)
   • Run: SELECT * FROM item;

2. Using curl:

   curl -X GET http://localhost:8080/items

3. Check application logs for any seeding errors during startup

Best Practices

• Keep data.sql simple with essential test/demo data only
• Use schema.sql for complex table structures or constraints
• Consider using profiles to load different data for different environments
• Don't put sensitive data in data.sql files that might be committed to version control