How to Execute SQL File with Java using File and IO Streams?
Last Updated :
28 Apr, 2025
In many cases, we often find the need to execute SQL commands on a database using JDBC to load raw data. While there are command-line or GUI interfaces provided by the database vendor, sometimes we may need to manage the database command execution using external software. In this article, we will learn how to execute an SQL file containing thousands of comments in just a few seconds, without relying on any external library or dependency. This process requires only a minimum knowledge of IO streams and JDBC connectivity.
Understanding the Concepts
Before we dive into the article, let's familiarize ourselves with the necessary concepts:
- Class name: The name of the JDBC driver class.
- Connection URL: The URL to connect to the database.
- Username and Password: Credentials for the database connectivity.
- SQL file: The file containing SQL commands without any syntax errors.
- Database connector jar file: In order to use JDBC, you need to include the appropriate database connector jar file in your project. The JDBC driver is a software component that allows Java applications to interact with the database.
Creating the SQLExecutor Class
To execute SQL commands efficiently, we'll create a class named "SQLExecutor" responsible for connecting to JDBC and executing the SQL file. The class will have three instance properties: URL, username, and password. Upon object initialization, the Driver class will be loaded, and any errors during this process will result in the constructor throws a "ClassNotFoundException.".
Java
import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.*;
public class SQLExecutor {
private final String url;
private final String userName;
private final String password;
public SQLExecutor(String className, String url, String userName, String password)
throws ClassNotFoundException {
Class.forName(className);
this.url = url;
this.userName = userName;
this.password = password;
}
}
And this class contains the following methods.
1. Getting a Connection:
We'll implement a utility method, "getConnection," that returns a new database connection using the provided URL, username, and password.
Java
private Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, userName, password);
}
2. Printing Metadata:
We'll create another method, "printMetaData," to display the metadata about the database connection, such as the database name, database version, driver name, and version.
Java
private void printMetaData(Connection connection)
throws SQLException
{
DatabaseMetaData metaData = connection.getMetaData();
String format = "\nDatabase metadata\n"
+ "Database name : %s\n"
+ "Database version : %s\n"
+ "Database driver name : %s\n"
+ "Database driver version : %s\n\n";
System.out.printf(format,
metaData.getDatabaseProductName(),
metaData.getDatabaseProductVersion(),
metaData.getDriverName(),
metaData.getDriverVersion());
}
3. Executing the SQL File:
Now, let's move on to the "executeFile" method. This method accepts the file path as a parameter and executes the SQL commands present in the file. It creates a buffered reader for the file, establishes a new database connection, and gets a statement from the connection. it uses the try with resources block to close the resources automatically.
Java
public void executeFile(String path)
{
try (FileReader reader = new FileReader(path);
// Wrap the FileReader in a BufferedReader for
// efficient reading.
BufferedReader bufferedReader
= new BufferedReader(reader);
// Establish a connection to the database.
Connection connection = getConnection();
// Create a statement object to execute SQL
// commands.
Statement statement
= connection.createStatement();) {
printMetaData(connection);
System.out.println("Executing commands at : "
+ path);
StringBuilder builder = new StringBuilder();
String line;
int lineNumber = 0;
int count = 0;
// Read lines from the SQL file until the end of the
// file is reached.
while ((line = bufferedReader.readLine()) != null) {
lineNumber += 1;
line = line.trim();
// Skip empty lines and single-line comments.
if (line.isEmpty() || line.startsWith("--"))
continue;
builder.append(line);
// If the line ends with a semicolon, it
// indicates the end of an SQL command.
if (line.endsWith(";"))
try {
// Execute the SQL command
statement.execute(builder.toString());
// Print a success message along with
// the first 15 characters of the
// executed command.
System.out.println(
++count
+ " Command successfully executed : "
+ builder.substring(
0,
Math.min(builder.length(), 15))
+ "...");
builder.setLength(0);
}
catch (SQLException e) {
// If an SQLException occurs during
// execution, print an error message and
// stop further execution.
System.err.println(
"At line " + lineNumber + " : "
+ e.getMessage() + "\n");
return;
}
}
}
catch (Exception e) {
e.printStackTrace();
}
}
Using the SQLExecutor Class
To utilize the SQLExecutor class, we'll create a main method that takes input from the user and initiates the execution process. The main method provided above allows users to interactively enter the required input, including the class name, connection string, username, password, and file path. It then proceeds to execute the SQL commands from the specified file.
Java
public static void main(String[] args)
{
Scanner scanner = new Scanner(System.in);
System.out.println("*** Welcome to SQl file executer ***\n");
System.out.print("Enter Class Name : ");
String className = scanner.nextLine();
System.out.print("Enter Connection string : ");
String url = scanner.nextLine();
System.out.print("Enter username : ");
String user = scanner.nextLine();
System.out.print("Enter password : ");
String password = scanner.nextLine();
try {
SQLExecutor executor = new SQLExecutor(
className, url, user, password);
System.out.print("Enter file path : ");
executor.executeFile(scanner.nextLine());
scanner.close();
}
catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
The SQLExecutor class can be utilized independently as a standalone tool or included in a project. Let's describe the use cases for both scenarios:
Standalone Use Case
When used independently as a standalone tool, the SQLExecutor class can be compiled against the JDBC driver's jar file. This allows to execute SQL commands without the need for an entire project setup.
JDK version - 18 or above
java -cp path/to/jar SQLExecuter.java
Inclusion in a Project:
In this use case, the SQLExecutor class is included as part of a larger Java project. we can leverage the class's functionality when executing SQL commands related to that specific project.
Conclusion
By utilizing the power of Java's IO streams and JDBC connectivity, we can now efficiently execute a bunch of SQL commands in just seconds. This approach proves to be very helpful for tasks like creating tables, inserting values, and executing some predefined definitions.
However, it's essential to keep in mind any syntax differences between databases, such as date and time value formats or varying support for certain databases. Happy coding!
Similar Reads
Reading and Writing Properties File in Java The property file is a file we use in the Java Programming language to keep the configuration parameters. These files we called Resource Bundle because it is a bundle of resources that we are going to use in the application. What are the configuration parameters? Configuration parameters are the par
4 min read
How to add Image to MySql database using Servlet and JDBC Structured Query Language or SQL is a standard Database language which is used to create, maintain and retrieve the data from relational databases like MySQL, Oracle, SQL Server, PostGre, etc. In this article, we will understand how to add an image to the MYSQL database using servlet. MYSQL is a rel
6 min read
How to Call Stored Functions and Stored Procedures using JDBC? JDBC or Java Database Connectivity is a Java API to connect and execute the query with the database. It is a specification from Sun microsystems that provides a standard abstraction(API or Protocol) for java applications to communicate with various databases. It provides the language with java datab
5 min read
How to Insert Records to a Table using JDBC Connection? Before inserting contents in a table we need to connect our java application to our database. Java has its own API which JDBC API which uses JDBC drivers for database connections. Before JDBC, ODBC API was used but it was written in C which means it was platform-dependent. JDBC API provides the appl
4 min read
Inserting Single and Multiple Records in MySQL using Java Java Database Connectivity is an API that helps us to connect a Java program to any SQL Database Instance. This connection gives power to developers to programmatically insert, delete, or update data from the database. JDBC contains in-built methods to run queries and updates on the database. In thi
6 min read
How to Use Callable Statement in Java to Call Stored Procedure? The CallableStatement of JDBC API is used to call a stored procedure. A Callable statement can have output parameters, input parameters, or both. The prepareCall() method of connection interface will be used to create CallableStatement object. Following are the steps to use Callable Statement in Jav
2 min read