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 the Author

author image
Dhina Prakash
Open Source Developer, TenthPlanet Technologies.

Comments

Leave a Reply

avatar
newest oldest most voted
Deepak
Guest
Deepak

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

Ramesh Shivakumar
Guest
Ramesh Shivakumar

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

Mohamed Ennahdi El Idrissi
Guest
Mohamed Ennahdi El Idrissi

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

Jason Robinson
Guest
Jason Robinson

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
Guest
Rainer Martinez Fraga

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

mukesh
Guest
mukesh

can i run microsoft sql server script using this code.

naresh parimi
Guest
naresh parimi

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

Mehdi
Guest
Mehdi

realy helpfull, quick and easy to implement
thank you :-)

mkyong
Admin
mkyong

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/

Armen
Guest
Armen

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
Guest
ramesh

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

Ben
Guest
Ben

I am aswell

Jason Robinson
Guest
Jason Robinson

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

Jesús Estrada López
Guest
Jesús Estrada López

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.