在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 type utils库.
• 该库必须已存在.没有小型宠物项目的东西.
• (编辑15/5/15)必须具有从类路径执行SQL文件的能力.

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

单线会很棒.像这样:

    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.

推荐答案

我刚刚尝试在

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屋！