Running a .SQL Script Using MySQL with Jdbc

Running a .sql script using MySQL with JDBC

Ok. You can use this class here (posted on pastebin because of file length) in your project. But remember to keep the apache license info.

JDBC ScriptRunner

It's ripoff of the iBatis ScriptRunner with dependencies removed.

You can use it like this

Connection con = ....
ScriptRunner runner = new ScriptRunner(con, [booleanAutoCommit], [booleanStopOnerror]);
runner.runScript(new BufferedReader(new FileReader("test.sql")));

That's it!

How to Execute SQL Script File in Java?

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.

Running SQL files scripts from a Java program

Ibatis provides a ScriptRunner that will help you. Simple code snippets you can refer:

Connection conn=getConnection();//some method to get a Connection
ScriptRunner runner=new ScriptRunner(conn, false, false);
InputStreamReader reader = new InputStreamReader(new FileInputStream("foo.sql"));
runner.runScript(reader);
reader.close();
conn.close();

Execute .sql files using JDBC

I guess Spring Framework ScriptUtils might do this trick for you.

Please check

http://docs.spring.io/autorepo/docs/spring/4.0.9.RELEASE/javadoc-api/org/springframework/jdbc/datasource/init/ScriptUtils.html

My plan was to keep the schema and data insertion in version control
as .sql files and execute them before the test to get a populated
database that i can use for testing.

For this purpose there is such library as DbUnit http://dbunit.sourceforge.net/

Which i personally find a little bit tricky to use without a propper wrapper.

Some of those wrappers:

Spring Test DbUnit https://springtestdbunit.github.io/spring-test-dbunit/

Unitils DbUnit http://www.unitils.org/tutorial-database.html

How to load a SQL file (stored in the source folder of my Java project) into MySQL from a Java Application?

I found that it is probably impossible to do it yet without adding additional tools and libraries.

We can do it by splitting the SQL file into smaller SQL files, each containing just one SQL command and then initiating a loop to execute all those files at once. I'd tried it and it works. It certainly does.

The following is the code I used to do that :

import javax.swing.* ;
import java.sql.* ;
import java.io.* ;

public class LoadSQLFile {
    public static void main(string args[ ]) {
        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {
                try {
                    String password = JOptionPane.showInputDialog(null, "We need your MySQL Password to run the application. Please enter it here.", " MySQL Password ?", JOptionPane.QUESTION_MESSAGE) ;
                    Class.forName("java.sql.Driver") ;
                    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/MyDB", "root", password) ;
                    Statement stmt = conn.createStatement() ;
                    int i = 0 ;
                    for(i=1;i<=16;i++) {
                        FileReader fr = new FileReader("src//sql_files//BCK"+i+".sql") ;
                        BufferedReader br = new BufferedReader(fr) ;
                        stmt.execute(br.readLine()) ;
                    }
                    stmt.close();
                    conn.close();
                    JOptionPane.showMessageDialog(null, " Records Successfully Inserted into database !", "Success !", 1) ;
                } catch(Exception e) {
                    JOptionPane.showMessageDialog(null, e, "ERROR", JOptionPane.ERROR_MESSAGE) ;
                }
            }
        });
    }
}

I was to execute SQL files stored in my project's source folder inside a package named "sql_files". The names of the files are - BCK1.sql, BCK2.sql, ...., BCK16.sql. Each file contained just one SQL file in just first line. It worked just fine for me.

I used the MySQL JDBC Driver for this.

Is there any way to run 'SET foreign_key_checks = 0 ' using JDBC?

You are trying to execute two statements, an INSERT followed by a SET, separated by a semicolon, in a single call to prepareStatement().

https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html

SQL syntax for prepared statements does not support multi-statements (that is, multiple statements within a single string separated by ; characters).

You must execute the SET statement in a separate statement.

---

Related Topics