How to Execute SQL Script File in Java?
I want to execute an SQL script file in Java without reading the entire file content into a big query and executing it. Is there any other standard way?
12 Answers 12
There is great way of executing SQL scripts from Java without reading them yourself as long as you don’t mind having a dependency on Ant. In my opinion such a dependency is very well justified in your case. Here is sample code, where SQLExec class lives in ant.jar:
private void executeSql(String sqlFilePath) < final class SqlExecuter extends SQLExec < public SqlExecuter() < Project project = new Project(); project.init(); setProject(project); setTaskType("sql"); setTaskName("sql"); >> SqlExecuter executer = new SqlExecuter(); executer.setSrc(new File(sqlFilePath)); executer.setDriver(args.getDriver()); executer.setPassword(args.getPwd()); executer.setUserid(args.getUser()); executer.setUrl(args.getUrl()); executer.execute(); > I tried this for few of my Oracle SQL scripts, it works for insert , create table . But for script having create or replace trigger it fails with java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement
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.
Flyway library is really good for this:
Flyway flyway = new Flyway(); flyway.setDataSource(dbConfig.getUrl(), dbConfig.getUsername(), dbConfig.getPassword()); flyway.setLocations("classpath:db/scripts"); flyway.clean(); flyway.migrate(); This scans the locations for scripts and runs them in order. Scripts can be versioned with V01__name.sql so if just the migrate is called then only those not already run will be run. Uses a table called ‘schema_version’ to keep track of things. But can do other things too, see the docs: flyway.
The clean call isn’t required, but useful to start from a clean DB. Also, be aware of the location (default is «classpath:db/migration»), there is no space after the ‘:’, that one caught me out.
No, you must read the file, split it into separate queries and then execute them individually (or using the batch API of JDBC).
One of the reasons is that every database defines their own way to separate SQL statements (some use ; , others / , some allow both or even to define your own separator).
You cannot do using JDBC as it does not support . Work around would be including iBatis iBATIS is a persistence framework and call the Scriptrunner constructor as shown in iBatis documentation .
Its not good to include a heavy weight persistence framework like ibatis in order to run a simple sql scripts any ways which you can do using command line
Since JDBC doesn’t support this option the best way to solve this question is executing command lines via the Java Program. Bellow is an example to postgresql:
private void executeSqlFile() < try < Runtime rt = Runtime.getRuntime(); String executeSqlCommand = "psql -U (user) -h (domain) -f (script_name) (dbName)"; Process pr = rt.exec(); int exitVal = pr.waitFor(); System.out.println("Exited with error code " + exitVal); >catch (Exception e) < System.out.println(e.toString()); >> The Apache iBatis solution worked like a charm.
The script example I used was exactly the script I was running from MySql workbench.
org.apache.ibatis ibatis-core 3.0 import java.io.BufferedReader; import java.io.File; import java.io.FileReader; import java.io.Reader; import java.sql.Connection; import org.apache.ibatis.jdbc.ScriptRunner; import lombok.extern.slf4j.Slf4j; @Slf4j public class SqlScriptExecutor < public static void executeSqlScript(File file, Connection conn) throws Exception < Reader reader = new BufferedReader(new FileReader(file)); log.info("Running script from file: " + file.getCanonicalPath()); ScriptRunner sr = new ScriptRunner(conn); sr.setAutoCommit(true); sr.setStopOnError(true); sr.runScript(reader); log.info("Done."); >> For my simple project the user should be able to select SQL-files which get executed. As I was not happy with the other answers and I am using Flyway anyway I took a closer look at the Flyway code. DefaultSqlScriptExecutor is doing the actual execution, so I tried to figure out how to create an instance of DefaultSqlScriptExecutor .
Basically the following snippet loads a String splits it into the single statements and executes one by one. Flyway also provides other LoadableResource s than StringResource e.g. FileSystemResource . But I have not taken a closer look at them.
As DefaultSqlScriptExecutor and the other classes are not officially documented by Flyway use the code-snippet with care.
public static void execSqlQueries(String sqlQueries, Configuration flyWayConf) throws SQLException < // create dependencies FlyWay needs to execute the SQL queries JdbcConnectionFactory jdbcConnectionFactory = new JdbcConnectionFactory(flyWayConf.getDataSource(), flyWayConf.getConnectRetries(), null); DatabaseType databaseType = jdbcConnectionFactory.getDatabaseType(); ParsingContext parsingContext = new ParsingContext(); SqlScriptFactory sqlScriptFactory = databaseType.createSqlScriptFactory(flyWayConf, parsingContext); Connection conn = flyWayConf.getDataSource().getConnection(); JdbcTemplate jdbcTemp = new JdbcTemplate(conn); ResourceProvider resProv = flyWayConf.getResourceProvider(); DefaultSqlScriptExecutor scriptExec = new DefaultSqlScriptExecutor(jdbcTemp, null, false, false, false, null); // Prepare and execute the actual queries StringResource sqlRes = new StringResource(sqlQueries); SqlScript sqlScript = sqlScriptFactory.createSqlScript(sqlRes, true, resProv); scriptExec.execute(sqlScript); >How to execute .sql script file using JDBC [duplicate]
I have an SQL script file which contains 40-50 SQL statements. Is it possible to run this script file using JDBC?
7 Answers 7
Pasted below for posterity:
/* * Slightly modified version of the com.ibatis.common.jdbc.ScriptRunner class * from the iBATIS Apache project. Only removed dependency on Resource class * and a constructor */ /* * Copyright 2004 Clinton Begin * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ import java.io.IOException; import java.io.LineNumberReader; import java.io.PrintWriter; import java.io.Reader; import java.sql.*; /** * Tool to run database scripts */ public class ScriptRunner < private static final String DEFAULT_DELIMITER = ";"; private Connection connection; private boolean stopOnError; private boolean autoCommit; private PrintWriter logWriter = new PrintWriter(System.out); private PrintWriter errorLogWriter = new PrintWriter(System.err); private String delimiter = DEFAULT_DELIMITER; private boolean fullLineDelimiter = false; /** * Default constructor */ public ScriptRunner(Connection connection, boolean autoCommit, boolean stopOnError) < this.connection = connection; this.autoCommit = autoCommit; this.stopOnError = stopOnError; >public void setDelimiter(String delimiter, boolean fullLineDelimiter) < this.delimiter = delimiter; this.fullLineDelimiter = fullLineDelimiter; >/** * Setter for logWriter property * * @param logWriter * - the new value of the logWriter property */ public void setLogWriter(PrintWriter logWriter) < this.logWriter = logWriter; >/** * Setter for errorLogWriter property * * @param errorLogWriter * - the new value of the errorLogWriter property */ public void setErrorLogWriter(PrintWriter errorLogWriter) < this.errorLogWriter = errorLogWriter; >/** * Runs an SQL script (read in using the Reader parameter) * * @param reader * - the source of the script */ public void runScript(Reader reader) throws IOException, SQLException < try < boolean originalAutoCommit = connection.getAutoCommit(); try < if (originalAutoCommit != this.autoCommit) < connection.setAutoCommit(this.autoCommit); >runScript(connection, reader); > finally < connection.setAutoCommit(originalAutoCommit); >> catch (IOException e) < throw e; >catch (SQLException e) < throw e; >catch (Exception e) < throw new RuntimeException("Error running script. Cause: " + e, e); >> /** * Runs an SQL script (read in using the Reader parameter) using the * connection passed in * * @param conn * - the connection to use for the script * @param reader * - the source of the script * @throws SQLException * if any SQL errors occur * @throws IOException * if there is an error reading from the Reader */ private void runScript(Connection conn, Reader reader) throws IOException, SQLException < StringBuffer command = null; try < LineNumberReader lineReader = new LineNumberReader(reader); String line = null; while ((line = lineReader.readLine()) != null) < if (command == null) < command = new StringBuffer(); >String trimmedLine = line.trim(); if (trimmedLine.startsWith("--")) < println(trimmedLine); >else if (trimmedLine.length() < 1 || trimmedLine.startsWith("//")) < // Do nothing >else if (trimmedLine.length() < 1 || trimmedLine.startsWith("--")) < // Do nothing >else if (!fullLineDelimiter && trimmedLine.endsWith(getDelimiter()) || fullLineDelimiter && trimmedLine.equals(getDelimiter())) < command.append(line.substring(0, line .lastIndexOf(getDelimiter()))); command.append(" "); Statement statement = conn.createStatement(); println(command); boolean hasResults = false; if (stopOnError) < hasResults = statement.execute(command.toString()); >else < try < statement.execute(command.toString()); >catch (SQLException e) < e.fillInStackTrace(); printlnError("Error executing: " + command); printlnError(e); >> if (autoCommit && !conn.getAutoCommit()) < conn.commit(); >ResultSet rs = statement.getResultSet(); if (hasResults && rs != null) < ResultSetMetaData md = rs.getMetaData(); int cols = md.getColumnCount(); for (int i = 0; i < cols; i++) < String name = md.getColumnLabel(i); print(name + "\t"); >println(""); while (rs.next()) < for (int i = 0; i < cols; i++) < String value = rs.getString(i); print(value + "\t"); >println(""); > > command = null; try < statement.close(); >catch (Exception e) < // Ignore to workaround a bug in Jakarta DBCP >Thread.yield(); > else < command.append(line); command.append(" "); >> if (!autoCommit) < conn.commit(); >> catch (SQLException e) < e.fillInStackTrace(); printlnError("Error executing: " + command); printlnError(e); throw e; >catch (IOException e) < e.fillInStackTrace(); printlnError("Error executing: " + command); printlnError(e); throw e; >finally < conn.rollback(); flush(); >> private String getDelimiter() < return delimiter; >private void print(Object o) < if (logWriter != null) < System.out.print(o); >> private void println(Object o) < if (logWriter != null) < logWriter.println(o); >> private void printlnError(Object o) < if (errorLogWriter != null) < errorLogWriter.println(o); >> private void flush() < if (logWriter != null) < logWriter.flush(); >if (errorLogWriter != null) < errorLogWriter.flush(); >> >