如何在 Java 中运行 .sql 脚本（从文件）并使用 Spring 返回 ResultSet？

How to run a .sql script (from file) in Java and return a ResultSet using Spring?

提问by Alex Burdusel

How to run a .sql script (from file) in Java and return a ResultSetusing Spring?

如何在 Java 中运行 .sql 脚本（从文件）并ResultSet使用 Spring返回一个？

I have a program that runs SQLqueries on a database that return ResultSetwhich I later process and use the data in my classes. I am currently using JDBCwith the scripts inside the Java program.

我有一个程序SQL在数据库上运行查询，该查询返回ResultSet我稍后处理并在我的类中使用数据。我目前正在使用JDBCJava 程序中的脚本。

StringBuilder query = new StringBuilder("some script on multiple lines");
PreparedStatement statement = connection.prepareStatement(query.toString());
ResultSet resultSet = statement.executeQuery();

I want to move the SQL queries outside of the Java program to .sql files, but I want to keep all the program logic from the executeQuerystatements on. This means I want to have the queries return a ResultSet.

我想将 Java 程序之外的 SQL 查询移动到 .sql 文件，但我想保留executeQuery语句中的所有程序逻辑。这意味着我想让查询返回一个 ResultSet。

I looked to several methods like using a ScriptRunner, using Spring JdbcTestUtils.executeSqlScriptor reading the .sql file using a BufferReaderand then passing the string to my statement. The ScriptRunnerand the Spring JdbcTestUtils.executeSqlScriptseem to not return a ResultSet, or I couldn't find the proper implementation. I want to stay away of the BufferReadermethod since it will require text parsing and a lot of exceptions to handle.

我查看了几种方法，例如使用 a ScriptRunner、使用 SpringJdbcTestUtils.executeSqlScript或使用 a读取 .sql 文件BufferReader，然后将字符串传递给我的语句。在ScriptRunner和SpringJdbcTestUtils.executeSqlScript似乎不返回ResultSet，或者我找不到正确执行。我想远离该BufferReader方法，因为它需要文本解析和许多异常处理。

ScriptRunner scriptRunner = new ScriptRunner(connection, true, true);
scriptRunner.runScript(new FileReader("script.sql"));

The runScriptmethod returns void. Same does the Springimplementation:

该runScript方法返回void。同样的情形的Spring实现：

MysqlDataSource ds = new MysqlDataSource();
ds.setServerName("host");
ds.setUser("user");
ds.setPassword("password");

JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);

Resource resource = new ClassPathResource("script.sql");
JdbcTestUtils.executeSqlScript(jdbcTemplate, resource, true);

I looked thorough the Springapi but couldn't find something similar to what I want. Is there a way to load a script from file and then run it so it returns a ResultSetusing Spring? I would prefer using Springsince it is actively mantained.

我仔细查看了Springapi，但找不到与我想要的类似的东西。有没有办法从文件中加载脚本然后运行它以便它返回ResultSetusing Spring？我更喜欢使用，Spring因为它是积极维护的。

采纳答案by Alex Burdusel

I found a way to do it using Spring:

我找到了一种使用 Spring 来做到这一点的方法：

MysqlDataSource ds = new MysqlDataSource();
ds.setServerName("host");
ds.setUser("user");
ds.setPassword("password");

JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);

BufferedReader in = new BufferedReader(new FileReader("script.sql"));
LineNumberReader fileReader = new LineNumberReader(in);
String query = JdbcTestUtils.readScript(fileReader);

Now we will use the jdbcTemplate.queryto query the database using the .sql script we read. The ResultSetrequired will be passed as parameter to the extractDataof ResultSetExtractorimplementation or to the mapRowof the RowMapperimplementation. So the processing of the ResultSetwill be done in the extractDataor mapRowimplementation and we will return the Collection/Object we need

现在我们将使用jdbcTemplate.query我们读取的 .sql 脚本来查询数据库。该ResultSet要求将作为参数传递给传递extractData的ResultSetExtractor实施或对mapRow中的RowMapper执行情况。所以处理ResultSet将在extractDataormapRow实现中完成，我们将返回我们需要的 Collection/Object

List<YourClass> result = jdbcTemplate.query(query, new RowMapper<YourClass>() {
            @Override
            public YourClass mapRow(ResultSet rs, int i) throws SQLException {
                // processing of the ResultSet
                return result;
            }
        });

or

或者

YourClass object = jdbcTemplate.query(query, new ResultSetExtractor<YourClass>() {
            @Override
            public YourClass extractData(ResultSet rs) throws SQLException, DataAccessException {
                // processing of the ResultSet
                return result;
            }
        });

Of course, using the last implementation you can return any object you want, even a collection of objects.

当然，使用最后一个实现，您可以返回任何您想要的对象，甚至是对象的集合。

回答by Evgeni Dimitrov

I would use property file to store sql queries.

我会使用属性文件来存储 sql 查询。

For example:

例如：

person-save=insert into person values....
person-get-all=select * from person....

With that you can read the statements from the file

有了它，您可以从文件中读取语句

In the context:

在上下文中：

<util:properties id="sqlProps" location="classpath:sqlProps.properties" />

In the DAO:

在 DAO 中：

@Autowired
@Qualifier("sqlProps")
private Properties sqlProps;

...

...

String query = sqlProps.getProperty("person-get-all");
PreparedStatement statement = connection.prepareStatement(query);
ResultSet resultSet = statement.executeQuery();

If you want to execute all the statements in a file, you have to implement that yourself. Just loop over the properties and execute them one by one. You will need some flag to determinate if its select or update/delete statement. Loop over properties:

如果要执行文件中的所有语句，则必须自己实现。只需遍历属性并一一执行它们。您将需要一些标志来确定其选择还是更新/删除语句。循环属性：

Enumeration e = props.propertyNames();

    while (e.hasMoreElements()) {
      String key = (String) e.nextElement();
      String query= props.getProperty(key);
    }