In Java, we can interact with databases using various libraries and frameworks, but sometimes we may need to run SQL script files to perform tasks like schema creation, data population, or database migrations. Before we can execute SQL script files, we need to choose a Java database library that suits our needs. Some popular options include JDBC, Hibernate, JPA (Java Persistence API), Spring JDBC, or MyBatis. For this article, we’ll explore how to run a SQL script file in Java using MyBatis, JDBC, and Spring JDBC.
1. Setting Up the Database Environment
In this article, we will be using MySQL which is one of the most popular open-source relational database management systems, known for its reliability, performance, and scalability. To download MYSQL, go to the MySQL official website (https://www.mysql.com/downloads/) and select the MySQL Community Server edition that matches your operating system.
On macOS, we can use the Homebrew package manager to install MySQL with the following commands:
brew update brew install mysql
After installation, we can start the MySQL server with:
brew services start mysql
Once MySQL is installed, we need to open a terminal and use the following command to log in to MySQL using the root user:
mysql -u root -p
The above command will prompt us to enter the root password. Once we’ve logged in successfully, we are all set to begin using MySQL.
1.1 What is a SQL (Database) Script File
A SQL (database) script file is a plain text file containing a sequence of structured queries and commands written in SQL (Structured Query Language). It is a document comprising numerous SQL queries distinctly separated. Typically, SQL script files have the file extension .sql.
These scripts are used to define, manipulate, or interact with a database. The primary purposes of database script files include:
- Database Schema Definition: Creating tables, defining relationships, specifying constraints, and establishing the overall structure of a database.
- Data Manipulation: Inserting, updating, or deleting data within the database. This can include seeding initial data or modifying existing records.
- Database Migration: Managing changes to the database schema over time, especially when transitioning between different versions of an application.
1.2 Prepare the SQL Script File
Next, create an SQL script file named students-script.sql with the SQL statements we want to execute. In this article, students-script.sql file contains the following content:
CREATE TABLE IF NOT EXISTS studentDatabase.students (
id INT AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(50) NOT NULL,
lastname VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL
);
INSERT INTO students (firstname, lastname, email) VALUES ('Thomas', 'P', 'charlesp@yahoo.com');
INSERT INTO students (firstname, lastname, email) VALUES ('Charles', 'D', 'charlesd@gmail.com');
INSERT INTO students (firstname, lastname, email) VALUES ('James', 'B', 'jamesb@yahoo.com');
INSERT INTO students (firstname, lastname, email) VALUES ('Jonathan', 'S', 'jonathans@gmail.com');
INSERT INTO students (firstname, lastname, email) VALUES ('John', 'Joe', 'john@example.com');
INSERT INTO students (firstname, lastname, email) VALUES ('Jane', 'Doe', 'jane@example.com');
The above file will create a table named students in a MySQL database called studentDatabase and populate it with two records.
2. Running a SQL Script File using MyBatis ScriptRunner
2.1 What is MyBatis
MyBatis is a popular Java-based persistence framework that provides a simple and efficient way for Java applications to interact with relational databases, and one of its powerful features is the ScriptRunner. MyBatis allows developers to map Java objects to SQL statements using XML or annotations. It supports a variety of database operations, including simple queries, complex joins, and stored procedures.
2.2 What is MyBatis ScriptRunner
MyBatis ScriptRunner simplifies the process of running multiple SQL statements and scripts by providing an API for reading SQL files and carrying out their execution on a preconfigured database connection.
By leveraging MyBatis ScriptRunner, we have the capability to automate tasks such as setting up database schemas, populating our database with initial data, and executing various database maintenance operations, eliminating the necessity to manually execute individual SQL statements one after another.
2.3 Set Up MyBatis in Your Java Project
To execute an SQL script using MyBatis ScriptRunner, we need to have MyBatis set up in our Java project, a pre-configured database connection, and the SQL script file that we want to run. To integrate MyBatis into our Java Maven project, add the MyBatis and the MySQL JDBC driver dependencies in our pom.xml file like this:
<dependencies>
<!-- Add MyBatis Dependencies -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.6</version>
</dependency>
<!-- Add MySQL JDBC Driver -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.26</version> <!-- Use the version that matches your MySQL server -->
</dependency>
</dependencies>
2.4 Configure MyBatis and ScriptRunner
Now, in our Java code, we need to configure MyBatis and the ScriptRunner to execute the SQL script file. We also need to create a new directory src/main/resources and place the students-script.sql file there. The updated code should look like the code sample shown below:
public class ExecuteSQLScriptWithMyBatis {
public static void main(String[] args) throws Exception {
//Register the Driver
Class.forName("com.mysql.cj.jdbc.Driver");
// Establish a MySQL database connection
String jdbcUrl = "jdbc:mysql://localhost:3306/studentDatabase";
String username = "your_username";
String password = "your_password";
Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
System.out.println("Connection Established......");
// Create a ScriptRunner instance
ScriptRunner scriptRunner = new ScriptRunner(connection);
// Set the delimiter to define the end of a statement (default is ';')
scriptRunner.setDelimiter(";");
// Read and execute the SQL script
Reader scriptReader = Resources.getResourceAsReader("students-script.sql");
scriptRunner.runScript(scriptReader);
// Close the resources
scriptReader.close();
connection.close();
}
}
In the code above, We start by establishing a MySQL database connection by providing the appropriate database URL, username, and password. Note that you need to replace "jdbc:mysql://localhost:3306/studentdatabase", "your_username", and "your_password" with your actual database URL, username, and password.
Next, we create an ScriptRunner instance and then set the delimiter (usually ;). Next, we create an Reader object named scriptReader to read the content of the students-script.sql script file. We then read and execute the SQL script using scriptRunner.runScript(scriptReader).
Finally, we close the resources to release the database connection. If we compile and run our Java application, It will execute the students-script.sql file against our configured MySQL studentDatabase , and the output on Netbeans IDE and MySQL workbench is shown in Fig 1 and Fig 2 respectively.
3. Running SQL Statements in Batches from a Script File using JDBC
JDBC is a Java-based API that allows Java applications to interact with relational databases providing an interface for connecting to databases and executing SQL queries.
When dealing with multiple operations, executing SQL statements one by one can be inefficient. JDBC provides a mechanism for executing SQL statements in batches, which can significantly improve performance.
The code listing below shows how to execute SQL statements in batches from an SQL script file in a Java application:
public class ExecuteSQLScriptJDBC {
public static void main(String[] args) throws Exception {
//Register the Driver
Class.forName("com.mysql.cj.jdbc.Driver");
// Establish a MySQL database connection
String jdbcUrl = "jdbc:mysql://localhost:3306/studentDatabase";
String username = "username";
String password = "password";
try {
Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
// Define the path to the SQL script file
// Update the path as needed
String script = readSQLScript("/Users/omozegieaziegbe/Development/students-script.sql");
executeScript(connection, script);
//Close the connection
connection.close();
//Handle Exceptions
} catch (SQLException | IOException e) {
e.printStackTrace();
}
}
// Read the SQL Script File
public static String readSQLScript(String filePath) throws IOException {
StringBuilder script = new StringBuilder();
try (BufferedReader reader = new BufferedReader(new FileReader(filePath))) {
String line;
while ((line = reader.readLine()) != null) {
script.append(line).append("\n");
}
}
return script.toString();
}
//Execute the SQL Script File
public static void executeScript(Connection connection, String script) throws SQLException {
try (Statement statement = connection.createStatement()) {
String[] sqlCommands = script.split(";");
for (String sqlCommand : sqlCommands) {
if (!sqlCommand.trim().isEmpty()) {
statement.addBatch(sqlCommand);
}
}
statement.executeBatch();
}
}
}
In the above code listings, we start by establishing a connection to the database. We use the DriverManager class to create a connection object by providing the database URL, username, and password. Replace "jdbc:mysql://localhost:3306/studentdatabase", "username", and "password" with your actual database URL, username, and password.
Next, we create a method named readSQLScript which we use to read the SQL statements from a script file (students-script.sql). To accomplish this, we use the BufferedReader class to read the script file from our file system. Note that this script file contains multiple SQL statements, separated by semicolons (;). We then split the script content into individual SQL commands using the semicolon as a delimiter using the string split(";") method storing the result of this operation in an String[] array variable declared as sqlCommands.
Next, we Iterate through the array of SQL statements and add them to the batch using the addBatch method. After adding all the statements to the batch, we execute them using the executeBatch method. Finally, In our main method, we call the executeScript(connection, script) method passing the established database connection (connection) and the script (script) to execute the SQL script on the database.
It’s important to note that for this code example to run successfully, we must ensure that our script file does not contain comments or lines starting with “–“, “/*”, or any other non-alphabetic characters.
3.1 Handling Exceptions
When working with databases, it is recommended to always handle exceptions properly. In the example above, we use the catch (SQLException | IOException e) method to handle any exception of type SQLException or IOException within our try block and If any exception of type SQLException or IOException is thrown, the code inside the catch block will be executed.
3.2 Closing the Connection
We make sure to properly close the database connection once we have completed our tasks in order to free up valuable resources. In this example, the close() method is called on the Connection object in a try block for proper resource cleanup.
4. Running a SQL Script File using Spring JDBC
Spring JDBC ScriptUtils is a convenient tool for executing SQL scripts in Java applications. The ScriptUtils class provides utility methods for handling SQL scripts when used alongside JDBC.
Listed below are some of the methods provided by the ScriptUtils class to execute a script file:
executeSqlScript(Connection connection, Resource resource)executeSqlScript(Connection connection, EncodedResource resource)executeSqlScript(Connection connection, EncodedResource resource, boolean continueOnError, boolean ignoreFailedDrops, String[] commentPrefixes, String separator, String blockCommentStartDelimiter, String blockCommentEndDelimiter)executeSqlScript(Connection connection, EncodedResource resource, boolean continueOnError, boolean ignoreFailedDrops, String commentPrefix, String separator, String blockCommentStartDelimiter, String blockCommentEndDelimiter)
To execute SQL scripts using Spring JDBC ScriptUtils in Java, create a Java Maven application and add the Spring JDBC, Spring Core, and MySQL JDBC driver dependencies to the pom.xml file in the project like this:
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.3.14</version> <!-- Replace with your Spring version -->
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>5.3.14</version> <!-- Replace with your Spring version -->
</dependency>
<!-- Add MySQL JDBC Driver -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.26</version> <!-- Use the version that matches your MySQL server -->
</dependency>
</dependencies>
Next, create or copy the SQL script file students-script.sql to a directory where you want to load the file and update the Java class to execute the SQL script. Note that the directory should be accessible by the application.
Below is the updated Java class:
public class ExecuteSQLScriptSpringJDBC {
public static void main(String[] args) throws Exception {
//Database URL
String url = "jdbc:mysql://localhost:3306/studentDatabase";
//Register the Driver
Class.forName("com.mysql.cj.jdbc.Driver");
// Establish a MySQL database connection
String username = "root";
String password = "omozegie";
Connection connection = DriverManager.getConnection(url, username, password);
System.out.println("Establishing Connection.....");
// Define the path to the SQL script file
// Update the path as needed
File file = new File("/Users/omozegieaziegbe/Development/students-script.sql");
Resource resource = new FileSystemResource(file);
EncodedResource encodedresource = new EncodedResource(resource);
connection.setAutoCommit(false);
// Execute the SQL script
ScriptUtils.executeSqlScript(connection, encodedresource);
connection.setAutoCommit(true);
connection.close();
System.out.println("Database Tables Created");
}
}
In the example above, the script file is loaded using File instead of a classpath resource. Replace the paths and file names with the appropriate values in your application.
We create a Resource object named resource which is used to read the SQL script file on our filesystem and create an EncodedResource object named encodedresource by passing the resource as an argument to its constructor. In Spring Framework, EncodedResource is a utility class used for reading resources with specified character encodings such as those in an SQL script file.
Finally, we use the ScriptUtils.executeSqlScript(connection, encodedresource) method to execute the SQL script. This method accepts two parameters – Connection connection which is the JDBC connection already configured and EncodedResource resource which is the resource to load the SQL script. This method generally takes care of opening a connection and executing the script.
When we run this Java application, it will execute the SQL script students-script, creating the students table and insert the specified records in the students’ database.
5. Conclusion
In this article, We have shown various code examples on how to execute SQL script files using JDBC, MyBatis, and Spring ScriptUtils library. Running an SQL script file in a Java application provides a flexible mechanism for interacting with databases. Whether you are initializing a database, performing data migration, or creating database tables, Java’s capabilities in conjunction with database libraries like Spring JDBC, and MyBatis, or using JDBC to execute SQL statements in batches make it a straightforward process.
By employing libraries such as Spring’s ScriptUtils or MyBatis ScriptRunner, we can efficiently execute SQL script files, ensuring that our application’s database interactions are well-structured. This approach allows for better management of database schema changes, minimizes the risk of errors, and provides an organized way to handle database-related operations.
Overall, running SQL script files in Java enables us to manage our database operations seamlessly, ultimately contributing to the robustness of our applications.
6. Download the Source Code
This was an example of how to run an SQL Script File in Java.
You can download the full source code of this example here: Running a SQL Script File in Java
