How to run a .sql script (from file) in Java and return a ResultSet using Spring?
I have a program that runs SQL queries on a database that return ResultSet which I later process and use the data in my classes. I am currently using JDBC with the scripts inside the Java program.
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 executeQuery statements on. This means I want to have the queries return a ResultSet.
I looked to several methods like using a ScriptRunner, using Spring JdbcTestUtils.executeSqlScript or reading the .sql file using a BufferReader and then passing the string to my statement. The ScriptRunner and the Spring JdbcTestUtils.executeSqlScript seem to not return a ResultSet, or I couldn't find the proper implementation. I want to stay away of the BufferReader method since it will require text parsing and a lot of exceptions to handle.
ScriptRunner scriptRunner = new ScriptRunner(connection, true, true);
scriptRunner.runScript(new FileReader("script.sql"));
The runScript method returns void. Same does the Spring implementation:
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 Spring api 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 ResultSet using Spring? I would prefer using Spring since it is actively mantained.