Normally everyone would encounter a situation where in they want to execute few of the database scripts while deploying a solution. These scripts might include anything like executing a procedure, running a batch, insert script for defaulting of few values, update to existing dataset etc.,
When such situation arises, we can follow the below mentioned approach as an initial approach to handle it.
Spring by default tries to load a script file "data.sql" located in the classpath i.e., where the application.properties, log4j.properties etc., are present.
Hence try to consolidate all the scripts into one file and name it as "data.sql" and place it under "<<apacheTomcatFolder>>\webapps\<<app>>\WEB-INF\classes",
Disadvantages of this approach :
- Since it is an automated process, any failure in the execution of scripts will not be known unless we go through the log files. Hence error percentage should be minimum.
- Using the above approach, we cannot divide all the scripts into different modules and execute with different names. By default the spring will look for a file named "data.sql" unless someone overrides this setting in application.properties which is not explained in this blogpost.
- A restart is needed for every change in this file. Try to use only when the script is flawless and also can be executed during the deployment. If there is a provision to execute the scripts in database then try to use that as it will provide more control over the scripts that will be run.