6

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.

Improve this question

3 Answers 3

Reset to default
6

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

The problem of using JdbcTestUtils is that you must package the test jars into your deployed application.
It is now deprecated, you should use: ScriptUtils.readScript()
ScriptUtils.readScript() is also now deprecated. What's the latest solution?
0

I would use property file to store sql queries.

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:

@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);
    }
Improve this answer

1 Comment

Isn't this similar to using a BufferReader to read the file? I will imply doing some parsing on the file. Do you know any implementation using Spring?
0

You can use this using commons-io:

<dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
    <version>2.11.0</version>
</dependency>

import org.apache.commons.io.IOUtils;
import org.springframework.core.io.ClassPathResource;
//...
String sqlQuery = IOUtils.toString((new ClassPathResource("me.sql")).getInputStream(), Charset.defaultCharset());
Improve this answer

1 Comment

import org.apache.commons.io.IOUtils; import org.springframework.core.io.ClassPathResource; <dependency> <groupId>commons-io</groupId> <artifactId>commons-io</artifactId> <version>2.11.0</version> </dependency>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.