如何在Java中执行SQL脚本文件？

I want to execute an SQL script file in Java without reading the entire file content into a big query and executing it.

我想用Java执行一个SQL脚本文件而不将整个文件内容读入一个大查询并执行它。

Is there any other standard way?

还有其他标准方式吗?

9 个解决方案

#1

There is no portable way of doing that. You can execute a native client as an external program to do that though:

没有可移植的方法。您可以执行本机客户端作为外部程序来执行此操作:

import java.io.*;
public class CmdExec {

  public static void main(String argv[]) {
    try {
      String line;
      Process p = Runtime.getRuntime().exec
        ("psql -U username -d dbname -h serverhost -f scripfile.sql");
      BufferedReader input =
        new BufferedReader
          (new InputStreamReader(p.getInputStream()));
      while ((line = input.readLine()) != null) {
        System.out.println(line);
      }
      input.close();
    }
    catch (Exception err) {
      err.printStackTrace();
    }
  }
}

Code sample was extracted from here and modified to answer question assuming that the user wants to execute a PostgreSQL script file.

代码示例从此处提取并修改为回答问题,假设用户想要执行PostgreSQL脚本文件。

#2

There is great way of executing SQL scripts from Java without reading them yourself as long as you don't mind having a dependency on Ant. In my opinion such a dependency is very well justified in your case. Here is sample code, where SQLExec class lives in ant.jar:

只要您不介意依赖Ant,就可以自己从Java执行SQL脚本而无需自己阅读。在我看来,这种依赖在你的情况下非常合理。以下是示例代码,其中SQLExec类位于ant.jar中:

private void executeSql(String sqlFilePath) {
    final class SqlExecuter extends SQLExec {
        public SqlExecuter() {
            Project project = new Project();
            project.init();
            setProject(project);
            setTaskType("sql");
            setTaskName("sql");
        }
    }

    SqlExecuter executer = new SqlExecuter();
    executer.setSrc(new File(sqlFilePath));
    executer.setDriver(args.getDriver());
    executer.setPassword(args.getPwd());
    executer.setUserid(args.getUser());
    executer.setUrl(args.getUrl());
    executer.execute();
}

#3

No, you must read the file, split it into separate queries and then execute them individually (or using the batch API of JDBC).

不,您必须读取文件,将其拆分为单独的查询,然后单独执行(或使用JDBC的批处理API)。

One of the reasons is that every database defines their own way to separate SQL statements (some use ;, others /, some allow both or even to define your own separator).

其中一个原因是每个数据库都定义了自己分离SQL语句的方式(一些使用;其他/,一些允许两者甚至定义自己的分隔符)。

#4

You cannot do using JDBC as it does not support . Work around would be including iBatis iBATIS is a persistence framework and call the Scriptrunner constructor as shown in iBatis documentation .

您不能使用JDBC,因为它不支持。解决方法包括iBatis iBATIS是一个持久性框架,并调用Scriptrunner构造函数,如iBatis文档中所示。

Its not good to include a heavy weight persistence framework like ibatis in order to run a simple sql scripts any ways which you can do using command line

包含像ibatis这样的重量级持久性框架以便以任何方式运行简单的sql脚本都是不错的,你可以使用命令行来完成

$ mysql -u root -p db_name < test.sql

#5

Flyway library is really good for this:

Flyway图书馆非常适合这个:

    Flyway flyway = new Flyway();
    flyway.setDataSource(dbConfig.getUrl(), dbConfig.getUsername(), dbConfig.getPassword());
    flyway.setLocations("classpath:db/scripts");
    flyway.clean();
    flyway.migrate();

This scans the locations for scripts and runs them in order. Scripts can be versioned with V01__name.sql so if just the migrate is called then only those not already run will be run. Uses a table called 'schema_version' to keep track of things. But can do other things too, see the docs: flyway.

这将扫描脚本的位置并按顺序运行它们。脚本可以使用V01__name.sql进行版本控制,因此如果只调用migrate,则只运行那些尚未运行的脚本。使用名为“schema_version”的表来跟踪事物。但也可以做其他事情,请参阅文档:flyway。

The clean call isn't required, but useful to start from a clean DB. Also, be aware of the location (default is "classpath:db/migration"), there is no space after the ':', that one caught me out.

干净调用不是必需的,但从干净的DB开始很有用。另外,要注意位置(默认为“classpath:db / migration”),':'后面没有空格,那个抓住了我。

#6

Since JDBC doesn't support this option the best way to solve this question is executing command lines via the Java Program. Bellow is an example to postgresql:

由于JDBC不支持此选项,因此解决此问题的最佳方法是通过Java程序执行命令行。贝娄是postgresql的一个例子:

private void executeSqlFile() {
     try {
         Runtime rt = Runtime.getRuntime();
         String executeSqlCommand = "psql -U (user) -h (domain) -f (script_name) (dbName)";
         Process pr = rt.exec();
         int exitVal = pr.waitFor();
         System.out.println("Exited with error code " + exitVal);
      } catch (Exception e) {
        System.out.println(e.toString());
      }
}

#7

The simplest external tool that I found that is also portable is jisql - https://www.xigole.com/software/jisql/jisql.jsp . You would run it as:

我发现最简单的外部工具也是可移植的jisql - https://www.xigole.com/software/jisql/jisql.jsp。你可以运行它:

java -classpath lib/jisql.jar:\
          lib/jopt-simple-3.2.jar:\
          lib/javacsv.jar:\
           /home/scott/postgresql/postgresql-8.4-701.jdbc4.jar 
    com.xigole.util.sql.Jisql -user scott -password blah     \
    -driver postgresql                                       \
    -cstring jdbc:postgresql://localhost:5432/scott -c \;    \
    -query "select * from test;"

#8

JDBC does not support this option (although a specific DB driver may offer this). Anyway, there should not be a problem with loading all file contents into memory.

JDBC不支持此选项(尽管特定的DB驱动程序可能会提供此选项)。无论如何,将所有文件内容加载到内存中应该没有问题。

#9

Try this code:

试试这段代码:

String strProc =
         "DECLARE \n" +
         "   sys_date DATE;"+
         "" +
         "BEGIN\n" +
         "" +
         "   SELECT SYSDATE INTO sys_date FROM dual;\n" +
         "" +
         "END;\n";

try{
    DriverManager.registerDriver ( new oracle.jdbc.driver.OracleDriver () );
    Connection connection = DriverManager.getConnection ("jdbc:oracle:thin:@your_db_IP:1521:your_db_SID","user","password");  
    PreparedStatement psProcToexecute = connection.prepareStatement(strProc);
    psProcToexecute.execute();
}catch (Exception e) {
    System.out.println(e.toString());  
}

#1