In this tutorial, I will show you how to run a MySQL script file using ibatis ScriptRunner class. First, download the ibatis and Mysql JDBC Driver, and add the jar files into your classpath.
Now, run below code. It will execute a script.sql file.
RunSqlScript.java
package com.mkyong;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import com.ibatis.common.jdbc.ScriptRunner;
/**
*
@author Dhinakaran Pragasam
*/
public class RunSqlScript {
/**
* @param args
* the command line arguments
*/
public static void main(String[] args) throws ClassNotFoundException,
SQLException {
String aSQLScriptFilePath = "path/to/sql/script.sql";
// Create MySql Connection
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/database", "username", "password");
Statement stmt = null;
try {
// Initialize object for ScripRunner
ScriptRunner sr = new ScriptRunner(con, false, false);
// Give the input file to Reader
Reader reader = new BufferedReader(
new FileReader(aSQLScriptFilePath));
// Exctute script
sr.runScript(reader);
} catch (Exception e) {
System.err.println("Failed to Execute" + aSQLScriptFilePath
+ " The error is " + e.getMessage());
}
}
}
Note
- sql script should have an semi colen (;) for each end of the statement.
- You sql script does not have any select statement.
When you say that the SQL script should not have any SELECT statements, I’m assuming you are referring to a SELECT statement where the results are being expected to be returned, but that a statement like this one would be perfectly OK to execute inside the script:
INSERT INTO MyTable (col1, col2) SELECT col3, col4 FROM MyOtherTable;
Where the SELECT is not returned to the console.
Hi , It will work with oracle database ?? kindly please answer my question
Si sirve muy bien para tablas y vistas, el problema es que estaba buscando un código que permita crear de manera correcta los triggers y procedures, la cuestión es que si los crea pero sin contenido el problema es al momento de colocar algún contenido en el cuerpo después del BEGIN no funciona.
How can i store the logs of script… my requirement is I have multiple sql files for each run I need to store the logs separately. can you please suggest??
after execution mysql.sql file special charaters like german, chaines are not working
Awesome.
“stmt” variable is not used by the way.
It looks like the ibatis project has been abandoned. Is there a modern way to read in a complicated SQL file and run it in its entirety?
They forked into MyBatis. You can find them now at github
can i run microsoft sql server script using this code.
my project is Maven based. here i am not able to fine maven dependency for ‘ibatis ScriptRunner’
realy helpfull, quick and easy to implement
thank you 🙂
Here is an alternative way to run a MySQL script without using any third party library.
http://coreyhulen.wordpress.com/2010/04/07/run-a-sql-script-for-mysql-using-java/
Hi,
Thenks, but where we can put the file? I am always have file not found exception, I trying run it from JSF managed bean.
Best Regards
yes i am also getting same “The system cannot find the file specified” error. could u please help on this
I am aswell
That source blog is now marked private, so this link is dead.
You can view the archived blog here https://web.archive.org/web/20130314135720/http://coreyhulen.wordpress.com/2010/04/07/run-a-sql-script-for-mysql-using-java/