Geek Answers Handbook

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

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

I have a program that runs queries SQLin a database that return ResultSet, which I later process and use the data in my classes. I am currently using JDBCscripting inside a 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 SQL queries outside of a Java program to .sql files, but I want to save all program logic from statements executeQuery. This means that I want the queries to return a ResultSet.

I looked at several methods, for example, using ScriptRunner, using Spring, JdbcTestUtils.executeSqlScriptor reading the .sql file with BufferReader, and then passing the string to my statement. ScriptRunnerand Spring JdbcTestUtils.executeSqlScriptdoesn't seem to return a ResultSet, or I could not find the correct implementation. I want to stay away from the method BufferReader, as this will require text parsing and many exceptions to handle.

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

The method runScriptreturns void. The implementation does the same 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 carefully studied the api Spring, but could not find something similar to what I want. Is there a way to load a script file from a file and then run it so that it returns ResultSetusing Spring? I would prefer to use Springas it is actively supported.

+4
3

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);

jdbcTemplate.query .sql script, . ResultSet extractData ResultSetExtractor mapRow RowMapper. , ResultSet extractData mapRow, /

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;
            }
        });


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

, , , , .

+5

sql.

:

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

:

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

DAO:

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

...

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

, . . , /. Loop over properties:

Enumeration e = props.propertyNames();

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

Read the sql file in the instruction manual using a loop (use as a delimiter). Run each as a PreparedStatement, and you can get a ResultSet after each run.

0
source

More articles:


All Articles