Main Tutorials

How to run a MySQL Script using Java

java mysql

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

  1. sql script should have an semi colen (;) for each end of the statement.
  2. You sql script does not have any select statement.

References

  1. ibatis ScriptRunner JavaDoc

About Author

author image
Open Source Developer, TenthPlanet Technologies.

Comments

Subscribe
Notify of
17 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Deepak
6 years ago

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??

Mohamed Ennahdi El Idrissi
8 years ago

Awesome.
“stmt” variable is not used by the way.

mkyong
11 years ago

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/

Jason Robinson
8 years ago
Reply to  mkyong

That source blog is now marked private, so this link is dead.

Armen
10 years ago
Reply to  mkyong

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

ramesh
10 years ago
Reply to  Armen

yes i am also getting same “The system cannot find the file specified” error. could u please help on this

Ben
9 years ago
Reply to  ramesh

I am aswell

Mike Sims
1 year ago

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.

Sairam
3 years ago

Hi , It will work with oracle database ?? kindly please answer my question

Jesús Estrada López
5 years ago

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.

Ramesh Shivakumar
7 years ago

after execution mysql.sql file special charaters like german, chaines are not working

Jason Robinson
8 years ago

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?

Rainer Martinez Fraga
8 years ago
Reply to  Jason Robinson

They forked into MyBatis. You can find them now at github

mukesh
9 years ago

can i run microsoft sql server script using this code.

naresh parimi
10 years ago

my project is Maven based. here i am not able to fine maven dependency for ‘ibatis ScriptRunner’

Mehdi
11 years ago

realy helpfull, quick and easy to implement
thank you 🙂