Run SQL script on JDBC connection, minimal approac

问题:

Long story short: I want to run a SQL script on an HSQLDB database.

I want to follow a minimalistic approach, which means:

• Absolutely no manual parsing of SQL
• No additional dependencies except for general Utilities. I make the distinction here because, for example I refuse to pull in Ibatis or Hibernate which are larger scope frameworks, but I will accept an apache commons or guava type utils library.
• The library MUST BE AVAILABLE ON MAVEN. No small-time pet-project stuff.
• (EDIT 12/5/15) Must have the ability to execute SQL file from classpath.

To give you some context:

    try {
        connection = DriverManager.getConnection("jdbc:hsqldb:file:mydb", "sa", "");
        // Run script here
    } catch (SQLException e) {
        throw new RuntimeException("Unable to load database", e);
    }

A one-liner would be great. Something like:

    FancyUtils.runScript(connection, new File("myFile.sql"));

I did find org.hsqldb.persist.ScriptRunner but it takes a Database object as an argument and I can't seem to figure out how to get an instance. Also, I don't like the description of "Restores the state of a Database", so does that mean my database will be cleared first? That's definitely not what I want.

回答1:

I just tried using the SqlFile object in SqlTool and it worked for me. The Maven dependency I used was

<dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>sqltool</artifactId>
    <version>2.4.1</version>
</dependency>

The SQL script file I wanted to execute was "C:/Users/Public/test/hsqldbCommands.sql":

INSERT INTO table1 (id, textcol) VALUES (2, 'stuff');
INSERT INTO table1 (id, textcol) VALUES (3, 'more stuff');

and my Java test code was

package hsqldbMaven;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.hsqldb.cmdline.SqlFile;

public class HsqldbMavenMain {

    public static void main(String[] args) {
        String connUrl = "jdbc:hsqldb:file:C:/Users/Public/test/hsqldb/personal";
        String username = "SA";
        String password = "";

        try (Connection conn = DriverManager.getConnection(connUrl, username, password)) {
            // clear out previous test data
            try (Statement st = conn.createStatement()) {
                st.executeUpdate("DELETE FROM table1 WHERE ID > 1");
            }

            System.out.println("Before:");
            dumpTable(conn);

            // execute the commands in the .sql file
            SqlFile sf = new SqlFile(new File("C:/Users/Public/test/hsqldbCommands.sql"));
            sf.setConnection(conn);
            sf.execute();

            System.out.println();
            System.out.println("After:");
            dumpTable(conn);

            try (Statement st = conn.createStatement()) {
                st.execute("SHUTDOWN");
            }
        } catch (Exception e) {
            e.printStackTrace(System.err);
        }
    }

    private static void dumpTable(Connection conn) throws SQLException {
        try (
                Statement st = conn.createStatement(); 
                ResultSet rs = st.executeQuery("SELECT id, textcol FROM table1")) {
            while (rs.next()) {
                System.out.printf("%d - %s%n", rs.getInt("id"), rs.getString("textcol"));
            }
        }
    }

}

producing

Before:
1 - Hello world!

After:
1 - Hello world!
2 - stuff
3 - more stuff

Edit: 2018-08-26

If you want to bundle your SQL script file into the project as a resource then see the example in the other answer.

Note also that this approach is not restricted to HSQLDB databases. It can be used for other databases as well (e.g., MySQL, SQL Server).

回答2:

This uses the SqlTool library, but reads the script directly from the classpath by using the SqlFile class:

try(InputStream inputStream = getClass().getResourceAsStream("/script.sql")) {
    SqlFile sqlFile = new SqlFile(new InputStreamReader(inputStream), "init", System.out, "UTF-8", false, new File("."));
    sqlFile.setConnection(connection);
    sqlFile.execute();
}

回答3:

Even though iBatis was mentioned by the OP as a non-requirement, I still want to recommend MyBatis - the iBatis fork by the original creators.

The core library (org.mybatis:mybatis) requires no dependencies (all of its dependencies are optional) and while larger than HSQLDB SqlTool, at 1.7MB binary it is not horribly big for most uses and is continuously maintained (the last release, 3.5, was last month as of this writing).

You can initialize ScriptRunner with a JDBC Connection, then call runScript(new InputStreamReader(sqlinputst, Standard chartered.UTF_8)) to run whatever SQL script you can get an input steam of.