在 JDBC 连接上运行 SQL 脚本，最小方法 [英] Run SQL script on JDBC connection, minimal approach

问题描述

长话短说:我想在 HSQLDB 数据库上运行 SQL 脚本.

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

我想遵循简约的方法，这意味着:

I want to follow a minimalistic approach, which means:

• 绝对不需要手动解析 SQL
• 除通用实用程序外，没有其他依赖项.我在这里进行区分是因为，例如，我拒绝引入 Ibatis 或 Hibernate 这些更大范围的框架，但我会接受 apache commons 或 guava 类型的 utils 库.
• 库必须在 MAVEN 上可用.没有小型的宠物项目.
• (编辑 12/5/15)必须能够从类路径执行 SQL 文件.

给你一些背景:

    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"));

我确实找到了 org.hsqldb.persist.ScriptRunner 但它将数据库对象作为参数，我似乎无法弄清楚如何获取实例.另外，我不喜欢恢复数据库状态"的描述，所以这是否意味着我的数据库将首先被清除?这绝对不是我想要的.

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.

推荐答案

我刚刚尝试使用 SqlTool 对我有用.我使用的 Maven 依赖是

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>

我想执行的 SQL 脚本文件是C:/Users/Public/test/hsqldbCommands.sql":

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');

我的 Java 测试代码是

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"));
            }
        }
    }

}

生产

Before:
1 - Hello world!

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

2018-08-26

如果您想将 SQL 脚本文件作为资源捆绑到项目中，请参阅其他答案中的示例.

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

另请注意，此方法不限于 HSQLDB 数据库.它也可以用于其他数据库(例如 MySQL、SQL Server).

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).

这篇关于在 JDBC 连接上运行 SQL 脚本，最小方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！