How to Execute SQL File with Java using File and IO Streams?

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:

1. Class name: The name of the JDBC driver class.
2. Connection URL: The URL to connect to the database.
3. Username and Password: Credentials for the database connectivity.
4. SQL file: The file containing SQL commands without any syntax errors.
5. 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!