How to execute a Mysql Script from java in linux?

Question

How to execute a Mysql Script from java in linux?

Navigation

#1 by (0 votes)
#2 by (-1 votes)

4

I'm trying to execute a script .sql generated with MySQL from a java application, I have read and researched different ways to do it and I have found several options (none of which had worked for me) and I found the following block of code :

private void executeScript (String host, String dbuser,
                                        String dbpassword, String scriptpath) {

        try {
            String[] cmd = new String[]{"mysql",
                    " --host="+host,
                    " --user=" + dbuser,
                    " --password=" + dbpassword,
                    " -e ",
                    "\"source " + scriptpath + "\""

            };
            //Aqui intente poner el commando directamente
            //String cmd = "mysql --host=192.168.0.13 --user=root_remote --password=root -e \'source "+scriptpath+"\'";
            System.err.println(cmd[0]+cmd[1]+cmd[2]+cmd[3]+cmd[4]+cmd[5]);
            Process proc = Runtime.getRuntime().exec(cmd);

            //Leemos los errores de la consola
                InputStream inputstream = proc.getErrorStream();
                InputStreamReader inputstreamreader = new InputStreamReader(inputstream);
                BufferedReader bufferedreader = new BufferedReader(inputstreamreader);

                String line;
                while ((line = bufferedreader.readLine()) != null) {
                    System.out.println(line);
                }

                try {
                    if (proc.waitFor() != 0) {
                        System.err.println("exit value = " +
                                proc.exitValue());
                    }
                }
                catch (InterruptedException e) {
                    System.err.println(e);
                }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

The block above worked correctly in Windows, but at the time of testing in an installation of Ubuntu I get an error saying that the database is unknown, but nevertheless when executing the command directly in the terminal it works correctly, as you can see in the following image:

I tried to run the command directly in Runtime.exec (), as you can see in the example and the same thing happens, I have also tried to delete the slash "\" before source and at the end and it does not work either, I repeat in windows it works correctly, the problem is in linux, I hope you can help me.

mysql java linux script terminal

asked by Aarón Zúñiga 14.01.2018 в 07:21

source

2 answers

-1

To be able to execute sentences in a database from Java you need to use the JDBC API (Java Database Connectivity) and in your particular case use the Driver for MySql (which can be downloaded from link ).

Added the Driver in your project, you can try the following:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class Main {

  public static void main(String[] args) throws SQLException {
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/DATABASE", "USER", "PASSWORD");
    Statement stmt = conn.createStatement();
    String query = "select * from TABLA;";
    ResultSet rs = stmt.executeQuery(query);
    ResultSetMetaData rsmd = rs.getMetaData();

    //ver datos en consola
    int columnsNumber = rsmd.getColumnCount();
    while (rs.next()) {
      for (int i = 1; i <= columnsNumber; i++) {
        if (i > 1) {
          System.out.print(",  ");
        }
        String columnValue = rs.getString(i);
        System.out.print(columnValue + " " + rsmd.getColumnName(i));
      }
      System.out.println("");
    }
  }
}

This is just an example, nowadays there are better options to connect to a database from Java, but as I mentioned, it is an example so that you can see the difference between the Wrapper of a console command from Java with the use of the API.

If you want to run from the terminal directly, I suggest creating a Bash script that will do what you need.

Greetings

answered by 06.02.2018 в 15:20

Local Database in Java Doubts about J2EE [closed]

score 0 · Accepted Answer

I commented that at last I was able to solve the problem, I leave the complete solution (Win, Linux) below.

For Linux:

public int runScriptLinux(String host, String dbuser, String dbpassword, String scriptpath) {

    int status = 1;

    try {

           Process proc = Runtime.getRuntime().exec( new String[]{ "bash", "-c", "mysql --host="+host+" --user="+dbuser+" --password="+dbpassword+" -e \"source "+scriptpath+"\""});

          InputStream inputstream = proc.getInputStream();
          InputStreamReader inputstreamreader = new InputStreamReader(inputstream);
          BufferedReader bufferedreader = new BufferedReader(inputstreamreader);

          String line;
          while ((line = bufferedreader.readLine()) != null) {
                  System.out.println(line);
          }

          try {
               if (proc.waitFor() == 0) {
                   status = 0;
                  }
                  else {
                      status = 1;
                  }
              }
              catch (InterruptedException e) {
                     System.err.println(e);
              }

       return status;

    } catch (Exception e) {
      e.printStackTrace();
       return status;
    }
}

For Windows:

public int runScriptWin(String host, String dbuser, String dbpassword, String scriptpath) {

    int status = 1;

    try {

           String pathMysql = "path de MySqlServer";

                    String[] cmd = new String[]{pathMysql+"mysql",
                            "--host="+host,
                            "--user=" + dbuser,
                            "--password=" + dbpassword,
                            "-e",
                            "\"source " + scriptpath + "\""

                    };

          InputStream inputstream = proc.getInputStream();
          InputStreamReader inputstreamreader = new InputStreamReader(inputstream);
          BufferedReader bufferedreader = new BufferedReader(inputstreamreader);

          String line;
          while ((line = bufferedreader.readLine()) != null) {
                  System.out.println(line);
          }

          try {
               if (proc.waitFor() == 0) {
                   status = 0;
                  }
                  else {
                      status = 1;
                  }
              }
              catch (InterruptedException e) {
                     System.err.println(e);
              }

       return status;

    } catch (Exception e) {
      e.printStackTrace();
       return status;
    }
}