Running a .sql script using MySQL with JDBC

Supercharged script execution with ScriptRunner

The JDBC ScriptRunner class, available free under the Apache license and on GitHub, is your multi-tool Swiss army knife for running .sql scripts. It's an all-in-one solution for managing multitude SQL statements, handling potential errors, and customizing transaction boundaries. Tight control over auto-commit behavior? ScriptRunner has got your back.

Practical implementations and examples with ScriptRunner

Fundamentals of ScriptRunner

Think of the JDBC ScriptRunner as the executor of multiple SQL statements with added options to the mix for transaction control and a boosted support for error handling.

try (Connection conn = DriverManager.getConnection("jdbc:mysql://host/db", "user", "pass")) {
    ScriptRunner runner = new ScriptRunner(conn);
    runner.setAutoCommit(true); // Autofuel for our train.
    runner.setStopOnError(true); // Brakes on if there's a cow on the track.
    Reader reader = new BufferedReader(new FileReader("script.sql"));
    runner.runScript(reader); // Full speed ahead!
} catch (IOException | SQLException e) {
    e.printStackTrace(); // In case of gremlins.
}


ScriptRunner can be found on GitHub as either a standalone Java class or as part of Apache's larger iBatis project.

Use Spring Framework for your spring cleaning

Harness the powers of ResourceDatabasePopulator from the Spring Framework to run scripts by using its mystical populate method. Attach your scripts to the ResourceDatabasePopulator and pass it down the connection:

ResourceDatabasePopulator rdp = new ResourceDatabasePopulator();
rdp.addScript(new ClassPathResource("script.sql")); // Attachment complete.
try (Connection conn = dataSource.getConnection()) {
    rdp.populate(conn); // Unleash the script.
} catch (SQLException e) {
    e.printStackTrace(); // Just in case our SQL has spring fever. 
}


Don’t abandon Spring’s JdbcTestUtils - it's a trusty companion in running SQL scripts:

JdbcTestUtils.executeSqlScript(new JdbcTemplate(dataSource), new ClassPathResource("script.sql"), false); // Easy peasy lemon squeezy.


Robust Error Handling: Prevention is better than cure

When dealing with raw JDBC or Spring implementation, always keep your guard up for SQLExceptions. Wrap your methods inside protective try-catch blocks to handle exceptions and return to a stable state if needed.

Less is More: Code efficiency with StreamReader and Scanner

Hunting for an effective way of executing .sql but want to minimize dependencies? StreamReader and Scanner to the rescue:

// Grab the megaphone... err, I mean file.
InputStream is = new FileInputStream("script.sql");
try (Scanner scanner = new Scanner(is).useDelimiter(";")) {
    try (Connection conn = DriverManager.getConnection("jdbc:mysql://host/db", "user", "pass");
         Statement stmt = conn.createStatement()) {     
        while(scanner.hasNext()) {
            String sqlStmt = scanner.next() + ";";
            if (!sqlStmt.trim().isEmpty()) {
                stmt.executeUpdate(sqlStmt); // Execute! No, not the prisoner.
            }
        }
    } 
} catch (FileNotFoundException | SQLException e) {
    e.printStackTrace(); // Whoops, a sly fox let loose!
}


This way, you get maximum control with minimum dependencies–perfect for streamlined and efficient codebases!