How to Execute .SQL Script File Using Jdbc

Running a .sql script using MySQL with JDBC

Ok. You can use this class here (posted on pastebin because of file length) in your project. But remember to keep the apache license info.

JDBC ScriptRunner

It's ripoff of the iBatis ScriptRunner with dependencies removed.

You can use it like this

Connection con = ....
ScriptRunner runner = new ScriptRunner(con, [booleanAutoCommit], [booleanStopOnerror]);
runner.runScript(new BufferedReader(new FileReader("test.sql")));

That's it!

Execute .sql files using JDBC

I guess Spring Framework ScriptUtils might do this trick for you.

Please check

http://docs.spring.io/autorepo/docs/spring/4.0.9.RELEASE/javadoc-api/org/springframework/jdbc/datasource/init/ScriptUtils.html

My plan was to keep the schema and data insertion in version control
as .sql files and execute them before the test to get a populated
database that i can use for testing.

For this purpose there is such library as DbUnit http://dbunit.sourceforge.net/

Which i personally find a little bit tricky to use without a propper wrapper.

Some of those wrappers:

Spring Test DbUnit https://springtestdbunit.github.io/spring-test-dbunit/

Unitils DbUnit http://www.unitils.org/tutorial-database.html

Execute SQL file from Spring JDBC Template

Maybe Spring's ScriptUtils will be useful in your case. Especially executeSqlScript methods.

Note that DEFAULT_STATEMENT_SEPARATOR has a default value of ';' (see Constant Field Values)

How to Execute SQL Script File in Java?

There is no portable way of doing that. You can execute a native client as an external program to do that though:

import java.io.*;
public class CmdExec {

  public static void main(String argv[]) {
    try {
      String line;
      Process p = Runtime.getRuntime().exec
        ("psql -U username -d dbname -h serverhost -f scripfile.sql");
      BufferedReader input =
        new BufferedReader
          (new InputStreamReader(p.getInputStream()));
      while ((line = input.readLine()) != null) {
        System.out.println(line);
      }
      input.close();
    }
    catch (Exception err) {
      err.printStackTrace();
    }
  }
}

• Code sample was extracted from here and modified to answer question assuming that the user wants to execute a PostgreSQL script file.

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

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.

How to execute sql script file in java in Unix

You can do that using JDBC and PreparedStatement, see these tutorials:

• http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html
• http://www.commandprompt.com/ppbook/x20921

Here are some examples:

• http://www.exampledepot.com/egs/java.sql/InsertPs.html
• http://www.roseindia.net/jdbc/jdbc-mysql/TwicePreparedStatement.shtml
• http://www.roseindia.net/jdbc/prepared-statement-example.shtml
• http://www.heimetli.ch/jdbc/JDBCPreparedStatement.html

If both your Java application and the SQL are located on the same server, then you just need to load it and execute as given above. See examples here:

• http://www.coderanch.com/t/306966/JDBC/java/Execute-sql-file-java
• How to execute .sql script file using JDBC
• Running a .sql script using MySQL with JDBC

If it's just running the SQL (i.e. the whole purpose of your application is to run that SQL and do nothing else), you may want to look at the alternative solutions such as using Ant for the task, see this:

• http://ant.apache.org/manual/Tasks/sql.html

or using whatever is your database command line utility, see some examples here:

• How to execute sql-script file in java?

This very much depends on your circumstances, so you would have to see what is important in your case to decide which way to go.

---

Related Topics