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

java, jdbc, spring, sql

Solution

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.

Problem

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.

Original source