CODEWITHSUNDEEP
javaspringspring-bootspring-jdbc
escudero380
escudero380

Reputation: 586

How to reload Initial Data in running Spring Boot applicaiton?

I'm using spring-boot-starter-data-jdbc dependency in my web-application. Spring Boot can automatically create the schema and initialize it from the root classpath locations schema.sql and data.sql on application startup, but I'm curious is there any way to re-initialize the database using very same sql-scripts after the application has already started?

I need this feature for demo mode, so that users can reset the database to initial state after they played around enough with the tables. This is how I would like my reset-controller to look like:

@Controller
@RequestMapping("/reset")
@Profile("demo")
public class ResetController {

    @GetMapping
    public String resetTables(HttpSession session) {
        // some code re-initializing the database 
        // form schema.sql and data.sql goes here
        session.invalidate();
        return "redirect:/home";
    }
}

I know, I can always use JdbcTemplate and its methods to drop, create and re-populate each table manually, following the logic of sql statements defined in schema.sql and data.sql, but that would be a little tedious. Maybe, Spring has some out-of-the-box method for executing those scripts against the database which would help to reload the tables with initial demonstration data?

Updated:

This is one of the possible solutions based on the Flyway migration, as suggested by Charles B in the accepted answer:

  1. Add flyway-core dependency to the project.
  2. Rename schema.sql and data.sql into V1__schema.sql and V2__data.sql following Flyway naming requirements and put them under /resources/db/migration directory.
  3. Set spring.flyway.baseline-on-migrate property to true.
  4. Then, the reset-controller described above could be rewritten, as simple as this:
@Controller
@RequestMapping("/reset")
@Profile("demo")
public class ResetController {

    private final Flyway flyway;

    public ResetController(Flyway flyway) {
        this.flyway = flyway;
    }

    @GetMapping
    public String resetTables(HttpSession session) {
        flyway.clean();
        flyway.migrate();
        session.invalidate();
        return "redirect:/home";
    }
    
}

Additionally, for different migration scenarios, it's possible to set spring.flyway.locations property referencing different SQL files - separately for each profile, or even disable Flyway migration for certain profiles by setting spring.flyway.enabled property to false.

Upvotes: 0

Views: 1586

Answers (3)

Ajeet Maurya
Ajeet Maurya

Reputation: 662

Spring Boot makes it really easy to manage our database changes in an easy way. We can use the data.sql and schema.sql files in Spring.

If we run our application, Spring Boot will create an empty table for us, but won't populate it with anything.

More details here -> https://www.baeldung.com/spring-boot-data-sql-and-schema-sql

Upvotes: 2

George
George

Reputation: 3030

You can try using Spring Boot Actuator endpoints to restart your entire application. That should reinitialize your ORM and reset the DB initial state.

Dependency:

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-actuator</artifactId>
</dependency>

Configuration:

management:
  endpoints:
    web:
      exposure:
        include: restart
  endpoint:
    restart:
      enabled: true

And you should be able to hit the /actuator/restart endpoint.

http://localhost:8080/actuator/restart

Upvotes: 0

Charles B
Charles B

Reputation: 97

If you migrate to something like Flyway, you could build a controller that calls flyway's clean method like referenced here. Would be easy to migrate and maintain long term.

Upvotes: 1

Related Questions