4

I want to load an SQL file (which is stored in the source folder of my NetBeans Java Project) into MySQL from my Java Application during runtime. How can I do this?

The SQL file is 15.15 MB in size and I am wondering whether I can copy into a String or not? Or if I anyhow manage to copy it to a String (though it may throw out of memory error), how to execute multiple commands in one go (because the SQL file contains many commands).

I want to do it without using any additional tools. Just using pre-existing libraries and classes in java (that are bundled with JDK).

v_ag
  • 278
  • 1
  • 5
  • 17
  • 2
    If the file is made up of separate smaller SQL commands, you should read each command in at a time. Theoretically you can load a 15Mb String, but you'll probably run out of memory. – Steve Smith Jan 20 '17 at 09:20
  • You could write a background service to use the `mysqlimport` command to load the file in one go, that will do the file splitting and running for you. – halfer Jan 22 '17 at 11:43
  • @halfer Thanks for the solution but I don't know how to do so. I am just a fresher to java programming. I'll be happy if you tell me how to do it. – v_ag Jan 22 '17 at 11:51
  • Maybe you can use the flyway lib. It is used to execute flyway migrations via java or sql files. It might be a bit too much for your goal but I suggets to have a look at https://flywaydb.org. – Anton Jan 22 '17 at 11:56
  • @Anton Thanks. I will look up the website... – v_ag Jan 22 '17 at 11:57
  • @VaibhavAgrawal you are welcome :-) – Anton Jan 22 '17 at 12:00
  • A similar question [here](http://stackoverflow.com/q/33336063/2144390) has an answer that uses [SqlTool](http://hsqldb.org/doc/2.0/util-guide/sqltool-chapt.html) to accomplish what you describe. – Gord Thompson Jan 24 '17 at 01:06
  • @GordThompson Thanks. I found it working. – v_ag Jan 24 '17 at 01:15
  • Good to hear. Perhaps you might consider posting an answer to let others know what ultimately worked for you. – Gord Thompson Jan 24 '17 at 01:18
  • @GordThompson Hey, Sorry but the answer you have referenced, just works with HSQL and not with MySQL. SQLTool too works with just HSQL. Not Working!!! – v_ag Jan 24 '17 at 12:25
  • *"SQLTool too works with just HSQL"* - Not true. If you look at the sample configuration file ("RC file") [here](http://hsqldb.org/doc/2.0/util-guide/sqltool-chapt.html#sqltool_auth-sect) you will see examples for Oracle, PostgreSQL, MySQL, SQL Server, and Derby. – Gord Thompson Jan 24 '17 at 12:59

4 Answers4

2

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.

v_ag
  • 278
  • 1
  • 5
  • 17
  • is this okay if the file of SQL is quite big? – gumuruh Apr 02 '19 at 10:58
  • Yeah, because it uses readLine(), it'll read only one line at a time. So, even if the SQL file is big, there won't be any problems. – v_ag Apr 03 '19 at 08:10
  • i'm afraid if the content of the blob or something related to it will have non-single-line.... so it will ends up failed. – gumuruh Apr 22 '19 at 09:03
0

I think ScriptUtils.executeSqlScript can help you.

Roy
  • 65
  • 1
  • 7
  • No. But not Sure. I think it'll not work because its connection can't be loaded for SQL execution to MySQL contained in the source folders of the Project. – v_ag Jan 26 '17 at 05:15
0

Use the following code:

public static String readFileAsString(Class clazz, String filePath) throws IOException {
    InputStream inputStream = clazz.getClassLoader().getResourceAsStream(filePath);
    if (inputStream == null) {
        throw new IllegalArgumentException("File not found: " + filePath);
    }
    return IOUtils.toString(inputStream);
}

public static final Path SQL_QUERY_FILE_PATH = Paths.get("SqlFolder", "productQuery.sql");

String sqlQuery = FileResourceUtil.readFileAsString(getClass(), SQL_QUERY_FILE_PATH.toString());
mattsmith5
  • 540
  • 4
  • 29
  • 67
0

Here is example, This will print 3 statements.

  public static void main(String[] args) {
        var sql = "select;insert(';')\n sdfsd;"
                + "update";

        List<String> stmt = new ArrayList<>();

        org.springframework.jdbc.datasource.init.ScriptUtils.splitSqlScript(sql, ";", stmt);

        stmt.forEach(e -> log.info(e));
    }
Ranjeet Rana
  • 65
  • 5