How to Execute SQL Script File in Java

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 execute all sql scripts in a Folder using Java?

As @mustaccio said, I tried to execute the file name instead of the contents of the file. So now what I do instead is this:

 public void sqlScripts() {

        try {
            File folder = new File("E:/maProject/sql");
            File[] listOfFiles = folder.listFiles();

            for (File file : listOfFiles) {
                ScriptRunner scriptRunner = new ScriptRunner(con, false, false);

                // Give the input file to Reader
                Reader reader = new BufferedReader(new FileReader(file));
                scriptRunner.runScript(reader);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

Executing a .sql file through Java

For simple scripts I generally use this class from ibatis - ScriptRunner. Alternative you can spawn a new db client process from Java and feed in the script you wan't execute. This will work for all scripts, as simple solutions like ScriptRunner don't work well when the delimiters in the sql files get changed for instance.

Here's an example how to feed the sql as a string to a spawed db client process:

private void runSql(String pSql) {
        String tCommand = "mysql -u " + username + (password != null ? " -p" + password : "") + " " + dbName;
        System.out.println(tCommand);

        try {
            Process tProcess = Runtime.getRuntime().exec(tCommand);
            OutputStream tOutputStream = tProcess.getOutputStream();
            Writer w = new OutputStreamWriter(tOutputStream);
            System.out.println(pSql);
            w.write(pSql);
            w.flush();

            Scanner in = new Scanner(tProcess.getErrorStream());

            String errorMessage = "";

            while (in.hasNext()) {
                errorMessage += in.next() + " ";
            }

            if (errorMessage.length() > 0) {
                System.out.println(errorMessage);
                throw new ClientSqlExecutionException(errorMessage);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

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.

Running SQL files scripts from a Java program

Ibatis provides a ScriptRunner that will help you. Simple code snippets you can refer:

Connection conn=getConnection();//some method to get a Connection
ScriptRunner runner=new ScriptRunner(conn, false, false);
InputStreamReader reader = new InputStreamReader(new FileInputStream("foo.sql"));
runner.runScript(reader);
reader.close();
conn.close();

Run .sql file Using java code

Use ProcessBuilder. Below sample code, I run select query and print the result in console.

public class RunOracleSql {
    public static void main(String[] args) {
        final String fileExtension = ".sql";
        String script_location = "C:/SQLFileLocation";
        try {
            File file = new File("C:/SQLFileLocation");
            File[] listFiles = file.listFiles(new FileFilter() {

                public boolean accept(File f) {
                    if (f.getName().toLowerCase().endsWith(fileExtension))
                        return true;
                    return false;
                }
            });
            for (int i = 0; i < listFiles.length; i++) {
                script_location = "@" + listFiles[i].getAbsolutePath();// ORACLE
                ProcessBuilder processBuilder = new ProcessBuilder("sqlplus",
                        "username/password@database_name", script_location); // ORACLE

                processBuilder.redirectErrorStream(true);
                Process process = processBuilder.start();
                BufferedReader in = new BufferedReader(new InputStreamReader(
                        process.getInputStream()));
                String currentLine = null;
                while ((currentLine = in.readLine()) != null) {
                    System.out.println(" " + currentLine);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}

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!

---

Related Topics