GianMS GianMS - 3 years ago 124
MySQL Question

How to rollback all changes in MySQL database with Spring Boot

I have a Spring MVC Application developed with Spring Boot. This is application is just for learning purposes, by the way.

By default, the app launches and uses a MySQL database. For unit and integration testing, I use an in-memory H2 database and it works perfectly.

Now, for UAT, it's required to have another MySQL database. So, in this new database, I've inserted enough data for the application to work properly, but each time the application runs under UAT profile, it's populated with data that I have to clean manually or restore from a sql file.

I have to 2 profiles. First, it's the production profile:

spring.datasource.driver-class-name = com.mysql.jdbc.Driver
spring.datasource.url = jdbc:mysql://localhost/dbprod
spring.datasource.username = root
spring.datasource.password = mysql

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

spring.thymeleaf.mode=LEGACYHTML5
spring.thymeleaf.cache=false


Second, it's the uat profile:

spring.datasource.driver-class-name = com.mysql.jdbc.Driver
spring.datasource.url = jdbc:mysql://localhost/dbuat
spring.datasource.username = root
spring.datasource.password = mysql

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

spring.thymeleaf.mode=LEGACYHTML5
spring.thymeleaf.cache=false


My question is then how to tell Spring that under UAT profile it should discard all changes that were made (inserts, updates and deletes) . It would be nice if this could be done in the application-uat.properties file

Answer Source

If you use JPA, you can set the 'create-drop' option in the application-uat.properties file for destory the schema after the session:

spring.jpa.hibernate.ddl-auto=create-drop 

Be careful!

If you NOT use JPA, you can use a shutdown hook at the exit of the application to clean the database (active uat profile)

https://docs.spring.io/spring-boot/docs/current-SNAPSHOT/reference/htmlsingle/#boot-features-application-exit

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download