How to run .SQL script using JDBC?

A database script file is a file that contains multiple SQL quries separated from each other. Usually, these files have the .sql extention.

Running .sql script files in Java

You can execute .sql script files in Java using the runScript() method of the ScriptRunner class of Apache iBatis. To this method you need to pass a connection object.

Therefore to run a script file −

• Register the MySQL JDBC Driver using the registerDriver() method of the DriverManager class.
• Create a connection object to establish connection with the MySQL database using the getConnection() method.
• Initialize the ScriptRunner class of the package org.apache.ibatis.jdbc.
• Create a Reader object to read the script file.
• Finally, execute the script using the runScript(reader) method.

Example

Let us create a script file with name sampleScript.sql copy the following contents init. This script creates a table with name cricketers_data in MySQL database an populates it with five records.

CREATE DATABASE exampleDB;
use exampleDB;
CREATE TABLE exampleDB.cricketers_data(
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Date_Of_Birth date,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255)
);
insert into cricketers_data values('Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');
insert into cricketers_data values('Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');
insert into cricketers_data values('Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');
insert into cricketers_data values('Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');
insert into cricketers_data values('Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');
select * from mydatabase.cricketers_data;

Add the following maven dependency (for the jar file mybatis-3.4.1.jar) to your pom.xml file −

<dependency>
   <groupId>org.mybatis</groupId>
   <artifactId>mybatis</artifactId>
   <version>3.4.5</version>
</dependency>

Example

Following JDBC program executes the sampleScript.sql file.

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.apache.ibatis.jdbc.ScriptRunner;
public class RunningScripts {
   public static void main(String args[]) throws Exception {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/talakai_noppi";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Initialize the script runner
      ScriptRunner sr = new ScriptRunner(con);
      //Creating a reader object
      Reader reader = new BufferedReader(new FileReader("E:\sampleScript.sql"));
      //Running the script
      sr.runScript(reader);
   }
}

Output

Connection established......
CREATE DATABASE exampleDB
use exampleDB
CREATE TABLE exampleDB.cricketers_data(
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Date_Of_Birth date,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255)
)
insert into cricketers_data values('Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India')
insert into cricketers_data values('Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica')
insert into cricketers_data values('Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka')
insert into cricketers_data values('Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India')
insert into cricketers_data values('Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India')
select * from mydatabase.cricketers_data
First_Name Last_Name Year_Of_Birth Place_Of_Birth Country
Shikhar Dhawan 1981-12-05 Delhi India
Jonathan Trott 1981-04-22 CapeTown SouthAfrica
Lumara Sangakkara 1977-10-27 Matale Srilanka
Virat Kohli 1988-11-05 Delhi India
Rohit Sharma 1987-04-30 Nagpur India