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

Question

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.

Answer 1

I found a way to do it using 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.query to query the database using the .sql script we read. The ResultSet required will be passed as parameter to the extractData of ResultSetExtractor implementation or to the mapRow of the RowMapper implementation. So the processing of the ResultSet will be done in the extractData or mapRow implementation and we will return the Collection/Object we need

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.