How to Execute SQL File with Java using File and IO Streams?
Last Updated :
24 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 Data to Excel File in Java using Apache POI
In Java, reading an Excel file is not similar to reading a Word file because of cells in an Excel file. JDK does not provide a direct API to read data from Excel files for which we have to toggle to a third-party library that is Apache POI. Apache POI is an open-source java library designed for read
5 min read
How to Write Data from Excel File into a HashMap using Java and Apache POI?
Apache POI is an open-source java library to create and manipulate various file formats based on Microsoft Office. Using POI, one should be able to perform create, modify and display/read operations on the following file formats. For Example, Java doesnât provide built-in support for working with ex
3 min read
How to Write Data from HashMap to Excel using Java in Apache POI?
Apache POI is an open-source java library to create and manipulate various file formats based on Microsoft Office. Using POI, one should be able to perform create, modify and display/read operations on the following file formats. For Example, Java doesnât provide built-in support for working with ex
3 min read
Writing a CSV file in Java using OpenCSV
A Comma-Separated Values (CSV) file is just a normal plain-text file, store data in a column by column, and split it by a separator (e.g normally it is a comma â, â). OpenCSV is a CSV parser library for Java. OpenCSV supports all the basic CSV-type operations you are want to do. Java 7 is currently
5 min read
Java Program to Append a String in an Existing File
In Java, we can append a string in an existing file using FileWriter which has an option to open a file in append mode. Java FileWriter class is used to write character-oriented data to a file. It is a character-oriented class that is used for file handling in Java. Unlike FileOutputStream class, we
3 min read
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
Reading a CSV file in Java using OpenCSV
A Comma-Separated Values (CSV) file is just a normal plain-text file, store data in column by column, and split it by a separator (e.g normally it is a comma â, â). OpenCSV is a CSV parser library for Java. OpenCSV supports all the basic CSV-type operations you are want to do. Java 7 is currently th
7 min read
How To Export and Import a .SQL File From Command Line With Options?
Structured Query Language is a computer language that we use to interact with a relational database.SQL is a tool for organizing, managing, and retrieving archived data from a computer database. In this article , we will learn to export and import .SQL files with command line options. Export:You can
2 min read
File handling in Java using FileWriter and FileReader
Java FileWriter and FileReader classes are used to write and read data from text files (they are Character Stream classes). It is recommended not to use the FileInputStream and FileOutputStream classes if you have to read and write any textual information as these are Byte stream classes. FileWrite
4 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